python - 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 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