Comparison with SQL

Pandas - Comparison with SQL

Many data analysts and scientists come to Pandas with a background in SQL. This section provides examples of how common SQL operations can be performed using Pandas, helping to bridge the gap between the two paradigms.

We’ll use the classic ‘tips’ dataset for demonstration.

import pandas as pd

# URL for the tips dataset (common example dataset)
url = 'https://raw.githubusercontent.com/pandas-dev/pandas/main/pandas/tests/io/data/csv/tips.csv'

# Read the CSV file into a Pandas DataFrame
tips = pd.read_csv(url)

# Display the first few rows
print("First 5 rows of the tips dataset:")
print(tips.head())

Output:

First 5 rows of the tips dataset:
   total_bill   tip     sex smoker  day    time  size
0       16.99  1.01  Female     No  Sun  Dinner     2
1       10.34  1.66    Male     No  Sun  Dinner     3
2       21.01  3.50    Male     No  Sun  Dinner     3
3       23.68  3.31    Male     No  Sun  Dinner     2
4       24.59  3.61  Female     No  Sun  Dinner     4

SELECT (Column Selection)

In SQL, you select specific columns using their names:

-- SQL Example
SELECT total_bill, tip, smoker, time
FROM tips
LIMIT 5;

In Pandas, you select columns by passing a list of column names using the [] operator:

# Pandas Equivalent
selected_columns = tips[['total_bill', 'tip', 'smoker', 'time']].head(5)
print("\nSelecting specific columns (Pandas):")
print(selected_columns)

Output:

Selecting specific columns (Pandas):
   total_bill   tip smoker    time
0       16.99  1.01     No  Dinner
1       10.34  1.66     No  Dinner
2       21.01  3.50     No  Dinner
3       23.68  3.31     No  Dinner
4       24.59  3.61     No  Dinner

To select all columns (like SQL’s SELECT *), simply use the DataFrame name without column selection: tips.head().

WHERE (Row Filtering)

In SQL, filtering rows is done using a WHERE clause:

-- SQL Example
SELECT *
FROM tips
WHERE time = 'Dinner'
LIMIT 5;

In Pandas, the most common way to filter rows is using boolean indexing. You create a boolean Series (True/False) based on your condition and pass it back to the DataFrame using [] or .loc[]:

# Pandas Equivalent (Boolean Indexing)
dinner_tips = tips[tips['time'] == 'Dinner'].head(5)
# Alternative using .loc for clarity:
# dinner_tips = tips.loc[tips['time'] == 'Dinner'].head(5)
print("\nFiltering for Dinner time (Pandas):")
print(dinner_tips)

Output:

Filtering for Dinner time (Pandas):
   total_bill   tip     sex smoker  day    time  size
0       16.99  1.01  Female     No  Sun  Dinner     2
1       10.34  1.66    Male     No  Sun  Dinner     3
2       21.01  3.50    Male     No  Sun  Dinner     3
3       23.68  3.31    Male     No  Sun  Dinner     2
4       24.59  3.61  Female     No  Sun  Dinner     4

The expression tips['time'] == 'Dinner' generates a boolean Series. Passing this Series inside tips[...] effectively selects only the rows where the condition is True.

Multiple conditions can be combined using & (AND) and | (OR), with parentheses for grouping:

-- SQL Example (Multiple Conditions)
SELECT *
FROM tips
WHERE time = 'Dinner' AND tip > 5.00;

# Pandas Equivalent (Multiple Conditions)
high_dinner_tips = tips[(tips['time'] == 'Dinner') & (tips['tip'] > 5.00)]
print("\nFiltering for Dinner time and Tip > 5 (Pandas):")
print(high_dinner_tips.head()) # Show a few examples

Output (example):

Filtering for Dinner time and Tip > 5 (Pandas):
    total_bill   tip     sex smoker  day    time  size
23        39.42  7.58    Male     No  Sat  Dinner     4
47        32.40  6.00    Male     No  Sun  Dinner     4
59        48.27  6.73    Male     No  Sat  Dinner     4
170       50.81 10.00    Male    Yes  Sat  Dinner     3
183       23.17  6.50    Male    Yes  Sun  Dinner     4

GROUP BY (Aggregation)

SQL uses GROUP BY to aggregate data within groups.

-- SQL Example (Count by group)
SELECT sex, COUNT(*)
FROM tips
GROUP BY sex;

Pandas uses the .groupby() method, often followed by an aggregation function (.size(), .count(), .sum(), .mean(), .agg(), etc.).

