comparing two large csv file and writing another one with python
<p>I am trying to compare two large csv files containing chemicals data.</p>

<p>the first one, "file1" is 14 Mb (which is not so heavy), but the second one, "file2" is  3Go (47798771 lines).</p>

<p>here a sample of file 1 (we'll focus on the fourth column, which contains inchikeys) :</p>

<pre><code>MFCD00134034    7440-42-8   B   UORVGPXVDQYIDP-UHFFFAOYSA-N
MFCD01745487    64719-89-7  B1BBBB(BBBBB1[Li])[Li]  XZXJQLAKEUKXOT-UHFFFAOYSA-N
MFCD01310566    19287-45-7  BB  QSJRRLWJRLPVID-UHFFFAOYSA-N
MFCD00011323    10035-10-6  Br  CPELXLSAUQHCOX-UHFFFAOYSA-N
        N(CCNCCCCCCCCCCNCCN(CC)CC)(CC)CC    PISAWRHWZGEVPP-UHFFFAOYSA-N
MFCD01744969    137638-86-9 O(C(=O)C(c1ccccc1)c1ccccc1)c1cc2c(C[C@H]3N(CC[C@]2(C)C3C)Cc2ccccc2)cc1  CIRJJEXKLBHURV-MAYWEXTGSA-N
        O(CCCN1CCCC1)c1ccc(NC(=Nc2ccccc2)c2ccccc2)cc1   KETUBKLQEXFJBX-UHFFFAOYSA-N
MFCD01694581    3810-31-9   S(CCN(CCSC(N)=N)CCSC(N)=N)C(N)=N    GGDUORJVTMUGNU-UHFFFAOYSA-N
MFCD06794992    60066-94-6  Brc1cc(C(=O)c2ncccc2)c(NC(=O)CNC(=O)[C@@H](N)CCCCN)cc1  NVOGGKXDMDDFEG-HNNXBMFYSA-N
MFCD06794980    60066-98-0  Brc1cc(C(=O)c2ncccc2)c(NC(=O)CNC(=O)[C@@H](N)CCCNC(N)=N)cc1 LFCYDGUHINTBOJ-AWEZNQCLSA-N
</code></pre>

<p>file 2 : </p>

<pre><code>lat_chemical_id stereo_chemical_id  source_cid  inchikey
CID100000001    CID000000001    1   RDHQFKQIGNGIED-UHFFFAOYSA-N
CID100000010    CID000000010    10  AUFGTPPARQZWDO-UHFFFAOYSA-N
CID100000100    CID000000100    100 UTIBHEBNILDQKX-UHFFFAOYSA-N
CID100001000    CID000001000    1000    ULSIYEODSMZIPX-UHFFFAOYSA-N
CID100010000    CID000010000    10000   ZPIFKCVYZBVZIV-UHFFFAOYSA-N
CID100100000    CID000100000    100000  SPTBIJLJJBZGDY-UHFFFAOYSA-N
CID101000000    CID001000000    1000000 XTNVYACQOFUTNH-UHFFFAOYSA-N
CID110000000    CID010000000    10000000    WUGPGGSZFRVGGA-UHFFFAOYSA-N
CID110000001    CID010000001    10000001    ANOUMYXLUIDQNL-UHFFFAOYSA-N
</code></pre>

<p>My goal is to compare the inchikeys, fourth row in boths files,to see if they are the same. Then when its the case, extract all info (from both files)and write them i third one. </p>

<p>here's my (naive) code : </p>

<pre><code>#!/usr/bin/env python
#-*- coding: utf-8 -*-
######################
import numpy as np
import argparse 
import csv 
#################################


def compare(tab_data_inchik,stitch,output):
    dt = open(tab_data_inchik, 'rb')
    st = open(stitch,'rb')
    out = open(output,'wb')
    data = csv.reader(dt, delimiter = '\t')
    database = csv.reader(st, delimiter = '\t')
    result = csv.writer(out, delimiter = '\t')
    for line in data:
        for row in database:
            if line[3] == row[3]:
                result.writerow((line[0],line[1],line[2],row[0],row[1],row[2],row[3]))        

    dt.close()
    st.close()
    out.close()

##############################""
if __name__ == '__main__':
    parser = argparse.ArgumentParser()
    parser.add_argument("tsv1", help = "Pr Data")
    parser.add_argument("tsv2", help = "Database")
    parser.add_argument("output", help = "output file")
    args=parser.parse_args()

    compare(args.tsv1,args.tsv2,args.output)
</code></pre>

<p>It seems that, the program does not even reach the second line of the database loop, I guess it's because the file is to large and my method is bot optimized. Maybe I should use numpy.where() but I don't see how.</p>

<p>Is there a way to get the information without the double loop ? 
Thanks in advance. </p>