Add column to dataframe based on corresponding values of two row values from another dataframe

Question

I have two data frames:

df1 = pd.DataFrame({'X1':[2,2,2,2,3,3,3,3,4,4,4,5,5,5,5], 
                'X2':[10,20,70,80,10,20,70,80,50,70,80,60,70,80,90], 
                'X3':[0,1,0,1,1,1,1,0,0,0,1,1,0,0,0]})


     X1  X2  X3
0    2  10   0
1    2  20   1
2    2  70   0
3    2  80   1
4    3  10   1
5    3  20   1
6    3  70   1
7    3  80   0
8    4  50   0
9    4  70   0
10   4  80   1
11   5  60   1
12   5  70   0
13   5  80   0
14   5  90   0

and the other one is df2:

df2 = pd.DataFrame({'X1':[2,3,3,4,5,5,5], 
                'X2':[10,20,70,50,60,70,80]})

   X1  X2
0   2  10
1   3  20
2   3  70
3   4  50
4   5  60
5   5  70
6   5  80

I need to find the corresponding X3 values for df2 based on the value of X1 and X2 in each row of df1.The results should be something like the following:

    X1  X2  X3
0   2  10   0
1   3  20   1
2   3  70   1
3   4  50   0
4   5  60   1
5   5  70   0
6   5  80   0

Chris · Accepted Answer · 2020-07-14 02:10:02Z

2

Use pandas.DataFrame.merge:

df3 = df2.merge(df1, on=["X1", "X2"])
print(df3)

Output:

   X1  X2  X3
0   2  10   0
1   3  20   1
2   3  70   1
3   4  50   0
4   5  60   1
5   5  70   0
6   5  80   0

answered Jul 14, 2020 at 2:10

Chris

29.8k3 gold badges34 silver badges56 bronze badges

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

1 Comment

Mary Pari Over a year ago

Thank you Chris. what if df1 has other columns such as X4, X5, .. and I just need df2 with X1, X2, and X3 columns.

Trenton McKinney · Accepted Answer · 2020-07-14 06:43:16Z

1

Use pandas.DataFrame.join
- If multiple values are given to the on= parameter, the other DataFrame must have a MultiIndex, so .set_index on df1.

df2 = df2.join(df1.set_index(['X1', 'X2']), on=['X1', 'X2'])

# display(df2)
   X1  X2  X3
0   2  10   0
1   3  20   1
2   3  70   1
3   4  50   0
4   5  60   1
5   5  70   0
6   5  80   0

edited Jul 14, 2020 at 6:43

answered Jul 14, 2020 at 1:39

Trenton McKinney

63.2k41 gold badges169 silver badges212 bronze badges

Collectives™ on Stack Overflow

Add column to dataframe based on corresponding values of two row values from another dataframe

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related