1

I have a pandas series myS

import pandas as pd

The index is a set of strings containing time only

myS.index

Out[28]: 
Index([u'12:00 AM', u'12:14 AM', u'12:18 AM', u'12:25 AM', u'12:26 AM',
       u'12:37 AM', u'12:41 AM', u'12:47 AM', u'12:55 AM', u'12:59 AM',
       ...
       u'11:00 PM', u'11:02 PM', u'11:09 PM', u'11:18 PM', u'11:25 PM',
       u'11:35 PM', u'11:42 PM', u'11:46 PM', u'11:50 PM', u'11:55 PM'],
      dtype='object', name=u'Time (CET)', length=169)

I can conveniently convert this to datetime correctly:

myS.index= pd.to_datetime(myS.index, format='%I:%M %p')

However, all the dates will be set to 1900-01-01

 '1900-01-01 23:50:00', '1900-01-01 23:55:00'],
              dtype='datetime64[ns]',

If I have a datetime available, how can I reset all the dates of the index to a desired value while leaving intact the time?

00__00__00
  • 4,834
  • 9
  • 41
  • 89

2 Answers2

2

I think you need add Date column and then convert to datetime:

myS.index = pd.to_datetime(myS['Date'].astype(str) + ' ' + myS.index)

Or add scalar:

myS.index = pd.to_datetime('2015-01-05' + ' ' + myS.index)

EDIT by comment:

myS.index = pd.to_datetime(str(mydatetime.date()) + ' ' + myS.index, 
                           format='%Y-%m-%d %I:%M %p',errors='coerce')

Or use strftime:

myS.index = pd.to_datetime(mydatetime.strftime('%Y-%m-%d') + ' ' + 
                           myS.index, format='%Y-%m-%d %I:%M %p',errors='coerce')

Sample:

idx = pd.Index([u'12:00 AM', u'12:14 AM', u'12:18 AM', u'12:25 AM'])
myS = pd.Series(range(4), index=idx)
print (myS)
12:00 AM    0
12:14 AM    1
12:18 AM    2
12:25 AM    3
dtype: int64

mydatetime = pd.datetime.now()
print (mydatetime)
2017-12-18 07:52:26.503385

myS.index = pd.to_datetime(str(mydatetime.date()) + ' ' + 
                           myS.index, format='%Y-%m-%d %I:%M %p',errors='coerce')

print (myS)
2017-12-18 00:00:00    0
2017-12-18 00:14:00    1
2017-12-18 00:18:00    2
2017-12-18 00:25:00    3
dtype: int64
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • This solution is correct. However, if my datetime was a single value and not an existing dataframe column, astype(str) would not work (AttributeError: 'datetime.datetime' object has no attribute 'astype') while str(mydatetime.date()) does – 00__00__00 Dec 18 '17 at 06:46
  • Thank you for comment, I add it to answer. – jezrael Dec 18 '17 at 06:48
  • oops, I lost your comment, sorry:( – jezrael Dec 18 '17 at 06:53
1
  1. Prepend all time strings with your desired date string.
  2. Use pd.to_datetime, with additional format string for your date.

For example, if you had 2017-03-05 12:18 AM, your format string becomes %Y-%m-%d %I:%M %p.

myS.index = pd.to_datetime('2017-03-05 ' + myS.index, '%Y-%m-%d %I:%M %p')

(Turns out the default format works well for this case, so format='%Y-%m-%d %I:%M %p' is optional.)

James Lim
  • 12,915
  • 4
  • 40
  • 65
  • thanks, it was correct. However, I am not really in favor of automatic format recognition as you will always get a 10-2-2017 somewhere, and the results might be data dependent if you do not set explict conversion rules – 00__00__00 Dec 18 '17 at 07:14