How to change outer level index in multi index in pandas dataframe?

user_13051902 0
Viewed 127

I've a dataframe with multi-level index with 9 million rows as follows. I wish to change the 0 level index which is non-unique in some cases to numerical order containing unique indices. Here's sample input: 

ORDER             NO         DATE
4200000303        1          2015-02-19
                  2          2015-02-19
                  3          2015-02-19
1200000303        1          2016-07-19
                  2          2016-07-19
                  3          2016-07-19
                  4          2016-07-19
1500000303        1          2017-08-19
                  2          2017-08-19
                  3          2017-08-19
                  4          2017-08-19
x8594345 rows

The desired output should be: 

ORDER             NO         DATE
0                 1          2015-02-19
                  2          2015-02-19
                  3          2015-02-19
1                 1          2016-07-19
                  2          2016-07-19
                  3          2016-07-19
                  4          2016-07-19
2                 1          2017-08-19
                  2          2017-08-19
                  3          2017-08-19
                  4          2017-08-19
x8594345 rows

I've tried the following code: 

transactions.index = pd.MultiIndex.from_arrays(
    [list(range(0,transactions.shape[0])), transactions.groupby(level=0).cumcount()],
    names=['ORDER', 'NO'])

But it's returning a flattened dataframe.

multi-index pandas dataframe numpy python
2 Answers

Use factorize by first level only:

transactions.index = pd.MultiIndex.from_arrays(
                              [pd.factorize(transactions.index.get_level_values(0))[0],
                               transactions.index.get_level_values(1)],
                              names=['ORDER', 'NO'])

print (transactions)
ORDER  NO
0      1     2015-02-19
       2     2015-02-19
       3     2015-02-19
1      1     2016-07-19
       2     2016-07-19
       3     2016-07-19
       4     2016-07-19
2      1     2017-08-19
       2     2017-08-19
       3     2017-08-19
       4     2017-08-19
Name: A, dtype: object
user_29010020

There might be a better way, but I would go for this now:

(df.reset_index()
  .assign(ORDER=lambda x: x['ORDER'].factorize()[0])
  .set_index(['ORDER','NO'])
)
user_42384080
Related