# -*- coding: utf-8 -*-
"""第 8 章《Pandas 数据处理与分析》示例代码 · 模块 7：聚合统计与数据透视表

配套页面：https://www.qixiangsun.com.cn/python-bigdata-2026-lesson8
运行环境：Python 3.14+ / pandas 3.0.5（pandas 2.x 亦可运行，个别输出格式略有差异）
说明：本模块：描述统计 · value_counts · groupby + agg · 结果整形 · pd.cut 分箱 · resample · pivot_table。
      页面中每段代码都独立可运行，本文件按页面顺序排列；
      页面上标注“故意报错”的示例未收录（运行会中断），请到页面查看。
用法：python 示例代码_模块7_聚合与透视表.py
"""
import pandas as pd

# ===== [k7-desc-basic] 例 8-28（上）：整列求和 / 计数 / 最值 / 平均 / 标准差 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('订购总金额：', round(df['金额'].sum(), 2))
print('订单记录数（非空）：', df['订单编号'].count())
print('最大的数量与金额：')
print(df[['数量', '金额']].max())
print('平均金额：', round(df['金额'].mean(), 2))
print('金额标准差：', round(df['金额'].std(), 2))


# ===== [k7-desc-cond] 例 8-28（下）：条件子集统计 + describe() 一次看 8 个指标 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('休闲鞋的订购总数量：', df[df.商品名称 == '休闲鞋']['数量'].sum())
print('金额达到 3000 的订单数：', df[df.金额 >= 3000]['订单编号'].count())
print('金额达到 3000 的金额合计：', df[df.金额 >= 3000]['金额'].sum())
print('运动服的平均单价：', round(df[df.商品名称 == '运动服']['单价'].mean(), 2))
print('三个金额列的 describe：')
print(df[['单价', '数量', '金额']].describe().round(2))


# ===== [k7-freq] value_counts 与 mode：各商品有多少条记录、谁最多 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('各商品的订单记录数：')
print(df['商品名称'].value_counts())
print('各商品的记录占比：')
print(df['商品名称'].value_counts(normalize=True).round(2))
print('出现次数最多的商品（可能并列）：')
print(df['商品名称'].mode())


# ===== [k7-nunique] nunique：数“有几个不同的值”，dropna 决定 NaN 算不算一类 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('商品种类数：', df['商品名称'].nunique())
print('商品种类数（含 NaN 一类）：', df['商品名称'].nunique(dropna=False))
print('下单用户数：', df['用户ID'].nunique())
print('有订单的日期数：', df['订单日期'].nunique())
print('订单编号去重后有几个：', df['订单编号'].nunique())


# ===== [k7-groupby-single] 例 8-29：每种商品的订购总金额，按金额降序排 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

df1 = df.groupby(by='商品名称', as_index=False).agg({'金额': 'sum'})
print('分组结果（as_index=False，商品名称还是普通列）：')
print(df1)
print('按金额降序：')
print(df1.sort_values('金额', ascending=False))


# ===== [k7-groupby-multi] 例 8-32：每周金额的最小值 / 最大值 + 数量合计（agg 传字典和列表） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])
df['周次'] = df['订单日期'].dt.isocalendar().week.astype(int)

print('每周金额 min / max，数量 sum（不同列不同函数）：')
print(df.groupby('周次').agg({'金额': ['min', 'max'], '数量': 'sum'}))
print('每周订单条数与金额合计（同一列也给两个函数）：')
print(df.groupby('周次').agg({'订单编号': 'count', '金额': 'sum'}))


# ===== [k7-groupby-week] 例 8-31 / 8-34：每周各商品的订购总数量与总金额（按两列分组） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])
df['周次'] = df['订单日期'].dt.isocalendar().week.astype(int)

g = df.groupby(['周次', '商品名称'], as_index=False).agg({'数量': 'sum', '金额': 'sum'})
print('每周各商品：')
print(g.sort_values(['周次', '金额'], ascending=[True, False]))


# ===== [k7-groupby-reset] 整形三步：reset_index → 改列名 → sort_values，以及有名聚合的写法 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

g = df.groupby('商品名称').agg({'数量': 'sum', '金额': 'sum'})
print('① 默认：商品名称 变成了索引')
print(g)
g2 = g.reset_index()
g2.columns = ['商品名称', '订购总数量', '订购总金额']
print('② 索引变回列、列名改成中文、按金额降序：')
print(g2.sort_values('订购总金额', ascending=False))
g3 = df.groupby('商品名称', as_index=False).agg(
    订购总数量=('数量', 'sum'), 订购总金额=('金额', 'sum'))
print('③ 有名聚合，一步到位：')
print(g3.head(3))


# ===== [k7-cut-bins] 例 8-35（上）：按指定间距划分金额等级，统计各等级数量（right=False） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

daily = df.groupby('订单日期').agg({'金额': 'sum'})
daily.columns = ['总金额']
bins = [0, 400, 1000, 3200, 6000]              # 指定分区间距
labels = ['L1', 'L2', 'L3', 'L4']               # 四个等级标签
daily['等级'] = pd.cut(daily['总金额'], bins=bins, labels=labels, right=False)
print(daily)
print('各等级的订单天数：')
print(daily['等级'].value_counts().sort_index())


# ===== [k7-cut-auto] 例 8-35（下）：bins 传整数 = 自动等宽分级，labels 自己命名 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

daily = df.groupby('订单日期').agg({'金额': 'sum'})
daily.columns = ['总金额']
daily['等级'] = pd.cut(daily['总金额'], bins=4, right=False,
                      labels=['低', '中', '中高', '高'])
print('自动分级的边界：', daily['等级'].cat.categories.tolist())
print(daily[['总金额', '等级']])
print('各等级天数：')
print(daily['等级'].value_counts().sort_index())


# ===== [k7-resample] 例 8-36：按 7 天一个周期重采样，统计每个周期的总数量与总金额 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

week = df.resample('7D', on='订单日期').agg({'数量': 'sum', '金额': 'sum'})
print('每 7 天一个周期：')
print(week)
print('周期起始日（就是结果的索引）：', week.index.tolist())


# ===== [k7-resample-m] resample('M') 在 pandas 3 里报错：捕获信息看提示，再改成 'ME' =====
import pandas as pd

s = pd.Series([1, 2, 3],
              index=pd.date_range('2021-02-01', periods=3, freq='D'))
try:
    s.resample('M').sum()                 # 旧教材写法，pandas 3 已删除
except ValueError as e:
    print('旧写法报错：')
    print(e)
print('改成 ME（月末）之后：')
print(s.resample('ME').sum())


# ===== [k7-pivot-basic] 例 8-37：每周各商品的订购总金额，加 margins 汇总行列 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])
df['周次'] = df['订单日期'].dt.isocalendar().week.astype(int)

print('df 的金额合计：', df['金额'].sum())
pv = df.pivot_table(values='金额', index='周次', columns='商品名称',
                    aggfunc='sum', margins=True)
print(pv)


# ===== [k7-pivot-margins] 例 8-38：每天各商品的订购数量，fill_value=0，汇总行列名改为“总计” =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

pv2 = df.pivot_table(values='数量', index='订单日期', columns='商品名称',
                     aggfunc='sum', fill_value=0,
                     margins=True, margins_name='总计')
print(pv2)

