Compare one column from one file with all columns in another file

Question

I have two files. File 1 has a pattern in the form of a single column that I want to compare with all columns in file 2 to ultimately count how many columns in file 2 show that pattern. The number of columns in file 2 is very large (~300,000 columns). I am not sure if Unix solution is the best way to tackle this large number of columns. I have only been able to figure out how to match column 1 from file 1 with a specific column in file 2 using awk. How do I compare column 1 in file 1 to all columns in file 2?

Example: File 1

0
0
0
0
1
1
0
0
0
0

File 2:

0 0 0 0
0 0 0 1
0 0 0 0
0 0 0 1
0 1 1 0
0 0 1 1
0 1 0 0
0 0 0 0
0 0 0 0
0 0 0 1

I would like to store the matching columns in a separate file and count the number of columns in this new file. So for the above example, only column 3 from file 2 matches file 1 and the output would be column 3 in a new file and the count would be 1.

Answer 1

It's going to be easier to match rows rather than columns. So, for example using rs to transpose the contents of both files

$ rs -T <File1 | grep -Ff- <(rs -T <File2) | rs -T
0
0
0
0
1
1
0
0
0
0

To count occurrences, there's no need to save them to a file - you can use grep -c:

$ rs -T <File1 | grep -cxFf- <(rs -T <File2)
1

Or if octave is available, you might consider using that, where you can do a columnwise vector subtraction of the vector from the matrix, and then count where all rows are zero:

x = dlmread('File1',' ');
A = dlmread('File2',' ');
sum(all(~(A-x)))

This might work better for large files - if you need to run it non-interactively you can do so for example from your shell using a here-document:

$ octave --no-gui --norc --quiet << \EOF
x = dlmread('File1','\t');
A = dlmread('File2','\t');
sum(all(~(A-x)))
EOF

(delimiters changed to tabs based on comments).

If your Octave version doesn't support implicit expansion of arrays with compatible sizes, you may need to change A-x to either bsxfun(@minus,A,x) or A-repmat(x,1,size(A,2))

Answer 2

Since python is in the tags, here's a pythonic solution, using Python 3's stdlib only and using numpy. The core of numpy is implemented in C, so it's much faster in operations on large data arrays than the pure python.

stdlib

with open('needle') as f:
    needle = [int(line.strip()) for line in f]

with open('haystack') as f:
    haystack = [[int(val) for val in line.strip().split()] for line in f]
    # transpose
    haystack = [list(row) for row in zip(*haystack)]

count = haystack.count(needle)
indices = [i for i, row in enumerate(haystack) if row == needle]

print('count:', count)
print('indices:', indices)

numpy

import numpy


needle = numpy.loadtxt('needle', dtype=int)
haystack = numpy.loadtxt('haystack', dtype=int).transpose()
match = (haystack == needle).all(-1)

count = numpy.count_nonzero(match)
indices = numpy.where(match == 1)[0]

print('count:', count)
print('indices:', indices)

Test data

For testing, I have generated a 1,000,000 columns of ones and zeroes with the following generator:

import numpy


arr = numpy.random.choice([0, 1], size=(10, 1000000))
mat = numpy.matrix(arr)

with open('generated', 'wb') as f:
    for line in mat:
        numpy.savetxt(f, line, fmt='%i', delimiter='\t')

Measuring time:

$ uname -a
Linux localhost 3.10.103-g35adc8d #1 SMP PREEMPT Wed Jun 27 20:11:35 UTC 2018 aarch64 GNU/Linux
$ time python search_stdlib.py >/dev/null

real    0m16.326s
user    0m14.867s
sys     0m0.617s
$ time python search_numpy.py >/dev/null

real    0m11.006s
user    0m10.487s
sys     0m0.307s