I have a pandas dataframe in python, with the following structure:
| Date | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 2022-01-01 10:00:00 | 1 | 3 | 0 | 4 | 1 | 0 | 0 | 1 |
| 2022-01-01 11:00:00 | 0 | 1 | 1 | 2 | 0 | 2 | 3 | 5 |
| 2022-01-01 12:00:00 | 0 | 2 | 0 | 1 | 0 | 1 | 0 | 0 |
| 2022-01-01 13:00:00 | 0 | 0 | 2 | 0 | 0 | 3 | 1 | 2 |
| 2022-01-02 10:00:00 | 0 | 3 | 0 | 1 | 2 | 4 | 4 | 1 |
| 2022-01-02 11:00:00 | 0 | 0 | 6 | 0 | 0 | 0 | 5 | 0 |
| 2022-01-02 12:00:00 | 0 | 0 | 6 | 0 | 2 | 4 | 5 | 3 |
| 2022-01-02 13:00:00 | 0 | 0 | 0 | 1 | 3 | 0 | 0 | 0 |
This is a snippet of my real dataframe, which has lots of "letter" columns (more than 100). My problem is that I need to obtain the sum of the numbers for each datetime and letter for different combinations.
This was answered in All possible combinations as new columns of a dataframe based on primary column, but it computes all possible combinations from N to M elements. For a df of 100 columns, all possible combinations from 1 sigle letter column to the full combined 100 is madness and impossible to compute.
Fortunately, I am only interested in certain combinations (again, ca. 50 in the real df), which are given in a list:
list_possibilities = ['A+B', 'A+B+D', 'B+D+E+G', 'F+H', 'D+E+F+G+H', 'D', 'F', 'G+H']
As you can see, single columns such as "D" or "F" may also be a possibility. So the final df would be fairly compact, and wouldn't need a brute-force computation of thousands of combinations (here shown only for the first two rows for simplicity):
| Date | A+B | A+B+D | B+D+E+G | F+H | D+E+F+G+H | D | F | G+H |
|---|---|---|---|---|---|---|---|---|
| 2022-01-01 10:00:00 | 4 | 8 | 8 | 1 | 6 | 4 | 0 | 1 |
| 2022-01-01 11:00:00 | 1 | 3 | 6 | 7 | 12 | 2 | 2 | 8 |
Knowing the prior structure of the combinations allows to exponentially decrease the combinations and sums pandas must do, but I am unable to generalize the previous code of the solution to this case. Any ideas?
