Pandas groupby with dynamic group membership

user_13944809 0
Viewed 38

I want to group my dataframe df on the column axis, according to the group membership defined by dfGroups:

df
Out[87]: 
   A  B
0  1  2
1  3  4
2  5  6

Importantly, the group membership of a column changes from row to row:

dfGroups
Out[106]: 
        A       B
0  group1  group2
1  group1  group3
2  group2  group3

I can accomplish this via the following:

df.stack().groupby([pd.Grouper(level=0), dfGroups.stack()]).mean().unstack()
Out[107]: 
   group1  group2  group3
0     1.0     2.0     NaN
1     3.0     NaN     4.0
2     NaN     5.0     6.0

But it feels clunky and I'm wondering if there's a more idiomatic way of accomplishing it.

pandas-groupby pandas
1 Answers

This is another route you can take, not sure about idiomatic though :

(
    pd.concat((df.stack(), dfGroups.stack()), axis=1)
    .droplevel(-1)
    .pivot(columns=1, values=0)
)



1   group1  group2  group3
0    1.0    2.0     NaN
1    3.0    NaN     4.0
2    NaN    5.0     6.0
user_71757130
Related