In Python How do I read 2 CSV files compare column 1 from both and then write to a new file where the Column 1s match

–1 vote
If I have 2 csv files as follows:

csv1.csv:

104,Bob,Bob@gmail.com,baseball
189,Tom,Tom@gmail.com.football
932,Bill,Bill@gmail.com,softball

csv2.csv:

134,Tim,Tim@gmail.com,cricket
189,Tom,TomR@gmail.com,tennis
692,Rob,Rob@gmail.com,soccer

I am looking for a Python way to compare the 2 CSV files (only Column 1), and if column1 is the same in both CSV files, then write the entire row from CSV1.csv to a new CSV file.  

So in this example, the only time column 1 is the same is '189'.  So the new output.csv file would just have 1 line, and be 189,Tom,Tom@gmail.com.football

I have tried import pandas, csv, and every time I attempt this I am hitting a roadblock.
Jul 24, 2019 in Python by Mike
• 120 points
33,265 views
What's your logic? Post your workaround.

2 answers to this question.

+1 vote

Hi @Mike. First, read both the csv files and store the data in two different dataframes. Next, you will have to run a nested loop to check if the values are the same. If they are same, add that row to another dataframe and finally export the dataframe to csv. Refer to the code below:

df_temp=pd.DataFrame()
df_new=pd.DataFrame()
for time1 in df1['time']:
        for time2 in df2['time']:
            if time1==time2:
                j=(df1[df1['time']==time].index.values.astype(int)[0])
                df_temp=df1.iloc[j]
                    df_new.append(df_temp)


Hope this helps!!

If you need to know more about Python, join Python certification course today.

Thanks!

answered Jul 24, 2019 by Omkar
• 69,230 points
Omkar, In this example - df1 and df2 are no defined as variables.  Can you check the code?
You can simpy use awk command to compare b/w two csv files here

$1 represent column 1

similarly if you want want to compare multiple columns then just column number as $2$3.. and so on

awk 'BEGIN{FS=","};FNR==NR{a[$1];next};($1 in a)' csv1.csv csv2.csv | cat > result.csv
0 votes
Hi try this one

import sys

import os

checklist =[]

os.system('rm '+sys.argv[3])

with open(sys.argv[1], 'r') as t1, open(sys.argv[2], 'r') as t2:

    fileone = t1.readlines()

    filetwo = t2.readlines()

with open(sys.argv[3], 'w') as outFile:

    for line in fileone:

        checktwo=line.split(",")

        checklist.append(checktwo[4])

    #print(checklist)

    for line in filetwo:

        checkone=line.split(",")

        if checkone[4] not in checklist:

            #print(checkone[4])

            outFile.write(line)

You can run the above code by following the below method
python3 compare.py filename1 filename2 resultfile
here compare.py - is the above code
filename1 -  the first file which in which each value of particular column is taken for comparing
filename2 - the second file where need to compare for column values are same
filename3 - if column value of fileone is same in filetwo then write in filename3
in the above code column 5 is compared
answered Sep 22, 2020 by Mujibur Rahiman K

Related Questions In Python

0 votes
1 answer

How to write a code In Python where input will be 1 then the output will be 2?

You can go through this:  def num(number): ...READ MORE

answered Oct 7, 2020 in Python by Gitika
• 65,890 points
1,167 views
+1 vote
0 answers

Sum the values of column matching and not matching from a .txt file and write output to a two different files using Python

Name                                                    value DR_CNDAOFSZAPZP_GPFS_VOL.0 139264 DR_CNDAOFSZAPZP_GPFS_VOL.1 15657 DR_CNDAOFSZAPZP_GPFS_VOL.0 139264 DR_CNDAOFSZAPZP_GPFS_VOL.1 156579 DR_CNDAOFSZAPZP_GPFS_VOL.2 156579 DR_CNDAOFSZAPZP_GPFS_VOL.3 ...READ MORE

Nov 20, 2019 in Python by Sagar
• 130 points
1,133 views
0 votes
1 answer

I want to download a file from the website by web scraping. Can anyone explain how to do this in jupyter lab (python) with an example?

Hey, Web scraping is a technique to automatically ...READ MORE

answered Apr 7, 2020 in Python by Gitika
• 65,890 points
2,328 views
0 votes
1 answer

how can i extact all the links from a website using python and save it in a csv file ?

Hi, @Shubham, Web scraping is the technique to ...READ MORE

answered Jun 16, 2020 in Python by Gitika
• 65,890 points
4,056 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,354 views
0 votes
1 answer
+3 votes
2 answers

how to print array integer without [] bracket in python like result = 1,2,3,4,5

Hey @abhijmr.143, you can print array integers ...READ MORE

answered Aug 5, 2018 in Python by Omkar
• 69,230 points

edited Aug 8, 2018 by Omkar 8,050 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