2

I have a csv file named eche in following format:

INCON,--,INITIAL,CONDITIONS,FOR*****,ELEMENTS,AT,TIME ,0.315570E+13
VC76,0.10000000E+00,0.2837726135782E+08,0.6756896308414E+02
1K02,0.10000000E+00,0.2837950666778E+08,0.6757007619124E+02
P476,0.10000000E+00,0.2837975332748E+08,0.6756827783643E+02
KG76,0.10000000E+00,0.2838117264779E+08,0.6756840947964E+02
1K05,0.10000000E+00,0.2610647023303E+08,0.1841342000212E+03
1K06,0.10000000E+00,0.2611103965949E+08,0.1845191988294E+03
1K07,0.10000000E+00,0.2611275589639E+08,0.1845173169920E+03
1K08,0.10000000E+00,0.2611346615238E+08,0.1846393014710E+03
VC37,0.10000000E+00,0.2611374063470E+08,0.1849489276098E+03
1K10,0.10000000E+00,0.2610224473371E+08,0.1835417139884E+03
M037,0.10000000E+00,0.2611370504845E+08,0.1854150556422E+03
KG37,0.10000000E+00,0.2611331725657E+08,0.1859451266535E+03

I have another file named eche.txt which looks like the following :

VC76,207.64,0.40,2000.00,1154.00
S876,241.00,0.40,2000.00,1154.00
P476,241.06,0.40,2000.00,1154.00
M076,263.66,0.40,2000.00,1154.00
KG76,276.73,0.40,2000.00,1154.00
KG76,284.31,0.40,2000.00,1154.00
IW76,291.11,0.40,2000.00,1154.00
IW76,297.40,0.40,2000.00,1154.00
VC37,177.33,0.21,1998.00,1284.00
S837,240.20,0.21,1998.00,1284.00
P437,241.11,0.21,1998.00,1284.00
M037,263.58,0.21,1998.00,1284.00
KG37,276.42,0.21,1998.00,1284.00
KG37,283.85,0.21,1998.00,1284.00

I would like to replace the values in column 4 of eche with the values in column 2 of eche.txt if the value in the first columns of the two files are the same but when not same I retain the line in eche file. I tried the following two scripts which works but fail to replace the value in column 4 of eche with the value in column 2 of eche.txt:

file1="eche"
file2="eche.txt"

awk -F',' 'NR==FNR{a[$2]=$3} NR>FNR{$2=a[$4];print}' OFS=' ' "$file2" "$file1" > test

perl -F',\s*' -lane '$k{$F[0]}=$F[1]; next if $#F < 6; s/$F[1]/$k{$F[3]}/; print' "$file2" "$file1" > test

Here is the desired output:

P476,0.10000000E+00,0.2837975332748E+08,241.06
VC76,0.10000000E+00,0.2837726135782E+08,207.64
KG37,0.10000000E+00,0.2611331725657E+08,283.85
M037,0.10000000E+00,0.2611370504845E+08,263.58
VC37,0.10000000E+00,0.2611374063470E+08,177.33
1K08,0.10000000E+00,0.2611346615238E+08,0.1846393014710E+03
1K05,0.10000000E+00,0.2610647023303E+08,0.1841342000212E+03
1K06,0.10000000E+00,0.2611103965949E+08,0.1845191988294E+03
1K07,0.10000000E+00,0.2611275589639E+08,0.1845173169920E+03
KG76,0.10000000E+00,0.2838117264779E+08,284.31
1K02,0.10000000E+00,0.2837950666778E+08,0.6757007619124E+02
1K10,0.10000000E+00,0.2610224473371E+08,0.1835417139884E+03
  • 1
    Could you add the desired output file for the given input files? – igal Nov 12 '17 at 16:30
  • It looks like the formatting on the first input file is incorrect - I don't think those line-breaks after the 3rd column are supposed to be there. – igal Nov 12 '17 at 16:30
  • Also, your header column for the etch file looks pretty mangled. Are you sure that's what you want? – igal Nov 12 '17 at 16:45
  • it would be better if you posted the expected result – RomanPerekhrest Nov 12 '17 at 16:51
0

Here's a Python script that does what you want:

#!/usr/bin/env python2
# -*- coding: ascii -*-
"""eche.py"""

import csv
from collections import OrderedDict

# Open the first file
with open("eche", 'r') as csvfile1:
    csvreader1 = csv.reader(csvfile1, delimiter=',')

    # Skip the header row
    next(csvreader1, None)

    # Read the data into a dictionary,
    # indexed by the value of the first column
    rows1 = OrderedDict((row[0], row) for row in csvreader1)

    # Open the second file
    with open("eche.txt", 'r') as csvfile2:

        # Read the data into a dictionary,
        # indexed by the value of the first column
        rows2 = {row[0]: row for row in csv.reader(csvfile2, delimiter=',')}

        # Iterate through the rows of the first file
        for key, row in rows1.iteritems():

            # If the key from the first file matches a row in the second file,
            # output the updated row
            if key in rows2:
                print(','.join(row[0:3] + [rows2[key][1]]))

            # If the key from the first file does NOT match
            # a row in the second file then output the row unchanged
            else:
                print(','.join(row))

Running this script with your example data produces the following output:

VC76,0.10000000E+00,0.2837726135782E+08,207.64
1K02,0.10000000E+00,0.2837950666778E+08,0.6757007619124E+02
P476,0.10000000E+00,0.2837975332748E+08,241.06
KG76,0.10000000E+00,0.2838117264779E+08,284.31
1K05,0.10000000E+00,0.2610647023303E+08,0.1841342000212E+03
1K06,0.10000000E+00,0.2611103965949E+08,0.1845191988294E+03
1K07,0.10000000E+00,0.2611275589639E+08,0.1845173169920E+03
1K08,0.10000000E+00,0.2611346615238E+08,0.1846393014710E+03
VC37,0.10000000E+00,0.2611374063470E+08,177.33
1K10,0.10000000E+00,0.2610224473371E+08,0.1835417139884E+03
M037,0.10000000E+00,0.2611370504845E+08,263.58
KG37,0.10000000E+00,0.2611331725657E+08,283.85
  • The script is okay but would like to retain the order of the values as original file – eric Nov 16 '17 at 8:19
  • @eric Updated. The order of the rows in the "eche" file is now preserved. – igal Nov 16 '17 at 12:03
  • supposing the one to be replaced is in column 3 it will print only up to column three and leave 4 blank. how do you you fix that? – eric Nov 16 '17 at 19:17
  • Any suggestions to fix it? – eric Nov 18 '17 at 8:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.