How can I strip the whitespace from Pandas DataFrame headers?
You can give functions to the rename
method. The str.strip()
method should do what you want:
In [5]: dfOut[5]: Year Month Value0 1 2 3[1 rows x 3 columns]In [6]: df.rename(columns=lambda x: x.strip())Out[6]: Year Month Value0 1 2 3[1 rows x 3 columns]
Note: that this returns a DataFrame
object and it's shown as output on screen, but the changes are not actually set on your columns. To make the changes take place, use:
Use the
inplace=True
argument [docs]df.rename(columns=lambda x: x.strip(), inplace=True)
Assign it back to your
df
variable:df = df.rename(columns=lambda x: x.strip())
You can now just call .str.strip
on the columns if you're using a recent version:
In [5]:df = pd.DataFrame(columns=['Year', 'Month ', 'Value'])print(df.columns.tolist())df.columns = df.columns.str.strip()df.columns.tolist()['Year', 'Month ', 'Value']Out[5]:['Year', 'Month', 'Value']
Timings
In[26]:df = pd.DataFrame(columns=[' year', ' month ', ' day', ' asdas ', ' asdas', 'as ', ' sa', ' asdas '])dfOut[26]: Empty DataFrameColumns: [ year, month , day, asdas , asdas, as , sa, asdas ]%timeit df.rename(columns=lambda x: x.strip())%timeit df.columns.str.strip()1000 loops, best of 3: 293 µs per loop10000 loops, best of 3: 143 µs per loop
So str.strip
is ~2X faster, I expect this to scale better for larger dfs
If you use CSV format to export from Excel and read as Pandas DataFrame, you can specify:
skipinitialspace=True
when calling pd.read_csv
.
From the documentation:
skipinitialspace : bool, default False
Skip spaces after delimiter.