How to identify several matching column values to select row and assign value from anothe table to new column with python

Question

I'm trying to use python to match values of 3 columns from a dataframe to another dataframe and get values from another column in the matching datframe. How do i loop through my dataframe to select the columns to match with the other dataframe and extract the values from the column i want? The matching conditions are columns a,b and c should be the same values.

This is my dataframe [df1]:

This is the other datframe[df2]:

This is the result I want to achieve[df3]:

Thanks.

What are your matching conditions? We need more information over here — Juan C, Aug 09 '19 at 16:49
in `df1` row 3, you've got ita-arg-sen, but in `df2` you've got ita-arg-rsa, but they match anyways in `df3`. Is that a typo ? — Juan C, Aug 09 '19 at 16:54
You should add data as text, will be easier for us to grab them an try a solution. Please be clearer about your matching conditions. "Coumn a, b, c" is too vague. Should be an exact match? To provide these info, please [edit](https://stackoverflow.com/posts/57434210/edit) your post. — Valentino, Aug 09 '19 at 17:03

Sammit · Answer 1 · 2019-08-09T17:14:10.163

1

What you actually want to do is to either join OR merge the columns. Use the following links to satisfy your query as per your required conditions, also refer to some tutorials.

new_df = df2.merge(df1, on=['a','b','c'], how='inner')
# use how='inner' when you want intersection
# use how='outer' when you want union

edited Aug 09 '19 at 17:14

answered Aug 09 '19 at 16:58

Sammit

169
1
8

Links can break, it's best to provide the contents of or summary for the links in your answer text. – ifly6 Aug 09 '19 at 17:06
made the required changes! @ifly6 – Sammit Aug 09 '19 at 17:15

score 1 · Accepted Answer · answered Aug 09 '19 at 17:05

1

It should be enough with this:

df3 = df2.merge(df1, on=['a','b','c'], how='inner')

answered Aug 09 '19 at 17:05

Juan C

5,846
2
17
51

score 0 · Answer 3 · answered Aug 09 '19 at 17:27

0

You could also do something like the following instead of merge:

dft = pd.concat([df1, df2.iloc[:, :3]])
df = df2.loc[dft[dft.duplicated()].index]

print(df)
     a    b    c    d
0  fra  chi  nga  can
1  wal  bra  rsa  usa
4  ita  arg  sen  jam

answered Aug 09 '19 at 17:27

manwithfewneeds

1,137
1
7
10

How to identify several matching column values to select row and assign value from anothe table to new column with python

3 Answers3