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.


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

  • file2:

  • Desired output:


    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
  • Why is james,27,pineapple outputted? Why isn't james,25,strawberry,rassberry,blueberry outputted?
    – Kusalananda
    Commented Nov 15, 2021 at 22:06
  • 2
    Because pineapple does not exist for james in file2
    – congen
    Commented Nov 15, 2021 at 22:11

2 Answers 2

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 >=3rd 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.

  • @afshin - looks like it is getting the desired output. Could you explain in detail ?
    – congen
    Commented Nov 16, 2021 at 14:38
  • @congen I added some explanation, hope it helps Commented Nov 17, 2021 at 1:16

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

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


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

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

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

You must log in to answer this question.

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