如何分组数据并将Revenue为0行的Cost汇总至同组非0Revenue行
2026-6-20
数据处理需求
- 分组依据：除
date列外的所有列（dim_channel_group、dim_channel、dim_hotel_name、dim_market） - 处理规则：
- 仅保留Revenue不为0的行
- 将同组中位于目标行之前的所有Revenue为0的行的Cost值，累加到目标行的Cost中
- 删除所有Revenue为0的行
原始数据结构
| date | dim_channel_group | dim_channel | dim_hotel_name | dim_market | Cost | Revenue |
|---|---|---|---|---|---|---|
| 10/10/2023 | Social | Snapchat | Hotel A | KSA | 28.5 | 0 |
| 11/10/2023 | Social | Snapchat | Hotel A | KSA | 108 | 0 |
| 12/10/2023 | Social | Snapchat | Hotel A | KSA | 126.7 | 0 |
| 13/10/2023 | Social | Snapchat | Hotel A | KSA | 156.1 | 8195.8 |
| 14/10/2023 | Social | Snapchat | Hotel A | KSA | 120.9 | 0 |
| 15/10/2023 | Social | Snapchat | Hotel A | KSA | 115.4 | 0 |
| 16/10/2023 | Social | Snapchat | Hotel A | KSA | 114.6 | 2352.4 |
| 10/01/2023 | Search | Hotel B | Australia | 34.1 | 0 | |
| 11/01/2023 | Search | Hotel B | Australia | 42.7 | 0 | |
| 12/01/2023 | Search | Hotel B | Australia | 93.6 | 0 | |
| 13/01/2023 | Search | Hotel B | Australia | 29.3 | 3500 | |
| 14/01/2023 | Search | Hotel B | Australia | 44.8 | 0 | |
| 15/01/2023 | Search | Hotel B | Australia | 88.6 | 0 | |
| 16/01/2023 | Search | Hotel B | Australia | 35.5 | 0 | |
| 17/01/2023 | Search | Hotel B | Australia | 81.9 | 7500 |
期望数据结构
| date | dim_channel_group | dim_channel | dim_hotel_name | dim_market | Cost | Revenue |
|---|---|---|---|---|---|---|
| 13/10/2023 | Social | Snapchat | Hotel A | KSA | 419.3 | 8195.8 |
| 16/10/2023 | Social | Snapchat | Hotel A | KSA | 350.9 | 2352.4 |
| 13/01/2023 | Search | Hotel B | Australia | 199.7 | 3500 | |
| 17/01/2023 | Search | Hotel B | Australia | 250.8 | 7500 |
内容的提问来源于stack exchange，提问作者Ibrahim Ayoup
相关产品推荐
相关产品推荐
