1

I'm working in forecasting the demand of a product using many scenarios per year. I have a MulitiIndexed dataframe (Simulation, Year, Month) and need to filter by one of them (let's say Simulation).

import pandas as pd
idx = pd.MultiIndex.from_tuples([(1,2020,1), (1,2020,2), (2,2020,1), (2,2020,2)], 
                                 names=['Simulation', 'Year', 'Month'])
d = {'Apples': [1,2,3,4], 'Organes': [4,5,6,8], 'Lemons': [9,10,11,12]}
df = pd.DataFrame(d, index=idx)
print(df)

Simulation   Year    Month     Apples  Oranges  Lemons
1            2020    1         1       4        9
1                    2         2       5        10
2            2020    1         3       6        11
2                    2         4       8        12

How can I filter by Simulation?

Expected output for filtering by simulation number 1 only

Simulation   Year    Month     Apples  Oranges  Lemons
1            2020    1         1       4        9
1                    2         2       5        10
jtweeder
  • 751
  • 3
  • 19
Javier Lorenzini
  • 23
  • 1
  • 4
  • 1
    Welcome to Stack Overflow, Javier! I recommend you to read the help page [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask). It is very important you share a snippet of the code you are using to achieve, check [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example). More information, more possibilities to have very good answers to solve your issue. – Ivanhercaz Feb 16 '20 at 17:04

1 Answers1

2

Say you want to index where Simulation is 1, you can use index.get_level_values as:

df[df.index.get_level_values(0) == 1]

                        Apples  Oranges  Lemons
Simulation Year Month                         
1          2020 1          10       30      10
                2          25       50       5
           2030 12         30       70       5

For multiple values, you can add an isin at the end to values in a list: 

df.loc[df.index.get_level_values(0).isin([1, 2])]


                        Apples  Oranges  Lemons
Simulation Year Month                         
1          2020 1          10       30      10
                2          25       50       5
           2030 12         30       70       5
2          2020 1          15       25      10
                2          20       50      15

get_level_values is basically returning a Int64Index containing all indices along the first axis:

df.index.get_level_values(0)
# Int64Index([1, 1, 1, 2, 2, 50], dtype='int64', name='Simulation')

We can then use the result to perform boolean indexing on the dataframe along the axis of interest.

Or you can also use pd.IndexSlice:

df.loc[pd.IndexSlice[[1,2], :, :]]

                        Apples  Oranges  Lemons
Simulation Year Month                         
1          2020 1          10       30      10
                2          25       50       5
           2030 12         30       70       5
2          2020 1          15       25      10
                2          20       50      15
yatu
  • 86,083
  • 12
  • 84
  • 139