<p>Its a simple join operation.</p>
<pre><code>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)
</code></pre>
<p>Output</p>
<pre><code>             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
</code></pre>
<p>'Iterator' just <code>pickle</code> via</p>
<pre><code>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)
</code></pre>
<p>... then pickle it again</p>

CC BY-SA 4.0
2023-01-07T15:05:11.180Z
@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.
M__
CC BY-SA 4.0
2023-01-05T23:25:42.590Z
2023-01-05T23:52:47.563Z
2023-01-05T23:52:47.563Z
M__
<p>I have 3 experiment files (<code>File1.tsv, File2.tsv, File3.tsv</code>) from three experiments and 1 library file (<code>library_file.tsv</code>). 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.</p>
<p><code>File 1</code></p>
<pre><code>id  gene    Exp_1
ID000000001 EGFR    0.127
ID000000002 ADAD1   0.421
ID000000003 ADAD1   0.301
</code></pre>
<p><code>File2</code></p>
<pre><code>id  gene    Exp2
ID000000001 EGFR    0.303
ID000000002 ADAD1   0.236
ID000000003 ADAD1   0.071
</code></pre>
<p><code>File3</code></p>
<pre><code>id  gene    Exp_3
ID000000001 EGFR    0.847
ID000000002 ADAD1   0.992
ID000000003 ADAD1   0.912
</code></pre>
<p><code>library_file</code></p>
<pre><code>id  value1  value2  
ID000000001 Single  Guides_Single
ID000000002 Single  Guides_Single
ID000000003 Single  Guides_Single
</code></pre>
<p><code>Expected output</code></p>
<pre><code>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
</code></pre>

<python><pandas><merge>
Merge the dataframes with python iterator
# Merge the dataframes with python iterator

(Asked by: username_0 on 2023-01-05)

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

**Tags:** python, pandas, merge

---

## 1 answers

### Answer by username_1 on 2023-01-05. Score: 2 | Accepted answer

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

> **Comments:**
> **username_1**: @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.  

---

