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

Question

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 dictionary and then map or perhaps something simpler. I'm unsure.

The answer I have accepted is a specific application of one of the functions that is covered in that article, yes. — ric
– ric, Commented Feb 21, 2022 at 10:59

ddejohn · Accepted Answer · 2022-02-18 18:49:36Z

2

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

edited Feb 18, 2022 at 18:49

answered Feb 18, 2022 at 18:44

ddejohn

9,0043 gold badges21 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

woblob · Accepted Answer · 2022-02-18 18:43:53Z

1

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

answered Feb 18, 2022 at 18:43

woblob

1,37710 silver badges13 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related