0

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.

0

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))

  • Thank you for your suggestions. I tried the first suggestion on the example dataset and it works! However, when I try on a subset of my actual data, File1 gets transposed as expected into one column, but for file2, all the data is getting transposed into just one row. Any idea why that would happen? – user210432 Jul 2 '18 at 21:42
  • Oh ok, it's because my actual data is tab delimited and not space as in this example. – user210432 Jul 2 '18 at 21:44
  • @user210432 you may need to do something like rs -c$(printf '\t') -T to set the input separator to a literal tab (I don't think it understands backslash escapes like \t itself); octave's dlmread should though e.g. A = dlmread('File2','\t') – steeldriver Jul 2 '18 at 21:51
  • Thank you. I converted all tabs to space in my datafile. But then the pipeline breaks as the file is too big for rs to handle. I get a segmentation fault error. For the second suggestion, I installed octave, but I get the error bash: syntax error near unexpected token `(' when I run it. – user210432 Jul 2 '18 at 22:04
  • @user210432 how are you trying to run octave? please see my latest update – steeldriver Jul 2 '18 at 22:18
1

Since 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

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.