0

I have data which is in-64 in the Index with values like "01/11/2018" in the index. It is data that has been imported from a csv. I am unable to convert it to a "01-11-2018" format. How do I do this because I get an error message:

'time data 0 does not match format '%Y' (match)'

I got the data from the following website: https://www.nasdaq.com/symbol/spy/historical and you can find a ' Download this file in Excel Format ' icon at the bottom.

  import datetime

  spyderdat.index =  pd.to_datetime(spyderdat.index, format='%Y')
  spyderdat.head()

How do I format this correctly?

Thanks a lot.

AnalysisNerd
  • 111
  • 2
  • 16

3 Answers3

1

Your format string must match exactly:

import datetime

spyderdat.index =  pd.to_datetime(spyderdat.index, format='%d/%m/%Y')
spyderdat.head()

Example w/o spyder: 

import datetime

date = "1/11/2018"
print(datetime.datetime.strptime(date,"%d/%m/%Y"))

Output:

2018-11-01 00:00:00

You can strftime this datetime then anyhow you like. See link for formats. Or you store datetimes.

Patrick Artner
  • 50,409
  • 9
  • 43
  • 69
  • 1
    I still get the error message 'time data 0 does not match format '%d/%m/%Y' (match)' – AnalysisNerd Nov 01 '18 at 22:23
  • 1
    @AnalysisNerd this should work if you really have strings in the given format - check for None values and check the format. It has to match exactly. – Patrick Artner Nov 01 '18 at 22:24
  • 1
    the values are int64 before I format them. The data I have is "01/11/2018". – AnalysisNerd Nov 01 '18 at 22:31
  • 1
    You probably already have datetime-objects - and padas is simply displaying them as you see them. Check https://stackoverflow.com/questions/38067704/how-to-change-the-datetime-format-in-pandas and if its a dupe, mark yours as it – Patrick Artner Nov 01 '18 at 22:33
  • 1
    I get 1970-01-01 00:00:00.000000000 01/11/2018 when I try spyderdat.index = pd.to_datetime(spyderdat.index ) – AnalysisNerd Nov 01 '18 at 22:36
1

Assuming your input is a string, simply converting the / to - won't fix the issue.
The real problem is that you've told to_datetime to expect the input string to be only a 4-digit year but you've handed it an entire date, days and months included.

If you meant to use only the year portion you should manually extract the year first with something like split.
If you meant to use the full date as a value, you'll need to change your format to something like %d/%m/%Y. (Although I can't tell if your input is days first or months first due to their values.)

Mr. Llama
  • 20,202
  • 2
  • 62
  • 115
1

The easy way is to try this

datetime.datetime.strptime("01/11/2018", '%d/%m/%Y').strftime('%d-%m-%Y')
Mahdi Nazari Ashani
  • 372
  • 1
  • 5
  • 22