The way to Rewrite and Optimize Your SQL Queries to Pandas in 5 Easy Examples | by Byron Dolon | Jun, 2023

Querying a complete desk

We will dive proper into it by wanting on the basic SELECT ALL from a desk.

Right here’s the SQL:

SELECT * FROM df

And right here’s the pandas

df

All you’ll want to do is name the DataFrame in Pandas to return the entire desk and all its columns.

You may additionally wish to simply take a look at a small subset of your desk as a fast verify earlier than writing a extra sophisticated question. In SQL, you’d use LIMIT 10 or one thing just like get solely a choose variety of rows. In Pandas, equally, you’ll be able to name df.head(10) or df.tails(10) to get the primary or final 10 rows of the desk.

Querying a desk with out null values

So as to add to our preliminary choose question, along with simply limiting the variety of rows, you’ll put situations to filter the desk inside a WHERE clause in SQL. For instance, in the event you’d need all rows within the desk with none null values within the Order_ID column, the SQL would appear like this:

SELECT * FROM df WHERE Order_ID IS NOT NULL

In Pandas, you will have two choices:

# Choice 1
df.dropna(subset="Order_ID")
# Choice 2
df.loc[df["Order_ID"].notna()]

Now, the desk we get again doesn’t have any null values from the Order_ID column (which you’ll be able to evaluate to the primary output above). Each choices will return a desk with out the null values, however they work barely otherwise.

You need to use the native dropna methodology in Pandas to return the DataFrame with none null rows, specifying within the subset parameter which columns you’d wish to drop nulls from.

