Convert Rows as Column Headers

Question

I have the following dataframe:

---------------------------------------------------------------
| | TORA |  PS  | Hutan Adat | Tahun | Bulan | Dashboard Name |
---------------------------------------------------------------
|0|  0   |   0  |      0     |  2021 |  Jan  |     Potensi    |   
|1|  0   |   0  |      0     |  2021 |  Jan  |     Usulan     |   
|2|  15  |   0  |      3     |  2021 |  Jan  |     Realisasi  |   
|3|  4   |   6  |      0     |  2021 |  Feb  |     Potensi    | 
|4|  0   |   0  |      0     |  2021 |  Feb  |     Usulan     |   
---------------------------------------------------------------

I want it this way.

------------------------------------------------------------
            | Potensi | Usulan | Realisasi | Tahun | Bulan |
------------------------------------------------------------
TORA        |   0     |   0    |  15       | 2021  |  Jan  | 
PS          |   0     |   0    |   0       | 2021  |  Jan  | 
Hutan Adat  |   0     |   0    |   3       | 2021  |  Jan  | 
TORA        |   4     |   0    |  NaN      | 2021  |  Feb  | 
PS          |   6     |   0    |  NaN      | 2021  |  Feb  | 
Hutan Adat  |   0     |   0    |  NaN      | 2021  |  Feb  |
------------------------------------------------------------

I have tried: #1

df = df.set_index('Dashboard Name').T

#2

df = df.pivot_table(['TORA','PS','Hutan Adat'],['Tahun','Bulan'],'Dashboard Name')

#3

df = df.set_index(['Dashboard Name', 'Tahun', 'Bulan'], drop = True).T

but I didnt get the expected output that I want

jezrael · Accepted Answer · 2021-04-22T05:08:29.157

First set Bulan columns to ordered Categoricals, for correct sorting:

months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep',
          'Oct', 'Nov', 'Dec']

df.Bulan = pd.Categorical(df.Bulan, 
                           ordered=True, 
                           categories=months)

Then reshape by melt with pivoting:

df = (df.melt(['Tahun','Bulan','Dashboard Name'])
        .pivot(index=['variable','Tahun','Bulan'], 
                     columns='Dashboard Name', 
                     values='value')
        .sort_index(level=[1,2])
        .reset_index(level=[1,2])
        .rename_axis(index=None, columns=None)
        
        )
print (df)
            Tahun Bulan  Potensi  Realisasi  Usulan
Hutan Adat   2021   Jan      0.0        3.0     0.0
PS           2021   Jan      0.0        0.0     0.0
TORA         2021   Jan      0.0       15.0     0.0
Hutan Adat   2021   Feb      0.0        NaN     0.0
PS           2021   Feb      6.0        NaN     0.0
TORA         2021   Feb      4.0        NaN     0.0

Or:

df = (df.set_index(['Tahun','Bulan','Dashboard Name'])
        .stack()
        .unstack(level=2)
        .reset_index(level=[0,1])
        .rename_axis(index=None, columns=None)
        
        )
print (df)
            Tahun Bulan  Potensi  Realisasi  Usulan
TORA         2021   Jan      0.0       15.0     0.0
PS           2021   Jan      0.0        0.0     0.0
Hutan Adat   2021   Jan      0.0        3.0     0.0
TORA         2021   Feb      4.0        NaN     0.0
PS           2021   Feb      6.0        NaN     0.0
Hutan Adat   2021   Feb      0.0        NaN     0.0

Last if order of last columns is important:

df = df[df.columns[2:].tolist() + df.columns[:2].tolist()]
print (df)
            Potensi  Realisasi  Usulan  Tahun Bulan
Hutan Adat      0.0        3.0     0.0   2021   Jan
PS              0.0        0.0     0.0   2021   Jan
TORA            0.0       15.0     0.0   2021   Jan
Hutan Adat      0.0        NaN     0.0   2021   Feb
PS              6.0        NaN     0.0   2021   Feb
TORA            4.0        NaN     0.0   2021   Feb

I got this error ```DataError: No numeric types to aggregate``` — ohai, Apr 22 '21 at 05:01
@ohai - Can you try secod solutio with `stack` and `unstack` ? — jezrael, Apr 22 '21 at 05:03
@ohai - Or if use some latest pandas version use `pivot` only without `aggfunc`, first solution was edited. — jezrael, Apr 22 '21 at 05:09

Convert Rows as Column Headers

1 Answers1