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