Alternatively, the loc methodology permits you to cross a masks or boolean label you’ll be able to specify to filter the DataFrame. Right here, we cross df["Order_ID"].notna(), which in the event you would name it by itself would return a Collection of True and False values that may map to the unique DataFrame rows for whether or not the Order_ID is null. After we cross it to the loc methodology, it as a substitute returns the DataFrame the place df["Order_ID"].notna() evaluates to True (so all rows the place the Order_ID column isn’t null.

Querying particular columns from a desk

Subsequent, as a substitute of choosing all columns from the desk, let’s as a substitute choose only a few particular columns. In SQL, you’d write the column names within the SELECT a part of the question like this:

SELECT Order_ID, Product, Quantity_Ordered FROM df

In Pandas, we’d write the code like this:

df[["Order_ID", "Product", "Quantity_Ordered"]]

To pick a particular subset of columns, you’ll be able to cross an inventory of the column names into the DataFrame in Pandas. You too can outline the checklist individually like this for readability:

target_cols = ["Order_ID", "Product", "Quantity_Ordered"]
df[target_cols]

Assigning an inventory of goal columns that you may then cross right into a DataFrame could make working with a desk over time when you’ll want to make modifications in your code slightly simpler. For instance, you would have a operate return the columns you want as an inventory, or append and take away columns to the checklist as wanted relying on what sort of output the person wants.

The GROUP BY in SQL and Pandas

We will now transfer on to aggregating knowledge. In SQL, we do that by passing a column to the SELECT and GROUP BY clauses that we wish to group on after which including the column to an combination measure like COUNT within the SELECT clause as effectively. For instance, doing so will allow us to group all the person Order_ID rows within the unique desk for every Product and depend what number of there are. The question can appear like this:

SELECT 
Product, 
COUNT(Order_ID)
FROM df
WHERE Order_ID IS NOT NULL
GROUP BY Product

In Pandas, it might appear like this:

df[df["Order_ID"].notna()].groupby(["Product"])["Order_ID"].depend()

The output is a Pandas Collection the place the desk is grouped the merchandise and there’s a depend of all of the Order_ID for every product. Along with our earlier question in Pandas the place we included a filter, we now do three issues:

1. Add groupby and cross a column (or checklist of columns) that you simply wish to group the DataFrame on;
2. Go the identify of the column in sq. brackets on the uncooked grouped DataFrame;
3. Name the depend (or every other combination) methodology to carry out the aggregation on the DataFrame for the goal column.

For higher readability, we will assign the situation to a variable (it will come in useful later) and format the question so it’s simpler to learn.

situation = df["Order_ID"].notna()
grouped_df = (
df.loc[condition]
.groupby("Product")
["Order_ID"]  # choose column to depend
.depend()
)
grouped_df

Now that we have now a lot of the elements of an entire SQL question, let’s check out a extra sophisticated one and see what it might appear like in Pandas.

SELECT 
Product,
COUNT(Order_ID)
FROM df
WHERE Order_ID IS NOT NULL
AND Purchase_Address LIKE "%Los Angeles%"
AND Quantity_Ordered == 1
GROUP BY Product
ORDER BY COUNT(Order_ID) DESC

Right here, we add slightly to our earlier question by together with a number of filter situations in addition to an ORDER BY in order that the desk returned in our question is sorted by the measure we’re aggregating on. Since there are a couple of extra elements to this question, let’s have a look step-by-step at how we’d implement this in Pandas.

First, as a substitute of passing a number of situations after we name the loc methodology, let’s as a substitute outline an inventory of situations and assign them to a variable FILTER_CONDITIONS.

FILTER_CONDITIONS = [
df["Order_ID"].notna(),
df["Purchase_Address"].str.comprises("Los Angeles"),
df["Quantity_Ordered"] == "1",
]

As earlier than, a situation handed into loc must be a Pandas masks that evaluates to both true or false. It’s doable to cross a number of situations to loc, however the syntax ought to appear like this:

df.loc[condition_1 & condition_2 & condition_3]

Nonetheless, simply passing an inventory of situations like this received’t work:

df.loc[FILTER_CONDITIONS]  
# does not work -> you'll be able to't simply cross an inventory into loc

You’ll get an error in the event you attempt the above as a result of every situation must be separated by the & operator for “and” situations (or the | operator in the event you want “or” situations). As a substitute, we will write some fast code to return the situations within the appropriate format. We’ll make use of the functools.scale back methodology to place the situations collectively.

If you wish to see what it seems to be like in a pocket book and see what it seems to be like to mix some strings utilizing the scale back operate, do that:

scale back(lambda x, y: f"{x} & {y}", ["condition_1", "condition_2", "condition_3"])

This outputs the string like this:

>>> 'condition_1 & condition_2 & condition_3'

Going again to our precise Pandas situations, we will write this as a substitute (with out the string formatting and simply utilizing our outlined checklist of situations within the FILTER_CONDITIONS variable).

scale back(lambda x, y: x & y, FILTER_CONDITIONS)

What scale back does is apply a operate cumulatively to the weather current in an iterable, or in our case run the lambda operate over the objects in our FILTER_CONDITIONS checklist which mixes every of them with the & operator. This runs till there aren’t any situations left, or on this case, for all three situations it might successfully return:

df["Order_ID"].notna() & df["Purchase_Address"].str.comprises("Los Angeles") & df["Quantity_Ordered"] == "1"

Lastly, let’s add the checklist of situations to create a closing group by question in Pandas:

final_df = (
df
.loc[reduce(lambda x, y: x & y, FILTER_CONDITIONS)]
.groupby("Product")
.dimension()
.sort_values(ascending=False)
)

You’ll discover two extra variations from the earlier question:

1. As a substitute of specifying the precise column to depend on, we will merely name the dimension methodology which is able to return the variety of rows within the DataFrame (as earlier than the place each Order_ID worth was distinctive and meant to symbolize one row after we counted on it);
2. There are a couple of alternative ways to do the ORDER BY in Pandas- a method is to easily name sort_values and cross ascending=False to type on descending order.

For those who wished to make use of the earlier syntax for aggregating the information it might appear like this:

final_df = (
df
.loc[reduce(lambda x, y: x & y, FILTER_CONDITIONS)]
.groupby("Product")
["Order_ID"].depend()
.sort_values(ascending=False)
)

The output of each strategies would be the similar as earlier than, which is a Collection with the column you’re grouping on and the counts for every product.

If as a substitute, you wished to output a DataFrame, you’ll be able to name the reset_index methodology on the sequence to get the unique column names again for which column you grouped on and the column you’re aggregating on (on this case we grouped on “Product” and are counting the “Order_ID”.

final_df.reset_index()

And there we have now it! All of the elements of a full SQL question however lastly written in Pandas. A few of the issues we will do additional to optimize this course of for working with knowledge over time embody:

• Placing the totally different lists of columns to SELECT or GROUP BY to their very own variables or capabilities (so that you or a person can modify them over time);
• Transfer the logic to mix the checklist of columns for a filter situation to its personal operate so the top person doesn’t must be confused over what the scale back logic is doing;
• After passing reset_index we will rename the output column (or columns if we’re aggregating on a number of) for readability, for instance to “Count_Order_ID”.