Python Pandas - How to compare values from two columns of a dataframe to another Dataframe columns?

Question

I have two dataframes which I need to compare between two columns based on condition and print the output. For example:

df1:

| ID    | Date      | value  |
| 248   | 2021-10-30| 4.5    |
| 249   | 2021-09-21| 5.0    |
| 100   | 2021-02-01| 3,2    |

df2:

| ID    | Date      | value  |
| 245   | 2021-12-14| 4.5    |
| 246   | 2021-09-21| 5.0    |
| 247   | 2021-10-30| 3,2    |
| 248   | 2021-10-30| 3,1    |
| 249   | 2021-10-30| 2,2    |
| 250   | 2021-10-30| 6,3    |
| 251   | 2021-10-30| 9,1    |
| 252   | 2021-10-30| 2,0    |

I want to write a code which compares ID column and date column between two dataframes is having a conditions like below,

if "ID and date is matching from df1 to df2": print(df1['compare'] = 'Both matching')
if "ID is matching and date is not matching from df1 to df2" : print(df1['compare'] = 'Date not matching')
if "ID is Not matching from df1 to df2" : print(df1['compare'] = 'ID not available')

My result df1 should look like below:

df1 (expected result):

| ID    | Date      | value  | compare
| 248   | 2021-10-30| 4.5    | Both matching
| 249   | 2021-09-21| 5.0    | Id matching - Date not matching
| 100   | 2021-02-01| 3,2    | Id not available

how to do this with Python pandas dataframe?

@Ashutosh sharma, No, it doesn't. Df1 will have less rows and df2 will have around 1000 rows — Ben Hardy
– Ben Hardy, Commented Mar 13, 2022 at 8:36
@Amirhossein Kiani, I wanted to add another column in df1 and add the values saying whether the values are matching are not. — Ben Hardy
– Ben Hardy, Commented Mar 13, 2022 at 8:38

TheFaultInOurStars · Accepted Answer · 2022-03-13 08:47:45Z

4

What I suggest you do is to use iterrows. It might not be the best idea, but still can solve your problem:

compareColumn = []
for index, row in df1.iterrows():
  df2Row = df2[df2["ID"] == row["ID"]]
  if df2Row.shape[0] == 0:
    compareColumn.append("ID not available")
  else:
    check = False
    for jndex, row2 in df2Row.iterrows():
      if row2["Date"] == row["Date"]:
        compareColumn.append("Both matching")
        check = True
        break
    if check == False:
      compareColumn.append("Date not matching")
df1["compare"] = compareColumn
df1

Output

	ID	Date	value	compare
0	248	2021-10-30	4.5	Both matching
1	249	2021-09-21	5	Date not matching
2	100	2021-02-01	3.2	ID not available

answered Mar 13, 2022 at 8:47

TheFaultInOurStars

3,6331 gold badge13 silver badges30 bronze badges

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

4 Comments

Ben Hardy Over a year ago

One question - How can we get the values of the index of df2 for " both matching " rows ?

TheFaultInOurStars Over a year ago

@BenHardy you can simply use: df[df1["compare"] == "Both matching"]

Ben Hardy Over a year ago

@ Amirhossein Kiani - I want to return the index from df2 . Lets say if the index of both matching row in df2 is 5, I want to return the value as 5

TheFaultInOurStars Over a year ago

@BenHardy Thanks for your comment, Ben. If you are sure your dataframe has an index, you can use: df.index = df.index.set_names(['my_index']) and then df.reset_index(inplace=True), then you can simply use: df1[df1["compare"] == "Both matching"]["my_index"]

SergFSM · Accepted Answer · 2022-03-13 11:13:09Z

2

suppose 'ID' column is the index, then we can do like this:

def f(x):
    if x.name in df2.index:
        return 'Both matching' if x['Date']==df2.loc[x.name,'Date'] else 'Date not matching'
    return 'ID not available'

df1 = df1.assign(compare=df1.apply(f,1))

print(df1)

           Date value            compare
ID                                      
248  2021-10-30   4.5      Both matching
249  2021-09-21   5.0  Date not matching
100  2021-02-01   3,2   ID not available

answered Mar 13, 2022 at 11:13

SergFSM

1,4991 gold badge6 silver badges9 bronze badges

1 Comment

Ben Hardy Over a year ago

thanks for the solution, ID column is not index since I will be having same ID repeated in some rows

Collectives™ on Stack Overflow

Python Pandas - How to compare values from two columns of a dataframe to another Dataframe columns?

2 Answers 2

Output

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Output

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related