合并Pandas groupby聚合结果并进行分组条形图可视化

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np

# 假设 day_df 是您的原始DataFrame，包含 'yr', 'season', 'weathersit', 'cnt' 等列
# 为了示例，我们创建一个模拟的 day_df
data = {
    'yr': [0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0, 1, 1, 1, 1],
    'season': [1, 1, 2, 2, 1, 1, 2, 2, 1, 1, 2, 2, 1, 1, 2, 2],
    'weathersit': [1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2],
    'cnt': [100, 150, 200, 250, 300, 350, 400, 450, 110, 160, 210, 260, 310, 360, 410, 460]
}
day_df = pd.DataFrame(data)

# 单独的聚合和绘图尝试
# day_df.groupby(by=["yr", "season", "weathersit"]).agg({"cnt": "sum"}).plot.barh()
# day_df.groupby(by=["yr", "season", "weathersit"]).agg({"cnt": "mean"}).plot.barh()

# 计算平均值并重置索引
day_mean_dataframe = day_df.groupby(by=["yr", "season", "weathersit"]).agg({"cnt": "mean"}).reset_index()

# 计算总和并重置索引
day_sum_dataframe = day_df.groupby(by=["yr", "season", "weathersit"]).agg({"cnt": "sum"}).reset_index()

# 合并数据框
# 使用 `on` 参数指定合并键，`suffixes` 参数处理同名列的后缀
merged_df = pd.merge(day_mean_dataframe, day_sum_dataframe, on=["yr", "season", "weathersit"], suffixes=('_mean', '_sum'))

print("合并后的数据框 (merged_df):")
print(merged_df)

# 创建图表和坐标轴对象
fig, ax = plt.subplots(figsize=(14, 7)) # 调整图表大小以适应更多标签

# 计算每个分组的条形图的基准位置
r1 = np.arange(len(merged_df))
width = 0.35 # 条形图的宽度

# 绘制平均值条形图
ax.bar(r1 - width/2, merged_df["cnt_mean"], width=width, label='平均值', color='skyblue')

# 绘制总和条形图，并向右偏移
ax.bar(r1 + width/2, merged_df["cnt_sum"], width=width, label='总和', color='lightcoral')

# 设置x轴刻度标签
# 将多级分组键组合成一个字符串作为标签
ax.set_xticks(r1)
ax.set_xticklabels([f'年:{row.yr}, 季:{row.season}, 天气:{row.weathersit}' for _, row in merged_df.iterrows()], rotation=45, ha='right')

# 添加图例和标题
ax.legend()
ax.set_xlabel('分组')
ax.set_ylabel('计数')
ax.set_title('各分组计数平均值与总和对比 (垂直条形图)')
plt.tight_layout() # 调整布局以避免标签重叠
plt.show()

# 创建图表和坐标轴对象
fig, ax = plt.subplots(figsize=(14, 8)) # 调整图表大小

# 计算每个分组的条形图的基准位置
r1 = np.arange(len(merged_df))
height = 0.35 # 条形图的高度

# 绘制平均值条形图
ax.barh(r1 - height/2, merged_df["cnt_mean"], height=height, label='平均值', color='skyblue')

# 绘制总和条形图，并向上偏移
ax.barh(r1 + height/2, merged_df["cnt_sum"], height=height, label='总和', color='lightcoral')

# 设置y轴刻度标签
ax.set_yticks(r1)
ax.set_yticklabels([f'年:{row.yr}, 季:{row.season}, 天气:{row.weathersit}' for _, row in merged_df.iterrows()])

# 添加图例和标题
ax.legend()
ax.set_xlabel('计数')
ax.set_ylabel('分组')
ax.set_title('各分组计数平均值与总和对比 (水平条形图)')
plt.tight_layout() # 调整布局
plt.show()