How to Run SQL Queries on Pandas Data Frames?
Tags: python, datascience, database, programming
Published: 2022-01-06 15:26:00

Pandas may be the de-facto standard library for data analysis in Python. Yet, most data scientists are comfortable with SQL than pandas data operations.

At times it may feel like things are easy to do in SQL. Wouldn't it be great if we have a way to get the best of both worlds? 

It does. 

A wonderful library in the Python ecosystem makes it possible to do this. 

___Related: [A Better Way to Summarize Pandas Dataframes.](https://www.the-analytics.club/summarize-datasets-in-pandas)___


## Write your first SQL query on a Pandas dataframe with Pandasql

Pandasql is an open-source package that lets you run SQL code on pandas data frames. You can check out its repository on [GitHub](https://github.com/yhat/pandasql/). 

You can [install Pandasql with PyPI](https://pypi.org/project/pandasql/) as follows. 

```shell
pip install pandasql
```

Once you've installed it, you can use its self function to query any data frame in the memory. 

```python
from pandasql import sqldf
from sklearn import datasets

data = datasets.load_wine(as_frame=True)['data']

sqldf("SELECT * FROM data LIMIT 10", globals())
```

We've imported the wine recognition dataset available in the scikit learn toy dataset repository in the above example. We then queried the first ten records from the dataset. 

The first argument to the `sqldf` function is straightforward to understand. It's a SQL query. The second argument, `global()`  tells the function use the global context to find the dataset. The other possible value for this argument is `local()` which tells the interpreter to look only within the context of a function block. 

If we're to do the same using only Pandas operations, it will look as follows. 

```python
df[:10]
```

Both operations results in the following dataset.

![Pandas dataframe queried with SQL](https://www.the-analytics.club/static/4216336a15a71900b4d2fc64dad988aa/7a3d6/pandas-sql-dataset.png "Pandas dataframe queried with SQL")

At first, it may seem counterintuitive to add complexity to the trivial task of querying the first ten records. But, as our query gets bigger, it becomes easier to do it using a SQL query than the Pandas way. 

__PRO TIP: Query pandas using SQL without having to mention the global environment.___

We can use the `functool.partial` utility to create a partial function that doesn't require you to mention the environment every time. Here's how to do it. 

```python
from functools import partial

gpandsql = partial(sqldf, env=globals())

gpandsql("SELECT * FROM data LIMIT 10")
```

## What types of SQL queries you can perform on a Pandas dataframe?

`Pandasql` uses sqlite as it's temporary backend. You can perform all kinds of SQL operations sqlite supports. This includes GROUPBY, WHERE, and different kinds of JOINS. 

Here's an example: 

```python
from functools import partial
from pandasql import sqldf
from sklearn import datasets

data = datasets.load_wine(as_frame=True)['data']
target = datasets.load_wine(as_frame=True)['target']


gpandsql = partial(sqldf, env=globals())

gpandsql("""
    SELECT * 
    FROM data d 
    LEFT JOIN target t 
    WHERE 
        d.ash = 1.36 
    LIMIT 5"""
).describe()
```

You can even perform more advanced operations such as [window functions](https://www.sqlite.org/windowfunctions.html). 

However, SQLite is not a database designed for large-scale applications. Also, we should note that [SQLite has some limitations](https://www.sqlite.org/omitted.html) we need to be aware of. 

Also, note that we can only query dataframes using Pandasql. We can not create, update, or delete records using this library. 

## Final thoughts
Most data scientists work well in SQL databases. For some, it isn't easy to use Pandas operations. 

For them, Pandasql is a fantastic tool to enjoy the best of both worlds. 

I've also noticed that SQL queries are excellent for readability. They follow the natural language flow, making it easy for even novice programmers to grasp.

---
Did you like what you read? [**_Consider subscribing to my email newsletter_**](https://thuwarakesh.medium.com/subscribe)  because I post more like this frequently. 

Thanks for reading, friend! Say Hi to me on [LinkedIn](https://www.linkedin.com/in/thuwarakesh/), [Twitter](https://twitter.com/Thuwarakesh), and [Medium](https://thuwarakesh.medium.com/).