2

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

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

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.

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.