Merge the dataframes with python iterator

Question

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

File2

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

File3

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

library_file

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

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)

Output

             gene1  score1  score2  score3      v1     v2
id                                                       
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

path='/home/desktop/df.pkl'
df.to_pickle(path)
# Reload
df = pd.read_pickle(path)
# Add another data set
df = df.join(newdf)
df.drop(['gene4'],axis=1,inplace=True)

... then pickle it again