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
  app.launch_new_instance()

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.

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.

  • yeah specifically, df.ix[0, 'Date'] = '1/4/1996' – Julien Marrec Nov 17 '16 at 8:22

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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