2

Hi all so using this past link:

I am trying to consolidate columns of values into rows using groupby: 

    hp = hp[hp.columns[:]].groupby('LC_REF').apply(lambda x: ','.join(x.dropna().astype(str))) 
#what I have
    22         23        24        LC_REF
   TV        | WATCH   | HELLO  |   2C16
   SCREEN    | SOCCER  | WORLD  |   2C16
   TEST      |  HELP   |    RED |   2C17
   SEND      |PLEASE   |PARFAIT |   2C17
#desired output
22 | TV,SCREEN
23 | WATCH, SOCCER
24 | HELLO, WORLD
25 | TEST, SEND
26 | HELP,PLEASE
27 | RED, PARFAIT

Or some sort of variation where column 22,23,24 is combined and grouped by LC_REF. My current code turns all of column 22 into one row, all of column 23 into one row, etc. I am so close I can feel it!! Any help is appreciated

codeninja
  • 377
  • 2
  • 4
  • 10

1 Answers1

1

It seems you need:

df = hp.groupby('LC_REF')
       .agg(lambda x: ','.join(x.dropna().astype(str)))
       .stack()
       .rename_axis(('LC_REF','a'))
       .reset_index(name='vals')
print (df)
  LC_REF   a          vals
0   2C16  22     TV,SCREEN
1   2C16  23  WATCH,SOCCER
2   2C16  24   HELLO,WORLD
3   2C17  22     TEST,SEND
4   2C17  23   HELP,PLEASE
5   2C17  24   RED,PARFAIT
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252