# Pandas Equivalent (Count by group)
count_by_sex = tips.groupby('sex').size()
# Alternative for counting non-null values per column in each group:
# count_by_sex_alt = tips.groupby('sex').count()
# Alternative for counting occurrences in a single column:
# count_by_sex_alt2 = tips['sex'].value_counts()
print("\nCount by sex (Pandas):")
print(count_by_sex)

Output:

Count by sex (Pandas):
sex
Female     87
Male      157
dtype: int64

Example: Multiple aggregations per group.

-- SQL Example (Multiple aggregations)
SELECT day, AVG(tip), COUNT(*)
FROM tips
GROUP BY day;

# Pandas Equivalent (Multiple aggregations using .agg())
agg_by_day = tips.groupby('day').agg({
    'tip': 'mean',      # Calculate mean of 'tip'
    'day': 'size'       # Count rows per group (use any column with .size)
}).rename(columns={'day': 'count'}) # Rename the size column for clarity

print("\nAverage tip and count by day (Pandas):")
print(agg_by_day)

Output:

Average tip and count by day (Pandas):
            tip  count
day
Fri   2.734737     19
Sat   2.993103     87
Sun   3.255132     76
Thur  2.771452     62

JOIN (Merging DataFrames)

SQL uses various JOIN clauses (INNER, LEFT, RIGHT, OUTER) to combine tables based on related columns.

-- SQL Example (Inner Join)
SELECT *
FROM table1
INNER JOIN table2
  ON table1.key = table2.key;

Pandas primarily uses the pd.merge() function to perform database-style joins. It offers arguments like how (‘inner’, ‘left’, ‘right’, ‘outer’) and on (column(s) to join on).

# Pandas Equivalent (Merge)
# Assume df1 and df2 are two DataFrames with a common 'key' column
# merged_df = pd.merge(df1, df2, on='key', how='inner')

# Example setup (not using tips dataset here)
df1 = pd.DataFrame({'key': ['A', 'B', 'C'], 'value1': [1, 2, 3]})
df2 = pd.DataFrame({'key': ['B', 'C', 'D'], 'value2': [4, 5, 6]})

inner_join = pd.merge(df1, df2, on='key', how='inner')
left_join = pd.merge(df1, df2, on='key', how='left')

print("\nInner Join Example (Pandas):")
print(inner_join)
print("\nLeft Join Example (Pandas):")
print(left_join)

Output:

Inner Join Example (Pandas):
  key  value1  value2
0   B       2       4
1   C       3       5

Left Join Example (Pandas):
  key  value1  value2
0   A       1     NaN
1   B       2     4.0
2   C       3     5.0

ORDER BY (Sorting)

SQL uses ORDER BY to sort results.

-- SQL Example
SELECT *
FROM tips
ORDER BY tip DESC
LIMIT 5;

Pandas uses the .sort_values() method.

# Pandas Equivalent
sorted_tips = tips.sort_values(by='tip', ascending=False).head(5)
print("\nSorting by tip descending (Pandas):")
print(sorted_tips)

Output:

Sorting by tip descending (Pandas):
     total_bill    tip     sex smoker  day    time  size
170       50.81  10.00    Male    Yes  Sat  Dinner     3
212       48.33   9.00    Male     No  Sat  Dinner     4
23        39.42   7.58    Male     No  Sat  Dinner     4
59        48.27   6.73    Male     No  Sat  Dinner     4
141       34.30   6.70    Male     No  Thur   Lunch     6

LIMIT (Selecting Top N Rows)

SQL uses LIMIT (or TOP in some dialects) to get the first N rows.

-- SQL Example
SELECT *
FROM tips
LIMIT 5;

Pandas uses the .head() method for the top N rows and .tail() for the bottom N rows.

# Pandas Equivalent
top_5_tips = tips.head(5)
print("\nSelecting top 5 rows (Pandas):")
print(top_5_tips)

Output:

Selecting top 5 rows (Pandas):
   total_bill   tip     sex smoker  day    time  size
0       16.99  1.01  Female     No  Sun  Dinner     2
1       10.34  1.66    Male     No  Sun  Dinner     3
2       21.01  3.50    Male     No  Sun  Dinner     3
3       23.68  3.31    Male     No  Sun  Dinner     2
4       24.59  3.61  Female     No  Sun  Dinner     4

This comparison covers some basic equivalences. Both SQL and Pandas offer much more advanced features, but understanding these core mappings provides a solid foundation for users transitioning between the two.