与 SQL 的比较
Pandas - 与 SQL 的对比
Section titled “Pandas - 与 SQL 的对比”许多数据分析师和科学家在接触 Pandas 之前已经有 SQL 背景。本节通过示例展示如何在 Pandas 中执行常见的 SQL 操作，帮助弥合这两种范式之间的差距。
我们将使用经典的 ‘tips’ 数据集进行演示。
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 DataFrametips = pd.read_csv(url)
# Display the first few rowsprint("First 5 rows of the tips dataset:")print(tips.head())输出：
First 5 rows of the tips dataset: total_bill tip sex smoker day time size0 16.99 1.01 Female No Sun Dinner 21 10.34 1.66 Male No Sun Dinner 32 21.01 3.50 Male No Sun Dinner 33 23.68 3.31 Male No Sun Dinner 24 24.59 3.61 Female No Sun Dinner 4SELECT（列选择）
Section titled “SELECT（列选择）”在 SQL 中，你使用列名选择特定列：
-- SQL ExampleSELECT total_bill, tip, smoker, timeFROM tipsLIMIT 5;在 Pandas 中，你使用 [] 运算符传入一个列名列表来选择列：
# Pandas Equivalentselected_columns = tips[['total_bill', 'tip', 'smoker', 'time']].head(5)print("\nSelecting specific columns (Pandas):")print(selected_columns)输出：
Selecting specific columns (Pandas): total_bill tip smoker time0 16.99 1.01 No Dinner1 10.34 1.66 No Dinner2 21.01 3.50 No Dinner3 23.68 3.31 No Dinner4 24.59 3.61 No Dinner要选择所有列（类似于 SQL 的 SELECT *），只需使用 DataFrame 名称而不进行列选择：tips.head()。
WHERE（行过滤）
Section titled “WHERE（行过滤）”在 SQL 中，使用 WHERE 子句过滤行：
-- SQL ExampleSELECT *FROM tipsWHERE time = 'Dinner'LIMIT 5;在 Pandas 中，过滤行的最常用方法是使用布尔索引（boolean indexing）。你基于条件创建一个布尔 Series（True/False），然后使用 [] 或 .loc[] 将其传回给 DataFrame：
# 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)输出：
Filtering for Dinner time (Pandas): total_bill tip sex smoker day time size0 16.99 1.01 Female No Sun Dinner 21 10.34 1.66 Male No Sun Dinner 32 21.01 3.50 Male No Sun Dinner 33 23.68 3.31 Male No Sun Dinner 24 24.59 3.61 Female No Sun Dinner 4表达式 tips['time'] == 'Dinner' 生成一个布尔 Series。将此 Series 传给 tips[...] 可以有效地只选择条件为 True 的行。
可以使用 &（AND）和 |（OR）组合多个条件，并使用括号进行分组：
-- SQL Example (Multiple Conditions)SELECT *FROM tipsWHERE 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输出（示例）：
Filtering for Dinner time and Tip > 5 (Pandas): total_bill tip sex smoker day time size23 39.42 7.58 Male No Sat Dinner 447 32.40 6.00 Male No Sun Dinner 459 48.27 6.73 Male No Sat Dinner 4170 50.81 10.00 Male Yes Sat Dinner 3183 23.17 6.50 Male Yes Sun Dinner 4GROUP BY（聚合）
Section titled “GROUP BY（聚合）”SQL 使用 GROUP BY 对组内数据进行聚合。
-- SQL Example (Count by group)SELECT sex, COUNT(*)FROM tipsGROUP BY sex;Pandas 使用 .groupby() 方法，通常后跟一个聚合函数（.size()、.count()、.sum()、.mean()、.agg() 等）。
# 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)输出：
Count by sex (Pandas):sexFemale 87Male 157dtype: int64示例：每组执行多个聚合。
-- SQL Example (Multiple aggregations)SELECT day, AVG(tip), COUNT(*)FROM tipsGROUP 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)输出：
Average tip and count by day (Pandas): tip countdayFri 2.734737 19Sat 2.993103 87Sun 3.255132 76Thur 2.771452 62JOIN（合并 DataFrame）
Section titled “JOIN（合并 DataFrame）”SQL 使用各种 JOIN 子句（INNER, LEFT, RIGHT, OUTER）基于相关列组合表。
-- SQL Example (Inner Join)SELECT *FROM table1INNER JOIN table2 ON table1.key = table2.key;Pandas 主要使用 pd.merge() 函数来执行数据库风格的连接操作。它提供了 how（‘inner’、‘left’、‘right’、‘outer’）和 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)输出：
Inner Join Example (Pandas): key value1 value20 B 2 41 C 3 5
Left Join Example (Pandas): key value1 value20 A 1 NaN1 B 2 4.02 C 3 5.0ORDER BY（排序）
Section titled “ORDER BY（排序）”SQL 使用 ORDER BY 对结果进行排序。
-- SQL ExampleSELECT *FROM tipsORDER BY tip DESCLIMIT 5;Pandas 使用 .sort_values() 方法。
# Pandas Equivalentsorted_tips = tips.sort_values(by='tip', ascending=False).head(5)print("\nSorting by tip descending (Pandas):")print(sorted_tips)输出：
Sorting by tip descending (Pandas): total_bill tip sex smoker day time size170 50.81 10.00 Male Yes Sat Dinner 3212 48.33 9.00 Male No Sat Dinner 423 39.42 7.58 Male No Sat Dinner 459 48.27 6.73 Male No Sat Dinner 4141 34.30 6.70 Male No Thur Lunch 6LIMIT（选择前 N 行）
Section titled “LIMIT（选择前 N 行）”SQL 使用 LIMIT（或某些方言中的 TOP）获取前 N 行。
-- SQL ExampleSELECT *FROM tipsLIMIT 5;Pandas 使用 .head() 方法获取前 N 行，.tail() 方法获取后 N 行。
# Pandas Equivalenttop_5_tips = tips.head(5)print("\nSelecting top 5 rows (Pandas):")print(top_5_tips)输出：
Selecting top 5 rows (Pandas): total_bill tip sex smoker day time size0 16.99 1.01 Female No Sun Dinner 21 10.34 1.66 Male No Sun Dinner 32 21.01 3.50 Male No Sun Dinner 33 23.68 3.31 Male No Sun Dinner 24 24.59 3.61 Female No Sun Dinner 4此对比涵盖了一些基本的对应关系。SQL 和 Pandas 都提供了更高级的功能，但理解这些核心映射可以为在两者之间转换的用户提供坚实的基础。