Reorder Dataframe through transpose and setting values from other columns (Pandas)

Question

Given a dataframe:

   Location    |    Rate   |   Skill    
San Francisco     $56-$64    architect      
Albany            $43-$50    architect      
San Francisco     $23-$48     tester

I'm trying to turn that into this expected result:

   Location    |  architect    |   tester   
San Francisco      $56-$64         $23-$48 
Albany             $43-$50

I thought about transposing on column 'Skill' and then setting it's value to the value of 'Rate' , but I'm not entirely sure how this can be done.

try to set index based on Location and Skill and get value as rate df.set_index(['Location', 'Skill'])['Rate'].unstack() — Suryaveer Singh, Aug 28 '20 at 20:34

score 1 · Accepted Answer · answered Aug 28 '20 at 20:15

If you expect only one row for each Location-Skill combination

df.groupby(['Location', 'Skill']).first().unstack()

Or you can use pivot

df.pivot(index='Location', columns='Skill', values='Rate')

Notice groupby will return only the first row of each combination and pivot will fail if there is more than one row for any combination.

Reorder Dataframe through transpose and setting values from other columns (Pandas)

1 Answers1