python join 2 files and write into new file

Question

I have a requirement like below:

input1_txt

a
b
c

input2_txt

1|2|3|a|e|f
1|3|4|b|g|h
3|2|4|c|f|h
d|f|g|i|h|j

I am trying to read both input1,input2 and writing into ouput_file

if input1 first column is equal to input2 fouth column then write all input2 to ouput_file.

Code:

read1 = csv.reader(open(input1_txt, 'r')) # read input file 1
write = csv.writer(open(output_file,"w"), delimiter="|", lineterminator="\n")  
 

for row1 in read1:
    #print(row1)
    read2 = csv.reader(open(input2_file, 'r'), delimiter="|", lineterminator="\n")
    for row2 in read2:
        #print(row2)
        if row1 == row2[4]:
            write.writerows(row2)

The expected output is as below: i..e only matched input1 first column and input2 fouth column

                  1|2|3|a|e|f
                  1|3|4|b|g|h
                  3|2|4|c|f|h

This code show how not getting any results.Please advise.

maybe first read all to memory and later use it. Reading again and again the same file in loop can be slow. — furas, Aug 06 '20 at 14:52
what is the expected output file? your description isn't clear — kaki gadol, Aug 06 '20 at 14:52
you should convert first file to list `["a", "b", "c"]` and then you can check `if row[4] in ["a", "b", "c"]` — furas, Aug 06 '20 at 14:54
You can't read the same file multiple times. Read the fields into a list, and then loop over that list as many times as you like. (Except, don't loop. Read them into a set and simply check if the fourth field is in that set.) — tripleee, Aug 06 '20 at 14:54
if you reall want to use two `for`-loops then you should use `for row1` inside `for row2` — furas, Aug 06 '20 at 14:56
is there any other better way? Can you please share the code. — SparkUser, Aug 06 '20 at 14:57
BTW: Python starts indexes at `0` so you have to use `[3]` to get fouth column. — furas, Aug 06 '20 at 15:04

score 0 · Accepted Answer · answered Aug 06 '20 at 14:59

import csv

read1 = csv.reader(open("input1.txt","r"))
read2 = csv.reader(open("input2.txt","r"),delimiter="|")

writter = csv.writer(open("output_file","w"),delimiter="|")

# this is needed because the csv iterator doesn't reset
all_row2 = [x for x in read2]

all_rows = []
for row1 in read1:
    # every row is a list
    # if you want a specific column you can do : if row1[0] in x[4]
    # you get a list of rows   
    all_rows.extend([x for x in all_row2 if row1[0] in x])
# write all at once .. faster
writter.writerows(all_rows)

This is working for only first row. Next matching rows are not writing into file. — SparkUser, Aug 06 '20 at 15:45

furas · Answer 2 · 2020-08-06T15:19:05.593

You have wrong indexes

    if row1[0] == row2[3]:

and this is all your problem

BTW: next time first use print() to see what you have in variables - it helps to see problem.

Minimal working code.

I use io.StringIO only to simulate files - this way everyone can run it.

data1 = '''a
b
c'''

data2 = '''1|2|3|a|e|f
1|3|4|b|g|h
3|2|4|c|f|h
d|f|g|i|h|j'''

import csv
import io

read1 = csv.reader(io.StringIO(data1)) # read input file 1
#read1 = csv.reader(open(input1_txt, 'r')) # read input file 1
#write = csv.writer(open(output_file,"w"), delimiter="|", lineterminator="\n")  
 

for row1 in read1:
    read2 = csv.reader(io.StringIO(data2), delimiter="|", lineterminator="\n")
    #read2 = csv.reader(open(input2_file, 'r'), delimiter="|", lineterminator="\n")
    for row2 in read2:
        if row1[0] == row2[3]:
            print('|'.join(row2))
            #write.writerows(row2)

python join 2 files and write into new file

2 Answers2