replace column values in one dataframe by values of another dataframe

Question

I have two dataframes, the first one has 1000 rows and looks like:

Date            Group         Family       Bonus 2011-06-09      tri23_1       Laavin       456 2011-07-09      hsgç_T2       Grendy       679 2011-09-10      bbbj-1Y_jn    Fantol       431 2011-11-02      hsgç_T2       Gondow       569 

The column Group has different values, sometimes repeated, but in general about 50 unique values.

The second dataframe contains all these 50 unique values (50 rows) and also the hotels, that are associated to these values:

Group             Hotel tri23_1           Jamel hsgç_T2           Frank bbbj-1Y_jn        Luxy mlkl_781          Grand Hotel vchs_94           Vancouver 

My goal is to replace the value in the column Group of the first dataframe by the corresponding values of the column Hotel of the second dataframe/or create the column Hotel with the corresponding values. When I try to make it just by assignment like

df1.loc[(df1.Group=df2.Group), 'Hotel']=df2.Hotel 

I have an error that the dataframes are not of equal size, so the comparison is not possible.

Answer 1

If you set the index to the 'Group' column on the other df then you can replace using map on your original df 'Group' column:

In [36]: df['Group'] = df['Group'].map(df1.set_index('Group')['Hotel']) df  Out[36]:          Date  Group  Family  Bonus 0  2011-06-09  Jamel  Laavin    456 1  2011-07-09  Frank  Grendy    679 2  2011-09-10   Luxy  Fantol    431 3  2011-11-02  Frank  Gondow    569 

Answer 2

You could also create a dictionary and use apply:

hotel_dict = df2.set_index('Group').to_dict() df1['Group'] = df1['Group'].apply(lambda x: hotel_dict[x])