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
  • 3,396
  • 1
  • 6
  • 24

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
  • 26,119
  • 6
  • 18
  • 32
2

Use df.pivot with proper arguments

df.pivot(index=['Name'], columns=['Program'], values=['INFO1', 'INFO2', 'INFO3'])
Vishnudev Krishnadas
  • 10,679
  • 2
  • 23
  • 55