How to create a new column based on values inside a multiindex pandas-CodePudding

I have a multi-index dataframe with custmer_key and month as index and then some fitted value.

The thing is, I want to create a new column using the value inside the month index. I have a total of 45 different months and I need to assing a number from 1 to 45 to each month in order, is there any way to do this?

                           fitted_values    period
Customer_Key    month   
12870          2018-01-01   -3.073268         1
               2018-02-01   -3.002010         2
               2018-03-01   -2.888226         3
               2018-05-01   -2.857996         5
2858439        2018-03-01   -2.857996         3
               2021-09-01   -2.857996         45
.
.
.

CodePudding user response：

It appears you want to find the months since the earliest date. You can convert the index to month period, and subtract it with the minimum value:

month = df.index.get_level_values(1).to_period('M').astype(int)
df['period'] = month - month.min()   1

df
                         fitted_values  period
Customer_Key month                            
12870        2018-01-01      -3.073268       1
             2018-02-01      -3.002010       2
             2018-03-01      -2.888226       3
             2018-05-01      -2.857996       5
2858439      2018-03-01      -2.857996       3
             2021-09-01      -2.857996      45

This assumes your month index is of datetime data type.

CodePudding user response：

Try this:

df['new_column'] = df.groupby(level=0).cumcount()