Use pandas dataframe column values to pivot other columns

Question

I have the following dataframe which I want to reshape:

  dir  hour  board_sign  pass
   1    5      d          294
   1    5      u          342
   1    6      d          1368
   1    6      u          1268
   1    7      d          3880
   1    7      u          3817

What I want to do is to use the values from "board_sign" as new columns which will include the values from "pass" column so that the dataframe will look as this:

  dir   hour     d     u
  1      5     294    342
  1      6     1368   1268
  1      7     3880   3817

I already tried several functions as melt pivot stack and unstack but it seems non of them give the wanted result, I also tried the pivot_table but it make is difficult to iterate since the multi index.

It's seems like an easy operation but I just cant get it right. Is there any other function I can use for this?

Thanks.

df.set_index(['dir','hour','board_sign'])['pass'].unstack() — BENY
– BENY, Commented Nov 2, 2018 at 15:55
Also df.pivot_table(columns='board_sign',index=['dir','hour'],values='pass') — Bharath M Shetty
– Bharath M Shetty, Commented Nov 2, 2018 at 16:00

Franco Piccolo · Accepted Answer · 2018-11-02 17:24:18Z

4

Use pivot_table:

df = df.pivot_table(index=['dir', 'hour'], columns='board_sign', values='pass').reset_index()
del df.columns.name

df
    dir hour    d   u
0   1     5   294   342
1   1     6   1368  1268
2   1     7   3880  3817

answered Nov 2, 2018 at 17:24

Franco Piccolo

7,4909 gold badges42 silver badges57 bronze badges

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

2 Comments

Orweisman Over a year ago

i tried the pivot_table option at first and it worked. the problem was that i was difficult to use the output because the mulitiindex in the pivot table.

Franco Piccolo Over a year ago

with reset_index() the multiindex is gone

Collectives™ on Stack Overflow

Use pandas dataframe column values to pivot other columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related