Merging CSV Using pandas dataframe
<p>I am using the below code. All my CSV files have uniform structure. When a dataframe is formed, it contains two columns for date in my CSV.</p>

<p>In the resulting dataframe, for few rows date value is in first date column, while for rest of the data, it goes to second date column.</p>

<p>Any idea, why two columns (Date columns), are getting generated for one column in the source CSV files.</p>

<pre><code>all_data = pd.DataFrame()
for f in glob.glob("/Users/tcssig/Desktop/Files/*.csv"):
    df = pd.read_csv(f)
    all_data = all_data.append(df,ignore_index=True)

In [76]: all_data.columns
Out[76]: Index(['0', '0.1', 'Channel_ID', 'Date', 'Date ', 'Duration (HH:MM)','Episode #', 'Image', 'Language', 'Master House ID', 'Parental Rating','Program Category', 'Program Title', 'StartTime_ET', 'StartTime_ET2','Synopsis'],
 dtype='object')
</code></pre>

<p>because you have a space in the second column:</p>

<pre><code>'Date', 'Date '
             ^
</code></pre>

<p>so you need to normalise the columns prior to appending</p>

<pre><code>all_data = pd.DataFrame()
for f in glob.glob("/Users/tcssig/Desktop/Files/*.csv"):
    df = pd.read_csv(f)
    df.columns = df.columns.str.strip()
    all_data = all_data.append(df,ignore_index=True)
</code></pre>

<p>here I use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.str.strip.html#pandas.Series.str.rstrip"><code>str.strip</code></a> to remove any leading and trailing whitespace</p>

python csv pandas