CODEWITHSUNDEEP
pythonpandasdataframe
lurknobserve
lurknobserve

Reputation: 230

Combining rows of a dataframe with string columns

I think this is a simple one, but I am not able to figure this out today and needed some help.

I have a pandas dataframe:

df = pd.DataFrame({
    'id': [0, 0, 1, 1, 2],
    'q.name':['A'] * 3 + ['B'] * 2,
    'q.value':['A1','A2','A3','B1','B2'],
    'w.name':['Q', 'W', 'E', 'R', 'Q'],
    'w.value':['B1','B2','C3','C1','D2']
})

that looks like this

    id  q.name  q.value w.name  w.value
0   0   A   A1  Q   B1
1   0   A   A2  W   B2
2   1   A   A3  E   C3
3   1   B   B1  R   C1
4   2   B   B2  Q   D2

I am looking to convert it to 

    id  q.name  q.value w.name  w.value
0   0   A A     A1 A2   Q W     B1 B2
1   1   A B     A3 B1   E R     C3 C1
2   2   B       B2      Q       D2

I tried pd.DataFrame(df.apply(lambda s: s.str.cat(sep=" "))) but that did not give me the result I wanted. I have done this before but I'm struggling to recall or find any post on SO to help me.

Update: I should have mentioned this before: Is there a way of doing this without specifying which column? The DataFrame changes based on context.

I have also updated the dataframe and shown an id field, as I just realised that this was possible. I think now a groupby on the id field should solve this.

Upvotes: 2

Views: 1156

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210842

UPDATE:

In [117]: df.groupby('id', as_index=False).agg(' '.join)
Out[117]:
   id q.name q.value w.name w.value
0   0    A A   A1 A2    Q W   B1 B2
1   1    A B   A3 B1    E R   C3 C1
2   2      B      B2      Q      D2

Old answer:

In [106]: df.groupby('category', as_index=False).agg(' '.join)
Out[106]:
  category      name
0        A  A1 A2 A3
1        B     B1 B2

Upvotes: 2

Related Questions