Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-01-07T15:05:11.180Z
Answers.Text: @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.
Answers.UserDisplayName: M__
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-01-05T23:25:42.590Z
Answers.LastActivityDate: 2023-01-05T23:52:47.563Z
Answers.LastEditDate: 2023-01-05T23:52:47.563Z
Answers.UserDisplayName: M__
Body: <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>
  
Tags: <python><pandas><merge>
Title: Merge the dataframes with python iterator
ThreadText: # 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**: @username_0 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.  
  
  ---
  
  