CODEWITHSUNDEEP
pythonpandasdictionarymapping
s_khan92
s_khan92

Reputation: 979

How to map the values in first dataframe based on second dataframe?

I have two dataframes. The df1 is the main df and df2 is reference dataframe. The df1 look like this:

u1d               Status: Partisipant status         Country:Country from URL
122                           2                                4    
123                           4                                11
124                           1                                14

and df2 look like this which is a reference.

enter image description here

I want that in df1 my all values map to code for example in Status: Participant status 2 is Terminated so it should map in place of 2 as Terminated and all the other columns. I have about 2000 columns so i need also efficient way.

Upvotes: 0

Views: 81

Answers (1)

jezrael
jezrael

Reputation: 862481

Possible solution is reshape by DataFrame.melt, add new columns by DataFrame.merge and last DataFrame.pivot:

df = (df1.melt('u1d', var_name='Variable_name', value_name='Value Code')
         .merge(df2, how='left')
         .pivot('u1d','Variable_name','Value Name'))

Another idea:

for c in df1.columns[1:]:
    s = df2[df2['Variable_name'].eq(c)].set_index('Value Code')['Value Name']
    df1[c] = df1[c].map(s)

Upvotes: 1

Related Questions