Mapping Data from one table to another?

I have a table which has primary sector as one column with different entries. I need to add one more column as major sector. Major sector is to be picked up from a mapping table. How this task can achieved.

Sample Data

Primary Sector    Major Sector
Skating
Painting
Engineer
Running
Gardening
Administrator
tennis
Reading
Cricket
Accountant

Mapping Table

                 Job     Hobby         Sports
Skating            0         0              1
Painting           0         1              0
Engineer           1         0              0
Running            0         0              1
Gardening          0         1              0
Administrator      1         0              0
tennis             0         0              1
Reading            0         1              0
Cricket            0         0              1
Accountant         1         0              0

1 answer

  • answered 2018-11-08 08:05 Sandeep Kadapa

    Use map with idxmax using parameter axis=1 for column-wise as:

    df1['Major Sector'] = df1['Primary Sector'].map(df2.idxmax(axis=1))
    
    print(df1)
      Primary Sector Major Sector
    0        Skating       Sports
    1       Painting        Hobby
    2       Engineer          Job
    3        Running       Sports
    4      Gardening        Hobby
    5  Administrator          Job
    6         tennis       Sports
    7        Reading        Hobby
    8        Cricket       Sports
    9     Accountant          Job
    

    print(df2.idxmax(axis=1))
    
    Skating          Sports
    Painting          Hobby
    Engineer            Job
    Running          Sports
    Gardening         Hobby
    Administrator       Job
    tennis           Sports
    Reading           Hobby
    Cricket          Sports
    Accountant          Job
    dtype: object