comparing parts of lines in two tsv files in python
<p>So I want to sum/analyse values pertaining to a given line in one file which match another file. 
The format of the first file I wish to compare against is:</p>

<pre><code>Acetobacter cibinongensis   Acetobacter Acetobacteraceae    
Rhodospirillales    Proteobacteria  Bacteria    
Acetobacter ghanensis   Acetobacter Acetobacteraceae    Rhodospirillales    Proteobacteria  Bacteria    
Acetobacter pasteurianus    Acetobacter Acetobacteraceae    Rhodospirillales    Proteobacteria  Bacteria
</code></pre>

<p>And the second file is like:</p>

<pre><code>Blochmannia endosymbiont of Polyrhachis (Hedomyrma) turneri Candidatus Blochmannia  Enterobacteriaceae  Enterobacteriales   Proteobacteria  Bacteria    1990    7.511    14946.9
Blochmannia endosymbiont of Polyrhachis (Hedomyrma) turneri Candidatus Blochmannia  Enterobacteriaceae  Enterobacteriales   Proteobacteria  Bacteria    2061    6.451    13295.5
Calyptogena okutanii thioautotrophic gill symbiont  Proteobacteria-undef    Proteobacteria-undef    Proteobacteria-undef    Proteobacteria  Bacteria    7121    2.466    17560.4
</code></pre>

<p>What I want to do is parse every line in the first file, and for every line in the second file where the first 6 fields match, perform analysis on the numbers in the 3 fields following the species info.</p>

<p>My code is as follows:</p>

<pre><code>with open('file1', 'r') as file1:
with open('file2', 'r') as file2:
    for line in file1:
        count = 0
        line = line.split("\t")
        for l in file2:
            l = l.split("\t")
            if l[0:6] == line[0:6]:
                count+=1
        count = str(count)
        print line + '\t' + count +'\t'+'\n'
</code></pre>

<p>Which I'm hoping will give me the line from the first file and the number of times that species was found in the second file. 
I know there's probably a better way of doing THIS particular part of the analysis but I wanted to give a simple example of the objective.. 
Anyway, I don't get any matches, i.e. I never see an instance where
l[0:6] == line[0:6]
is True.
Any ideas?? :-S</p>

<p>The root cause is that you consume <code>file2</code> at the first iteration, then it always iterate over nothing.</p>

<p>Quick fix: read file2 fully and put it in a list. However, this is rather inefficient in terms of speed (O(N^2): double loop). Could be better if creating a dictionary with key = tuple of the 6 first values.</p>

<pre><code>with open('file2', 'r') as f:
    file2 = list(f)
with open('file1', 'r') as file1:

    for line in file1:
        count = 0
        line = line.split("\t")
        for l in file2:
            l = l.split("\t")
            if l[0:6] == line[0:6]:
                count+=1
        count = str(count)
        print line + '\t' + count +'\t'+'\n'
</code></pre>

<p>Also, using <code>csv</code> module configured with TAB as separator would avoid you some surprises in the future.</p>

<p>Better version, using a dictionary for faster access on data of <code>file2</code> (the first 6 elements are the key, note that we cannot use a <code>list</code> as key since it's mutable but we have to convert it to a <code>tuple</code>):</p>

<pre><code>d = dict()

# create the dictionary from file2
with open('file2', 'r') as file2:
    for l in file2:
        fields = l.split("\t")
        d[tuple(fields[0:6])] = fields[6:]

# iterate through file1, and use dict lookup on data of file2
# much, much faster if file2 contains a lot of data

with open('file1', 'r') as file1:

    for line in file1:
        count = 0
        line = line.split("\t")

        if tuple(line[0:6]) in d:  # check if in dictionary
            count+=1
            # we could extract the extra data by accessing
            # d[tuple(line[0:6])]
        count = str(count)
        print(line + '\t' + count +'\t'+'\n')
</code></pre>

python csv compare