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

developer1 June 2023

0 0 6 minutes read

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 that 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 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, if you happen to’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 examine to the primary output above). Each choices will return a desk with out the null values, however they work barely in another way.

You should 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 helps 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 if you happen to 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 methodology, it as an alternative 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 an alternative of choosing all columns from the desk, let’s as an alternative 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 may as well outline the record 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 that you must make adjustments in your code somewhat simpler. For instance, you possibly can have a perform 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 information. 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 Sequence 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:

Add groupby and cross a column (or record of columns) that you just wish to group the DataFrame on;
Go the title of the column in sq. brackets on the uncooked grouped DataFrame;
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’ve a lot of the parts of a whole 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 somewhat 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 number of extra parts to this question, let’s have a look step-by-step at how we’d implement this in Pandas.

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

FILTER_CONDITIONS = [
df["Order_ID"].notna(),
df["Purchase_Address"].str.incorporates("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 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]  
# does not work -> you'll be able to'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” situations (or the | operator if you happen to want “or” situations). As an alternative, we will write some fast code to return the situations within the right format. We’ll make use of the functools.cut back methodology to place the situations collectively.

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

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 situations, we will write this as an alternative (with out the string formatting and simply utilizing our outlined record of situations within the FILTER_CONDITIONS variable).

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

What cut back does is apply a perform cumulatively to the weather current in an iterable, or in our case run the lambda perform over the objects in our FILTER_CONDITIONS record 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.incorporates("Los Angeles") & df["Quantity_Ordered"] == "1"

Lastly, let’s add the record of situations to create a last 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:

As an alternative of specifying the particular column to depend on, we will merely name the measurement methodology which can return the variety of rows within the DataFrame (as earlier than the place each Order_ID worth was distinctive and meant to signify one row once we counted on it);
There are a number of 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.

When 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"].depend()
.sort_values(ascending=False)
)

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

If as an alternative, you wished to output a DataFrame, you’ll be able to name the reset_index methodology 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 it! All of the parts 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 information 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 perform so the tip consumer doesn’t must be confused over what the cut 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”.