CODEWITHSUNDEEP
pythonpandasdataframe
Zaman Azam
Zaman Azam

Reputation: 39

Python compare two columns of excel sheet as pair

I have workbook which contains 2 excel sheets. i want to compare two columns as pair of first excel sheet with 2 columns in a pair of second excel sheet.

Thanks

Upvotes: 0

Views: 1513

Answers (2)

Lars Skaug
Lars Skaug

Reputation: 1386

Set operations are made easier by the introduction of pandasql. Check this out:

import pandas as pd
import pandasql as ps

df1 = pd.read_excel(file, sheet_name = 'Sheet 1')
df2 = pd.read_excel(file, sheet_name = 'Sheet 2')


df_res = ps.sqldf("""select select case when df2.col1 is null 
                      then 'no match' 
                      else 'they match' end as do_they_match
                     from df1
                      left join df2
                       on df1.col1 = df2.col1
                        and df1.col2 = df2.col2 """, locals())

Upvotes: 1

Adam Zeldin
Adam Zeldin

Reputation: 908

You could try the below approach to add the first two columns in each (if they're strings they'll concatenate) and then compare values in each. This would require they be the same length, and this assumes you want to compare based on position, not inclusion (in which case you could just merge on those two columns to see what matches).

df1 = pd.read_excel(file, sheet_name = 'Sheet 1')
df2 = pd.read_excel(file, sheet_name = 'Sheet 2')

df1.iloc[:,0] + df1.iloc[:,1] == df2.iloc[:,0] + df2.iloc[:,1]

Upvotes: 0

Related Questions