Converting a Pandas datetime index on a DataFrame object to MultiIndex with the levels "month" and "year"

Question

Say I have a table of data with monthly datetime indices (the following code gives two years, january through december):

import pandas as pd
import numpy as np
from datetime import datetime
N = 12*2
c = [datetime(1970 + i//12, (i%12)+1, 1) for i in range(N)]
d = pd.DataFrame(np.random.rand(N), index=c)
print(d)

What is the best way to convert the DateTimeIndex into a MultiIndex with the separate levels month and year? Perhaps there is a way to do this with groupby, but I'm not sure.

akuiper · Accepted Answer · 2017-03-07 04:28:53Z

4

You can construct a MultiIndex object from the year and month and assign it to the data frame's index:

import pandas as pd
d.index = pd.MultiIndex.from_arrays([d.index.year, d.index.month])

d.index
# MultiIndex(levels=[[1970, 1971], [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]],
#            labels=[[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1], [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]])

d.head()

#                  0
#1970   1   0.657130
#       2   0.047241
#       3   0.984799
#       4   0.868508
#       5   0.678536

answered Mar 7, 2017 at 4:28

akuiper

216k33 gold badges362 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DYZ · Accepted Answer · 2017-03-07 04:30:53Z

0

d.index = pd.MultiIndex.from_tuples(d.reset_index()['index'].\
                                    apply(lambda x:(x.year,x.month)))

answered Mar 7, 2017 at 4:30

DYZ

57.3k10 gold badges73 silver badges101 bronze badges

Collectives™ on Stack Overflow

Converting a Pandas datetime index on a DataFrame object to MultiIndex with the levels "month" and "year"

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related