15 Pandas Functions to Replicate Basic SQL Queries in Python: An AI/ML Perspective

ByJordan Brown

As an artificial intelligence and machine learning expert, proficiency in data manipulation and analysis is a crucial skill. While SQL remains the standard for working with relational databases, Python‘s Pandas library has emerged as a powerful alternative, particularly in the AI/ML community.

In this in-depth guide, we‘ll explore 15 key Pandas functions that replicate the functionality of common SQL queries. Understanding how to translate between SQL and Pandas will allow you to leverage the full potential of Python for data-driven AI/ML projects.

Why Pandas for AI/ML?

According to a 2020 Kaggle survey of over 20,000 data professionals, Pandas was the most commonly used data analysis tool, with 74% of respondents using it regularly[^1]. In contrast, only 58% used SQL.

So why is Pandas so popular among data scientists and machine learning practitioners? Here are a few key reasons:

  1. Integration with other Python libraries: Pandas seamlessly integrates with the rich ecosystem of Python libraries for AI/ML like scikit-learn, TensorFlow, PyTorch, and more[^2]. This allows you to go from data manipulation to model training and deployment in a single workflow.

  2. Richer data structures: Compared to SQL, Pandas offers more diverse and flexible data structures like Series and DataFrame. These allow you to work with heterogeneous and hierarchical data that are common in AI/ML applications[^3].

  3. Faster prototyping and iteration: Pandas‘ concise and expressive syntax makes it faster to manipulate data compared to SQL[^4]. When combined with interactive development environments like Jupyter notebooks, you can rapidly explore and visualize your data.

  4. Memory efficiency: By loading data into memory, Pandas can perform certain operations much faster than reading from a database[^5]. While this limits the size of data you can work with, modern hardware can comfortably handle datasets up to a few gigabytes.

Now let‘s dive into the 15 Pandas functions that will enable you to replicate key SQL operations in your Python workflow.

Querying Data

1. Selecting Columns with Square Brackets

The most basic way to select specific columns from a Pandas DataFrame is to use square brackets and a list of column names:

df[[‘col1‘, ‘col2‘, ‘col3‘]]

This is equivalent to the SQL query:

SELECT col1, col2, col3 FROM table;

2. Selecting Columns with df.loc

Alternatively, you can use the df.loc accessor to select columns by passing a colon to represent all rows and a list of column names:

df.loc[:, [‘col1‘, ‘col2‘, ‘col3‘]]

3. Selecting Columns by Attribute Access

For simple single-column selections, you can access a DataFrame column directly as an attribute:

df.col1

However, this only works if the column name is a valid Python identifier (i.e. contains only letters, numbers, and underscores, and doesn‘t start with a number).

Filtering Data

4. Filtering with Boolean Indexing

You can use boolean indexing to filter a DataFrame by passing a boolean Series or array:

df[df.col1 > 5]

This is equivalent to:

SELECT * FROM table WHERE col1 > 5;

5. Filtering with df.loc

Similar to selecting columns, you can use df.loc to filter rows by passing a boolean condition for the rows and a colon to represent all columns:

df.loc[df.col1 > 5, :]

6. Filtering with df.query

Starting in version 0.13, Pandas introduced the df.query() method which allows you to filter using an SQL-like syntax:

df.query(‘col1 > 5 & col2 == "A"‘)

The df.query() method also supports more complex conditions with boolean operators like and, or, and not.

Aggregating Data

7. Grouping and Aggregating with df.groupby()

Pandas‘ groupby() function replicates the functionality of SQL‘s GROUP BY clause. You can group a DataFrame by one or more columns and then apply an aggregation function:

df.groupby(‘category‘)[‘price‘].mean()

This is equivalent to:

SELECT category, AVG(price) FROM table GROUP BY category;

The groupby() method returns a DataFrameGroupBy object which you can then apply various aggregation functions to like count(), sum(), mean(), median(), min(), max(), etc.

8. Multiple Aggregations with agg()

To apply multiple aggregation functions at once, you can pass a list of functions or a dictionary mapping columns to functions to the agg() method:

df.groupby(‘category‘).agg([‘min‘, ‘max‘])
df.groupby(‘category‘).agg({‘price‘: ‘mean‘, ‘quantity‘: ‘sum‘})

This allows you to calculate multiple summary statistics in a single groupby operation.

Filtering Aggregated Data

9. Filtering Grouped Data

To filter the result of a groupby aggregation, you can wrap the aggregation in parentheses and apply a boolean condition:

df.groupby(‘category‘)[‘price‘].mean()[lambda x: x > 100]

This is equivalent to:

SELECT category, AVG(price) FROM table 
GROUP BY category
HAVING AVG(price) > 100;

Sorting Data

10. Sorting with df.sort_values()

To sort a DataFrame by one or more columns, use the df.sort_values() method:

df.sort_values(‘col1‘)
df.sort_values([‘col1‘, ‘col2‘])

By default, this sorts in ascending order. To sort in descending order, pass ascending=False:

df.sort_values(‘col1‘, ascending=False)

This is equivalent to:

SELECT * FROM table ORDER BY col1;
SELECT * FROM table ORDER BY col1, col2;  
SELECT * FROM table ORDER BY col1 DESC;

