Add a column to a dataframe by looking up values in another dataframe-CodePudding

Consider these two dataframes:

index = [0, 1, 2, 3]
columns = ['col0', 'col1']
data = [['A', 'D'],
        ['B', 'E'],
        ['C', 'F'],
        ['A', 'D']
       ]
df1 = pd.DataFrame(data, index, columns)

df2 = pd.DataFrame(data = [10, 20, 30, 40], index = pd.MultiIndex.from_tuples([('A', 'D'), ('B', 'E'), ('C', 'F'), ('X', 'Z')]), columns = ['col2'])

I want to add a column to df1 that tells me the value from looking at df2. The expected result would be like this:

index = [0, 1, 2, 3]
columns = ['col0', 'col1', 'col2']
data = [['A', 'D', 10],
        ['B', 'E', 20],
        ['C', 'F', 30],
        ['A', 'D', 10]
       ]
df3 = pd.DataFrame(data, index, columns)

What is the best way to achieve this? I am wondering if it should be done with a vector operation or a lambda operation or perhaps something even simpler, but I'm unsure.

CodePudding user response：

Merge normally:

pd.merge(df1, df2, left_on=["col0", "col1"], right_index=True, how="left")

Output:

  col0 col1  col2
0    A    D    10
1    B    E    20
2    C    F    30
3    A    D    10

CodePudding user response：

try this:

indexes = list(map(tuple, df1.values))
df1["col2"] = df2.loc[indexes].values

Output:

#print(df1)
  col0 col1 col2
0    A    D   10
1    B    E   20
2    C    F   30
3    A    D   10