$$ %---- MACROS FOR SETS ----% \newcommand{\znz}[1]{\mathbb{Z} / #1 \mathbb{Z}} \newcommand{\twoheadrightarrowtail}{\mapsto\mathrel{\mspace{-15mu}}\rightarrow} % popular set names \newcommand{\N}{\mathbb{N}} \newcommand{\Z}{\mathbb{Z}} \newcommand{\Q}{\mathbb{Q}} \newcommand{\R}{\mathbb{R}} \newcommand{\C}{\mathbb{C}} \newcommand{\I}{\mathbb{I}} % popular vector space notation \newcommand{\V}{\mathbb{V}} \newcommand{\W}{\mathbb{W}} \newcommand{\B}{\mathbb{B}} \newcommand{\D}{\mathbb{D}} %---- MACROS FOR FUNCTIONS ----% % linear algebra \newcommand{\T}{\mathrm{T}} \renewcommand{\ker}{\mathrm{ker}} \newcommand{\range}{\mathrm{range}} \renewcommand{\span}{\mathrm{span}} \newcommand{\rref}{\mathrm{rref}} \renewcommand{\dim}{\mathrm{dim}} \newcommand{\col}{\mathrm{col}} \newcommand{\nullspace}{\mathrm{null}} \newcommand{\row}{\mathrm{row}} \newcommand{\rank}{\mathrm{rank}} \newcommand{\nullity}{\mathrm{nullity}} \renewcommand{\det}{\mathrm{det}} \newcommand{\proj}{\mathrm{proj}} \renewcommand{\H}{\mathrm{H}} \newcommand{\trace}{\mathrm{trace}} \newcommand{\diag}{\mathrm{diag}} \newcommand{\card}{\mathrm{card}} \newcommand\norm[1]{\left\lVert#1\right\rVert} % differential equations \newcommand{\laplace}[1]{\mathcal{L}\{#1\}} \newcommand{\F}{\mathrm{F}} % misc \newcommand{\sign}{\mathrm{sign}} \newcommand{\softmax}{\mathrm{softmax}} \renewcommand{\th}{\mathrm{th}} \newcommand{\adj}{\mathrm{adj}} \newcommand{\hyp}{\mathrm{hyp}} \renewcommand{\max}{\mathrm{max}} \renewcommand{\min}{\mathrm{min}} \newcommand{\where}{\mathrm{\ where\ }} \newcommand{\abs}[1]{\vert #1 \vert} \newcommand{\bigabs}[1]{\big\vert #1 \big\vert} \newcommand{\biggerabs}[1]{\Bigg\vert #1 \Bigg\vert} \newcommand{\equivalent}{\equiv} \newcommand{\cross}{\times} % statistics \newcommand{\cov}{\mathrm{cov}} \newcommand{\var}{\mathrm{var}} \newcommand{\bias}{\mathrm{bias}} \newcommand{\E}{\mathrm{E}} \newcommand{\prob}{\mathrm{prob}} \newcommand{\unif}{\mathrm{unif}} \newcommand{\invNorm}{\mathrm{invNorm}} \newcommand{\invT}{\mathrm{invT}} \newcommand{\P}{\text{P}} \newcommand{\pmf}{\text{pmf}} \newcommand{\pdf}{\text{pdf}} % real analysis \renewcommand{\sup}{\mathrm{sup}} \renewcommand{\inf}{\mathrm{inf}} %---- MACROS FOR ALIASES AND REFORMATTING ----% % logic \newcommand{\forevery}{\ \forall\ } \newcommand{\OR}{\lor} \newcommand{\AND}{\land} \newcommand{\then}{\implies} % set theory \newcommand{\impropersubset}{\subseteq} \newcommand{\notimpropersubset}{\nsubseteq} \newcommand{\propersubset}{\subset} \newcommand{\notpropersubset}{\not\subset} \newcommand{\union}{\cup} \newcommand{\Union}[2]{\bigcup\limits_{#1}^{#2}} \newcommand{\intersect}{\cap} \newcommand{\Intersect}[2]{\bigcap\limits_{#1}^{#2}} \newcommand{\intersection}[2]{\bigcap\limits_{#1}^{#2}} \newcommand{\Intersection}[2]{\bigcap\limits_{#1}^{#2}} \newcommand{\closure}{\overline} \newcommand{\compose}{\circ} % linear algebra \newcommand{\subspace}{\le} \newcommand{\angles}[1]{\langle #1 \rangle} \newcommand{\identity}{\mathbb{1}} \newcommand{\orthogonal}{\perp} \renewcommand{\parallel}[1]{#1^{||}} % calculus \newcommand{\integral}[2]{\int\limits_{#1}^{#2}} \newcommand{\limit}[1]{\lim\limits_{#1}} \newcommand{\approaches}{\rightarrow} \renewcommand{\to}{\rightarrow} \newcommand{\convergesto}{\rightarrow} % algebra \newcommand{\summation}[2]{\sum\nolimits_{#1}^{#2}} \newcommand{\product}[2]{\prod\limits_{#1}^{#2}} \newcommand{\by}{\times} \newcommand{\integral}[2]{\int_{#1}^{#2}} \newcommand{\ln}{\text{ln}} % exists commands \newcommand{\notexist}{\nexists\ } \newcommand{\existsatleastone}{\exists\ } \newcommand{\existsonlyone}{\exists!} \newcommand{\existsunique}{\exists!} \let\oldexists\exists \renewcommand{\exists}{\ \oldexists\ } % statistics \newcommand{\distributed}{\sim} \newcommand{\onetoonecorresp}{\sim} \newcommand{\independent}{\perp\!\!\!\perp} \newcommand{\conditionedon}{\ |\ } \newcommand{\given}{\ |\ } \newcommand{\notg}{\ngtr} \newcommand{\yhat}{\hat{y}} \newcommand{\betahat}{\hat{\beta}} \newcommand{\sigmahat}{\hat{\sigma}} \newcommand{\muhat}{\hat{\mu}} \newcommand{\transmatrix}{\mathrm{P}} \renewcommand{\choose}{\binom} % misc \newcommand{\infinity}{\infty} \renewcommand{\bold}{\textbf} \newcommand{\italics}{\textit} \newcommand{\step}{\text{step}} $$

How to Rewrite SQL Queries in Pandas

Kung Fu Panda Cover Image

Kung Fu Panda

If you’re reading this, you’re probably a data scientist already familiar with SQL and Pandas. But if you’re like me, you scratch your head each time you wish to write a SQL query against your Pandas DataFrame.

This article attempts to provide a mapping between common operations in SQL and their counterpart in Pandas using a fictional housing dataset.

Select, order by, and limit

Say you want to select all columns in your dataset and limit the number of rows coming back. In SQL,

select *
from housing
limit 10

In Pandas,

housing_df.head(10)

Want to order by number of bedrooms? In SQL,

select *
from housing
order by num_bedrooms
limit 10

In Pandas,

housing_df.sort_values(by=['num_bedrooms']).head(10)

Just want to select a few columns? In SQL,

select num_bedrooms, price
from housing

In Pandas,

housing_df[['num_bedrooms', 'price']]

Filtering with where

Only care about houses with three or more bedrooms? In SQL,

select *
from housing
where num_bedrooms >= 3

In Pandas,

housing_df[housing_df.num_bedrooms >= 3]

Want multiple filters? In SQL,

select *
from housing
where num_bedrooms >= 3 and price < 450000

In Pandas,

housing_df[(housing_df.num_bedrooms >= 3) & (housing_df.price < 450000)]

Or what if you want to filter rows by values in a column?

select *
from housing
where building_material in ('brick', 'wood')

In Pandas,

housing_df[housing_df.building_material.isin(['brick', 'wood'])]

Want rows not in the list?

housing_df[~housing_df.building_material.isin(['brick', 'wood'])]

Joins

I’ve found myself many times in the situation where I want to join two tables together on a particular column so that I have as many features available for later analysis. In SQL, this has been relativey easy:

select *
from housing
left outer join more_housing
on housing.house_id = more_housing.house_id

In Pandas,

pd.merge(housing_df, more_housing_df, on='house_id', how='left')

Joining on multiple columns between both tables? In SQL,

select *
from housing
left outer join more_housing
on housing.house_id = more_housing.house_id
   and housing.price = more_housing.price

In Pandas,

pd.merge(housing_df, more_housing_df, on=['house_id', 'price'], how='left')

Want to stack or union two tables? Let’s get a list of all housing prices from both tables.

select price
from housing
union
select price
from more_housing

In Pandas,

pd.concat([housing_df, more_housing_df]).price

Group by and having

Say we want to count how many houses there are when grouped by num_bedrooms and is_two_story.

select num_bedrooms,
       is_two_story,
       count(*)
from housing
group by num_bedrooms, is_two_story

In Pandas,

housing.groupby(['num_bedrooms', 'is_two_story']).size()

Want to sort those values now?

housing.groupby(['num_bedrooms', 'is_two_story']).size().to_frame('size').reset_index().sort_values(['num_bedrooms', 'size'])

Or what if you just want to consider groups where the count is greater than 5?

housing.groupby(['num_bedrooms', 'is_two_story']).filter(lambda x: len(x) > 5).groupby(['num_bedrooms', 'is_two_story']).size().to_frame('size').reset_index().sort_values(['num_bedrooms', 'size'])

A note about multiple indices

Say you’re doing some quick analysis on COVID-19 data and want to get the total number of confirmed cases over time for the US. You’d start with:

us_df = df[df['Country'] == 'US'].groupby(['Country', 'Last Updated']).sum()[['Confirmed']]

The problem is that now the dataframe as two indices and this can make it hard to plot the data. We can unstack the first column in the groupby, Country, to leave us with the Last Updated column as an index:

us_df = us_df.unstack(level=0)

Now we just rename the only column we have, add the index as a new column, then just replace the index with the row count.

us_df.columns = ['Confirmed']
us_df['Last Updated'] = us_df.index
us_df.index = np.arange(us_df.shape[0])

Hopefully this helps!

For more details about implementation, make sure to check out Pandas’ documentation. There are also some other good posts about SQL to Pandas usage like this one.

comments powered by Disqus