Combine 2 dataframes when the dataframes have different size [solved]-CodePudding

I have 2 df one is

df1 = {'col_1': [3, 2, 1, 0], 'col_2': ['a', 'b', 'c', 'd']}

df2 = {'col_1': [3, 2, 1, 3]}

I want the result as follows

df3 = {'col_1': [3, 2, 1, 3], 'col_2': ['a', 'b', 'c', 'a']}

so the column 2 of the new df is the same as the column 2 of the df1 depending on the value of the df1 I hope I made myself clear I wonder if there is any way to do that

CodePudding user response：

You can do it with merge after converting the dicts to df with pd.DataFrame():

output = pd.DataFrame(df2)
output = output.merge(pd.DataFrame(df1),on='col_1',how='left')

Or in a one-liner:

output = pd.DataFrame(df2).merge(pd.DataFrame(df1),on='col_1',how='left')

Outputs:

   col_1 col_2
0      3     a
1      2     b
2      1     c
3      3     a

CodePudding user response：

Add the new column by mapping the values from df1 after setting its first column as index:

df3 = df2.copy()
df3['col_2'] = df2['col_1'].map(df1.set_index('col_1')['col_2'])

output:

   col_1 col_2
0      3     a
1      2     b
2      1     c
3      3     a

CodePudding user response：

You can apply a function to df2['col_1'] that looks up corresponding values of each number

df3 = df2[['col_1']]
df3['col_2'] = df3['col_1'].apply(lambda x: df1.loc[df1['col_1']==x,'col_2'].values[0])

Output:

   col_1 col_2
0      3     a
1      2     b
2      1     c
3      3     a

CodePudding user response：

This could be a simple way of doing it.

# use df1 to create a lookup dictionary
lookup = df1.set_index("col_1").to_dict()["col_2"]

# look up each value from df2's "col_1" in the lookup dict
df2["col_2"] = df2["col_1"].apply(lambda d: lookup[d])