Pandas and SQL side by side

SQL is a must have skill if you are working with data. Pandas is as essential for data analysis in the python. If you're someone who knows pandas and want to learn SQL or you know SQL and want to learn pandas, this post will introduce you to some basic skills.

To work on SQL you need access to a database hosted locally or on the cloud. Some popular databases you can work with are the following:

• MySQL
• MS SQL Server
• Oracle
• PostgreSQL
• Teradata
• BigQuery
• Redshift

There are many more but I've tried to list down the popular ones.
To show side by side examples, however, I am not using any database. I have using a SQL engine called pandassql. So to run these examples, you will have to read some data into pandas dataframe and then use pandasql to run SQL queries.

So the steps are as follows:

1. !pip install -U pandassql
2. import pandas as pd
3. from pandassql import sqldf
4. data = pd.read_csv()
5. Run these examples

SELECTing Data

Once you import the data, select 5 rows from the data just to inspects. Below is an example of how to select top 5 rows in pandas:

data.head()

You can achieve the same result in SQL:

sqldf("SELECT * FROM data LIMIT 5")

Both pandas and SQL allow you to select specific columns from the dataframe(pandas) or table(SQL).

data[['Name', 'Age', 'Team', 'Year']].head()

sqldf('SELECT Name, Age, Team, Year FROM data LIMIT 5')

Filtering with constraints

iloc property of a pandas dataframe allows you to select a row by its index as shown below. Here we are particulary selecting the 6th row by applying the filter on index.

data.iloc[6]

ID                                       5
Name              Christine Jacoba Aaftink
Sex                                      F
Age                                   25.0
Height                               185.0
Weight                                82.0
Team                           Netherlands
NOC                                    NED
Games                          1992 Winter
Year                                  1992
Season                              Winter
City                           Albertville
Sport                        Speed Skating
Event     Speed Skating Women's 500 metres
Medal                                  NaN
Name: 6, dtype: object

In SQL most databases provide a rowid index to select a specific row from the table.

sqldf("SELECT * FROM data WHERE rowid=6")

You can filter on a condition applied on column or multiple columns. In case of multiple columns, the conditions are combined with boolen expressions such as AND, OR etc.

Here is how to count records which satisfy the condition "Age of the athelete is greater than 30".

data[data['Age']>30]['ID'].count()

42107

sqldf('SELECT COUNT(*) FROM data WHERE Age > 30')

Below is an example of multiple conditions. We are selecting all records of Hockey for team India.

data[(data['Team'] == 'India') & (data['Sport']=='Hockey')]

345 rows × 15 columns

sqldf('SELECT * FROM data WHERE Team="India" AND Sport="Hockey"')

345 rows × 15 columns

SORTING

Sorting is another common operation required during data analysis. You can sort on one or more columns and in ascending (low to high) or descending (high to low) order.

data.sort_values(by='Age', ascending=False).head()

sqldf('SELECT * from data ORDER BY Age DESC LIMIT 5')

Joining

Joining one dataset with another is a common operation while working with data. This is a simple aspect of SQL which stumps more people than it should.

I reckon, it is important to work with a simple example using few rows of data to get this right. That is what I am going to do using synthetic tables employee and assets with dummy data.

Please notice the column we are joining on, usually termed as key. If the key has duplicate values, the records multiply.

Inner Join

The simplest type of join is the inner join.
This operation returns the common records from the left table (employee) and the right table (asset).

Pandas uses merge method to join to tables.

employee.merge(asset, on='EmployeeID')

\

sqldf('SELECT * FROM employee E JOIN asset A ON E.EmployeeID = A.EmployeeID')

Left Outer Join

In Left Outer Join, you get all records from the left table, and only matching records from the right table.
Note the NaN and None values resulting in rows for which key doesn't match.

employee.merge(asset, on='EmployeeID', how='left')

sqldf('SELECT E.*, A.* FROM employee E LEFT JOIN asset A ON E.EmployeeID = A.EmployeeID')

Right Outer Join

Right join has the opposite effect of the left join.

employee.merge(asset, on='EmployeeID', how='right')

In fact pandassql doesn't support Right Join but you can achieve the same result by calling asset as the left table and employee as the right table.

sqldf('SELECT E.*, A.* FROM asset A LEFT JOIN employee E ON A.EmployeeID = E.EmployeeID')

Another type of join pandasql doesn't support is the Full outer join, which returns all matching as well as not matching records from both the tables.

You need not worry about these missing type of joins in pandassql, all database that I have worked with support Right Join and Full Outer Join. With some trickery you can even achieve these in pandassql but it's not required for this conversation.

Queries with aggregates

Aggregates are another SQL technique where you group your records based on one or more columns and perform some aggregate calculation on another set of columns such as MIN, MAX, SUM, COUNT, AVG, and so on.

Here is an example of count where we count Medals by team, sort them in descending order and show top % results.

data.groupby('Team', as_index=False)['Medal'].count().sort_values('Medal', ascending=False).head()

sqldf('SELECT Team, COUNT(Medal) FROM data GROUP BY Team ORDER BY COUNT(Medal) DESC LIMIT 5')

Here is another example where we filter just one sport swimming and do the earlier calculation.

data[data['Sport']=='Swimming'].groupby('Team', as_index=False)['Medal'].count().sort_values('Medal', ascending=False).head()

sqldf('SELECT Team, COUNT(Medal) FROM data Where Sport = "Swimming" GROUP BY Team ORDER BY COUNT(Medal) DESC LIMIT 5')

There is a lot more you can do with SQL, like filtering on aggregated data usin HAVING clause. Partition data by row, rank or dense rank. Use analytical functions to compute values across rows or over a window.

There is even more that you can do with pandas. Here I am listing some resources if you're ready for a deep dive.

Pandas Resources

• Python for data analysis by Wes McKinney

SQL Resources

• Kaggle's Intro to SQL
• SQL Bolt
• SQL for Data Scientists by Renee Teate