1

I have a df with weekly columns as below. I want to change my column index to timestamp. Here is my df.columns

df.columns:
Int64Index([201601, 201602, 201603, 201604, 201605, 201606, 201607,
        201608, 201609,
        ...],
       dtype='int64', name='timeline', length=104)

df.columns[0]:
201553

I want to change my df.columns into timestamp as below

 df.columns:
 DatetimeIndex(['2016-01-04', '2016-01-11', '2016-01-18', '2016-01-25',
           '2016-02-01', '2016-02-08', '2016-02-15', '2016-02-22',
           '2016-02-29'.....],
           dtype='int64', name='timeline', length=104)
df.columns[0]:
Timestamp('2016-01-04 00:00:00')

Bottom line is that my df.columns are in int format that indicates the yyyyww value. From this int, I want to change it to Timestamp that shows date of Monday of each week. Please let me know a good way to change this. THank you

EJ Kang
  • 455
  • 2
  • 5
  • 17

1 Answers1

3

You can use to_datetime, but first add 1 for Mondays and use %W with%w:

Source - http://strftime.org/:

%w Weekday as a decimal number, where 0 is Sunday and 6 is Saturday.
%W Week number of the year (Monday as the first day of the week) as a decimal number. All days in a new year preceding the first Monday are considered to be in week 0.

a = pd.Int64Index([201601, 201602, 201603, 201604, 201605, 201606, 201607,
        201608, 201609])

print (pd.to_datetime(a.astype(str) + '1', format='%Y%W%w'))
DatetimeIndex(['2016-01-04', '2016-01-11', '2016-01-18', '2016-01-25',
               '2016-02-01', '2016-02-08', '2016-02-15', '2016-02-22',
               '2016-02-29'],
              dtype='datetime64[ns]', freq=None)
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • One thing that keeps bugging me is that my other set of data colums starts with '201553' which is a week before 201601. And when I apply the code above, 201553 keep turning into 20160104. Do you have any idea why? – EJ Kang Jan 15 '18 at 11:07
  • YEs, it is a bit coplicated, I think it is explain [here](https://stackoverflow.com/q/29262859/2901002) – jezrael Jan 15 '18 at 11:24
  • If datetimeindex I believe you can use `df = df.rename({pd.datetime(2016, 1, 4): pd.datetime(2015, 12, 28)})` – jezrael Jan 15 '18 at 11:40
  • I tried SI_col[0] = pd.datetime(2015,12,28) and 'Index does not support mutable operations' error occurs. – EJ Kang Jan 16 '18 at 00:56
  • You need create new index, so use `df.index = [pd.datetime(2015,12,28)] + SI_col[1:].tolist()` – jezrael Jan 16 '18 at 05:13