Merge Pandas DataFrame rows into a string in one column

Question

Given this DataFrame

       r3  value
r1 r2           
1  2    3      1
   2    4      1
   3    2      1
   3    4      1
   4    2      1
   4    3      1
2  1    3      1
   1    4      1
   3    1      1
   3    4      1
   4    1      1
   4    3      1

...what's the best way to do this?

        r3     value
r1 r2           
1  2    3,4    2
   3    2,4    2
   4    2,3    2
2  1    3,4    2
   3    1,4    2
   4    1,3    2

Basically, I'm trying to condense the r3 column into a comma delimited string. The value column can be achieved a different way later on if necessary, or if it can be done through this whole process, even better.

So you want to group by (r1, r2) with r3 becoming the concatenation of all the individual r3s and value becoming the sum of all the individual values? (Your data could make this more obvious by having differing numbers of rows grouped and by using something other than 1 for some of the values). — holdenweb
– holdenweb, Commented Aug 7, 2014 at 15:21
Yes, r3 will be a concatenation. The value figure is basically a "count" column... so it would be # of rows concatenated from the r3 column. — TravisVOX
– TravisVOX, Commented Aug 7, 2014 at 15:30

ZJS · Accepted Answer · 2014-08-07 17:34:28Z

5

You could use the agg function after grouping the dataframe. if df is your dataframe use this...

strJoin = lambda x:",".join(x.astype(str))     
df.groupby(level=[0,1]).agg({"r3":strJoin,"value":np.sum})

answered Aug 7, 2014 at 17:34

ZJS

4,0812 gold badges18 silver badges23 bronze badges

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

1 Comment

TravisVOX Over a year ago

This was perfect. Thank you. As a part time, amateur coder this would have taken me a long time. Many thanks.

Collectives™ on Stack Overflow

Merge Pandas DataFrame rows into a string in one column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related