python transpose a dataframe and group and append new columns

user_18174007 0
Viewed 87

I'm trying to arrange a pandas dataframe that looks like this:

{'Sample': {0: 'Mark', 1: 'Mark', 2: 'Mark', 3: 'Mark', 4: 'Mark', 5: 'John', 6: 'John', 7: 'John', 8: 'John', 9: 'John', 10: 'Lisa', 11: 'Lisa', 12: 'Lisa', 13: 'Lisa', 14: 'Lisa'}, 'CO': {0: 3, 1: 3, 2: 2, 3: 4, 4: 5, 5: 10, 6: 11, 7: 11, 8: 14, 9: 11, 10: 23, 11: 25, 12: 26, 13: 23, 14: 45}, 'Argon': {0: 7, 1: 8, 2: 9, 3: 5, 4: 7, 5: 45, 6: 65, 7: 45, 8: 67, 9: 43, 10: 34, 11: 78, 12: 79, 13: 87, 14: 67}, 'Carbon': {0: 35, 1: 43, 2: 78, 3: 54, 4: 34, 5: 56, 6: 58, 7: 50, 8: 53, 9: 55, 10: 87, 11: 77, 12: 88, 13: 89, 14: 85}}
Sample CO Argon Carbon
Mark 3 7 35
Mark 3 8 43
Mark 2 9 78
Mark 4 5 54
Mark 5 7 34
John 10 45 56
John 11 65 58
John 11 45 50
John 14 67 53
John 11 43 55
Lisa 23 34 87
Lisa 25 78 77
Lisa 26 79 88
Lisa 23 87 89
Lisa 45 67 85

To this:

{'Chemical': {0: 'Argon', 1: 'Argon', 2: 'Argon', 3: 'Argon', 4: 'Argon', 5: 'Carbon', 6: 'Carbon', 7: 'Carbon', 8: 'Carbon', 9: 'Carbon', 10: 'CO', 11: 'CO', 12: 'CO', 13: 'CO', 14: 'CO'}, 'Mark': {0: 7, 1: 8, 2: 9, 3: 5, 4: 7, 5: 35, 6: 43, 7: 78, 8: 54, 9: 34, 10: 3, 11: 3, 12: 2, 13: 4, 14: 5}, 'John': {0: 45, 1: 65, 2: 45, 3: 67, 4: 43, 5: 56, 6: 58, 7: 50, 8: 53, 9: 55, 10: 10, 11: 11, 12: 11, 13: 14, 14: 11}, 'Lisa': {0: 34, 1: 78, 2: 79, 3: 87, 4: 67, 5: 87, 6: 77, 7: 88, 8: 89, 9: 85, 10: 23, 11: 25, 12: 26, 13: 23, 14: 45}}
Chemical Mark John Lisa
Argon 7 45 34
Argon 8 65 78
Argon 9 45 79
Argon 5 67 87
Argon 7 43 67
Carbon 35 56 87
Carbon 43 58 77
Carbon 78 50 88
Carbon 54 53 89
Carbon 34 55 85
CO 3 10 23
CO 3 11 25
CO 2 11 26
CO 4 14 23
CO 5 11 45

The data is body measurements of three people, and each chemical was measured 5 times per person

It looks like a simple case of transposing, but not surprisingly, it does exactly what it says on the tin! I've also tried melting the dataframe and groupby, but I'm really not getting anywhere with it. Any help would be most welcome.

Thank you!

pandas python
3 Answers

Use melt with pivot_table in one-liner version:

>>> df.melt('Sample', var_name='Chemical') \
      .assign(Idx=lambda x: x.groupby('Sample').cumcount()) \
      .pivot_table('value', ['Idx', 'Chemical'], 'Sample') \
      .droplevel(0).rename_axis(columns=None).reset_index()

   Chemical  John  Lisa  Mark
0        CO    10    23     3
1        CO    11    25     3
2        CO    11    26     2
3        CO    14    23     4
4        CO    11    45     5
5     Argon    45    34     7
6     Argon    65    78     8
7     Argon    45    79     9
8     Argon    67    87     5
9     Argon    43    67     7
10   Carbon    56    87    35
11   Carbon    58    77    43
12   Carbon    50    88    78
13   Carbon    53    89    54
14   Carbon    55    85    34

You can execute all steps independently to see the transformation:

>>> out = df.melt('Sample', var_name='Chemical')
>>> out = out.assign(Idx=lambda x: x.groupby('Sample').cumcount())
>>> out = out.pivot_table('value', ['Idx', 'Chemical'], 'Sample')
>>> out = out.droplevel(0).rename_axis(columns=None).reset_index()
user_152399510

Try:

dfm = df.melt('Sample', var_name='Chemical')
dfm.set_index([dfm.groupby('Sample').cumcount(), 'Chemical', 'Sample']).unstack()['value']

Output:

Sample       John  Lisa  Mark
   Chemical                  
0  CO          10    23     3
1  CO          11    25     3
2  CO          11    26     2
3  CO          14    23     4
4  CO          11    45     5
5  Argon       45    34     7
6  Argon       65    78     8
7  Argon       45    79     9
8  Argon       67    87     5
9  Argon       43    67     7
10 Carbon      56    87    35
11 Carbon      58    77    43
12 Carbon      50    88    78
13 Carbon      53    89    54
14 Carbon      55    85    34
user_63615310 
a = a.set_index("Sample")
pd.concat([a.loc[nm].unstack().rename(nm).droplevel("Sample")
           for nm in a.index.unique()], axis=1)
#         Mark  John  Lisa
# CO         3    10    23
# CO         3    11    25
# CO         2    11    26
# CO         4    14    23
# CO         5    11    45
# Argon      7    45    34
# Argon      8    65    78
# Argon      9    45    79
# Argon      5    67    87
# Argon      7    43    67
# Carbon    35    56    87
# Carbon    43    58    77
# Carbon    78    50    88
# Carbon    54    53    89
# Carbon    34    55    85
user_71289340
Related