Transpose Pandas Dataframe Python

Question

I have this following dataframe:

  Status    Percentage  Value   Name    Tahun
0   X       66.666667    4.0     A      2021
1   Y       33.333333    2.0     A      2021
2   Z       0.000000     0.0     A      2021
0   X       25.000000    2.0     A      2020
1   Y       62.500000    5.0     A      2020
2   Z       12.500000    1.0     A      2020

I want to transpose the dataframe and change the column header to Status values. Ideally the output should look like

X            Y           Z          Type         Name    Tahun
66.666667    33.333333   0.000000   Percentage    A       2021 
4.0          2.0         0.0        Value         A       2021
25.000000    62.500000   12.500000  Percentage    A       2020
2.0          5.0         1.0        Value         A       2020

I tried this one:

df = df.set_index('Status').T

but I didnt get output as my expected. How can I change the rest of column names?

score 1 · Answer 1 · answered Sep 20 '21 at 06:21

stack (Percentage and Value) + unstack (Status):

(df.set_index(['Name', 'Tahun', 'Status'])
   .stack()
   .unstack(level='Status')
   .rename_axis(('Name', 'Tahun', 'Type'))
   .reset_index())

Status Name  Tahun        Type          X          Y     Z
0         A   2020  Percentage  25.000000  62.500000  12.5
1         A   2020       Value   2.000000   5.000000   1.0
2         A   2021  Percentage  66.666667  33.333333   0.0
3         A   2021       Value   4.000000   2.000000   0.0

U13-Forward · Accepted Answer · 2021-09-20T07:13:44.357

0

Or just use melt and pivot:

(df.melt(['Name', 'Tahun', 'Status'], var_name='Type')
   .pivot('value', ['Name', 'Tahun', 'Type'], 'Status')
   .reset_index()
   .rename_axis(columns=None))

  Name  Tahun        Type          X          Y     Z
0    A   2020  Percentage  25.000000  62.500000  12.5
1    A   2020       Value   2.000000   5.000000   1.0
2    A   2021  Percentage  66.666667  33.333333   0.0
3    A   2021       Value   4.000000   2.000000   0.0

This code melts the dataframe so that the Percentage and Value columns get merged and a new column Type get's created, then it pivots it so that the Status column values become columns.

If there are duplicates:

(df.melt(['Name', 'Tahun', 'Status'], var_name='Type')
   .pivot_table('value', ['Name', 'Tahun', 'Type'], 'Status')
   .reset_index()
   .rename_axis(columns=None))

Difference is that pivot_table has an aggfunc argument, default set to mean, so if there are duplicate values, it will find the average of the other values, whereas pivot doesn't have that argument.

edited Sep 20 '21 at 07:13

answered Sep 20 '21 at 06:32

U13-Forward

69,221
14
89
114

@jezrael Ah, edited my answer now – U13-Forward Sep 20 '21 at 06:48
@jezrael Then what should be `aggfunc`? – U13-Forward Sep 20 '21 at 06:50
@jezrael What? I don't really understand what you say... :) – U13-Forward Sep 20 '21 at 06:51
@jezrael What if I do `aggfunc=pd.Series`? – U13-Forward Sep 20 '21 at 06:52
@jezrael It works – U13-Forward Sep 20 '21 at 06:53
@jezrael Then with `aggfunc=lambda x: x.item() if x.size == 1 else x.tolist()` – U13-Forward Sep 20 '21 at 06:58
@jezrael Done! Hope it's good now – U13-Forward Sep 20 '21 at 07:08
@jezrael Added. – U13-Forward Sep 20 '21 at 07:13
1

Super, it is exactly what missing here. Btw, your answers are very ofteen missing explanation :( – jezrael Sep 20 '21 at 07:14

Transpose Pandas Dataframe Python

2 Answers2