Pandas pivot table with prefix to columns

Question

I have a dataframe:

df = C1 A1. A2. A3. Type
     A   1.   5. 2.   AG
     A   7.   3. 8.   SC

And I want to create:

df = C1 A1_AG A1_SC A2_AG A2_SC
     A   1.    7.   5.     3
 

How can it be done?

Answer 1

You can rather use a melt and transpose:

(df.melt('Type')
   .assign(col=lambda d: d['Type']+'_'+d['variable'])
   .set_index('col')[['value']].T
)

Output:

col    AG_A1  SC_A1  AG_A2  SC_A2  AG_A3  SC_A3
value      1      7      5      3      2      8

with additional columns(s):

(df.melt(['C1', 'Type'])
   .assign(col=lambda d: d['Type']+'_'+d['variable'])
   .pivot(index=['C1'], columns='col', values='value')
   .reset_index()
)

Output:

col C1  AG_A1  AG_A2  AG_A3  SC_A1  SC_A2  SC_A3
0    A      1      5      2      7      3      8