1

I have a dataframe such as :

Name Program INFO1 INFO2 INFO3
Dog  P1      A     B    67
Dog  P2      A     F    89
Cat  P1      L     M    - 
Bird P1      A     -    1
Bird P2      A     B    2
Bird P3      J     K    90

And I would like to transform this dataframe with only one row per Name

Name P1_INFO1 P1_INFO2 P1_INFO3 P2_INFO1 P2_INFO2 P2_INFO3 P3_INFO1 P3_INFO2 P3_INFO3
Dog  A        B        67       A        F        89       NA       NA       NA
Cat  L        M        -        NA       NA       NA       NA       NA       NA
Bird A        -        1        A        B        -        J        K        90
chippycentra
  • 2,838
  • 1
  • 5
  • 14

2 Answers2

2

Stack and unstack then collapse multiindex columns into a one level column

s=df.set_index(['Name','Program']).stack().unstack('Name').T
s.columns = [f'{a}_{b}' for a, b in s.columns]

    P1_INFO1 P1_INFO2 P1_INFO3 P2_INFO1 P2_INFO2 P2_INFO3 P3_INFO1 P3_INFO2  \
Name                                                                           
Bird        A        -        1        A        B        2        J        K   
Cat         L        M        -      NaN      NaN      NaN      NaN      NaN   
Dog         A        B       67        A        F       89      NaN      NaN   

     P3_INFO3  
Name           
Bird       90  
Cat       NaN  
Dog       NaN
wwnde
  • 22,093
  • 5
  • 13
  • 27
2

Use df.pivot with proper arguments

df.pivot(index=['Name'], columns=['Program'], values=['INFO1', 'INFO2', 'INFO3'])
Vishnudev
  • 9,498
  • 1
  • 15
  • 50