Search code examples
pythonpandasdataframetranspose

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

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.

Solution

  • 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.