1

I have the following types of data. How to combine rows by id, and the type record a separate column (all types 10) (or record types, separated by commas in a single column)

id,type,value
1,8,value1
1,2,value1
1,7,value1
1,3,value1
1,10,value1
2,3,value1
2,8,value1
2,7,value1

desired output:

 id        type    value 
0   1  8,2,7,3,10  value1 
1   2       3,8,7  value1
Ekaterina
  • 305
  • 1
  • 4
  • 10

1 Answers1

4

I think you can use groupby with apply join, but first convert int to str:

df = df.groupby('id')['type'].apply(lambda x: ','.join(x.astype(str))).reset_index()
print (df)
   id        type
0   1  8,2,7,3,10
1   2       3,8,7
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • as a group when there are three columns (the third has the same value) - one group, to collect in the second list and a third leave? @jezrael – Ekaterina Jan 27 '17 at 08:45
  • I am not sure if understand. Can you post new question with sample and desired output? – jezrael Jan 27 '17 at 09:05
  • `value1` is same? – jezrael Jan 27 '17 at 09:30
  • 1
    If yes, add column to `groupby` like `df = df.groupby(['id', 'value'])['type'].apply(lambda x: ','.join(x.astype(str))).reset_index()` – jezrael Jan 27 '17 at 09:31
  • what to do if there are different values? @jezrael – Ekaterina Jan 27 '17 at 09:35
  • Then need aggregate by `agg` - e.g. `df.groupby('id').agg({'type': lambda x: ','.join(x.astype(str)), 'value': 'mean'}).reset_index()` – jezrael Jan 27 '17 at 09:47
  • 'mean' - Only for numeric values @jezrael – Ekaterina Jan 27 '17 at 10:01
  • 1
    There are strings? Then it is possible use `df.groupby('id').agg({'type': lambda x: ','.join(x.astype(str)), 'value': lambda x: ','.join(x)}).reset_index()` or `df.groupby('id').agg({'type': lambda x: ','.join(x.astype(str)), 'value': 'first'}).reset_index()` – jezrael Jan 27 '17 at 10:03
  • the second option is suitable, thank you very much @jezrael – Ekaterina Jan 27 '17 at 10:08
  • Super. Btw, in future better is create new question as edited old, becasue now my old answer not matched with new edited question ;) – jezrael Jan 27 '17 at 10:09