pandas read_csv with pound sign in column headers
<p>I need to read data from a tab-delimited file where the 1st row contains column headers but the 1st character of that row is a pound sign/octothorpe/hastag <code>#</code>.</p>

<p>The data look like this:</p>

<pre><code>FILE_CONTENTS = """\
#   year-month-day  spam    eggs
1956-01-31  11  21
1985-03-20  12  22
1940-11-22  13  23
"""
</code></pre>

<p>I have a solution (answer posted below) but it feels like there is probably a better way.</p>

<p>There is a <a href="http://stackoverflow.com/questions/7086945%20%22related%20question%20about%20doing%20this%20in%20R2">related question about doing this in R</a>.</p>

<p>You still have to shift the column names by a single position to the left to account for the empty column getting created due to the removal of <code>#</code> char. </p>

<p>Then, remove the extra column whose values are all <code>NaN</code>.</p>

<pre><code>def column_cleaning(frame):
    frame.columns = np.roll(frame.columns, len(frame.columns)-1)
    return frame.dropna(how='all', axis=1)

FILE_CONTENTS = """\
#   year-month-day  spam    eggs
1956-01-31  11  21
1985-03-20  12  22
1940-11-22  13  23
"""

df = pd.read_csv(StringIO(FILE_CONTENTS), delim_whitespace=True, escapechar="#")

column_cleaning(df)
</code></pre>

<p><a href="http://i.stack.imgur.com/Lucxf.png" rel="nofollow"><img src="http://i.stack.imgur.com/Lucxf.png" alt="Image"></a></p>

python python-3.x pandas