awk - Compare columns from two files and replace text in first file

Question

I have two files. The first has 1 column and the second has 3 columns. I want to compare first columns of both files. If there is a coincidence, replace column 2 and 3 for specific values; if not, print the same line.

File 1:

$ cat file1
26
28
30

File 2:

$ cat file2
1,a,0
2,a,0
22,a,0
23,a,0
24,a,0
25,a,0
26,r,1510139756
27,a,0
28,r,1510244156
29,a,0
30,r,1510157364
31,a,0
32,a,0
33,r,1510276164
34,a,0
40,a,0

Desired output:

$ cat file2
1,a,0
2,a,0
22,a,0
23,a,0
24,a,0
25,a,0
26,a,0
27,a,0
28,a,0
29,a,0
30,a,0
31,a,0
32,a,0
33,r,1510276164
34,a,0
40,a,0

I am using gawk to do this (it's inside a shell script and I am using solaris) but I can't get the output right. It only prints the lines that matches:

$fuente="file2"
gawk -v fuente="$fuente" 'FNR==NR{a[FNR]=$1; next}{print $1,$2="a",$3="0" }' $fuente file1 > file3

The output I got:

$ cat file3
26 a 0
28 a 0
30 a 0

score 0 · Answer 1 · 2017-11-10T20:48:38.463

Really spread out for clarity; called (fuente.awk) like so:

    awk -F \, -v fuente=file1 -f fuente.awk file2  # -F == IFS


    BEGIN {
        OFS=","          # set OFS to make printing easier

        while (getline x < fuente > 0)   # safe way; read file into array
        {
            a[++i]=x     # stuff indexed array
        }
    }
    {   # For each line in file2
        for (k=1 ; k<=i ; k++)   # Lop over array (elements in file1)
        {
            if (($1==a[k]) && (! flag))
            {
                print($1,"a",0)    # Found print new line
                flag=1             # print only once
            }
        }

        if (! flag)     # Not found
        {
            print($0)   # print original
        }

        flag=0          # reset flag
    }
    END { }

score 0 · Accepted Answer · answered Nov 10 '17 at 21:37

0

awk one-liner:

awk 'NR==FNR{ a[$1]; next }$1 in a{ $2="a"; $3=0 }1' file1 FS=',' OFS=',' file2

The output:

1,a,0
2,a,0
22,a,0
23,a,0
24,a,0
25,a,0
26,a,0
27,a,0
28,a,0
29,a,0
30,a,0
31,a,0
32,a,0
33,r,1510276164
34,a,0
40,a,0

answered Nov 10 '17 at 21:37

RomanPerekhrest

88,541
4
65
105

Thank you for your answer. It worked pretty well. Could you explain to me why $1 is out of the brackets? – Nicole C Nov 10 '17 at 22:39
@NicoleC. welcome, `$1 in a` means logical operation that checks if the 1st field of `file2` is contained within keys of array `a`. If it evaluates to `true` - the further statement `{ $2="a"; $3=0 }` will be executed. – RomanPerekhrest Nov 11 '17 at 09:32

awk - Compare columns from two files and replace text in first file

2 Answers2