Home > front end >  Possible combinations of pandas columns from a list
Possible combinations of pandas columns from a list

Time:12-22

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?

CodePudding user response:

Use concat in a dictionary comprehension:

out = (pd
   .concat({cols: df.set_index('Date')[cols.split(' ')].sum(axis=1)
            for cols in list_possibilities}, axis=1)
   .reset_index()
)

Output:

                  Date  A B  A B D  B D E G  F H  D E F G H  D  F  G H
0  2022-01-01 10:00:00    4      8        8    1          6  4  0    1
1  2022-01-01 11:00:00    1      3        6    7         12  2  2    8
2  2022-01-01 12:00:00    2      3        3    1          2  1  1    0
3  2022-01-01 13:00:00    0      0        1    5          6  0  3    3
4  2022-01-02 10:00:00    3      4       10    5         12  1  4    5
5  2022-01-02 11:00:00    0      0        5    0          5  0  0    5
6  2022-01-02 12:00:00    0      0        7    7         14  0  4    8
7  2022-01-02 13:00:00    0      1        4    0          4  1  0    0
  • Related