> For the complete documentation index, see [llms.txt](https://docs.coffbox.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.coffbox.com/zh/shi-yong-shi-li/pivot-table.md).

# 数据透视表

根据用户订单记录，自动生成每人每个月花费。

![](https://github.com/svcvit/excel_cursor_docs/blob/main/images/Xnip2024-09-13_20-22-10.jpg)

> 💡 统计一下每个人每个月的消费金额，每行一条用户的数据，列需要保留会员编号和姓名，之后的列展示每个月的金额

## 示例数据

| 交易时间                | 会员名称 | 会员性别 | 会员状态      | 会员编码           | 信息完整度 | 出生日期 | 可退换货数量 | 商品名称       | 商品编码     | 支付金额  | 最后购买时间     | 最后购买金额 | 来源渠道           | 标准售价  | 活跃度  | 累计购买次数 | 累计购买金额   | 订单编码     | 退货数量 | 销售净额  | 销售数量 |
| ------------------- | ---- | ---- | --------- | -------------- | ----- | ---- | ------ | ---------- | -------- | ----- | ---------- | ------ | -------------- | ----- | ---- | ------ | -------- | -------- | ---- | ----- | ---- |
| 2024-08-20 18:15:03 | 杭源垣  | 未知   | \[50]正式会员 | PP240321689842 | 0     |      | 2      | 维生素 AD 滴剂  | 12051054 | 91.60 | 2024-08-20 | 91.6   | 上海高济余天成大药房有限公司 | 45.80 | 活跃 A | 3      | 3,352    | 22859008 | 0    | 91.60 | 2    |
| 2024-08-20 18:00:26 | 宁媚如  | 男    | \[50]正式会员 | PP181127268580 | 0     |      | 1      | 甲磺酸溴隐亭片    | 22041774 | 90.50 | 2024-08-20 | 90.5   | 高济总部           | 90.50 | 活跃 A | 6      | 3,730.9  | 22858992 | 0    | 90.50 | 1    |
| 2024-08-20 15:09:14 | 蓬妤殉  | 女    | \[50]正式会员 | PP181127269347 | 0     |      | 1      | 萘敏维滴眼液     | 13186276 | 13.97 | 2024-08-20 | 30.2   | 一德泰和药房         | 13.97 | 活跃 A | 230    | 15,987.1 | 22858897 | 0    | 13.97 | 1    |
| 2024-08-20 15:09:14 | 蓬妤殉  | 女    | \[50]正式会员 | PP181127269347 | 0     |      | 1      | 复方醋酸地塞米松乳膏 | 13187858 | 16.23 | 2024-08-20 | 30.2   | 一德泰和药房         | 16.23 | 活跃 A | 230    | 15,987.1 | 22858897 | 0    | 16.23 | 1    |

## 提示词

```
统计一下每个人每个月的消费金额，每行一条用户的数据，列需要保留会员编号和姓名，之后的列展示每个月的金额
```

## AI 回复内容

首先，根据需求，我们需要完成以下步骤：

1. 将 `交易时间` 列转换为日期格式，并提取出年份和月份。
2. 按照 `会员编码`、`会员名称`、年份和月份进行分组，并计算每个用户每个月的 `支付金额` 总和。
3. 将结果转换为宽格式，以便每个月的消费金额在不同的列中展示。

接下来是生成相应的 pandas 代码：

```python
import pandas as pd

# 将 '交易时间' 列转换为日期格式
data["CRM Data"]['交易时间'] = pd.to_datetime(data["CRM Data"]['交易时间'])

# 提取年份和月份
data["CRM Data"]['年月'] = data["CRM Data"]['交易时间'].dt.to_period('M')

# 按照会员编码、会员名称和年月分组，并计算支付金额总和
monthly_spending = data["CRM Data"].groupby(['会员编码', '会员名称', '年月'])['支付金额'].sum().reset_index()

# 将数据转换为宽格式
df_output = monthly_spending.pivot_table(index=['会员编码', '会员名称'], columns='年月', values='支付金额', fill_value=0)

# 重置索引
df_output.reset_index(inplace=True)

# 输出结果
df_output
```

## 结果

| 会员编码           | 会员名称 | 2023-08 | 2023-09 | 2023-10 | 2023-11 | 2023-12 | 2024-01 | 2024-02 | 2024-03 | 2024-04 | 2024-05 | 2024-06 | 2024-07 | 2024-08 |
| -------------- | ---- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- | ------- |
| PP181127265147 | 松波炼  | 727.4   | 0       | 0       | 0       | 78.9    | 304.5   | 119.4   | 0       | 0       | 0       | 0       | 0       | 0       |
| PP181127265153 | 汤崴弋  | 0       | 0       | 0       | 0       | 0       | 0       | 0       | 0       | 0       | 130.2   | 0       | 0       | 0       |
| PP181127265173 | 殷晏   | 0       | 0       | 333.9   | 589.2   | 2502.3  | 168.9   | 183.7   | 136.1   | 105.1   | 333.8   | 33.6    | 629.9   | 5.9     |
| PP181127265187 | 齐悦珊  | 0       | 34.2    | 0       | 0       | 0       | 0       | 0       | 0       | 0       | 0       | 0       | 0       | 0       |
| PP181127265340 | 卫锟战  | 0       | 0       | 0       | 0       | 3615.3  | 0       | 0       | 0       | 0       | 0       | 0       | 0       | 0       |