Ggtcf

I am wondering, how to efficiently do something like groupby when I have predefined groups, and elements might belong to multiple groups at the same time.

Suppose, I have the following DataFrame:

df = pd.DataFrame('value': [0, 2, 4], index=['A', 'B', 'C'])

 value
A 0
B 2
C 4

and I have the following predefined groups, which might be overlapping and of different size:

groups = 'group 1': ['A', 'B'],
 'group 2': ['A', 'B', 'C']

Now, I want to perform a function on the DataFrame groups. For example, I want to calculate the mean of value for each group.

I was thinking to create an intermediate "expanded" DataFrame on which I could run a groupby:

intermediate_df = pd.DataFrame(columns=['id', 'group', 'value'])
intermediate_df['value'] = intermediate_df['value'].astype(float)

for group, members in groups.items():
 for id_ in members:
 row = pd.Series([id_, group, df.at[id_, 'value']],
 index=['id', 'group', 'value'])
 intermediate_df = intermediate_df.append(row, ignore_index=True)

 id group value
0 A group 1 0.0
1 B group 1 2.0
2 A group 2 0.0
3 B group 2 2.0
4 C group 2 4.0

Then, I could do

intermediate_df.groupby('group').mean()

which would give me the desired result:

 value
group 
group 1 1.0
group 2 2.0

Of course, the way I create this intermediate DataFrame is absolutely inefficient. What would be an efficient solution for my problem?

You can create your intermediate_df with Pandas.concat and a list comprehension:

intermediate_df = pd.concat([df.loc[v].assign(group=k) for k, v in groups.items()])

[OUT]

 value group
A 0 group 1
B 2 group 1
A 0 group 2
C 4 group 2

Edit try for uneven groups:

pd.DataFrame().from_dict(groups, orient='index').T.stack().map(df.squeeze()).mean(level=1)

You can do it this way also:

pd.DataFrame(groups).stack().map(df.squeeze()).mean(level=1)

Output:

group 1 1
group 2 2
dtype: int64

Building on previous answers, I use list comprehension for an intermediate_df

intermediate_df = pd.DataFrame([[group, id_] for group, members in groups.items() for id_ in members], 
 columns=['group', 'id']).merge(df, left_on='id', right_index=True)

This seems to be the fastest solution compared to the other answers:

n=10000
m=1000
df = pd.DataFrame('value': np.random.normal(size=n), index=np.arange(n).astype(str))
groups = str(i): list(df.sample(5).index) for i in range(m)

%%timeit
intermediate_df = pd.concat([df.loc[members].assign(group=group) for group, members in groups.items()])
intermediate_df.groupby('group').mean()

948 ms ± 63.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
pd.DataFrame(groups).stack().map(df.squeeze()).mean(level=1)

42.4 ms ± 183 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

%%timeit
intermediate_df = pd.DataFrame([[group, id_] for group, members in groups.items() for id_ in members], 
 columns=['group', 'id']).merge(df, left_on='id', right_index=True)
intermediate_df.groupby('group').mean()

6.13 ms ± 50.1 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)