CODEWITHSUNDEEP
linuxawk
chas
chas

Reputation: 1645

HOw to add new column from another file in awk

I have two files like shown below which are tab-delimited:

file A

123,789   aa b c d
123       aa b c d
234       a  b c d
345       aa b c d
456       a  b c d
....

file B

123    add    c    d    e
345    add   e    f    g
789    sub   e    f    g
...

I want to add column 2 from file B to file A based on column 1 such that output looks as shown below: output:

123,789   add,sub  aa b c d
123        add     aa b c d
234                a  b c d
345        add     aa b c d
456                a  b c d
....

I tried using:

awk 'NR==FNR{a[$1]=$2;next}{$2=a[$1]FS$2;print}' OFS='\t' fileB file A

which gives the output:

123,789            aa b c d
123        add     aa b c d
234                a  b c d
345        add     aa b c d
456                a  b c d

The problem is with columns having multiple strings separated by comma in column1 of file A. The awk code treats it is a single string due to which it cannot match with fileB. Could someone give an edit to awk code or any fix. Thanks.

Upvotes: 1

Views: 1445

Answers (1)

Wintermute
Wintermute

Reputation: 44043

I'd say

awk -F '\t' 'BEGIN { OFS = FS } NR == FNR { saved[$1] = $2; next } { n = split($1, a, ","); sep = ""; field = ""; for(i = 1; i <= n; ++i) { if(a[i] in saved) { field = field sep saved[a[i]]; sep = "," } } $1 = $1 OFS field } 1' fileB fileA

That is:

BEGIN { OFS = FS }                  # Output separated like input

NR == FNR {                         # while processing fileB:
  saved[$1] = $2                    # just remember stuff
  next
}

{                                   # while processing fileA:
  n = split($1, a, ",")             # split first field at commas

  sep   = ""                        # reset temps
  field = ""

  for(i = 1; i <= n; ++i) {         # wade through comma-separated parts of $1
    if(a[i] in saved) {             # if a corresponding line existed in fileB
      field = field sep saved[a[i]] # append it to the new field
      sep = ","                     # from the second forward, separate by ","
    }
  }

  $1 = $1 OFS field                 # insert the new field into the line
}
1                                   # then print.

Upvotes: 1

Related Questions