Collapse two rows into 1 in Pandas

Question

I am trying to collapse the segments into one row from two while country will remain distinct in pandas. I have about 3000 different products with different segments in different countries.

Initial table -

Product   Segment     Country    Company      Market
Product A Segment 1   US         Company A    Market A
Product A Segment 2   US         Company A    Market A
Product A Segment 1   UK         Company A    Market A 
Product A Segment 2   UK         Company A    Market A
Product B Segment 5   US         Company B    Market B
Product B Segment 6   US         Company B    Market B
Product B Segment 7   US         Company B    Market B 
Product B Segment 5   UK         Company B    Market B
Product B Segment 6   UK         Company B    Market B
Product B Segment 7   UK         Company B    Market B

Want to collapse the segment and keep the country distinct.

This is the result I am looking out for -

Product    Segment                          Country    Company      Market
Product A  Segment 1; Segment 2             US         Company A    Market A
Product A  Segment 1; Segment 2             UK         Company A    Market A
Product B  Segment 5; Segment 6; Segment 7  US         Company B    Market B
Product B  Segment 5; Segment 6; Segment 7  UK         Company B    Market B

Can anyone help me?

@dfundako somehow it did not work. The answer below worked. Thank you — Santoo, Sep 29 '20 at 20:47

score 2 · Accepted Answer · answered Sep 29 '20 at 20:42

2

Try groupby() and add segment to list using agg.

df.groupby(['Product', 'Country', 'Company', 'Market'])['Segment'].agg(list).reset_index()



  Product Country   Company   Market                         Segment
0  ProductA      UK  CompanyA  MarketA            [Segment1, Segment2]
1  ProductA      US  CompanyA  MarketA            [Segment1, Segment2]
2  ProductB      UK  CompanyB  MarketB  [Segment5, Segment6, Segment7]
3  ProductB      US  CompanyB  MarketB  [Segment5, Segment6, Segment7]

answered Sep 29 '20 at 20:42

wwnde

26,119
6
18
32

Thanks a lot. This works perfect. – Santoo Sep 29 '20 at 20:46
1

I was not aware. I did that now. Thanks. – Santoo Sep 29 '20 at 20:58

Collapse two rows into 1 in Pandas

1 Answers1