Search and match based on two conditions

Viewed 103

I am using the code below to make a search on a .csv file and match a column in both files and grab a different column I want and add it as a new column. However, I am trying to make the match based on two columns instead of one. Is there a way to do this?

import pandas as pd
df1 = pd.read_csv("matchone.csv")
df2 = pd.read_csv("comingfrom.csv")

def lookup_prod(ip):
    for row in df2.itertuples():
        if ip in row[1]:
            return row[3]
    else:
        return '0'

df1['want'] = df1['name'].apply(lookup_prod)

df1[df1.want != '0']
print(df1)
#df1.to_csv('file_name.csv')

The code above makes a search from the column name 'samename' in both files and gets the colum I request ([3]) from the df2. I want to make the code make a match for both column 'name' and another column 'price' and only if both columns in both df1 and df2 matches then the code takes the value on ([3]).

df 1 :

name price value
a     10    35
b     10    21
c     10    33
d     10    20
e     10    88

df 2 :
name price want
a     10   123
b     5    222
c     10   944
d     10   104
e     5    213

When the code is run (asking for want column from d2, based on both if df1 name = df2 name) the produced result is :

name price value want
a     10    35   123
b     10    21   222
c     10    33   944
d     10    20   104
e     10    88   213

However, what I want is if both df1 name = df2 name and df1 price = df2 price, then take the column df2 want so desired result is:

name price value want
a     10    35   123
b     10    21    0
c     10    33   944
d     10    20   104
e     10    88    0
1 Answers

You need to use pandas.DataFrame.merge() method with multiple keys:

df1.merge(df2, on=['name','price'], how='left').fillna(0)

Method represents missing values as NaNs, so that the column's dtype changes to float64 but you can change it back after filling the missed values with 0.

Also please be aware that duplicated combinations of name and price in df2 will appear several times in the result.

Related