Groupby_SQL和pandas等效例子

在SQL中使用PARTITION BY与在Pandas中使用groupby().agg()有一定的相似性，因为它们都是用于分组数据并对每组应用某种聚合函数。

例一

下面将给出一个SQL的例子和等效的Pandas代码。

SQL 示例
假设我们有一个名为employees的表，包含以下列：id, department_id, salary。我们想要计算每个部门的平均工资。

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

这个查询将返回每个部门的ID和该部门的平均工资。
Pandas 示例
在Pandas中，我们可以使用groupby()方法来分组数据，然后使用agg()方法来应用聚合函数。

import pandas as pd
# 假设df是包含员工数据的DataFrame
# df = pd.read_csv('employees.csv')  # 如果数据来自CSV文件
data = {
    'id': [1, 2, 3, 4, 5],
    'department_id': [101, 102, 101, 102, 103],
    'salary': [50000, 60000, 55000, 65000, 70000]
}
df = pd.DataFrame(data)

# 使用groupby().agg()计算每个部门的平均工资
average_salary = df.groupby('department_id')['salary'].agg('mean').reset_index()
average_salary.columns = ['department_id', 'average_salary']

print(average_salary)

在这个Pandas示例中：
• groupby(‘department_id’): 根据department_id列对数据进行分组。
• [‘salary’]: 指定我们想要聚合的列。
• .agg(‘mean’): 应用平均值聚合函数。
• reset_index(): 将结果转换回常规的DataFrame格式。
• columns = [‘department_id’, ‘average_salary’]: 重命名结果DataFrame的列。
等效性
• SQL的GROUP BY department_id对应于Pandas的groupby(‘department_id’)。
• SQL的AVG(salary)对应于Pandas的.agg(‘mean’)。

说明

在Pandas中，agg()方法非常灵活，允许你一次性应用多个聚合函数，或者对不同的列应用不同的聚合函数。例如：

# 计算每个部门的平均工资和最大工资
department_stats = df.groupby('department_id').agg({
    'salary': ['mean', 'max']
}).reset_index()
# 重命名列
department_stats.columns = ['department_id', 'average_salary', 'max_salary']
print(department_stats)

在这个例子中，我们对salary列应用了两个不同的聚合函数：mean和max。

例二

我们将对三列应用三种不同的聚合函数，并对结果进行重命名。

SQL 示例
假设我们有一个名为sales的表，包含以下列：id, region_id, year, sales_amount, profit。我们想要计算每个地区的年销售总额、平均利润和最大销售额。

SELECT 
    region_id,
    SUM(sales_amount) AS total_sales,
    AVG(profit) AS average_profit,
    MAX(sales_amount) AS max_sales
FROM sales
GROUP BY region_id;

Pandas 示例
在Pandas中，我们可以使用groupby()方法来分组数据，然后使用agg()方法来应用多个聚合函数，并使用rename()方法来重命名列。

import pandas as pd
data = {
    'id': [1, 2, 3, 4, 5],
    'region_id': [101, 102, 101, 102, 101],
    'year': [2020, 2021, 2020, 2021, 2022],
    'sales_amount': [10000, 20000, 15000, 25000, 30000],
    'profit': [2000, 4000, 3000, 5000, 6000]
}
df = pd.DataFrame(data)
# 使用groupby().agg()计算每个地区的年销售总额、平均利润和最大销售额
result = df.groupby('region_id').agg({
    'sales_amount': 'sum',  # 年销售总额
    'profit': 'mean',        # 平均利润
    'sales_amount': 'max'   # 最大销售额
}).rename(columns={'sales_amount__sum': 'total_sales', 'sales_amount__max': 'max_sales'})
# 重命名利润列
result.columns = ['region_id', 'total_sales', 'average_profit', 'max_sales']
print(result)

还可以用以下方法同时重命名!!!

df.groupby("region").agg(
	total_sales=pd.NamedAgg(column="sales_amount", aggfunc="sum"),
	average_profit=pd.NamedAgg(column="profit", aggfunc="mean"), 
	max_sales=pd.NameAgg(column="sales_amount", aggfunc="max")
)

解释

1. SQL:
   • SUM(sales_amount) AS total_sales: 计算每个地区的年销售总额。
   • AVG(profit) AS average_profit: 计算每个地区的平均利润。
   • MAX(sales_amount) AS max_sales: 计算每个地区的最大销售额。
2. Pandas:
   • groupby(‘region_id’): 根据region_id列对数据进行分组。
   • .agg({‘sales_amount’: ‘sum’, ‘profit’: ‘mean’, ‘sales_amount’: ‘max’}): 对sales_amount列应用求和和最大值聚合函数，对profit列应用平均值聚合函数。
   • rename(columns={‘sales_amount__sum’: ‘total_sales’, ‘sales_amount__max’: ‘max_sales’}): 重命名列。

注意事项

• 在Pandas中，当对同一列应用多个聚合函数时，列名会被自动添加后缀（例如sales_amount__sum），需要使用rename方法进行重命名。
• 在SQL中，可以直接使用AS关键字来重命名列。