2

I am trying to merge two csv files using AWK. If the 1st column in file 1 matches the 1st column in file 2 I want to change file1's 3rd column to the value of file2's 2nd column. If there is no match, make no changes and move on to next row.

file1.csv

alice,soccer,24
bob,baseball,21
jessica,swimming,22
mike,running,20

file2.csv

alice,5
jessica,9

desired output:

alice,soccer,5
bob,baseball,21
jessica,swimming,9
mike,running,20

My code so far:

#!/bin/bash

    awk -F"," 'BEGIN{OFS=","}
        { if (NR==NFR){ 
         array[$1]=$1; 
        } 
        if ( $1 in array ){
         $3=array[$2]
        }
         print
        }' file2.csv file1.csv
1
  • 1
    Not array[$1]=$1 but array[$1]=$2 -- that will actually capture the data in file2 Jul 7, 2020 at 19:23

1 Answer 1

2

Try:

$ awk -F, 'FNR==NR{a[$1]=$2;next} $1 in a{$3=a[$1]} 1' OFS=, file2.csv file1.csv
alice,soccer,5
bob,baseball,21
jessica,swimming,9
mike,running,20

How it works

  • -F,

    This sets the input field separator to a comma.

  • FNR==NR{a[$1]=$2;next}

    When reading file2.csv, this saves the second field under the key of the first field in array a.

    Notice the use of next. This tells awk to skip the rest of the commands and start over on the next line.

    For example, after reading your file2.csv, the contents of array a are: a['alice']=5 and a['jessica']=9.

  • $1 in a{$3=a[$1]}

    When reading file1.csv, if the first field exists as a key in array a, then this updates the third field to the value stored in a.

    The test $1 in a is true if the content of $1 (which we are reading from file1.csv) is one of the keys (not one of the values) of array a.

  • 1

    This is shorthand for {print}.

  • OFS=,

    This sets the output field separator to a comma.

4
  • Thanks that did the job! :) There is one thing i can't quite wrap my head around. FNR==NR{a[$1]=$2;next} will contain 5,9 or what exactly will the array a contain? If it contains 5,9 the check $1 in a{$3=a[$1]} does not make sense to me. Can you elaborate?
    – tortor11
    Jul 7, 2020 at 19:21
  • 1
    After reading file2.csv, the contents of array a are: a['alice']=5 and a['jessica']=9. The test $1 in a is true if the contents of $1 are one of the keys (not one of the values) of array a.
    – John1024
    Jul 7, 2020 at 19:24
  • 1
    "if the contents of $1 in file1" Jul 7, 2020 at 19:25
  • 1
    Thanks, that clarified it!
    – tortor11
    Jul 7, 2020 at 19:57

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.