1

I have 2 files as below:

a.txt (tab delimited and it has 3 columns and too many lines):

 city   plate   region
 istanbul   36  marmara
 trabzon    61  karadeniz

b.txt (comma delimited and it has 4 columns and too many lines):

name,city,age,nationality
mehmet,trabzon,,
murat,istanbul,,
john,london,,

What I want to do is: check if the 2nd column (city field) of b.txt exists in a.txt (in first column - city) and if it exists in a.txt; update the file b.txt and set 4th column (nationality) to "turkish". So expected output:

name,city,age,nationality
mehmet,trabzon,,turkish
murat,istanbul,,turkish
john,london,,

I tried following command but it did not generated the desired output:

nawk 'BEGIN { FS = "\t" } FNR == NR {x[$1] = $1; next;} {FS=OFS=","} FNR>1{if ($2 in x) {($4 = "turkish")} } 1' a.txt b.txt
name,city,age,nationality
mehmet,trabzon,,
murat,istanbul,,
john,london,,

I need to find the mistake in my command.

4 Answers 4

1

Your script works fine. My guess is the first file has spaces instead of tabs as separators.

Remove the useless BEGIN { FS = "\t" } to check if it is the case.

1
  • You are totally right, it worked after removing the BEGIN { FS = "\t" } phrase.
    – Murat
    Commented Oct 16, 2015 at 9:21
0

Try with sed

sed -n 's|\(.*\S\)\s\+[0-9].*|/\1/s/$/turkish/|p' a.txt |
sed -f - b.txt
0

With awk (notice that the first file a.txt has to be loaded into memory completely):

awk -F"[\t, ]" 'FNR==NR&&NR!=1{a[$1]="turkish"}
  FNR!=NR{OFS=",";if(FNR!=1){$NF=a[$2]};print}' a.txt b.txt

  • -F"[\t,]" sets awks delimiter to tab and comma
  • FNR==NR&&NR!=1 only applies to the first file a.txt and ignore the first line (header)
    • a[$1]="turkish" set in an array called a the index to the first field $1
  • FNR!=NR applies only to the second file b.txt
  • OFS="," set the output field separator
  • if(FNR!=1) only do something when it's not the header line
  • $NF=a[$2] set the last field to the previously saved value turkish if it exists in the array
  • print and print the line if the value existed or not

The output:

name,city,age,nationality
mehmet,trabzon,,turkish
murat,istanbul,,turkish
john,london,,
3
  • Thanks for your response but it did not work. With awk (empty result): bash-3.00$ awk -F"[\t,]" 'FNR==NR&&NR!=1{a[$1]="turkish"} FNR!=NR{OFS=",";if(FNR!=1){$NF=a[$2]};print}' a.txt b.txt With nawk (b.txt file not updated): bash-3.00$ nawk -F"[\t,]" 'FNR==NR&&NR!=1{a[$1]="turkish"} FNR!=NR{OFS=",";if(FNR!=1){$NF=a[$2]};print}' a.txt b.txt name,city,age,nationality mehmet,trabzon,, murat,istanbul,, john,london,,
    – Murat
    Commented Oct 16, 2015 at 8:58
  • @Murat Possible you have space separator instead tab so just add space to delimiters [ \t,]
    – Costas
    Commented Oct 16, 2015 at 9:12
  • A some shorter awk -F"[ \t,]" 'FNR==NR{if(NR!=1)a[$1]=1;next} $2 in a{$NF="turkish"}1' OFS="," a.txt b.txt
    – Costas
    Commented Oct 16, 2015 at 9:17
0
tr   -s \[:blank:] , <a.txt|    #dont mess with weird columns
cut  -d, -f1  |  sort -u   |    #drop all but unique first column entries
sed  -e's/[]*^\./$[]/\\&/g'\    #handle any regex metachars properly
     -e's/.*/^[^,]*,&,/'   |    #match only second column
grep -nf - -- b.txt        |    #get line numbers at head of matched lines
sed  -e's/:.*/be/'         |    #keep only matched line numbers + 'be'
sed  -f - -eb -e:e         \    #branch away or append turkish
     -e's/$/turkish/' -- b.txt

mehmet,trabzon,,turkish
murat,istanbul,,turkish
john,london,,

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .