How to compare a value in csv to another csv with pandas and python?

Question

I have 2 CSV files which look something like these

SiteID  Price
A12     33
B98     48

SiteID  Price
B57     100
A12     33.5

what I am trying to do is from CSV1 look for A12 in CSV2 and if present compare the price with upto 10% difference. I tried doing this with pandas but not getting the right results. If not pandas then any other way of doing this in Python? Here is my sample code

ab = df.loc[df['SiteID'] =='A12',['price']]
co = 33
if co>=ab-ab*0.1 and co<= ab+ab*0.1:
   print("Value is correct")

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

This could be done by merge. What is your expected output? — Quang Hoang
– Quang Hoang, Commented Sep 16, 2020 at 17:02
If the value is within range just print and do nothing but if the value is out of range append the 'SiteID to a new list — Ronron
– Ronron, Commented Sep 16, 2020 at 17:13

Quang Hoang · Accepted Answer · 2020-09-16 17:30:07Z

2

Let's try this with merge and boolean indexing:

# align the two dataframes by `SiteID`
s = df1.merge(df2, on='SiteID', how='left')

# valid rows where the ratio is between [0.9, 1.1]
valid_rows = (s['Price_x']/s['Price_y']).between(0.9,1.1)

# output
out = list(s.loc[valid_rows, 'SiteID'])

Output:

['A12']

answered Sep 16, 2020 at 17:30

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

2 Comments

Ronron Over a year ago

Thank you, this worked but what if I want A12 only to be appended to list if its not in within the 10% range, example in CSV2 the value of A12 is 50 which is outside the 10% range of A12 in CSV1. I tried using NOT but that didnt work

Quang Hoang Over a year ago

Use ~valid_rows instead of valid_rows in my answer.

Yash · Accepted Answer · 2020-09-16 17:07:53Z

1

this is simply because df.loc returns a dataframe and not a value so truth value cannot be determined.

Try:

ab = df.loc[df['SiteID'] =='A12']
for idx,rows in ab.iterrows():
    co = 33
    if co>=rows["Price"]-rows["Price"]*0.1 and co<= rows["Price"]+rows["Price"]*0.1:
       print("Value is correct")

answered Sep 16, 2020 at 17:07

Yash

1,2911 gold badge7 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to compare a value in csv to another csv with pandas and python?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related