Pandas - set index values as column names

Question

Dataframe

df_player_week_goals_means is indexed by player:

                           GameWeek  Goals  Exp_Mean
Player                                              
Pierre-Emerick Aubameyang         1      1  1.000000
Pierre-Emerick Aubameyang         2      1  1.000000
Pierre-Emerick Aubameyang         3      0  0.666667
Pierre-Emerick Aubameyang         4      1  0.750000
Pierre-Emerick Aubameyang         5      2  1.000000
...                             ...    ...       ...
Sadio Mane                       23      0  0.500000
Sadio Mane                       24      0  0.478261
Sadio Mane                       25      0  0.458333
Sadio Mane                       26      1  0.480000
Sadio Mane                       27      1  0.500000

Desired output:

I would like to transform it, setting 'DateWeek' as index, dropping 'Goals' column and have each unique player as a new column, ending up with:

    Pierre-Emerick Aubameyang  Sadio Mane ...
GameWeek          
1           1.000000            0.000000
2           1.000000            0.500000
3           0.666667            0.333333
4           0.750000            0.500000
5           1.000000            0.800000
...

what I have s far is:

df_player_weekly_means = df_player_weekly_means.set_index("GameWeek").\
                                                drop('Goals', axis=1)

which prints:

          Exp_Mean
GameWeek          
1         1.000000
2         1.000000
3         0.666667
4         0.750000
5         1.000000
...
1         1.000000
2         1.000000
3         1.333333
4         1.500000
5         1.400000

Still far from my goal...

How do I do this?

BENY · Accepted Answer · 2020-05-19T01:32:31.900

2

That is more like pivot , we use unstack for this

df=df_player_weekly_means.set_index("GameWeek",append=True)['Exp_Mean'].unstack(level=0)

edited May 19 '20 at 01:32

answered May 19 '20 at 01:25

BENY

317,841
20
164
234

something wrong with `df_player_weekly_means` name, no? should it not be `df_player_week_goals_means`? – 8-Bit Borges May 19 '20 at 01:30
thank you. any good documentation on `pivot` and `unstack()`? – 8-Bit Borges May 19 '20 at 01:40
1

@8-BitBorges https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe – BENY May 19 '20 at 01:46

score 2 · Answer 2 · answered May 19 '20 at 01:31

Here's a different way using pivot_table directly. The nulls are from this being a partial dataset:

import pandas as pd

data = {
    'Player':[
        'Pierre-Emerick Aubameyang',
        'Pierre-Emerick Aubameyang',
        'Pierre-Emerick Aubameyang',
        'Pierre-Emerick Aubameyang',
        'Pierre-Emerick Aubameyang',
        'Sadio Mane',
        'Sadio Mane',
        'Sadio Mane',
        'Sadio Mane',
        'Sadio Mane',
    ],
    'GameWeek':[
        1,2,3,4,5,
        23,24,25,26,27,
    ],
    'Goals':[
        1,1,0,1,2,
        0,0,0,1,1,
    ],
    'Exp_Mean':[
        1,1,0.6,0.75,1.0,
        0.5,0.47,0.45,0.48,0.5,
    ]
}

df = pd.DataFrame(data)
df = df.set_index('Player')
print('Before')
print(df)



df = pd.pivot_table(df, index='GameWeek', columns='Player', values='Exp_Mean')
print('After')
print(df)

Pandas - set index values as column names

2 Answers2