I have 3 experiment files (File1.tsv, File2.tsv, File3.tsv) from three experiments and 1 library file (library_file.tsv). I want to merge the files by creating a python iterator because the number of experiment files will increase with every additional experiment and the library file will be always the same. All the experiment files are in the same directory.

File 1

id  gene    Exp_1
ID000000001 EGFR    0.127
ID000000002 ADAD1   0.421
ID000000003 ADAD1   0.301


id  gene    Exp2
ID000000001 EGFR    0.303
ID000000002 ADAD1   0.236
ID000000003 ADAD1   0.071


id  gene    Exp_3
ID000000001 EGFR    0.847
ID000000002 ADAD1   0.992
ID000000003 ADAD1   0.912


id  value1  value2  
ID000000001 Single  Guides_Single
ID000000002 Single  Guides_Single
ID000000003 Single  Guides_Single

Expected output

id  gene    Exp_1   Exp2    Exp_3   value1  value2
ID000000001 EGFR    0.127   0.303   0.847   Single  Guides_Single
ID000000002 ADAD1   0.421   0.236   0.992   Single  Guides_Single
ID000000003 ADAD1   0.301   0.071   0.912   Single  Guides_Single

1 Answer 1


Its a simple join operation.

import pandas as pd

df1 = pd.DataFrame({'id':['ID000000001','ID000000002','ID000000003'], 'gene1':['EGFR', 'ADAD1','ADAD1'],'score1':[0.127, 0.421, 0.301]}).set_index('id')
df2 = pd.DataFrame({'id':['ID000000001','ID000000002','ID000000003'], 'gene2':['EGFR', 'ADAD1','ADAD1'], 'score2':[0.303, 0.236, 0.071]}).set_index('id')
df3 = pd.DataFrame({'id':['ID000000001','ID000000002','ID000000003'], 'gene3':['EGFR', 'ADAD1','ADAD1'], 'score3':[0.847,0.992,0.912]}).set_index('id')
df4 = pd.DataFrame({'id':['ID000000001','ID000000002','ID000000003'], 'v1':['Single', 'Single', 'Single'], 'v2':['Guide', 'Guide', 'Guide']}).set_index('id')

df = df1.join([df2,df3,df4])
df.drop(['gene2', 'gene3'],axis=1,inplace=True)
print (df)


             gene1  score1  score2  score3      v1     v2
ID000000001   EGFR   0.127   0.303   0.847  Single  Guide
ID000000002  ADAD1   0.421   0.236   0.992  Single  Guide
ID000000003  ADAD1   0.301   0.071   0.912  Single  Guide

'Iterator' just pickle via

# Reload
df = pd.read_pickle(path)
# Add another data set
df = df.join(newdf)

... then pickle it again

  • $\begingroup$ @Riya there are quite a few aspects to this question so maybe worth posting a new question. For example, pickle is one approach there are 3 others depending on what you want (readable, fast, long-term storage and compression). There is also the read csv and argparse issues. So you might consider refining the code towards a versatile application. $\endgroup$
    – M__
    Commented Jan 7, 2023 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.