Find lines in one "key - value-list" file that contain data fields not mapped to that key in a second "key-value" file

Question

I have 2 files. One file contains a minimum of two fields (a username, an age, and a variable number of "fruits" each in their own field) per line, the other file always contains two fields per line (the username and one "fruit"). I would call file1 the "database" file and file2 the "mapping" file.

For each username I want to check if any of the lines in file1 corresponding to that user contains a "fruit" that is not mapped to that user as per file2.

Example:

• file1 (the number of fruits in each row is variable in this file):

  james,25,strawberry,rassberry,blueberry
  james,25,strawberry,rassberry,mango
  james,26,blueberry
  james,27,pineapple
  erik,30,strawberry,rassberry,mango
  

• file2:

  james,strawberry
  james,rassberry
  james,blueberry
  erik,blueberry
  erik,rassberry
  

• Desired output:

  james,25,strawberry,rassberry,mango
  james,27,pineapple
  erik,30,strawberry,rassberry,mango
  

  These lines are selected because file2 contains no associations of mango or pineapple with user james and no association of strawberry and mango with user erik.

I was only able to get partial solution with my code. I have tried below, but this is not checking for other columns in the same row.

awk 'BEGIN{FS=OFS=","}NR==FNR{a[$1]=$2;next}
{if (a[$1] && (a[$1]!=$3)){print $0, a[$1]}}' file2 file1

Answer 1

awk -F, '
    !nxtfile{ join[$1]= (join[$1]==""?"": join[$1] FS) $2; next }
            { split(join[$1], tmp, ","); for(x in tmp) fruits[tmp[x]];
              for(i=3; i<=NF; i++) if(!($i in fruits)) { print; break }
            }
' file2 nxtfile=1 file1

in the first block we are processing the input file2 and joining all the fruits in different lines together for the same name and store them as pair of key/value into the associated array called join.

in the second block we are processing the input file1 and fetch the value of the matched first field from the array and split that on comma delimiter into a temporary array tmp, then we rebuild another array fruits with the values of the tmp array used as keys for fruits array (in other word, we are swapping the tmp array's values to become the keys to the fruits array).

then the last step is to looping over the >=3^rd field to the end and checking one by one if that fields is exist in the fruits array, on a first non-matching field we print the entire line and break the loop as there is no need to continue reading rest of the fields.

Answer 2

Certainly more lines than the accepted awk solution, but maybe clearer if you don't know awk (like me).

This uses Python's sets, which allow us to cleanly ask, "does this list of fruits have an element (fruit) that isn't in the lookup/reference list?":

import csv
import sys
from collections import defaultdict

# Will look something like { james: [strawberry, ...], erik: [blue, ...] }
lookup = defaultdict(set)

with open('file2', newline='') as f:
    reader = csv.reader(f)

    for row in reader:
        name, fruit = row
        lookup[name].add(fruit)


writer = csv.writer(sys.stdout)

with open('file1', newline='') as f:
    reader = csv.reader(f)

    for row in reader:
        name = row[0]
        these_fruits = set([x for x in row[2:] if x])

        # see my note below on how set.difference(set) works
        if not these_fruits.difference(lookup[name]):
            # no difference
            continue

        writer.writerow(row)

Here's how set.difference(set) works:

>>> {1,2}.difference({1,2,3})
set()
>>> {1,2,3}.difference({1,2,3})
set()
>>> {1,2,4}.difference({1,2,3})
{4}

It isn't until the left-hand side has an element that the right-hand doesn't that there appears to be a difference.