How to convert multi-indexed datetime index into integer?

Question

I have a multi indexed dataframe(groupby object) as the result of groupby (by 'id' and 'date').

                x  y
id  date            
abc 3/1/1994  100  7
    9/1/1994   90  8
    3/1/1995   80  9
bka 5/1/1993   50  8
    7/1/1993   40  9

I'd like to convert those dates into an integer-like, such as

             x  y
id  date            
abc day 0  100  7
    day 1   90  8
    day 2   80  9
bka day 0   50  8
    day 1   40  9

I thought it would be simple but I couldn't get there easily. Is there a simple way to work on this?

check [this](https://stackoverflow.com/questions/33899369/ranking-order-per-group-in-pandas) asnwer. — vb_rises, Oct 02 '19 at 19:17
since `groupby` ensures a unique index, you could `cumcount` — ALollz, Oct 02 '19 at 19:19

score 1 · Answer 1 · answered Oct 02 '19 at 19:21

You can calculate the new level and create a new index:

lvl1 = 'day ' +  df.groupby('id').cumcount().astype('str')
df.index = pd.MultiIndex.from_tuples((x,y) for x,y in zip(df.index.get_level_values('id'), lvl1) )

output:

             x  y
abc day 0  100  7
    day 1   90  8
    day 2   80  9
bka day 0   50  8
    day 1   40  9

score 1 · Accepted Answer · answered Oct 02 '19 at 19:23

1

Try this:

s = 'day ' + df.groupby(level=0).cumcount().astype(str)
df1 = df.set_index([s], append=True).droplevel(1)

             x  y
id
abc day 0  100  7
    day 1  90   8
    day 2  80   9
bka day 0  50   8
    day 1  40   9

answered Oct 02 '19 at 19:23

Andy L.

24,909
4
17
29

How to convert multi-indexed datetime index into integer?

2 Answers2