How do I remove the year and month from a DatetimeIndex in pandas?

Question

I have a pandas dataframe:

import numpy as np
import pandas as pd
idx = pd.DataFrame(["2017-01-01 00:00:41","2017-01-01 00:06:53",\
                   "2017-01-01 00:07:10"],columns=["DateTime"])
df = pd.DataFrame([221,676,356],columns=["Value"])
df.index = pd.DatetimeIndex(idx["DateTime"])
df

which produces something like

      DateTime         Value
2017-01-01 00:13:41     221
2017-01-02 00:06:53     676
2017-01-05 00:22:10     356

What I would like to do next is remove the year and month information from this DateTime index, so as to produce

  DateTime     Value
01 00:13:41     221
02 00:06:53     676
05 00:22:10     356

I know that in the DataFrame idx I can drop it as follows:

idx["DateTime"] = idx["DateTime"].str(8:)

The problem is that this is no longer recognised by pandas as a DatetimeIndex. Moreover plotting this becomes very tricky (something I'd like to do subsequently). Any ideas how I can achieve this? (I'm sure it can be done, Python/pandas are too versatile not to have some cunning trick for achieving it!)

True. But I deeply suspect there must be a neat python/pandas approach to achieving it. Formatting axes feels comparatively ugly. — Stromael, Oct 31 '18 at 15:25
@Stromael Curious if you every found a good solution to this? — Stephen C, Oct 31 '18 at 17:57

Stephen C · Answer 1 · 2018-10-31T15:37:01.947

1

If you just want the time, this would be what you are looking for

df.index = df.index.time

>>>df
          Value
00:00:41    221
00:06:53    676
00:07:10    356

>>>type(df.index[0])
datetime.time

As far as I can tell, the two possible objects are time objects and datetime objects, which means either way, you would need something for the year...

You could try to just standardize the year across them all like this:

df.index = [pd.datetime.strptime(s, '%d %HH-%MM-%SS') 
               for s in [pd.datetime.strftime(values, "%d %HH-%MM-%SS") 
               for values in df.index]]

(Just a first attempt... maybe a simpler way to accomplish this...)

edited Oct 31 '18 at 15:37

answered Oct 31 '18 at 15:21

Stephen C

1,966
1
16
30

I tried `df.index.time`, but it leaves me without the day information. I want the output index to have the form DD HH:MM:SS. – Stromael Oct 31 '18 at 15:28
1

@Stephen Try plotting something with several days. This will cause overlapping plots. – JE_Muc Oct 31 '18 at 15:32
Ah, sorry. Just realized you were looking for day _and_ time. But leaving off the year.... hmmm... – Stephen C Oct 31 '18 at 15:32

iamklaus · Answer 2 · 2018-10-31T15:56:10.800

1

This works for me (updated)

df

                    Datetime  Values
0 2018-10-31 21:24:08.380554     258
1 2018-10-31 21:24:09.002616     586

df['Datetime'] = df['Datetime'].apply(lambda x: datetime.strftime(x, "%d %H:%M:%S"))

      Datetime  Values
0  31 21:24:08     258
1  31 21:24:09     586

edited Oct 31 '18 at 15:56

answered Oct 31 '18 at 15:27

iamklaus

3,720
2
12
21

This is not showing dates when plotting the dataframe. – JE_Muc Oct 31 '18 at 15:32
Converts to strings which is the OP problem. – Stephen C Oct 31 '18 at 15:32

score 0 · Answer 3 · answered Oct 31 '18 at 15:35

0

try this,

print (df['DateTime'].dt.day).astype(str) +' '+ (df['DateTime'].dt.time).astype(str)

Output:

0    1 00:13:41
1    2 00:06:53
2    5 00:22:10
Name: DateTime, dtype: object

answered Oct 31 '18 at 15:35

Mohamed Thasin ah

10,754
11
52
111

How do I remove the year and month from a DatetimeIndex in pandas?

3 Answers3