replacing values in one with the values in another file

Question

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

Answer 1

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