I am trying to read in an excel file into a pandas data frame using pandas.read_excel() function. The first column is 'Date'. The first entry into that column has asterisk mark which I want to remove. Here are the first 2 rows of the data frame DF.

0   1/4/1996*   11540   10841   3592    1827    26476   14510   6900    0   0   NaN NaN NaN NaN NaN NaN NaN NaN NaN 
1   1996-01-11 00:00:00 13881   11735   2532    4188    32064   21989   3819    2586    0   NaN NaN`

In order to remove that *, I am doing this:DF['Date'][0] = '1/4/1996'. However, once I do that, I am getting a warning message

/anaconda/lib/python2.7/site-packages/ipykernel/__main__.py:3: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

and it changes the cell value, but, it also attaches another 'Date' column at the end of the df like this:

0   1/4/1996   11540   10841   3592    1827    26476   14510   6900    0   0   NaN NaN NaN NaN NaN NaN NaN NaN NaN 1/4/1996

I don't understand why this is happening.
I would greatly appreciate it if someone can explain this or point out the correct way of changing the cell value.


Use a combination of string processing and type casting to process the whole column in one swoop:

excelDF[datecol] = pandas.to_datetime(excelDF[datecol].str.replace('*',''))

I tried using str.rstrip('*') but I could not get it to work.

| improve this answer | |

The issue is in how Pandas indices / selectors work. When you only do Dataframe['Column'], you are selecting a copy/view of the dataframe. If you do Dataframe['NewColumn'], so the 'NewColumn' column doesn't exist yet, it creates this new column. But if it does exist, it will raise this warning.

What you should do is use .iloc, .ix or something similar to select data from the frame and be able to alter it.

Or follow @Emre advice.

| improve this answer | |
  • $\begingroup$ yeah specifically, df.ix[0, 'Date'] = '1/4/1996' $\endgroup$ – Julien Marrec Nov 17 '16 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.