I have Pandas DataFrame that looks like:
id a b c col
1 a 1 2 Null 'aa'
2 a 2 2 3 'aa'
3 b 4 3 1 'bb'
4 c 1 Null 3 'gg'
5 c Null 2 Null 'gg'
I want to groupby the columns to get the following:
id new_col col
1 a [1, 2, 2, 2, 3] 'aa'
2 b [4, 3, 1] 'bb'
3 c [1, 3, 2] 'gg'
Is it possible to do it using pd.groupby?
Thanks
You can use df.melt with groupby+agg:
final = (df.replace('Null',np.nan).melt(['id','col'],value_name='new_col').groupby('id'
,as_index=False).agg({'new_col':lambda x: x.dropna().tolist(),'col':'first'}))
Or stack first with set_index then groupby+agg
final1 = (df.replace('Null',np.nan).set_index(['id','col']).stack().rename('new_col')
.reset_index('col').groupby(level=0).agg({'new_col':list,'col':'first'}))
id new_col col
0 a [1, 2, 2, 2, 3] 'aa'
1 b [4, 3, 1] 'bb'
2 c [1, 2, 3] 'gg'
Use GroupBy.apply with DataFrame.stack by all columns without specified in list by Index.difference:
df = df.replace('Null', np.nan)
c = df.columns.difference(['id','col'])
f = lambda x: x.stack().tolist()
df = df.groupby(['id','col'])[c].apply(f).reset_index(name='new_col')[['id','new_col','col']]
print (df)
id new_col col
0 a [1, 2, 2, 2, 3] 'aa'
1 b [4, 3, 1] 'bb'
2 c [1, 3, 2] 'gg'
df["d"] = df[['a', 'b', 'c']].values.tolist()
dup = df.groupby(['id','col'])['d'].sum().reset_index(name='new_col')
