CODEWITHSUNDEEP
unixawk
Nolohice
Nolohice

Reputation: 339

Using two files to compare columns and rows and printing conditional values

I have two files:

file one

,air,rode,hand,man,chip,dog
clock,,,,,,
mind,,,,,,
finger,,,,,,
tuna,,,,,,

file two

mind,air,rode
clock,hand,man,dog
finger,air,rode,hand,dog
tuna,chip,dog
desk,chip,hand,
move,dog,air,rode

What is going on is all the unique strings in file two that aren't in the first column have been translated to the first row of file one. Also, the first column of each file has a set of strings, every string in the first column of file 1 is present in the first column of file 2 but not every string in the first column of file two is present in the first column of file 1. What I want to do is use these two files so that if the string in row 1 of file one is present in any of the rows in file two, for the matching string in the first column of both files then a one is printed, else a zero is printed

So the output would be: 

,air,rode,hand,man,chip,dog
clock,0,0,1,1,0,1
mind,1,1,0,0,0,0
finger,1,1,1,0,0,1
tuna,0,0,0,0,1,1

Upvotes: 1

Views: 92

Answers (1)

karakfa
karakfa

Reputation: 67467

This script should do most of the task, except the order will be specified in the second file

    NR == 1 {                            # grab first line from file 1
            n = split($0, cols, ",")     # n is the number of fields
            print $0
            next
    }

    FNR == NR       {                    # grab required row keys from file 1
            keys[$1] = 1
    }

    FNR != NR && $1 in keys {            # process file 2 for the keys
            for (i = 2; i <= NF; i++) {  # construct a map for the given associations
                    map[$1, $i] = 1
            }
            line = $1                    # construct line, starting with key
            for (i = 2; i <= n; i++) {   
                    v = ($1, cols[i]) in map   # does the mapping exist?
                    line = line FS v           # add the value to line separated with FS
            }
            print line                   # print line
    }

$ awk -F, -f script.awk file1.txt file2.txt

will give

,air,rode,hand,man,chip,dog
mind,1,1,0,0,0,0
clock,0,0,1,1,0,1
finger,1,1,1,0,0,1
tuna,0,0,0,0,1,1

If order needs to be is specified by file 1, you can put the lines in an array and print within an END block.

Upvotes: 1

Related Questions