Skip to content
Advertisement

Pandas multiIndex slicing by aggregate

I have a pandas Series (S) that has an index like:

bar  one  a
          b
     two  a
          b
baz  one  a
.
.

I have a conditional function that returns a lower dimensional index. What I am doing is performing something like S.groupby(level=(0,1)).median() > 1

This returns a Series with Index like so:

bar  one 
baz  two
foo  one 
. 
. 

How do I slice the original Series with the lower dimensional index?

I know I can reset index and select rows using .isin but I would like to use MultiIndex if possible.

Thanks in advance!

===

Here is what the actual Series (s) looks like:

BATCH    ITEM  SEQ   X   Y 
D1M2     765   6005  -5   0    5.085
         769   6005  -3  -2    6.174
         767   6005  -4  -1    5.844
         769   6005  -3  -1    5.702
                     -4   2    5.154
         767   6005  -3   2    5.337
                     -2   4    5.683
                      3   0    6.178
         769   6005  -3   2    5.128
         765   6005   1  -4    4.791

I perform the following operation:

sm = s.groupby(level=(0,1,2)).median()
sigma = sm.std()
sms = sm[sm - sm.median() < sigma/2]

Now sms looks like:

BATCH    ITEM  SEQ 
D1M2     765   6005    4.938
         769   6005    5.428

Now I want to slice the series s that match the index in sms only.

So I want this slice of s (that matches the index of sms):

BATCH    ITEM  SEQ   X   Y 
D1M2     765   6005  -5   0    5.085
         769   6005  -3  -2    6.174
                     -3  -1    5.702
                     -4   2    5.154
                     -3   2    5.128
         765   6005   1  -4    4.791

Advertisement

Answer

It’s possible only if your index levels are the same which is not the case here because in s, you have ['BATCH', 'ITEM', 'SEQ', 'X', 'Y'] and in sms, you have only ['BATCH', 'ITEM', 'SEQ'].

You have to drop X and Y levels before to match indexes:

# Statically
>>> s[s.index.droplevel(['X', 'Y']).isin(sms.index)]

# Dynamically
>>> s[s.index.droplevel(s.index.names.difference(sms.index.names)).isin(sms.index)]

# Output
BATCH  ITEM  SEQ   X   Y 
DIM2   765   6005  -5   0    5.085
       769   6005  -3  -2    6.174
                       -1    5.702
                   -4   2    5.154
                   -3   2    5.128
       765   6005   1  -4    4.791
dtype: float64
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement