lookup value associate with list items

Question

I am trying to approximate vlookup from excel.

I have two files.

file #1 - list.txt:

green
purple
orange

file #2 - reads.txt:

blue 2
green 3
red 5
purple 6

I am trying to read in the entries of list.txt, then go to reads.txt and pull out the associated value.

the desired output would be:

green 3
purple 6
orange 0

if I write:

awk -F ' ' 'FNR == NR {keys[$1]; next} {if ($1 in keys) print $1,$2}' list.txt reads.txt

I get:

green 3
purple 6

but nothing for orange, and I need the line:

orange 0

If I write

awk -F ' ' 'FNR == NR {keys[$1]; next} {if ($1 in keys) print $1,$2; else print $1,0}' list.txt reads.txt

I get:

blue 0
green 3
red 0
purple 6

any ideas how to fix this?

major newbie here, so any help appreciated!

Answer 1

$ awk 'NR==FNR{map[$1]=$2; next} {print $1, map[$1]+0}' reads.txt list.txt
green 3
purple 6
orange 0

Answer 2

1st solution(with as per shown samples): Could you please try following, written and tested with shown samples.

awk '
FNR==NR{
  arr[$1]=$2
  next
}
($0 in arr){
  print $0,arr[$0]
  next
}
{
  print $0,0
}
' reads.txt list.txt

Output will be as follows, for shown samples.

green 3
purple 6
orange 0

---

---

2nd solution(Generic solution): In case your Input_file named reads.txt has multiple values of same first column and you want to print all values which are present in list.txt then please try following.

awk '
FNR==NR{
  ++arr[$1]
  val[$1 OFS arr[$1]]=$2
  next
}
($0 in arr){
  for(i=1;i<=arr[$0];i++){
    print $0,val[$0 OFS i]
  }
  next
}
{
  print $0,0
}
' reads.txt list.txt

Sample of run: Let's say your sample of reads.txt is this:

cat reads.txt
blue 2
green 3
red 5
purple 6
green 15
green 120

Now after running this Generic solution we will get following.

green 3
green 15
green 120
purple 6
orange 0