Pandas and SQL side by side
Tags: pandas, sql, python
Published: 2022-01-13 05:08:53

---
title: Pandas and SQL side by side
published: true
description: Learn pandas from sql or vice-versa
tags: pandas, SQL, python
cover_image: ![Databases](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/czmb4y4uw1nerhblxi8s.jpeg)
---

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](https://www.mysql.com/)
- [MS SQL Server](https://www.microsoft.com/en-us/sql-server/sql-server-downloads)
- [Oracle](https://www.oracle.com/database/)
- [PostgreSQL](https://www.postgresql.org/)
- [Teradata](https://www.teradata.com/Products/Software/Database)
- [BigQuery](https://cloud.google.com/bigquery/)
- [Redshift](https://aws.amazon.com/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](https://github.com/yhat/pandasql/). So to run these examples, you will have to read some [data](https://www.kaggle.com/heesoo37/120-years-of-olympic-history-athletes-and-results) 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(<path-to-athlete_events.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:


```python
data.head()
```





</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>ID</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>Height</th>
      <th>Weight</th>
      <th>Team</th>
      <th>NOC</th>
      <th>Games</th>
      <th>Year</th>
      <th>Season</th>
      <th>City</th>
      <th>Sport</th>
      <th>Event</th>
      <th>Medal</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>1</td>
      <td>A Dijiang</td>
      <td>M</td>
      <td>24.0</td>
      <td>180.0</td>
      <td>80.0</td>
      <td>China</td>
      <td>CHN</td>
      <td>1992 Summer</td>
      <td>1992</td>
      <td>Summer</td>
      <td>Barcelona</td>
      <td>Basketball</td>
      <td>Basketball Men's Basketball</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>1</th>
      <td>2</td>
      <td>A Lamusi</td>
      <td>M</td>
      <td>23.0</td>
      <td>170.0</td>
      <td>60.0</td>
      <td>China</td>
      <td>CHN</td>
      <td>2012 Summer</td>
      <td>2012</td>
      <td>Summer</td>
      <td>London</td>
      <td>Judo</td>
      <td>Judo Men's Extra-Lightweight</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>2</th>
      <td>3</td>
      <td>Gunnar Nielsen Aaby</td>
      <td>M</td>
      <td>24.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>Denmark</td>
      <td>DEN</td>
      <td>1920 Summer</td>
      <td>1920</td>
      <td>Summer</td>
      <td>Antwerpen</td>
      <td>Football</td>
      <td>Football Men's Football</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>3</th>
      <td>4</td>
      <td>Edgar Lindenau Aabye</td>
      <td>M</td>
      <td>34.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>Denmark/Sweden</td>
      <td>DEN</td>
      <td>1900 Summer</td>
      <td>1900</td>
      <td>Summer</td>
      <td>Paris</td>
      <td>Tug-Of-War</td>
      <td>Tug-Of-War Men's Tug-Of-War</td>
      <td>Gold</td>
    </tr>
    <tr>
      <th>4</th>
      <td>5</td>
      <td>Christine Jacoba Aaftink</td>
      <td>F</td>
      <td>21.0</td>
      <td>185.0</td>
      <td>82.0</td>
      <td>Netherlands</td>
      <td>NED</td>
      <td>1988 Winter</td>
      <td>1988</td>
      <td>Winter</td>
      <td>Calgary</td>
      <td>Speed Skating</td>
      <td>Speed Skating Women's 500 metres</td>
      <td>NaN</td>
    </tr>
  </tbody>
</table>


You can achieve the same result in SQL:


```python
sqldf("SELECT * FROM data LIMIT 5")
```





</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>ID</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>Height</th>
      <th>Weight</th>
      <th>Team</th>
      <th>NOC</th>
      <th>Games</th>
      <th>Year</th>
      <th>Season</th>
      <th>City</th>
      <th>Sport</th>
      <th>Event</th>
      <th>Medal</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>1</td>
      <td>A Dijiang</td>
      <td>M</td>
      <td>24.0</td>
      <td>180.0</td>
      <td>80.0</td>
      <td>China</td>
      <td>CHN</td>
      <td>1992 Summer</td>
      <td>1992</td>
      <td>Summer</td>
      <td>Barcelona</td>
      <td>Basketball</td>
      <td>Basketball Men's Basketball</td>
      <td>None</td>
    </tr>
    <tr>
      <th>1</th>
      <td>2</td>
      <td>A Lamusi</td>
      <td>M</td>
      <td>23.0</td>
      <td>170.0</td>
      <td>60.0</td>
      <td>China</td>
      <td>CHN</td>
      <td>2012 Summer</td>
      <td>2012</td>
      <td>Summer</td>
      <td>London</td>
      <td>Judo</td>
      <td>Judo Men's Extra-Lightweight</td>
      <td>None</td>
    </tr>
    <tr>
      <th>2</th>
      <td>3</td>
      <td>Gunnar Nielsen Aaby</td>
      <td>M</td>
      <td>24.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>Denmark</td>
      <td>DEN</td>
      <td>1920 Summer</td>
      <td>1920</td>
      <td>Summer</td>
      <td>Antwerpen</td>
      <td>Football</td>
      <td>Football Men's Football</td>
      <td>None</td>
    </tr>
    <tr>
      <th>3</th>
      <td>4</td>
      <td>Edgar Lindenau Aabye</td>
      <td>M</td>
      <td>34.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>Denmark/Sweden</td>
      <td>DEN</td>
      <td>1900 Summer</td>
      <td>1900</td>
      <td>Summer</td>
      <td>Paris</td>
      <td>Tug-Of-War</td>
      <td>Tug-Of-War Men's Tug-Of-War</td>
      <td>Gold</td>
    </tr>
    <tr>
      <th>4</th>
      <td>5</td>
      <td>Christine Jacoba Aaftink</td>
      <td>F</td>
      <td>21.0</td>
      <td>185.0</td>
      <td>82.0</td>
      <td>Netherlands</td>
      <td>NED</td>
      <td>1988 Winter</td>
      <td>1988</td>
      <td>Winter</td>
      <td>Calgary</td>
      <td>Speed Skating</td>
      <td>Speed Skating Women's 500 metres</td>
      <td>None</td>
    </tr>
  </tbody>
</table>



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


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





</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Name</th>
      <th>Age</th>
      <th>Team</th>
      <th>Year</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>A Dijiang</td>
      <td>24.0</td>
      <td>China</td>
      <td>1992</td>
    </tr>
    <tr>
      <th>1</th>
      <td>A Lamusi</td>
      <td>23.0</td>
      <td>China</td>
      <td>2012</td>
    </tr>
    <tr>
      <th>2</th>
      <td>Gunnar Nielsen Aaby</td>
      <td>24.0</td>
      <td>Denmark</td>
      <td>1920</td>
    </tr>
    <tr>
      <th>3</th>
      <td>Edgar Lindenau Aabye</td>
      <td>34.0</td>
      <td>Denmark/Sweden</td>
      <td>1900</td>
    </tr>
    <tr>
      <th>4</th>
      <td>Christine Jacoba Aaftink</td>
      <td>21.0</td>
      <td>Netherlands</td>
      <td>1988</td>
    </tr>
  </tbody>
</table>




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





</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Name</th>
      <th>Age</th>
      <th>Team</th>
      <th>Year</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>A Dijiang</td>
      <td>24.0</td>
      <td>China</td>
      <td>1992</td>
    </tr>
    <tr>
      <th>1</th>
      <td>A Lamusi</td>
      <td>23.0</td>
      <td>China</td>
      <td>2012</td>
    </tr>
    <tr>
      <th>2</th>
      <td>Gunnar Nielsen Aaby</td>
      <td>24.0</td>
      <td>Denmark</td>
      <td>1920</td>
    </tr>
    <tr>
      <th>3</th>
      <td>Edgar Lindenau Aabye</td>
      <td>34.0</td>
      <td>Denmark/Sweden</td>
      <td>1900</td>
    </tr>
    <tr>
      <th>4</th>
      <td>Christine Jacoba Aaftink</td>
      <td>21.0</td>
      <td>Netherlands</td>
      <td>1988</td>
    </tr>
  </tbody>
</table>



### 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.


```python
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.


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





</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>ID</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>Height</th>
      <th>Weight</th>
      <th>Team</th>
      <th>NOC</th>
      <th>Games</th>
      <th>Year</th>
      <th>Season</th>
      <th>City</th>
      <th>Sport</th>
      <th>Event</th>
      <th>Medal</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>5</td>
      <td>Christine Jacoba Aaftink</td>
      <td>F</td>
      <td>21.0</td>
      <td>185.0</td>
      <td>82.0</td>
      <td>Netherlands</td>
      <td>NED</td>
      <td>1988 Winter</td>
      <td>1988</td>
      <td>Winter</td>
      <td>Calgary</td>
      <td>Speed Skating</td>
      <td>Speed Skating Women's 1,000 metres</td>
      <td>None</td>
    </tr>
  </tbody>
</table>



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".


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




    42107




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




</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>COUNT(*)</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>42107</td>
    </tr>
  </tbody>
</table>



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


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




</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>ID</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>Height</th>
      <th>Weight</th>
      <th>Team</th>
      <th>NOC</th>
      <th>Games</th>
      <th>Year</th>
      <th>Season</th>
      <th>City</th>
      <th>Sport</th>
      <th>Event</th>
      <th>Medal</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>2513</th>
      <td>1415</td>
      <td>Shakeel Ahmed</td>
      <td>M</td>
      <td>21.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>India</td>
      <td>IND</td>
      <td>1992 Summer</td>
      <td>1992</td>
      <td>Summer</td>
      <td>Barcelona</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>4289</th>
      <td>2453</td>
      <td>Anil Alexander Aldrin</td>
      <td>M</td>
      <td>24.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>India</td>
      <td>IND</td>
      <td>1996 Summer</td>
      <td>1996</td>
      <td>Summer</td>
      <td>Atlanta</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>4732</th>
      <td>2699</td>
      <td>Shaukat Ali</td>
      <td>M</td>
      <td>30.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>India</td>
      <td>IND</td>
      <td>1928 Summer</td>
      <td>1928</td>
      <td>Summer</td>
      <td>Amsterdam</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>Gold</td>
    </tr>
    <tr>
      <th>4735</th>
      <td>2702</td>
      <td>Syed Ali</td>
      <td>M</td>
      <td>19.0</td>
      <td>169.0</td>
      <td>60.0</td>
      <td>India</td>
      <td>IND</td>
      <td>1976 Summer</td>
      <td>1976</td>
      <td>Summer</td>
      <td>Montreal</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>4736</th>
      <td>2703</td>
      <td>Syed Mushtaq Ali</td>
      <td>M</td>
      <td>22.0</td>
      <td>165.0</td>
      <td>61.0</td>
      <td>India</td>
      <td>IND</td>
      <td>1964 Summer</td>
      <td>1964</td>
      <td>Summer</td>
      <td>Tokyo</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>Gold</td>
    </tr>
    <tr>
      <th>...</th>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
    </tr>
    <tr>
      <th>253354</th>
      <td>126867</td>
      <td>Sunil Sowmarpet Vitalacharya</td>
      <td>M</td>
      <td>27.0</td>
      <td>176.0</td>
      <td>68.0</td>
      <td>India</td>
      <td>IND</td>
      <td>2016 Summer</td>
      <td>2016</td>
      <td>Summer</td>
      <td>Rio de Janeiro</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>256143</th>
      <td>128249</td>
      <td>Devindar Sunil Walmiki</td>
      <td>M</td>
      <td>24.0</td>
      <td>178.0</td>
      <td>69.0</td>
      <td>India</td>
      <td>IND</td>
      <td>2016 Summer</td>
      <td>2016</td>
      <td>Summer</td>
      <td>Rio de Janeiro</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>263723</th>
      <td>131974</td>
      <td>William Xalco</td>
      <td>M</td>
      <td>20.0</td>
      <td>167.0</td>
      <td>60.0</td>
      <td>India</td>
      <td>IND</td>
      <td>2004 Summer</td>
      <td>2004</td>
      <td>Summer</td>
      <td>Athina</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>264075</th>
      <td>132142</td>
      <td>Renuka Yadav</td>
      <td>F</td>
      <td>22.0</td>
      <td>159.0</td>
      <td>53.0</td>
      <td>India</td>
      <td>IND</td>
      <td>2016 Summer</td>
      <td>2016</td>
      <td>Summer</td>
      <td>Rio de Janeiro</td>
      <td>Hockey</td>
      <td>Hockey Women's Hockey</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>266934</th>
      <td>133554</td>
      <td>Sayed Muhammad Yusuf</td>
      <td>M</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>India</td>
      <td>IND</td>
      <td>1928 Summer</td>
      <td>1928</td>
      <td>Summer</td>
      <td>Amsterdam</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>Gold</td>
    </tr>
  </tbody>
</table>
<p>345 rows Ã 15 columns</p>




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




</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>ID</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>Height</th>
      <th>Weight</th>
      <th>Team</th>
      <th>NOC</th>
      <th>Games</th>
      <th>Year</th>
      <th>Season</th>
      <th>City</th>
      <th>Sport</th>
      <th>Event</th>
      <th>Medal</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>1415</td>
      <td>Shakeel Ahmed</td>
      <td>M</td>
      <td>21.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>India</td>
      <td>IND</td>
      <td>1992 Summer</td>
      <td>1992</td>
      <td>Summer</td>
      <td>Barcelona</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>None</td>
    </tr>
    <tr>
      <th>1</th>
      <td>2453</td>
      <td>Anil Alexander Aldrin</td>
      <td>M</td>
      <td>24.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>India</td>
      <td>IND</td>
      <td>1996 Summer</td>
      <td>1996</td>
      <td>Summer</td>
      <td>Atlanta</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>None</td>
    </tr>
    <tr>
      <th>2</th>
      <td>2699</td>
      <td>Shaukat Ali</td>
      <td>M</td>
      <td>30.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>India</td>
      <td>IND</td>
      <td>1928 Summer</td>
      <td>1928</td>
      <td>Summer</td>
      <td>Amsterdam</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>Gold</td>
    </tr>
    <tr>
      <th>3</th>
      <td>2702</td>
      <td>Syed Ali</td>
      <td>M</td>
      <td>19.0</td>
      <td>169.0</td>
      <td>60.0</td>
      <td>India</td>
      <td>IND</td>
      <td>1976 Summer</td>
      <td>1976</td>
      <td>Summer</td>
      <td>Montreal</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>None</td>
    </tr>
    <tr>
      <th>4</th>
      <td>2703</td>
      <td>Syed Mushtaq Ali</td>
      <td>M</td>
      <td>22.0</td>
      <td>165.0</td>
      <td>61.0</td>
      <td>India</td>
      <td>IND</td>
      <td>1964 Summer</td>
      <td>1964</td>
      <td>Summer</td>
      <td>Tokyo</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>Gold</td>
    </tr>
    <tr>
      <th>...</th>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
    </tr>
    <tr>
      <th>340</th>
      <td>126867</td>
      <td>Sunil Sowmarpet Vitalacharya</td>
      <td>M</td>
      <td>27.0</td>
      <td>176.0</td>
      <td>68.0</td>
      <td>India</td>
      <td>IND</td>
      <td>2016 Summer</td>
      <td>2016</td>
      <td>Summer</td>
      <td>Rio de Janeiro</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>None</td>
    </tr>
    <tr>
      <th>341</th>
      <td>128249</td>
      <td>Devindar Sunil Walmiki</td>
      <td>M</td>
      <td>24.0</td>
      <td>178.0</td>
      <td>69.0</td>
      <td>India</td>
      <td>IND</td>
      <td>2016 Summer</td>
      <td>2016</td>
      <td>Summer</td>
      <td>Rio de Janeiro</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>None</td>
    </tr>
    <tr>
      <th>342</th>
      <td>131974</td>
      <td>William Xalco</td>
      <td>M</td>
      <td>20.0</td>
      <td>167.0</td>
      <td>60.0</td>
      <td>India</td>
      <td>IND</td>
      <td>2004 Summer</td>
      <td>2004</td>
      <td>Summer</td>
      <td>Athina</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>None</td>
    </tr>
    <tr>
      <th>343</th>
      <td>132142</td>
      <td>Renuka Yadav</td>
      <td>F</td>
      <td>22.0</td>
      <td>159.0</td>
      <td>53.0</td>
      <td>India</td>
      <td>IND</td>
      <td>2016 Summer</td>
      <td>2016</td>
      <td>Summer</td>
      <td>Rio de Janeiro</td>
      <td>Hockey</td>
      <td>Hockey Women's Hockey</td>
      <td>None</td>
    </tr>
    <tr>
      <th>344</th>
      <td>133554</td>
      <td>Sayed Muhammad Yusuf</td>
      <td>M</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>India</td>
      <td>IND</td>
      <td>1928 Summer</td>
      <td>1928</td>
      <td>Summer</td>
      <td>Amsterdam</td>
      <td>Hockey</td>
      <td>Hockey Men's Hockey</td>
      <td>Gold</td>
    </tr>
  </tbody>
</table>
<p>345 rows Ã 15 columns</p>



### 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.


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




</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>ID</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>Height</th>
      <th>Weight</th>
      <th>Team</th>
      <th>NOC</th>
      <th>Games</th>
      <th>Year</th>
      <th>Season</th>
      <th>City</th>
      <th>Sport</th>
      <th>Event</th>
      <th>Medal</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>257054</th>
      <td>128719</td>
      <td>John Quincy Adams Ward</td>
      <td>M</td>
      <td>97.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>United States</td>
      <td>USA</td>
      <td>1928 Summer</td>
      <td>1928</td>
      <td>Summer</td>
      <td>Amsterdam</td>
      <td>Art Competitions</td>
      <td>Art Competitions Mixed Sculpturing, Statues</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>98118</th>
      <td>49663</td>
      <td>Winslow Homer</td>
      <td>M</td>
      <td>96.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>United States</td>
      <td>USA</td>
      <td>1932 Summer</td>
      <td>1932</td>
      <td>Summer</td>
      <td>Los Angeles</td>
      <td>Art Competitions</td>
      <td>Art Competitions Mixed Painting, Unknown Event</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>60863</th>
      <td>31173</td>
      <td>Thomas Cowperthwait Eakins</td>
      <td>M</td>
      <td>88.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>United States</td>
      <td>USA</td>
      <td>1932 Summer</td>
      <td>1932</td>
      <td>Summer</td>
      <td>Los Angeles</td>
      <td>Art Competitions</td>
      <td>Art Competitions Mixed Painting, Unknown Event</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>60861</th>
      <td>31173</td>
      <td>Thomas Cowperthwait Eakins</td>
      <td>M</td>
      <td>88.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>United States</td>
      <td>USA</td>
      <td>1932 Summer</td>
      <td>1932</td>
      <td>Summer</td>
      <td>Los Angeles</td>
      <td>Art Competitions</td>
      <td>Art Competitions Mixed Painting, Unknown Event</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>60862</th>
      <td>31173</td>
      <td>Thomas Cowperthwait Eakins</td>
      <td>M</td>
      <td>88.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>United States</td>
      <td>USA</td>
      <td>1932 Summer</td>
      <td>1932</td>
      <td>Summer</td>
      <td>Los Angeles</td>
      <td>Art Competitions</td>
      <td>Art Competitions Mixed Painting, Unknown Event</td>
      <td>NaN</td>
    </tr>
  </tbody>
</table>




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




</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>ID</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>Height</th>
      <th>Weight</th>
      <th>Team</th>
      <th>NOC</th>
      <th>Games</th>
      <th>Year</th>
      <th>Season</th>
      <th>City</th>
      <th>Sport</th>
      <th>Event</th>
      <th>Medal</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>128719</td>
      <td>John Quincy Adams Ward</td>
      <td>M</td>
      <td>97.0</td>
      <td>None</td>
      <td>None</td>
      <td>United States</td>
      <td>USA</td>
      <td>1928 Summer</td>
      <td>1928</td>
      <td>Summer</td>
      <td>Amsterdam</td>
      <td>Art Competitions</td>
      <td>Art Competitions Mixed Sculpturing, Statues</td>
      <td>None</td>
    </tr>
    <tr>
      <th>1</th>
      <td>49663</td>
      <td>Winslow Homer</td>
      <td>M</td>
      <td>96.0</td>
      <td>None</td>
      <td>None</td>
      <td>United States</td>
      <td>USA</td>
      <td>1932 Summer</td>
      <td>1932</td>
      <td>Summer</td>
      <td>Los Angeles</td>
      <td>Art Competitions</td>
      <td>Art Competitions Mixed Painting, Unknown Event</td>
      <td>None</td>
    </tr>
    <tr>
      <th>2</th>
      <td>31173</td>
      <td>Thomas Cowperthwait Eakins</td>
      <td>M</td>
      <td>88.0</td>
      <td>None</td>
      <td>None</td>
      <td>United States</td>
      <td>USA</td>
      <td>1932 Summer</td>
      <td>1932</td>
      <td>Summer</td>
      <td>Los Angeles</td>
      <td>Art Competitions</td>
      <td>Art Competitions Mixed Painting, Unknown Event</td>
      <td>None</td>
    </tr>
    <tr>
      <th>3</th>
      <td>31173</td>
      <td>Thomas Cowperthwait Eakins</td>
      <td>M</td>
      <td>88.0</td>
      <td>None</td>
      <td>None</td>
      <td>United States</td>
      <td>USA</td>
      <td>1932 Summer</td>
      <td>1932</td>
      <td>Summer</td>
      <td>Los Angeles</td>
      <td>Art Competitions</td>
      <td>Art Competitions Mixed Painting, Unknown Event</td>
      <td>None</td>
    </tr>
    <tr>
      <th>4</th>
      <td>31173</td>
      <td>Thomas Cowperthwait Eakins</td>
      <td>M</td>
      <td>88.0</td>
      <td>None</td>
      <td>None</td>
      <td>United States</td>
      <td>USA</td>
      <td>1932 Summer</td>
      <td>1932</td>
      <td>Summer</td>
      <td>Los Angeles</td>
      <td>Art Competitions</td>
      <td>Art Competitions Mixed Painting, Unknown Event</td>
      <td>None</td>
    </tr>
  </tbody>
</table>



### 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.






</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>EmployeeID</th>
      <th>DepartmentID</th>
      <th>Name</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>100</td>
      <td>100</td>
      <td>John Doe</td>
    </tr>
    <tr>
      <th>1</th>
      <td>101</td>
      <td>100</td>
      <td>Will green</td>
    </tr>
    <tr>
      <th>2</th>
      <td>102</td>
      <td>200</td>
      <td>Wilson Miner</td>
    </tr>
    <tr>
      <th>3</th>
      <td>104</td>
      <td>200</td>
      <td>Rochel Dmello</td>
    </tr>
    <tr>
      <th>4</th>
      <td>105</td>
      <td>300</td>
      <td>Dickie Bird</td>
    </tr>
  </tbody>
</table>










</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>AssetID</th>
      <th>EmployeeID</th>
      <th>DepartmentID</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>aab</td>
      <td>100</td>
      <td>100</td>
    </tr>
    <tr>
      <th>1</th>
      <td>aba</td>
      <td>101</td>
      <td>100</td>
    </tr>
    <tr>
      <th>2</th>
      <td>baa</td>
      <td>201</td>
      <td>200</td>
    </tr>
    <tr>
      <th>3</th>
      <td>cde</td>
      <td>202</td>
      <td>200</td>
    </tr>
    <tr>
      <th>4</th>
      <td>efg</td>
      <td>103</td>
      <td>300</td>
    </tr>
  </tbody>
</table>



#### 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](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.merge.html) method to join to tables.


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




\
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>EmployeeID</th>
      <th>DepartmentID_x</th>
      <th>Name</th>
      <th>AssetID</th>
      <th>DepartmentID_y</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>100</td>
      <td>100</td>
      <td>John Doe</td>
      <td>aab</td>
      <td>100</td>
    </tr>
    <tr>
      <th>1</th>
      <td>101</td>
      <td>100</td>
      <td>Will green</td>
      <td>aba</td>
      <td>100</td>
    </tr>
  </tbody>
</table>




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





</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>EmployeeID</th>
      <th>DepartmentID</th>
      <th>Name</th>
      <th>AssetID</th>
      <th>EmployeeID</th>
      <th>DepartmentID</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>100</td>
      <td>100</td>
      <td>John Doe</td>
      <td>aab</td>
      <td>100</td>
      <td>100</td>
    </tr>
    <tr>
      <th>1</th>
      <td>101</td>
      <td>100</td>
      <td>Will green</td>
      <td>aba</td>
      <td>101</td>
      <td>100</td>
    </tr>
  </tbody>
</table>




#### 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.


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





</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>EmployeeID</th>
      <th>DepartmentID_x</th>
      <th>Name</th>
      <th>AssetID</th>
      <th>DepartmentID_y</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>100</td>
      <td>100</td>
      <td>John Doe</td>
      <td>aab</td>
      <td>100</td>
    </tr>
    <tr>
      <th>1</th>
      <td>101</td>
      <td>100</td>
      <td>Will green</td>
      <td>aba</td>
      <td>100</td>
    </tr>
    <tr>
      <th>2</th>
      <td>102</td>
      <td>200</td>
      <td>Wilson Miner</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>3</th>
      <td>104</td>
      <td>200</td>
      <td>Rochel Dmello</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>4</th>
      <td>105</td>
      <td>300</td>
      <td>Dickie Bird</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
  </tbody>
</table>



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





</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>EmployeeID</th>
      <th>DepartmentID</th>
      <th>Name</th>
      <th>AssetID</th>
      <th>EmployeeID</th>
      <th>DepartmentID</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>100</td>
      <td>100</td>
      <td>John Doe</td>
      <td>aab</td>
      <td>100.0</td>
      <td>100</td>
    </tr>
    <tr>
      <th>1</th>
      <td>101</td>
      <td>100</td>
      <td>Will green</td>
      <td>aba</td>
      <td>101.0</td>
      <td>100</td>
    </tr>
    <tr>
      <th>2</th>
      <td>102</td>
      <td>200</td>
      <td>Wilson Miner</td>
      <td>None</td>
      <td>NaN</td>
      <td>None</td>
    </tr>
    <tr>
      <th>3</th>
      <td>104</td>
      <td>200</td>
      <td>Rochel Dmello</td>
      <td>None</td>
      <td>NaN</td>
      <td>None</td>
    </tr>
    <tr>
      <th>4</th>
      <td>105</td>
      <td>300</td>
      <td>Dickie Bird</td>
      <td>None</td>
      <td>NaN</td>
      <td>None</td>
    </tr>
  </tbody>
</table>



#### Right Outer Join

Right join has the opposite effect of the left join.


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





</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>EmployeeID</th>
      <th>DepartmentID_x</th>
      <th>Name</th>
      <th>AssetID</th>
      <th>DepartmentID_y</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>100</td>
      <td>100.0</td>
      <td>John Doe</td>
      <td>aab</td>
      <td>100</td>
    </tr>
    <tr>
      <th>1</th>
      <td>101</td>
      <td>100.0</td>
      <td>Will green</td>
      <td>aba</td>
      <td>100</td>
    </tr>
    <tr>
      <th>2</th>
      <td>201</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>baa</td>
      <td>200</td>
    </tr>
    <tr>
      <th>3</th>
      <td>202</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>cde</td>
      <td>200</td>
    </tr>
    <tr>
      <th>4</th>
      <td>103</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>efg</td>
      <td>300</td>
    </tr>
  </tbody>
</table>



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.


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





</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>EmployeeID</th>
      <th>DepartmentID</th>
      <th>Name</th>
      <th>AssetID</th>
      <th>EmployeeID</th>
      <th>DepartmentID</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>100.0</td>
      <td>100.0</td>
      <td>John Doe</td>
      <td>aab</td>
      <td>100</td>
      <td>100</td>
    </tr>
    <tr>
      <th>1</th>
      <td>101.0</td>
      <td>100.0</td>
      <td>Will green</td>
      <td>aba</td>
      <td>101</td>
      <td>100</td>
    </tr>
    <tr>
      <th>2</th>
      <td>NaN</td>
      <td>NaN</td>
      <td>None</td>
      <td>baa</td>
      <td>201</td>
      <td>200</td>
    </tr>
    <tr>
      <th>3</th>
      <td>NaN</td>
      <td>NaN</td>
      <td>None</td>
      <td>cde</td>
      <td>202</td>
      <td>200</td>
    </tr>
    <tr>
      <th>4</th>
      <td>NaN</td>
      <td>NaN</td>
      <td>None</td>
      <td>efg</td>
      <td>103</td>
      <td>300</td>
    </tr>
  </tbody>
</table>


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 `Medal`s by team, sort them in descending order and show top % results.


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





</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Team</th>
      <th>Medal</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>1095</th>
      <td>United States</td>
      <td>5219</td>
    </tr>
    <tr>
      <th>976</th>
      <td>Soviet Union</td>
      <td>2451</td>
    </tr>
    <tr>
      <th>398</th>
      <td>Germany</td>
      <td>1984</td>
    </tr>
    <tr>
      <th>412</th>
      <td>Great Britain</td>
      <td>1673</td>
    </tr>
    <tr>
      <th>361</th>
      <td>France</td>
      <td>1550</td>
    </tr>
  </tbody>
</table>




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





</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Team</th>
      <th>COUNT(Medal)</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>United States</td>
      <td>5219</td>
    </tr>
    <tr>
      <th>1</th>
      <td>Soviet Union</td>
      <td>2451</td>
    </tr>
    <tr>
      <th>2</th>
      <td>Germany</td>
      <td>1984</td>
    </tr>
    <tr>
      <th>3</th>
      <td>Great Britain</td>
      <td>1673</td>
    </tr>
    <tr>
      <th>4</th>
      <td>France</td>
      <td>1550</td>
    </tr>
  </tbody>
</table>


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


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





</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Team</th>
      <th>Medal</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>197</th>
      <td>United States</td>
      <td>1066</td>
    </tr>
    <tr>
      <th>10</th>
      <td>Australia</td>
      <td>401</td>
    </tr>
    <tr>
      <th>56</th>
      <td>East Germany</td>
      <td>152</td>
    </tr>
    <tr>
      <th>70</th>
      <td>Germany</td>
      <td>152</td>
    </tr>
    <tr>
      <th>72</th>
      <td>Great Britain</td>
      <td>127</td>
    </tr>
  </tbody>
</table>



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





</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Team</th>
      <th>COUNT(Medal)</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>United States</td>
      <td>1066</td>
    </tr>
    <tr>
      <th>1</th>
      <td>Australia</td>
      <td>401</td>
    </tr>
    <tr>
      <th>2</th>
      <td>Germany</td>
      <td>152</td>
    </tr>
    <tr>
      <th>3</th>
      <td>East Germany</td>
      <td>152</td>
    </tr>
    <tr>
      <th>4</th>
      <td>Great Britain</td>
      <td>127</td>
    </tr>
  </tbody>
</table>



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](https://www.amazon.com/Python-Data-Analysis-Wrangling-IPython/dp/1491957662/ref=sr_1_1?crid=FHSZBNN2LW2O&keywords=wes+mckinney&qid=1641999545&sprefix=wes+mckinney%2Caps%2C417&sr=8-1)

### SQL Resources

- [Kaggle's Intro to SQL](https://kaggle.com/learn/intro-to-sql)
- [SQL Bolt](https://t.co/GRhUKFSyS6)
- [SQL for Data Scientists by Renee Teate](https://t.co/c1KRNA74vY)