CODEWITHSUNDEEP
pythonpandasdataframe
Husnain Iqbal
Husnain Iqbal

Reputation: 89

Conditional matching of rows by iterating in python

I have the following dfs.

df1 

Date          Dollar  EURO  GBP
12/03/2019    100      80   90
12/02/2019    101      81   89
12/01/2019    1000     79   91

df2

Product   Currency  Rate   Date
ABC        EURO      80    12/03/2019
xyz        USD       105   11/30/2019
ert        GBP       90    11/29/2019

basically what I want is to place a new column(having rate of that currency and date) in df2 and further add a column if rate of df1 and df2 matches or not. 

Product   Currency  Rate   Date          df1.rate     Check
ABC        EURO      80    12/03/2019    80           Match
xyz        USD       105   11/30/2019    N/A          Not Match
ert        GBP       90    11/29/2019    N/A          Not Match

I have tried .

USD = df2['Currency'] == "US $"
GBP = df2['Currency'] == "GBP"
EURO = df2['Currency'] == "EURO"

if USD:
    df2['Check'] = df2['rate'] == df1['Dollar']
elif GBP:
    df2['Check'] = df2['rate'] == df1['GBP']
elif EURO:
    df2['Check'] = df2['rate'] == df1['EURO']

it gives the following error on line 1. 

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Please suggest a remedy.

Upvotes: 0

Views: 126

Answers (1)

jezrael
jezrael

Reputation: 862541

Use DataFrame.melt with DataFrame.merge with left join and indicator parameter, last set values by numpy.where:

df = df1.melt('Date', var_name='Currency', value_name='Rate')

df2 = df2.merge(df, how='left', indicator='Check')
mask = df2['Check'].eq('both')
df2['Check'] = np.where(mask, 'Match','Not Match')
print (df2)
  Product Currency  Rate        Date      Check
0     ABC     EURO    80  12/03/2019      Match
1     xyz      USD   105  11/30/2019  Not Match
2     ert      GBP    90  11/29/2019  Not Match

If need also column df1.rate add DataFrame.insert:

df2.insert(len(df2.columns)-1, 'df1.rate', df2['Rate'].where(mask))
print (df2)
  Product Currency  Rate        Date  df1.rate      Check
0     ABC     EURO    80  12/03/2019      80.0      Match
1     xyz      USD   105  11/30/2019       NaN  Not Match
2     ert      GBP    90  11/29/2019       NaN  Not Match

Explanation with details: Reshape values of df1 for same format of data like df2 by melt: 

print (df1.melt('Date', var_name='Currency', value_name='Rate'))
         Date Currency  Rate
0  12/03/2019   Dollar   100
1  12/02/2019   Dollar   101
2  12/01/2019   Dollar  1000
3  12/03/2019     EURO    80
4  12/02/2019     EURO    81
5  12/01/2019     EURO    79
6  12/03/2019      GBP    90
7  12/02/2019      GBP    89

Then merge with left join, indicator parameter create new column with info, if matching in both or left DataFrames:

print (df2.merge(df, how='left', indicator='Check'))
  Product Currency  Rate        Date      Check
0     ABC     EURO    80  12/03/2019       both
1     xyz      USD   105  11/30/2019  left_only
2     ert      GBP    90  11/29/2019  left_only

Last replace values by mask:

df2['Check'] = np.where(mask, 'Match','Not Match')
print (df2)
  Product Currency  Rate        Date      Check
0     ABC     EURO    80  12/03/2019      Match
1     xyz      USD   105  11/30/2019  Not Match
2     ert      GBP    90  11/29/2019  Not Match

Upvotes: 2

Related Questions