1

Need to vlookup from two different files having duplicate entries:

File 1

abc     10
xyz     20
bhy     30
hgf     40

File 2

a   abc     
b   xyz     
c   bhy     
d   abc     
e   abc     
f   xyz     

Output required:

abc     10  a,d,e
xyz     20  b,f
bhy     30  c
hgf     40  Not_Available
7
  • What is vlookup? Do you actually use the first file for anything? It seems as if the output could be compiled from only the second file.
    – Kusalananda
    May 30 '18 at 14:38
  • We can ignore 3rd column of File 2. Updated files are:
    – user279255
    May 30 '18 at 14:42
  • File 1: abc 10 xyz 20 bhy 30 File 2: a abc b xyz c bhy d abc e abc f xyz Output: abc 10 a,d,e xyz 20 b,f bhy 30 c
    – user279255
    May 30 '18 at 14:43
  • Please edit your question to add any clarifications. Comments don't really count.
    – Kusalananda
    May 30 '18 at 14:47
  • 1
    Have you made any efforts to solve this yourself? If yes, please show your work. May 30 '18 at 15:03
0

With awk

awk -v OFS='\t' '
    NR == FNR {val[$1]=$2; next} 
    {items[$2] = items[$2] " " $1}
    END {
        for (a in val) {
            sub(/^ /, "", items[a])
            gsub(/ /, ",", items[a]) 
            print a, val[a], items[a]
        }
    }
' file1 file2
bhy     30      c
abc     10      a,d,e
xyz     20      b,f

If you want it sorted, pipe into | sort -k2,2n

Just for fun, a tricky bit of perl

perl -lae '
    if ($. == ++$nr) { $val{$F[0]} = $F[1] }
    else             { push @{$items{$F[1]}}, $F[0] }
  } continue {
    close ARGV if eof
  } END { 
    printf "%s\t%s\t%s\n", $_, $val{$_}, join(",", @{$items{$_}}) for keys %items 
' file1 file2
6
  • Can you please explain how it is working.
    – user279255
    May 31 '18 at 10:09
  • Which specific parts do you need help understanding? NR==FNR? arrays? May 31 '18 at 12:09
  • Array part of the command
    – user279255
    May 31 '18 at 13:52
  • Looking at this line NR == FNR {val[$1]=$2; next} what we have is: for each record of the first file (NR==FNR), set the "val" of the first field to be the second field. This maps "abc" to "10". This is known as an "associative array" where the array indices are arbitrary strings or numbers. Other languages call the associative array a "map" or a "hash" or a "dictionary". May 31 '18 at 14:32
  • We can test if a string is an index into the array with the test "some string" in a, (where "a" is the variable name of an associative array) and we can iterate over the indices of the array with for (index in a) {do something with the "index" variable}. May 31 '18 at 14:33
0

Awk solution:

awk 'NR == FNR { a[$1] = $2 OFS; next }
     $2 in a { a[$2] = a[$2] (a[$2] ~ /\t$/? "" : ",") $1 }
     END { for (i in a) print i, a[i] }' OFS='\t' file1 file2

The output:

bhy 30  c
abc 10  a,d,e
xyz 20  b,f

For your last condition use the following modification:

awk 'NR == FNR { a[$1] = $2 OFS; next }
     $2 in a { a[$2] = a[$2] (a[$2] ~ /\t$/? "" : ",") $1 }
     END {
         for (i in a) print i, a[i] (a[i] ~ /\t$/? "Not_Available" : "")
     }' OFS='\t' file1 file2
6
  • Can you please explain how it is working.
    – user279255
    May 31 '18 at 10:09
  • @user279255, did it solve your issue? May 31 '18 at 11:09
  • Hi, Can you please guide how to add a comment in case there is some value which is not available in one of the file. e.g out put file should be: bhy 30 c abc 10 a,d,e xyz 20 b,f hgf 40 Not_Available
    – user279255
    Jun 7 '18 at 13:44
  • @user279255, make an update so I could see the extended output in formatted view Jun 7 '18 at 14:34
  • Hi, Updated the details.
    – user279255
    Jun 8 '18 at 10:11

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.