Im trying to group all columns (except date column) I have and keep the rows where Revenue dosent equel to zero and sum all Cost columns to.
For example, the first three rows will be summed with the fourth row since their Revenue is zero. The three rows will then be dropped, and the summed Cost values will grouped be in the fourth row.
Original data shape
| 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 |
Desired data shape
| 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 |