Merge two csv file in pyhton
<p>I trying to merge two csv file, I don't want to remove duplicate I want simply to check first column "PDB ID" and then check second column "Chain ID". all values has input files. I want to merge and add column file 1 and file 2. </p>

<pre><code>import pandas as pd


a = pd.read_csv("testfile.csv")
b = pd.read_csv("testfile_1.csv")
b = b.dropna(axis=1)
merged = a.merge(b, on='PDB ID')
merged.to_csv("output.csv", index=False)
</code></pre>

<p>I used above script but getting result one row three time same value. </p>

<pre><code>File 1: Input
PDB ID  Chain ID    Ligand ID   Uniprot Acc
3RSQ    A   NAI   Q9X024
3RTD    A   NAI   Q9X024
1E3E    A   NAI   Q9QYY9
1E3E    B   NAI   Q9QYY9
1E3I    A   NAI   Q9QYY9
1E3I    B   NAI   Q9QYY9

File 2: Input
PDB ID  Chain ID    Avg
1E3E    A   31.566
1E3E    B   17.867
3RSQ    A   57.653   
1E3I    A   27.63
1E3I    B   17.867
3RTD    A   48.806

Getting Output: 
PDB ID  Chain ID_x  Avg Ligand ID   Uniprot Acc
3RSQ    A   57.653  NAI   Q9X024
3RTD    A   48.806  NAI   Q9X024
1E3E    A   31.566  NAI   Q9QYY9
1E3E    A   31.566  NAI   Q9QYY9
1E3E    B   17.867  NAI   Q9QYY9
1E3E    B   17.867  NAI   Q9QYY9
1E3I    A   27.63   NAI   Q9QYY9
1E3I    A   27.63   NAI   Q9QYY9
1E3I    B   17.867  NAI   Q9QYY9
1E3I    B   17.867  NAI   Q9QYY9


Expected Output:

3RSQ    A   57.653  NAI   Q9X024
3RTD    A   48.806  NAI   Q9X024
1E3E    A   31.566  NAI   Q9QYY9
1E3E    B   17.867  NAI   Q9QYY9
1E3I    A   27.63   NAI   Q9QYY9
1E3I    B   17.867  NAI   Q9QYY9
</code></pre>

<p>Maybe you can use the <code>left_index</code> and <code>right_index</code> parameters of <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html" rel="nofollow">pandas <code>merge</code></a> method to not duplicate rows. Additionally, using <a href="http://stackoverflow.com/a/19125531/6779606">this solution</a> to not duplicate column names, I suggest the following:</p>

<pre><code>import pandas as pd


a = pd.read_csv("testfile.csv")
b = pd.read_csv("testfile_1.csv")
b = b.dropna(axis=1)
cols = b.columns.difference(a.columns)
merged = a.merge(b[cols], left_index=True, right_index=True)
merged.to_csv("output.csv", index=False)
</code></pre>

<p>which resulted in this:</p>

<pre><code>   Chain ID Ligand ID PDB ID Uniprot Acc     Avg
0        A       NAI   3RSQ      Q9X024  57.653
1        A       NAI   3RTD      Q9X024  48.806
2        A       NAI   1E3E      Q9QYY9  31.566
3        B       NAI   1E3E      Q9QYY9  17.867
4        A       NAI   1E3I      Q9QYY9   21.63
5        B       NAI   1E3I      Q9QYY9  17.867
</code></pre>

<hr>

<h1>EDIT:</h1>

<p>In order to accomplish this when the indices of each DataFrame do not correspond to the same <code>PDB ID</code>, I ended up sorting DataFrame <code>a</code> to retrieve its indices and setting the indices of the sorted version of DataFrame <code>b</code> to these values. Finally, I sort DataFrame <code>b</code> by its indices and <code>PDB ID</code> should be ordered the same way as DataFrame <code>a</code>.</p>

<pre><code>import pandas as pd


a = pd.read_csv("testfile.csv")
b = pd.read_csv("testfile_1.csv")
b = b.dropna(axis=1)
b = b.sort_values(by='PDB ID')
b.index = a.sort_values(by='PDB ID').index
b = b.sort_index()
cols = b.columns.difference(a.columns)
merged = a.merge(b[cols], left_index=True, right_index=True)
merged.to_csv("output.csv", index=False)
</code></pre>

<p>where merged resulted in this:</p>

<pre><code>    Chain ID Ligand ID PDB ID Uniprot Acc     Avg
0        A       NAI   3RSQ      Q9X024  57.653
1        A       NAI   3RTD      Q9X024  48.806
2        A       NAI   1E3E      Q9QYY9  31.566
3        B       NAI   1E3E      Q9QYY9  17.867
4        A       NAI   1E3I      Q9QYY9   27.63
5        B       NAI   1E3I      Q9QYY9  17.867
</code></pre>

<hr>

<h1>EDIT 2:</h1>

<p>Here is a much simpler solution, as found in <a href="http://stackoverflow.com/a/27314641/6779606">this answer</a>.</p>

<pre><code>import pandas as pd


a = pd.read_csv("testfile.csv")
b = pd.read_csv("testfile_1.csv")
b = b.dropna(axis=1)
merged = a.merge(b, on=['PDB ID', 'Chain ID'], how='outer')
merged.to_csv("output.csv", index=False)
</code></pre>

<p>The number of rows do not have to be equal and the result should be what you expect (my last row is an example of different number of rows):</p>

<pre><code>  Chain ID Ligand ID PDB ID Uniprot Acc     Avg
0        A       NAI   3RSQ      Q9X024  57.653
1        A       NAI   3RTD      Q9X024  48.806
2        A       NAI   1E3E      Q9QYY9  31.566
3        B       NAI   1E3E      Q9QYY9  17.867
4        A       NAI   1E3I      Q9QYY9   27.63
5        B       NAI   1E3I      Q9QYY9  17.867
6        a         a      a           a     NaN
</code></pre>

python python-2.7 python-3.x