Compare 2 columns in same excel sheet in pandas

0 votes

I want to compare the two columns in an excel sheet. names of the columns, such as file 1 and file 2. Want to add a third column with the name "diff" using the Excel formula "countifs" utilizing these two columns. If the entries in the file 1 and file 2 columns match, the diff column value should be 0 (Zero), otherwise, it should be 1. But the outcome is not what I expected. Could you please assist anyone?

here is my inputs :

   file_1    file_2 
    G          G
    A          B
    C          F
    E          H
    A          C
    H          E

Output Dataframe :

    file_1       file_2       diff 
     G          G              0
     A          B              1
     C          F              1
     E          H              0
     A          C              0
     H          E              0

Sample code :

     df = pd.read_excel('file1.xlsx')
     df1 = df[df['file_1'].isin(df['file_2'])]
     df['diff'] = df1
     print(df)
Dec 16, 2022 in Others by Kithuzzz
• 38,010 points
2,091 views

1 answer to this question.

0 votes

Try this:

import pandas as pd
import numpy as np
df['diff'] = np.where(df['file_1'] == df['file_2'] , '0', '1')
answered Dec 16, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Want to compare two columns in excel

Hello To compare two columns in excel ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
856 views
0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,420 points
1,136 views
0 votes
1 answer

Excel:- Comparing 2 columns and then copying value in 3rd column

There is an excel if() function for ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,420 points
200 views
0 votes
1 answer

Excel: Is it possible to reorder the data in 2 columns to match up if they have a certain number of characters / a string in common?

Try this: =LET(files,A1:A4, URLs,B1:B4, f,BYROW(files,LAMBDA(r,TEX ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,420 points
262 views
0 votes
1 answer

Convert column in excel date format (DDDDD.tttt) to datetime using pandas

Given # s = df['date'] s 0 ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,420 points
2,746 views
0 votes
1 answer

How to freeze the top row and the first column using XlsxWriter?

You can use worksheet.freeze_panes() to achieve this . There ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,420 points
1,739 views
0 votes
1 answer

Export DataFrame timedelta column to timestamp Excel column

The reason that the column format isn't ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,420 points
1,058 views
0 votes
1 answer
0 votes
1 answer

Create charts in excel sheet [ in same sheet along with data C#]

Unfortunately, it's not that simple. Use the ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,420 points
716 views
0 votes
1 answer

How to save a new sheet in an existing excel file, using Pandas?

import pandas as pd import numpy as np path ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 63,420 points
6,347 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP