CODEWITHSUNDEEP
pythonpandasanalytics
Ashish
Ashish

Reputation: 11

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

Upvotes: 0

Views: 261

Answers (1)

Space Impact
Space Impact

Reputation: 13255

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

Upvotes: 1

Related Questions