CODEWITHSUNDEEP
pythonpandasdatetimeindexing
KOB
KOB

Reputation: 4555

Set two levels of indexes (indices?) as datetime.date() and datetime.time() using a column of datetime objects

I have a DataFrame that has a column 'timestamp' which consists of datetime objects (YYYY-mm-dd HH:MM:SS). I would like to extract the date (datetime.date()) from these timestamps and set it as the level 0 index, and the time (datetime.time()) as the level 1 index.

Example:

                   timestamp    value1    value2
index    
    0    2018-01-01 09:00:00        10        20
    1    2018-01-01 09:01:00        11        21
    2    2018-01-02 09:00:00        12        22
    3    2018-01-02 09:01:00        13        23

Would become:

                          value1    value2
date          time
2018-01-01    09:00:00        10        20
              09:01:00        11        21
2018-01-02    09:00:00        12        22
              09:01:00        13        23

Upvotes: 2

Views: 51

Answers (2)

piRSquared
piRSquared

Reputation: 294488

Option 1
Use drop and set_index

df.set_index([df.timestamp.dt.date, df.timestamp.dt.time]).drop('timestamp', 1)

                      value1  value2
timestamp  timestamp                
2018-01-01 09:00:00       10      20
           09:01:00       11      21
2018-01-02 09:00:00       12      22
           09:01:00       13      23

Option 2 

d = df.set_index('timestamp')
d.index = [d.index.date, d.index.time]
d

                     value1  value2
2018-01-01 09:00:00      10      20
           09:01:00      11      21
2018-01-02 09:00:00      12      22
           09:01:00      13      23

Upvotes: 3

jezrael
jezrael

Reputation: 863226

Use set_index with MultiIndex.from_arrays and last drop original column:

mux = pd.MultiIndex.from_arrays([df['timestamp'].dt.date, df['timestamp'].dt.time], 
                                 names=('date','time'))
df = df.set_index(mux).drop('timestamp', 1)

Or add rename_axis:

df = (df.set_index([df['timestamp'].dt.date, df['timestamp'].dt.time])
        .drop('timestamp', 1)
        .rename_axis(('date','time')))

print (df)
                     value1  value2
date       time                    
2018-01-01 09:00:00      10      20
           09:01:00      11      21
2018-01-02 09:00:00      12      22
           09:01:00      13      23

Upvotes: 2

Related Questions