How do I select repeated values from a pandas MultiIndex?

user_875343 0
Viewed 40

I have a large pandas dataframe which is sorted such that a multiindex saves quite a bit of space. I want to keep the efficiency of the large multiindex while selecting repeated rows in a specific order, but if I have slices in the indexers then pandas ignores duplicate values:

import pandas as pd
idx = pd.MultiIndex.from_arrays([[1,1,2,2,2,3,3],
                                 [0,1,0,1,2,0,1],
                                 ['w','w','w','x','y','w','z']])
multi = pd.DataFrame({'uid':[10,11,12,13,14,15,16]}, index=idx)

idx = pd.IndexSlice
print(multi.loc[idx[[1,2,1], :, :, :], :])

           uid
1   0   w   10
    1   w   11
2   0   w   12
    1   x   13
    2   y   14

The output I want should be six rows long, just like the above with the top two rows repeated at the end. I can get the output I want by resetting the index and merging, like this:

selector = pd.Series([1,2,1], name='level_0')
multi.loc[multi.index.get_level_values(0).isin(selector)] \
     .reset_index() \
     .merge(selector, how='right')

    level_0 level_1 level_2 uid
0         1       0       w  10
1         1       1       w  11
2         2       0       w  12
3         2       1       x  13
4         2       2       y  14
5         1       0       w  10
6         1       1       w  11

But this requires making selectors and doing multi.loc[] for every level I want to select from or else resetting the index on the entire dataframe.

Is there a way to do this by slicing a MultiIndex?

pandas dataframe python
1 Answers

Since you can't just do multi.loc[[1, 2, 1]] for some reason, perhaps you can use pd.concat with list-comprehension:

indexes = [1, 2, 1]
repeated = pd.concat([multi.loc[[i]] for i in indexes])

Output:

>>> repeated
       uid
1 0 w   10
  1 w   11
2 0 w   12
  1 x   13
  2 y   14
1 0 w   10
  1 w   11
Admin1
Related