Pandas merge 5 csv files with only 1 different column name
<p>I have 5 csv files which I'm trying to merge using Python Pandas, also I'm running 64-bit Python cause of Memory issue.</p>

<p>All 5 csv files have identical column names:
<code>['A', 'B', 'C', ... 'Start_time', 'end_time', 'Unique_column']</code></p>

<p>Here <strong>Unique_column</strong> is the different column name per CSV file. So I need to merge all 5 files with each other, so in the end I will get DataFrame as </p>

<p><code>['A', 'B', 'C', ... 'Start_time', 'end_time', 'Unique_column1', 'Unique_colum2', ... 'Unique_colum5']</code></p>

<p>Is it <code>pandas.merge</code> or <code>pandas.concat</code> method?</p>

<p><strong>UPDATE</strong>:</p>

<pre><code>&gt;&gt;&gt; import os
&gt;&gt;&gt; import glob
&gt;&gt;&gt; import numpy as np
&gt;&gt;&gt; import pandas as pd
&gt;&gt;&gt; dir_name = r'C:\Users\data'
&gt;&gt;&gt; dfs = []
&gt;&gt;&gt; files = glob.glob(os.path.join(dir_name, '*.csv'))
&gt;&gt;&gt; for f in files:
...   df = pd.read_csv(f)
...   dfs.append(df)
...
&gt;&gt;&gt; common_cols = ['Target', 'POS', 'Start_Week', 'End_Week', 'Measure_Metric']
&gt;&gt;&gt; res = pd.concat([df.set_index(common_cols) for df in dfs], axis=1).reset_index()
Traceback (most recent call last):
  File "&lt;stdin&gt;", line 1, in &lt;module&gt;
  File "c:\Python27x64\lib\site-packages\pandas\tools\merge.py", line 846, in concat
    return op.get_result()
  File "c:\Python27x64\lib\site-packages\pandas\tools\merge.py", line 1031, in get_result
    indexers[ax] = obj_labels.reindex(new_labels)[1]
  File "c:\Python27x64\lib\site-packages\pandas\indexes\multi.py", line 1422, in reindex
    raise Exception("cannot handle a non-unique multi-index!")
Exception: cannot handle a non-unique multi-index!
&gt;&gt;&gt;
</code></pre>

<p>IIUC, use <code>pd.concat</code> after having set the index on each to be all common columns.</p>

<p>Imagine you have all files imported into the list <code>dfs</code></p>

<pre><code>dfs = [df1, df2, df3, df4, df5]
</code></pre>

<p>Then concat like</p>

<pre><code>common_cols = ['A', 'B', 'C', 'Start_time', 'end_time']
pd.concat([df.set_index(common_cols) for df in dfs], axis=1).reset_index()
</code></pre>

python csv pandas merge