Pandas instead of SQL: Working with data in a new way

Previously, SQL as a tool was enough for research analysis: quick data search and preliminary report on data.

Nowadays, data come in different forms and do not always refer to them as “relational databases”. They can be CSV files, plain text, Parquet, HDF5, and many others. This is where the Pandas library can help you.

What is Pandas?

Pandas is a library in the Python language, created for data analysis and processing. It has an open-source code and is supported by Anaconda developers. This library is well suited for structured (tabular) data. More information can be found in the documentation. Pandas allow you to generate data queries and much more.

SQL is a declarative language. It allows you to declare everything in such a way that a query is similar to a normal sentence in English. The syntax of Pandas is very different from SQL. Here you apply operations to a dataset and chain them together for conversion and modification.

SQL parsing

An SQL query consists of several keywords. Between these words are added data characteristics that you want to see. An example of a query framework without specifics:

SELECT... FROM... WHERE...
GROUP BY...
ORDER BY...
LIMIT... OFFSET...

There are other expressions, but these are the most basic. To translate expressions into Pandas, you need to load the data first:

import pandas as pd

airports = pd.read_csv('data/airports.csv')
airport_freq = pd.read_csv('data/airport-frequencies.csv')
runways = pd.read_csv('data/runways.csv')

You can download this data here.

SELECT, WHERE, DISTINCT, LIMIT

Below are several variants of expressions with the SELECT operator. Unnecessary results are cut off with LIMIT and filtered out with WHERE. DISTINCT is used to remove duplicate results.

SELECT with multiple conditions

Several selection conditions are combined using the operand &. If you only want a subset of some columns from a table, this subset is used in another pair of square brackets.

ORDER BY

By default, Pandas will sort the data in ascending order. For reverse sorting use the expression ascending=False.

IN and NOT IN

To filter not a single value but whole lists, there is an IN condition. In Pandas, the .isin() operator works exactly the same way. To override any condition, use ~ (tilde).

GROUP BY, COUNT, ORDER BY

Grouping is performed using the .groupby() operator. There is a small difference between COUNT semantics in SQL and Pandas. In Pandas .count() will return non-null/NaN values. To get a result like in SQL, use .size().

Below is a grouping by several fields. By default, Pandas sorts by the same field list, so in the first example, there is no need for .sort_values(). If you want to use different fields for sorting or DESC instead of ASC as in the second example, you need to specify the selection explicitly:

The use of .to_frame() and .reset_index() is determined by sorting by a specific field (size). This field must be part of the DataFrame type. After grouping in Pandas, the result is another type called GroupByObject. Therefore you need to convert it back to DataFrame. With .reset_index(), the line numbering for the data frame is restarted.

HAVING

In SQL, you can additionally filter grouped data using the HAVING condition. In Pandas, you can use .filter() and provide the Python function (or lambda expression), which will return True if a group of data is to be included in the result.

The first N records

Let’s say some preliminary queries have been made and there is now a data frame named by_country that contains the number of airports in each country:

In the following example, let’s organize airport_count data and select only the first 10 countries with the largest number of airports. The second example is a more complex case, where we select the “next 10” after the first 10 entries:

Aggregate functions: MIN, MAX, MEAN

Considering the data frame above (runway data), let’s calculate the minimum, maximum, and average runway length.

Note that with an SQL query, the data is a column. But in Pandas, the data is represented by rows.

The data frame can be easily transposed with .T to get the columns.

JOIN

Use .merge() to attach data frames to Pandas. You need to specify which columns to join (left_on and right_on) and the connection type: inner (default), left (corresponds to LEFT OUTER in SQL), right (RIGHT OUTER in SQL) or outer (FULL OUTER in SQL).

UNION ALL and UNION

pd.concat() is the UNION ALL equivalent in SQL.

The UNION equivalent (deduplication) is .drop_duplicates().

INSERT

So far, only sampling has been performed, but data can be changed during the preliminary analysis. In Pandas, there is no INSERT equivalent in SQL to add the required data. Instead, you should create a new data frame containing new records and then combine the two frames.

UPDATE

Suppose now you need to fix some incorrect data in the original frame.

DELETE

The easiest and most convenient way to remove data from a frame in Pandas is to split the frame into rows. Then get line indices and use them in the .drop() method.

Variability

By default, most operators in Pandas return a new object. Some operators accept inplace=True, which allows them to work with the original data frame instead of the new one. For example, you can reset the index in a place like this:

df.reset_index(drop=True, inplace=True)

However, the .loc operator (above in the example with UPDATE) simply finds the record indexes to update them, and the values change on the spot. Also, if all values in a column are updated (df[‘url’] = ‘http://google.com’) or a new column is added (df[‘total_cost’] = df[‘price’] * df[‘quantity’]), these data will change in place.

Pandas is more than just a query mechanism. Other transformations can be done with data.

Exporting to many formats

df.to_csv(...) # in csv file
df.to_hdf(...) # in an HDF5 file
df.to_pickle(...) # into the serialized object
df.to_sql(...) # to an SQL database
df.to_excel(...) # to an Excel file
df.to_json(...) # into JSON string
df.to_html(...) # display as an HTML table
df.to_feather(...) # in binary feather format
df.to_latex(...) # in a table environment
df.to_stata(...) # into Stata binary data files
df.to_msgpack(...) # msgpack-object (serialization)
df.to_gbq(...) # in the BigQuery-table (Google)
df.to_string(...) # into console output
df.to_clipboard(...) # in the clipboard that can be inserted into Excel

Drawing up charts

top_10.plot(
x='iso_country',
y='airport_count',
kind='barh',
figsize=(10, 7),
title='Top 10 countries with most airports')

We’ll get it:

Opportunity to share your work

The best place to publish Pandas queries, charts, and the like is the Jupyter notebook. Some people (like Jake Vanderplas) even publish whole books there. It is easy to create a new entry in the notebook:

$ pip install jupyter
$ jupyter notebook

After that you can:

• go to the address localhost:8888;
• press “New” and give your notebook a name;
• query and display the data;
• create a GitHub repository and add your notebook (a file with the extension .ipynb).

GitHub has an excellent built-in Jupyter notepad viewer with a Markdown design.

Why Combine Pandas and SQL