Adding data to line in CSV if value exists in external file

Question

Here is my sample data:

1,32425,New Zealand,number,21004
1,32425,New Zealand,number,20522
1,32434,Australia,number,1542
1,32434,Australia,number,986
1,32434,Fiji,number,1

Here is my expected output:

1,32425,New Zealand,number,21004,No
1,32425,New Zealand,number,20522,No
1,32434,Australia,number,1542,No
1,32434,Australia,number,986,No
1,32434,Fiji,number,1,Yes

Basically I am trying to append the Yes/No based on if field 3 is contained in an external file. Here is what I have currently but as I understand it grep is eating all the stdin in the while loop. So I am only getting No added to the end of each line as the first value is not contained in the external file.

while IFS=, read -r type id country number volume
do
  if grep $country externalfile.csv
  then
    echo "${country}"
    sed 's/$/,Yes/' >> file2.csv
  else
    echo "${country}"
    sed 's/$/,No/' >> file2.csv
  fi
done < file1.csv

I added the echo "${country}" as I was trying to troubleshoot and that's how I discovered it was only parsing the first line.

What does the other file look like? How big is it? I'd probably parse it into a lookup table to avoid all those calls to grep. — Paul Hodges
– Paul Hodges, Commented Jun 14, 2021 at 15:33
There's about 240 lines, and each line is just one country name. — Slater
– Slater, Commented Jun 14, 2021 at 15:41
Also if there are say 1k lines then grep and sed will also run 1k times... — Jetchisel
– Jetchisel, Commented Jun 14, 2021 at 16:02

Paul Hodges · Accepted Answer · 2021-06-14 16:04:39Z

3

Assuming there are no headers -

 awk -F, 'NR==FNR{lookup[$1]=$1; next;}
   { if ( lookup[$3] == $3 ) { print $0 ",Yes" } else { print $0 ",No" } }
         ' externalfile.csv file2.csv

This will parse both files in one pass.

If you just prefer to do it in pure bash,

declare -A lookup
while read c; do lookup["$c"]="$c"; done < externalfile.csv

declare -p lookup # this is just to show you what my example loaded
declare -A lookup='([USA]="USA" [Fiji]="Fiji" )'

while IFS=, read a b c d; do 
  [[ -n "${lookup[$c]}" ]] && echo "$a,$b,$c,$d,Yes" || echo "$a,$b,$c,$d,No"
done < file2.csv
1,32425,New Zealand,number,21004,No
1,32425,New Zealand,number,20522,No
1,32434,Australia,number,1542,No
1,32434,Australia,number,986,No
1,32434,Fiji,number,1,Yes

No grep needed.

edited Jun 14, 2021 at 16:04

answered Jun 14, 2021 at 15:45

Paul Hodges

16k1 gold badge24 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

glenn jackman Over a year ago

Alternatively, { print $0, ($3 in lookup ? "Yes" : "No") } with -v OFS=","

konsolebox · Accepted Answer · 2021-06-14 16:02:20Z

2

awk -F, -v OFS=, 'NR == FNR { ++a[$1]; next } { $(++NF) = $3 in a ? "Yes" : "No" } 1' externalfile.csv file2.csv

answered Jun 14, 2021 at 16:02

konsolebox

76.3k13 gold badges110 silver badges114 bronze badges

Comments

Mikołaj Głodziak · Accepted Answer · 2021-06-14 15:37:42Z

1

Try this:

while read -r line
do
country=`echo $line | cut -d',' -f3`
if grep "$country" externalfile.csv
then
        echo "$line,Yes" >> file2.csv
else
        echo "$line,No" >> file2.csv
fi
done < test.txt

You need to put $country inside the ", because some country could contains more than 1 word. For example New Zealand. You can also set country variable easier using cut command.

answered Jun 14, 2021 at 15:37

Mikołaj Głodziak

5,35513 silver badges31 bronze badges

3 Comments

Slater Over a year ago

Works great! Just so I understand, this works because we set the variable within the while loop, whereas previously I was reading from the variable set by while which was consuming the rest of the stdin?

Mikołaj Głodziak Over a year ago

This is easiest way to catch interesting data. Try this in the terminal: echo "1,32425,New Zealand,number,20522" | cut -d',' -f3. You can look how it works ;) In the loop you are now reading whole line and the you are cutting specific field.

Paul Hodges Over a year ago

This will still run grep on every line of file2.csv. That's not very kind to the machine.

Collectives™ on Stack Overflow

Adding data to line in CSV if value exists in external file

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related