Advanced Operations

11. Windowing Functions

Pandas provides a variety of windowing functions that allow you to perform calculations across a sliding window of data. These are similar to SQL‘s analytic functions like ROW_NUMBER(), RANK(), LAG(), etc.

To use a windowing function in Pandas, call the rolling() or expanding() method on a Series or DataFrame with a window size. You can then apply an aggregation function to the window:

df[‘rolling_avg‘] = df[‘price‘].rolling(window=7).mean()

This calculates a rolling 7-day average price for each row.

12. Merge and Join Operations

Just like SQL‘s JOIN clause, Pandas allows you to combine multiple DataFrames based on a common key using the merge() and join() functions.

The pd.merge() function performs an inner join by default:

pd.merge(left, right, on=‘key‘)

To perform a left, right, or outer join, specify the how parameter:

pd.merge(left, right, on=‘key‘, how=‘left‘)

You can also join two DataFrames using the join() method:

left.join(right, on=‘key‘, how=‘inner‘)

13. MultiIndex and Reshaping

One powerful feature of Pandas is its support for hierarchical indexing using MultiIndex. This allows you to work with higher-dimensional data and perform complex reshaping operations[^6].

To create a MultiIndex, you can pass a list of arrays or tuples to the pd.MultiIndex.from_arrays() or pd.MultiIndex.from_tuples() constructor:

df.index = pd.MultiIndex.from_tuples(
    [(‘A‘, 1), (‘A‘, 2), (‘B‘, 1), (‘B‘, 2)]
)

You can then select data based on the MultiIndex using tuple notation:

df.loc[(‘A‘, 1), :]

To pivot data from long to wide format, use the df.pivot() method:

df.pivot(index=‘date‘, columns=‘item‘, values=‘price‘)

This reshapes the data so that unique values in the item column become new columns, with price values filled in accordingly.

14. Reading from SQL Databases

Pandas provides a convenient way to read data directly from SQL databases into DataFrames using the pd.read_sql() function. This accepts a SQL query string and a database connection object:

import sqlite3
conn = sqlite3.connect(‘example.db‘)

df = pd.read_sql(‘SELECT * FROM table‘, conn)

You can use this to query data from various SQL databases like SQLite, MySQL, and PostgreSQL.

15. Method Chaining

One of the most powerful features of Pandas is the ability to chain multiple methods together to perform complex data manipulations in a single line of code. This allows you to write concise and expressive data pipelines.

For example, you can combine grouping, aggregation, filtering, and sorting in one chain:

(
    df.groupby(‘category‘)
      .agg({‘price‘: ‘mean‘})
      .query(‘price > 100‘)
      .sort_values(‘price‘, ascending=False)
)

This performs the equivalent of:

SELECT category, AVG(price) AS price
FROM table
GROUP BY category  
HAVING AVG(price) > 100
ORDER BY price DESC;

Conclusion

In this guide, we‘ve covered 15 powerful Pandas functions that enable you to replicate and exceed the functionality of SQL for data manipulation and analysis. By leveraging Pandas in your Python workflows, you can streamline your data science and AI/ML projects.

While Pandas is a highly capable tool, it‘s important to understand its limitations. Pandas is not suitable for working with datasets larger than memory, and certain operations can be slow compared to a well-optimized SQL database[^7]. For production use cases with very large datasets, you‘ll likely need to use a combination of SQL and Pandas, possibly with cloud-based tools like Dask or Spark.

However, for most AI/ML projects, Pandas will provide more than enough functionality and performance. Its integration with the larger PyData ecosystem and support for advanced features like MultiIndex and method chaining make it an indispensable tool for any data professional.

To learn more, check out the following resources:

You can also find the Jupyter notebook with all the code examples from this article on my GitHub repository: https://github.com/yourusername/pandas-sql-examples

Happy data wrangling!

[^1]: Kaggle. (2020). Kaggle 2020 State of Machine Learning and Data Science Survey. https://www.kaggle.com/c/kaggle-survey-2020
[^2]: McKinney, W. (2018). Python for Data Analysis: Data Wrangling with Pandas, NumPy, and IPython. O‘Reilly Media, Inc.
[^3]: Nishimura, R. (2018). Pandas: Powerful Python Data Analysis Toolkit. https://pandas.pydata.org/docs/pandas.pdf
[^4]: Agarwal, S. (2019). Turbocharge Exploratory Data Analysis using Pandas Profiling. https://towardsdatascience.com/turbocharge-exploratory-data-analysis-using-pandas-profiling-5ceca097c38a
[^5]: Li, S. (2019). A Beginner‘s Guide to Optimizing Pandas Code for Speed. https://engineering.upside.com/a-beginners-guide-to-optimizing-pandas-code-for-speed-c09ef2c6a4d6
[^6]: Gorelick, M., & Ozsvald, I. (2020). High Performance Python: Practical Performant Programming for Humans. O‘Reilly Media, Inc.
[^7]: Koehrsen, W. (2019). 5 Reasons Why You Should Switch from Pandas to Vaex. https://towardsdatascience.com/5-reasons-why-you-should-switch-from-pandas-to-vaex-f1f1e6402399

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

You May Like to Read,

Similar Posts