4

I have the following pandas (related to the example here: pandas: slice a MultiIndex by range of secondary index)

import numpy as np
import pandas as pd

variable = np.repeat(['a','b','c'], [5,5,5])
time = [0,1,5,10,20,0,1,5,10,20,0,1,5,10,20]

arra = [variable, time]

index=pd.MultiIndex.from_arrays(arra, names=("variable", "time"))

s = pd.Series(
    np.random.randn(len(sequence)), 
    index=index
)

Output would be

# In [1]: s
variable  time
a         0      -1.284692
          1      -0.313895
          5      -0.980222
          10     -1.452306
          20     -0.423921
b         0       0.248625
          1       0.183721
          5      -0.733377
          10      1.562653
          20     -1.092559
c         0       0.061172
          1       0.133960
          5       0.765271
          10     -0.648834
          20      0.147158
dtype: float64

If I slice here on both multiindex, it would work like this: 

# In [2]: s.loc[("a",0),:]
variable  time
a         0       1.583589
          1      -1.081401
          5      -0.497904
          10      0.352880
          20     -0.179062
dtype: float64

But how can I just slice on secondary index "time" at e.g. time=0 and get every row with first index? The following won't work:

# In [3]: s.loc[(0),:]

KeyError: 0

How would I do that here?

Rockbar
  • 1,081
  • 1
  • 20
  • 31
  • Possible dupe of [Python Pandas slice multiindex by second level index (or any other level)](https://stackoverflow.com/q/33194016/2662901) – feetwet Sep 10 '22 at 17:04

2 Answers2

7

Use xs with specify second level or loc with : for select all values of first level and 0 for select values of second level:

print (s.xs(0, level=1))

Or:

print (s.loc[:, 0])


a    0.376784
b   -0.643836
c   -0.440340
dtype: float64

If working with indices and column(s) of DataFrame use slicers:

idx = pd.IndexSlice
df = pd.concat([s,s * 10], axis=1, keys=['a','b'])
print (df)
                      a          b
variable time                     
a        0     1.054582  10.545820
         1    -1.716213 -17.162130
         5    -0.187765  -1.877645
         10   -0.419005  -4.190047
         20   -0.772808  -7.728078
b        0    -0.022520  -0.225202
         1    -0.638453  -6.384531
         5     0.410156   4.101559
         10    0.512189   5.121889
         20   -1.241232 -12.412322
c        0    -0.134815  -1.348148
         1    -1.007632 -10.076318
         5    -0.859790  -8.597898
         10   -0.623177  -6.231767
         20   -0.635504  -6.355036

print (df.loc[idx[:, 0], 'a'])
variable  time
a         0       1.054582
b         0      -0.022520
c         0      -0.134815
Name: a, dtype: float64
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Great. ".xs" works now, I am currently trying the solution with my real dataframe, but I want to select/keep all columns instead of "a". Leaving only the comma results in " UnsortedIndexError: 'MultiIndex Slicing requires the index to be fully lexsorted tuple len (2), lexsort depth (1)'" – Rockbar Dec 06 '17 at 09:29
  • Yes, then need `df = df.sort_index()` first. – jezrael Dec 06 '17 at 09:29
  • In docs it is [here](http://pandas.pydata.org/pandas-docs/stable/advanced.html#sorting-a-multiindex) – jezrael Dec 06 '17 at 09:30
  • Can I ask a very related question to the same example here, or do I need a new question – Rockbar Dec 06 '17 at 14:12
  • I am offline now, on phone only, so better new question. – jezrael Dec 06 '17 at 14:59
  • 1
    ok, done. was a misinterpretation on my side. everything fine now. Thanks again – Rockbar Dec 06 '17 at 15:28
1

The answer suggested by jezrael is neat and works. As an alternative approach you could use swaplevel() like this:

print(s.swaplevel().loc[0, :])

variable
a    1.534837
b    1.311133
c    0.215539
dtype: float64
EyJay
  • 180
  • 1
  • 8