CODEWITHSUNDEEP
pythonshell
ltps
ltps

Reputation: 37

merge the two files

I have two files in this format

1.txt

1233445555,
4333333322,
12223344343
22333444337
33443445555

2.txt

42202123456,
42202234567,
42203234568,
42204356789,

what I want is to to take the position of first column in file 2 by comparing the first column of file 1, if the first string in column 1 of file 2 is found in file 1, output should give the position of that row in file1

from my awk command i was able to sort the file as per the column 1 of 2.csv, but not able to find the position of each row

awk -F, 'FNR==NR {a[$1]=$0; next}; $1 in a {print a[$1]}' 1.csv  2.csv  > 3.txt

cat 3.csv
38202123456
48202234567
672032345682
76204356789
88205443456

Upvotes: 0

Views: 57

Answers (2)

funk
funk

Reputation: 2287

Please find below a solution to the initial problem based on the Python's index method.

# Reading  the CSV files
with open( '1.csv' ) as f1:
    rows_1 = f1.read().split('\n')

with open( '2.csv' ) as f2:
    rows_2 = f2.read().split('\n')

# Extracting the colmuns of each file
for i in xrange( len( rows_1) ):
    rows_1[i] = rows_1[i].split(',')

# ...note that from the second CSV file we need only the first column
for i in xrange( len( rows_2) ):
    rows_2[i] = rows_2[i].split(',')[0]

# Comparing the data
res = []
for r in rows_1:
    try:
        res.append( r[0] + ',' + r[1] +
                   ' -position ' + str( rows_2.index( r[0] )+1 ) )
    except:
        pass

# Saving the result in a new file
with open('3.csv', 'w') as f3:
    for r in res:
        f3.write( r+'\n' )

Upvotes: 0

Jean-François Fabre
Jean-François Fabre

Reputation: 140168

First create a dictionary key => row index from second file, using a dictionary comprehension, and indexes starting at 1.

Then open file 1 and lookup for the key in file 2. If found write data & position, using writerows and a generator comprehension as arguments, so performance is maximized.

import csv

# create row => index dictionary
with open("file2.csv") as f2:
    # we only need first row
    # so we discard the rest using *_ special syntax when unpacking rows
    d = {first_cell:i+1 for i,(first_cell,*_) in enumerate(csv.reader(f2))}

# write output
with open("file1.csv") as f1, open("3.csv","w",newline='') as f3:
    csv.writer(f3).writerows([k,"{} -position {}".format(v,d[k])] for k,v in csv.reader(f1) if k in d)

note: python 2 users should replace:

  • {first_cell:i+1 for i,(first_cell,*_) in enumerate(csv.reader(f2))} by {row[0]:i+1 for i,row in enumerate(csv.reader(f2))}
  • open("3.csv","w",newline='') by open("3.csv","wb")

Upvotes: 1

Related Questions