I have a data-frame like this :
id value1 value2
0 1 1
1 2 3
2 1 4
3 1 5
4 2 1
i want it to be like this :
id value1 value2 count
0 1 1,4,5 3
1 2 3,1 2
Asked
Active
Viewed 1,599 times
1
Mayank Porwal
- 33,470
- 8
- 37
- 58
welu
- 329
- 3
- 12
-
What datatype is column value2 supposed to be? string? – timgeb Dec 12 '18 at 11:56
-
Also it looks like you want to group by value1, not by id. – timgeb Dec 12 '18 at 12:05
2 Answers
2
Use aggregation by agg with join and size, but is necessary converting column to strings:
tups = [('value2', lambda x: ','.join(x.astype(str))), ('count', 'size')]
df1 = df.groupby('value1')['value2'].agg(tups).reset_index()
print (df1)
value1 value2 count
0 1 1,4,5 3
1 2 3,1 2
Alternative:
tups = [('value2', ','.join), ('count', 'size')]
df1 = df['value2'].astype(str).groupby(df['value1']).agg(tups).reset_index()
jezrael
- 822,522
- 95
- 1,334
- 1,252
2
Something like this could also work:
In [2468]: df.value2 = df['value2'].apply(str)
In [2494]: res = df.groupby('value1')['value2'].apply(lambda x:','.join(x)).reset_index()
In [2498]: res['count'] = df.groupby('value1').size().reset_index()[0]
In [2499]: res
Out[2499]:
value1 value2 count
0 1 1,4,5 3
1 2 3,1 2
Mayank Porwal
- 33,470
- 8
- 37
- 58