Pandas - Group by one column and aggregate other column to list

Question

I have a dataframe that has multiple entries for users. These users can also be assigned to multiple ID's.

I would like to group by the users and then store a list of these ID's in another column as shown below:

I'd like to go from this:

df1 = pd.DataFrame({'USER': ['BOB','STEVE','PAUL','KEITH','STEVE','STEVE','BOB'],'ID':[1,2,3,4,5,6,7]})

To this. Only showing values if that user is attached to multiple ID's

Does this answer your question? [How to group dataframe rows into list in pandas groupby](https://stackoverflow.com/questions/22219004/how-to-group-dataframe-rows-into-list-in-pandas-groupby) — ScootCork, Dec 01 '20 at 15:44
I saw that earlier. Sadly not as it converts all other columns into lists — scottapotamus, Dec 01 '20 at 16:16

score 9 · Accepted Answer · answered Dec 01 '20 at 15:51

9

groupby + map

u = df1.groupby("USER")["ID"].agg(list)
df1["MULTI_IDS"] = df1["USER"].map(u[u.str.len().ge(2)])

USER      ID  MULTI_IDS
0    BOB   1     [1, 7]
1  STEVE   2  [2, 5, 6]
2   PAUL   3        NaN
3  KEITH   4        NaN
4  STEVE   5  [2, 5, 6]
5  STEVE   6  [2, 5, 6]
6    BOB   7     [1, 7]

answered Dec 01 '20 at 15:51

user3483203

50,081
9
65
94

what is 'ge(2)' doing on the answer? – Catarina Nogueira Mar 21 '23 at 10:51
ge means greater than or equal to 2. without it row 2 and 3 Paul and Keith will also show [3] and [4]. – Shirin Yavari Apr 07 '23 at 22:07

score 1 · Answer 2 · answered Jan 22 '22 at 19:09

1

Not as elegant as the previous solution but easier to interpret)

import numpy as np

df1['MULTI_IDS']=[df1.groupby("USER")["ID"].agg(list)[i] if len(df1.groupby("USER")["ID"].agg(list)[i] )>1 else np.nan for i in df1.USER]

answered Jan 22 '22 at 19:09

Victoria

247
4
8

Pandas - Group by one column and aggregate other column to list

2 Answers2

Linked