pivot dataframe using columns and values

Question

I have data frame like

Year    Month   Date    X   Y
2015    5   1   0.21120733  0.17662421
2015    5   2   0.36878636  0.14629167
2015    5   3   0.27969632  0.37910569
2016    5   1   -1.2968733  8.29E-02
2016    5   2   -1.1575716  -0.20657887
2016    5   3   -1.0049003  -0.39670503
2017    5   1   -1.5630698  1.1710221
2017    5   2   -1.70889    0.93349206
2017    5   3   -1.8548334  0.86701781
2018    5   1   -7.94E-02   0.3962194
2018    5   2   -2.91E-02   0.39321879

I want to make it like

2015    2016    2017    2018
0.21120733  -1.2968733  -1.5630698  -7.94E-02
0.36878636  -1.1575716  -1.70889    -2.91E-02
0.27969632  -1.0049003  -1.8548334  NA

I tried using df.pivot(columns='Year',values='X') but the answer is not as expected

Anurag Dabas · Accepted Answer · 2021-07-06 06:50:00Z

1

Try passing index in pivot():

out=df.pivot(columns='Year',values='X',index='Date')
#If needed use:
out=out.rename_axis(index=None,columns=None)

OR

Try via agg() and dropna():

out=df.pivot(columns='Year',values='X').agg(sorted,key=pd.isnull).dropna(how='all')
#If needed use:
out.columns.names=[None]

output of out:

     2015       2016            2017     2018
0   0.211207    -1.296873   -1.563070   -0.0794
1   0.368786    -1.157572   -1.708890   -0.0291
2   0.279696    -1.004900   -1.854833    NaN

edited Jul 6, 2021 at 6:50

answered Jul 6, 2021 at 6:44

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pivot dataframe using columns and values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related