Pandas and SQL similarity 2

This article is the next in the series about the topic of Pandas and SQL similarity which we discussed earlier

Pandas and SQL similarity (2)

Check the dtypes

Pandas –

To check data types of the data frame, type ‘df.dtypes’ command

SQL –

type sp_help table_name in the query editor. You may be wondering what is ‘nvarchar’

The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar.

You can visit for more information on varchar and nvarchar.

Check the data

Pandas –

Once you load the data in to the pandas you can view the data by running the ‘df.head()’ command. This command will give you output of first 5 rows and all columns by default. This output is not in the order.

You can specify the number of rows you want to see e.g. 10

To view last rows in the data frame, type ‘df.tail()’ command

We can sort the data frame by column name in the ascending order

by descending order

SQL –

In SQL we have to use ‘ORDER BY’ Column to view the data.

Check the NULL Value

Every data set will have some kind of NULL or missing values either in one column or multiple column. Command to find out NULL Value is

Pandas –

df.isnull().sum()

The ‘Product Base Margin’ column has 72 missing values.

SQL –

In SQL to get NULL Value we have to run below query for each column.

SELECT * from table where columnname IS NULL

Select the columns

What if we want to see the specific columns, how do we select them?

Pandas –

Note – Pandas will not display all rows in the output by default, to see all rows please run the command –> ‘pd.set_option(‘display.max_rows’, None)’

SQL –

select [Row ID],[Order Priority] from table name.

Filter the data

Every data scientist or data analyst have to filter the data to get some insight. How do we do that in both pandas and SQL?

Pandas –

Lets say, we need details of all the orders shipped through ‘Regular Air’ only

and sent to California state.

SQL –

SELECT * from table where [Ship Mode] = ‘Regular Air’

Hope you like the article about pandas and SQL command similarity part 2, Stay tuned for next update.