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

Question

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.

Comments

What's your logic? Post your workaround.

Answer 1

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!

Comments

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

Answer 2

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