title: merge two txt files together by one common column python
body: <p>how to read in two tab delimited files .txt and map them together by one common column. </p>
  
  <p>For example, from these two files create a mapping of gene to pathway:</p>
  
  <p>First file, pathway.txt</p>
  
  <pre><code> Pathway                                                Protein
   Binding and Uptake of Ligands by Scavenger Receptors   P69905                                          
   Erythrocytes take up carbon dioxide and release oxygen P69905                                         
   Metabolism                                             P69905
   Amyloids                                               P02647
   Metabolism                                             P02647
   Hemostasis                                             P68871
  </code></pre>
  
  <p>Second file, gene.txt</p>
  
  <pre><code> Gene   Protein
   Fabp3  P11404
   HBA1   P69905
   APOA1  P02647
   Hbb-b1 P02088
   HBB    P68871
   Hba    P01942
  </code></pre>
  
  <p>output would be like, </p>
  
  <pre><code> Gene  Protein     Pathway
   Fabp3  P11404     
   HBA1   P69905     Binding and Uptake of Ligands by Scavenger Receptors, Erythrocytes take up carbon dioxide and release oxygen, Metabolism
   APOA1  P02647     Amyloids, Metabolism
   Hbb-b1 P02088
   HBB    P68871     Hemostasis
   Hba    P01942   
  </code></pre>
  
  <p>Leave blank if there is no pathway corresponds to gene base on the protein id information. </p>
  
  <p>UPDATE: </p>
  
  <pre><code> import pandas as pd
  
   file1= pd.read_csv("gene.csv")
   file2= pd.read_csv("pathway.csv")
  
   output = pd.concat([file1,file2]).fillna(" ")
   output= output[["Gene","Protein"]+list(output.columns[1:-1])]
   output.to_csv("mapping of gene to pathway.csv", index=False)
  </code></pre>
  
  <p>So this only gives me the merged file which is not i expected. </p>