Combining rows of a dataframe with string columns

Question

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.

Possible duplicate of pandas groupby concatenate strings in multiple columns — Zero
– Zero, Commented Aug 9, 2017 at 12:29
Can you update the question by adding another column to dataframe and its result? Since you don't want to specify the column — Bharath M Shetty
– Bharath M Shetty, Commented Aug 9, 2017 at 12:36

MaxU - stand with Ukraine · Accepted Answer · 2017-08-09 14:49:27Z

2

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

edited Aug 9, 2017 at 14:49

answered Aug 9, 2017 at 12:27

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lurknobserve Over a year ago

Hello, thank you for your answer, but I have updated the question above.

lurknobserve Over a year ago

I updated the data again, but your solution holds. Thank you for this.

Collectives™ on Stack Overflow

Combining rows of a dataframe with string columns

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related