I have this first dataFrame
df1: A B C D Car 0 Bike 0 Train 0 Plane 0 Other_1 Plane 2 Other_2 Plane 3 Other 3 Plane 4 and this other one:
df2: A B Car 4 % Bike 5 % Train 6 % Plane 7 % So I want to get this combination:
df1: A B C D Car 0 4 % Bike 0 5 % Train 0 6 % Plane 0 7 % Other_1 Plane 2 2 Other_2 Plane 3 3 Other 3 Plane 4 4 Which is the best way to do this?
If df and df2 are identically indexed, then you can use:
df['D'] = df2['B'].combine_first(df['C']) Output:
A B C D 0 Car NaN 0 4 % 1 Bike NaN 0 5 % 2 Train NaN 0 6 % 3 Plane NaN 0 7 % 4 Other_1 Plane 2 2 5 Other_2 Plane 3 3 6 Other_3 Plane 4 4 If not identically index, then you can use merge on column A:
df_out = df.merge(df2, on ='A', how='left', suffixes=('','y')) df_out.assign(D = df_out.By.fillna(df_out.C)).drop('By', axis=1) or use @piRSquared improved one-liner:
df.drop('D',1).merge(df2.rename(columns={'B':'D'}), how='left',on ='A') Output:
A B C D 0 Car NaN 0 4 % 1 Bike NaN 0 5 % 2 Train NaN 0 6 % 3 Plane NaN 0 7 % 4 Other_1 Plane 2 2 5 Other_2 Plane 3 3 6 Other_3 Plane 4 4 
df1.drop('D', 1).merge(df2.rename(columns={'B': 'D'}), how='left', on='A')mapdf1.assign(D=df1.A.map(dict(zip(df2.A, df2.B)))) A B C D 0 Car NaN 0 4 % 1 Bike NaN 0 5 % 2 Train NaN 0 6 % 3 Plane NaN 0 7 % 4 Other_1 Plane 2 NaN 5 Other_2 Plane 3 NaN 6 Other_3 Plane 4 NaN