How to Rewrite and Optimize Your SQL Queries to Pandas in 5 Simple Examples | by Byron Dolon | Jun, 2023

Querying an entire desk

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

Right here’s the SQL:

SELECT * FROM df

And right here’s the pandas

df

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

You may additionally need to simply have a look at a small subset of your desk as a fast verify earlier than writing a extra difficult question. In SQL, you’d use LIMIT 10 or one thing just like get solely a choose variety of rows. In Pandas, equally, you possibly can 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’d put situations to filter the desk inside a WHERE clause in SQL. For instance, should 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 may 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 in a different way.

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

Alternatively, the loc technique permits you to cross a masks or boolean label you possibly can specify to filter the DataFrame. Right here, we cross df["Order_ID"].notna(), which should you would name it by itself would return a Sequence of True and False values that may map to the unique DataFrame rows for whether or not the Order_ID is null. Once we cross it to the loc technique, 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 just some 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 possibly can cross an inventory of the column names into the DataFrame in Pandas. You may as well 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 would be able to then cross right into a DataFrame could make working with a desk over time when you should make adjustments in your code a bit of simpler. For instance, you might have a perform 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 consumer 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 need to group on after which including the column to an combination measure like COUNT within the SELECT clause as properly. For example, doing so will allow us to group all the person Order_ID rows within the unique desk for every Product and rely 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"].rely()

The output is a Pandas Sequence the place the desk is grouped the merchandise and there’s a rely 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 just need to group the DataFrame on;
2. Move the title of the column in sq. brackets on the uncooked grouped DataFrame;
3. Name the rely (or another combination) technique to carry out the aggregation on the DataFrame for the goal column.

For higher readability, we will assign the situation to a variable (this may turn out to be 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 rely
.rely()
)
grouped_df

Now that we’ve got a lot of the elements of an entire SQL question, let’s check out a extra difficult 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 a bit of 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 just a few 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 once we name the loc technique, 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.accommodates("Los Angeles"),
df["Quantity_Ordered"] == "1",
]

As earlier than, a situation handed into loc needs to 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]

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

df.loc[FILTER_CONDITIONS]  
# would not work -> you possibly can't simply cross an inventory into loc

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

If you wish to see what it appears like in a pocket book and see what it appears like to mix some strings utilizing the scale back perform, do this:

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 perform cumulatively to the weather current in an iterable, or in our case run the lambda perform over the gadgets 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.accommodates("Los Angeles") & df["Quantity_Ordered"] == "1"

Lastly, let’s add the checklist of situations to create a ultimate 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 an alternative of specifying the precise column to rely on, we will merely name the dimension technique 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 characterize one row once we counted on it);
2. There are just a few alternative ways to do the ORDER BY in Pandas- a technique is to easily name sort_values and cross ascending=False to kind on descending order.

In the event you wished to make use of the earlier syntax for aggregating the info it might appear like this:

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

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

If as a substitute, you wished to output a DataFrame, you possibly can name the reset_index technique on the collection 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’ve got it! All of the elements of a full SQL question however lastly written in Pandas. A number 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 consumer can modify them over time);
• Transfer the logic to mix the checklist of columns for a filter situation to its personal perform so the tip consumer 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”.