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?

0 votes
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 23 in Python by Mike
• 130 points
1,308 views
What's your logic? Post your workaround.

1 answer 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)
answered Jul 24 by Omkar
• 68,160 points
Omkar, In this example - df1 and df2 are no defined as variables.  Can you check the code?

Related Questions In Python

+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 in Python by Sagar
• 130 points
93 views
0 votes
1 answer

How to create and read from a temporary file in Python?

Hi, there is a very simple solution ...READ MORE

answered Jan 29 in Python by Nymeria
• 3,520 points
100 views
0 votes
1 answer

How can I find out the index of an element from row and column in Python?

You probably want to use np.ravel_multi_index: [code] import numpy ...READ MORE

answered Apr 16, 2018 in Python by charlie_brown
• 7,720 points
106 views
+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 6 in Python by Neha
• 330 points

edited Jul 8 by Kalgi 394 views
+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 4, 2018 in Python by Omkar
• 68,160 points

edited Aug 8, 2018 by Omkar 1,296 views