8

I have a dataframe where the names of the columns are dates (Year-month) in the form of strings. How can I convert these names in datetime format? I tried doing this:

new_cols = pd.to_datetime(df.columns)
df = df[new_cols]

but I get the error:

KeyError: "DatetimeIndex(
['2000-01-01', '2000-02-01',
 '2000-03-01', '2000-04-01',
 '2000-05-01', '2000-06-01', 
'2000-07-01', '2000-08-01',               
'2000-09-01', '2000-10-01',
'2015-11-01', '2015-12-01', 
'2016-01-01', '2016-02-01',
'2016-03-01', '2016-04-01', 
'2016-05-01', '2016-06-01',
'2016-07-01', '2016-08-01'],
dtype='datetime64[ns]', length=200, freq=None) not in index"

Thanks!

Istvan
  • 7,500
  • 9
  • 59
  • 109
gtroupis
  • 161
  • 1
  • 4
  • 13

2 Answers2

26

If select by loc columns values was not changed, so get KeyError.

So you need assign output to columns:

df.columns = pd.to_datetime(df.columns)

Sample:

cols = ['2000-01-01', '2000-02-01', '2000-03-01', '2000-04-01', '2000-05-01']
vals = np.arange(5)
df = pd.DataFrame(columns = cols, data=[vals])
print (df)
   2000-01-01  2000-02-01  2000-03-01  2000-04-01  2000-05-01
0           0           1           2           3           4

print (df.columns)
Index(['2000-01-01', '2000-02-01', '2000-03-01', '2000-04-01', '2000-05-01'], dtype='object')

df.columns = pd.to_datetime(df.columns)

print (df.columns)
DatetimeIndex(['2000-01-01', '2000-02-01', '2000-03-01', '2000-04-01',
               '2000-05-01'],
              dtype='datetime64[ns]', freq=None)

Also is possible convert to period:

print (df.columns)
Index(['2000-01-01', '2000-02-01', '2000-03-01', '2000-04-01', '2000-05-01'], dtype='object')

df.columns = pd.to_datetime(df.columns).to_period('M')

print (df.columns)
PeriodIndex(['2000-01', '2000-02', '2000-03', '2000-04', '2000-05'],
             dtype='period[M]', freq='M')
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • does this work on a subset of columns? e.g., `df.columns = ['ID', '2000-01', '2000-02']` convert only the date columns to datetime. – Brian D Jun 26 '19 at 20:23
  • 1
    Tried the following `df.columns[2:] = pd.to_datetime(df.columns[2:])` but that returns an error `TypeError: Index does not support mutable operations` – Brian D Jun 26 '19 at 20:43
  • 2
    @BrianD - Use `df.columns = df.columns[:1].tolist() + pd.to_datetime(df.columns[1:]).tolist()` – jezrael Jun 27 '19 at 04:05
2

As an expansion to jezrael's answer, the original code will be trying to slice the df array by the array stored in new_cols and store the result as df - but since those values don't exist in df yet it returns an error saying it can't find that index to slice by.

As such you need to declare that you're changing the name of the columns, as in jezrael's answer.

Fred Cascarini
  • 62
  • 1
  • 6