Comparing multiple columns of two csv files and save output as matching/not matching in new csv file

Question

Suppose i have Columns in file1.csv as

Customer id    Name 

Q1             Alen
W2             Ricky
E3             Katrina
R4             Anya
T5             Leonardo

and Columns in file2.csv as

Customer id    Name

Q1             Alen
W2             Harry
E3             Katrina
R4             Anya
T5             Leonard

here as you can see for Customer id: W2 the corresponding name is not matching. so the output.csv should be like below:

Customer id  Status

Q1           Matching
W2           Not matching
E3           Matching
R4           Matching
T5           Matching

How can i get the above output using python.

P.S. whats the code for comparing multiple columns, not just column Name?

My code

import csv
with open('file1.csv', 'rt', encoding='utf-8') as csvfile1:
    csvfile1_indices = dict((r[1], i) for i, r in enumerate(csv.reader(csvfile1)))

with open('file2.csv', 'rt', encoding='utf-8') as csvfile2:
    with open('output.csv', 'w') as results:    
        reader = csv.reader(csvfile2)
        writer = csv.writer(results)

        writer.writerow(next(reader, []) + ['status'])

        for row in reader:
            index = csvfile1_indices.get(row[1])
            if index is not None:
                message = 'matching'
                writer.writerow(row + [message])

            else:
                 message = 'not matching'
                 writer.writerow(row + [message])

    results.close()

This is working fine, but can i write in any other easier way to get the same output? and what changes do i need to make to compare multiple columns?

What have you tried so far? How about just use string comparison tool like winmerge? — Circle Hsiao
– Circle Hsiao, Commented Nov 5, 2018 at 9:50
@蕭為元 You can see the code i tried. I've edited the question — pytorch
– pytorch, Commented Nov 5, 2018 at 10:08

Siliam · Accepted Answer · 2018-11-06 09:41:13Z

2

If you don't mind using Pandas, you can do it in 5 lines of code :

import pandas as pd 

# assuming id columns are identical and contain the same values
df1 = pd.read_csv('file1.csv', index_col='Customer_id')
df2 = pd.read_csv('file2.csv', index_col='Customer_id')

df3 = pd.DataFrame(columns=['status'], index=df1.index)
df3['status'] = (df1['Name'] == df2['Name']).replace([True, False], ['Matching', 'Not Matching'])

df3.to_csv('output.csv')

Edit : removed sep = '\t' to use default comma separator.

edited Nov 6, 2018 at 9:41

answered Nov 5, 2018 at 10:16

Siliam

663 bronze badges

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

2 Comments

pytorch Over a year ago

I got ValueError: Index Customer_id invalid. but changing sep='\t' to sep=',' solved the error

Siliam Over a year ago

Sorry my bad ! you can actually omit the separator argument altogether if you're using comma-separated values/

Sanjay Idpuganti · Accepted Answer · 2018-11-05 09:49:41Z

0

Read both csv files into two different dictionaries and iterate over any of the dictionary and check for the same key in other. If you want order use OrderedDict

answered Nov 5, 2018 at 9:49

Sanjay Idpuganti

3025 silver badges11 bronze badges

1 Comment

pytorch Over a year ago

python script ? @Sanjay Idpuganti

Alex · Accepted Answer · 2018-11-05 11:40:23Z

0

You can merge on multiple columns:

f1
  Customer_id      Name
0          Q1      Alen
1          W2     Ricky
2          E3   Katrina
3          R4      Anya
4          T5  Leonardo

f2
  Customer_id      Name
0          Q1      Alen
1          W2     Harry
2          E3   Katrina
3          R4      Anya
4          T5  Leonardo

m = f1.merge(f2, on=['Customer_id', 'Name'], indicator='Status', how='outer')
  Customer_id      Name      Status
0          Q1      Alen        both
1          W2     Ricky   left_only
2          E3   Katrina        both
3          R4      Anya        both
4          T5  Leonardo        both
5          W2     Harry  right_only

m['Status'] = m['Status'].map({'both': 'Matching', 
                               'left_only': 'Not matching', 
                               'right_only': 'Not matching'})

m.drop_duplicates(subset=['Customer_id', 'Status'])
m.drop(['Name'], axis=1)
  Customer_id        Status
0          Q1      Matching
1          W2  Not matching
2          E3      Matching
3          R4      Matching
4          T5      Matching

edited Nov 5, 2018 at 11:40

answered Nov 5, 2018 at 10:23

Alex

7,1654 gold badges27 silver badges43 bronze badges

2 Comments

Karn Kumar Over a year ago

@ Alex, this will not result into the desired output , otherwise its easy :)

Alex Over a year ago

@pytorch I have updated the code a little bit to make it shorter/easier to maintain.

Collectives™ on Stack Overflow

Comparing multiple columns of two csv files and save output as matching/not matching in new csv file

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related