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 are able to dive proper into it by trying 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 must do is name the DataFrame in Pandas to return the entire desk and all its columns.

You might also wish to simply take a look at a small subset of your desk as a fast test earlier than writing a extra difficult question. In SQL, you’d use LIMIT 10 or one thing much like get solely a choose variety of rows. In Pandas, equally, you may 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 circumstances to filter the desk inside a WHERE clause in SQL. For instance, if you happen to’d need all rows within the desk with none null values within the Order_ID column, the SQL would seem like this:

SELECT * FROM df WHERE Order_ID IS NOT NULL

In Pandas, you could 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 evaluate to the primary output above). Each choices will return a desk with out the null values, however they work barely in another 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 wish to drop nulls from.

Alternatively, the loc technique helps you to cross a masks or boolean label you may specify to filter the DataFrame. Right here, we cross df["Order_ID"].notna(), which if you happen to 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. 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 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 out a particular subset of columns, you may cross an inventory of the column names into the DataFrame in Pandas. You may also outline the record individually like this for readability:

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

Assigning an inventory of goal columns you could then cross right into a DataFrame could make working with a desk over time when you must make adjustments in your code a bit of simpler. For instance, you possibly can have a operate return the columns you want as an inventory, or append and take away columns to the record as wanted relying on what sort of output the consumer wants.

The GROUP BY in SQL and Pandas

We are able to 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 properly. 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 seem like this:

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

In Pandas, it will seem 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 record of columns) that you just wish to group the DataFrame on;
2. Move the title of the column in sq. brackets on the uncooked grouped DataFrame;
3. Name the depend (or every other combination) technique to carry out the aggregation on the DataFrame for the goal column.

For higher readability, we are able to 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 now we have many of the elements of an entire SQL question, let’s check out a extra difficult one and see what it will seem 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 circumstances 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 circumstances once we name the loc technique, let’s as a substitute outline an inventory of circumstances 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 ought to be a Pandas masks that evaluates to both true or false. It’s potential to cross a number of circumstances to loc, however the syntax ought to seem like this:

df.loc[condition_1 & condition_2 & condition_3]

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

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

You’ll get an error if you happen to attempt the above as a result of every situation ought to be separated by the & operator for “and” circumstances (or the | operator if you happen to want “or” circumstances). As an alternative, we are able to write some fast code to return the circumstances within the right format. We’ll make use of the functools.cut back technique to place the circumstances collectively.

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

cut 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 circumstances, we are able to write this as a substitute (with out the string formatting and simply utilizing our outlined record of circumstances within the FILTER_CONDITIONS variable).

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

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

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

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

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

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

1. As an alternative of specifying the particular column to depend on, we are able to merely name the measurement technique which can 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 a couple of other ways to do the ORDER BY in Pandas- a method is to easily name sort_values and cross ascending=False to kind on descending order.

For those who wished to make use of the earlier syntax for aggregating the information it will seem 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 identical 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 may name the reset_index technique 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 now we have it! All of the elements of a full SQL question however lastly written in Pandas. A few of the issues we are able to 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 features (so that you or a consumer can modify them over time);
• Transfer the logic to mix the record of columns for a filter situation to its personal operate so the tip consumer doesn’t must be confused over what the cut back logic is doing;
• After passing reset_index we are able to rename the output column (or columns if we’re aggregating on a number of) for readability, for instance to “Count_Order_ID”.