How to slice a Pandas DataFrame with a MultiIndex index and a MultiIndex column?

Question

I am looking to create a new DataFrame that corresponds to the results of Devices A and B based on Silicon.

The following is my code for creating the DataFrame:

import numpy as np
import pandas as pd

x = np.array(
    [
        [0.26, 0.92, 0.05, 0.43],
        [1.00, 0.62, 1.00, 1.00],
        [1.00, 0.97, 0.04, 1.00],
        [0.00, 1.00, 1.00, 0.88],
        [1.00, 1.00, 1.00, 0.79],
        [0.98, 1.00, 0.79, 0.99],
        [0.99, 1.00, 1.00, 1.00],
        [0.18, 1.00, 0.26, 1.00],
        [0.22, 0.00, 0.34, 0.82],
    ]
)
rowIndx = pd.MultiIndex.from_product(
    [["Slurm", "Zoidberg", "Wernstrom"], ["A", "B", "C"]],
    names=["Laboratory", "Device"],
)
colIndex = pd.MultiIndex.from_product(
    [["Replicant 1 ", "Replicant 2 "], ["Silicon", "Carbon"]]
)
robot = pd.DataFrame(data=x, index=rowIndx, columns=colIndex)
robot

Here is an image of the table.

This is the code that I thought would somewhat work, but it just gives me errors, so now I don't know what to try, robot[(robot.Device=="A") & (robot.Device=="B")][["Silicon"]]

A table that include the Silicon values for all of the A & B's. — Fire
– Fire, Commented Dec 4, 2019 at 7:42
First of all robot.Device cannot be equal to "A" & "B" at the same time. You need "|". robot[(robot.Device=="A") | (robot.Device=="B")]. Secondly you have a multiindex dataframe and if you want to access robot.Device one way is reset_index() — Iron Hand Odin
– Iron Hand Odin, Commented Dec 4, 2019 at 7:43
and finally you can read more from here <pandas.pydata.org/pandas-docs/stable/user_guide/advanced.html> — Iron Hand Odin
– Iron Hand Odin, Commented Dec 4, 2019 at 7:52
Is there a different way to do it that doesn't involve reset_index()? I am open to other options. I am very poor at Python, and from the limited knowledge I have, this was just the only code that was the closest code I could remember. — Fire
– Fire, Commented Dec 4, 2019 at 7:52

charlesreid1 · Accepted Answer · 2019-12-04 08:21:18Z

3

I think you want something like this:

In [6]: robot.loc[:, (robot.columns.get_level_values(level=1)=='Silicon')]
Out[6]:
                  Replicant 1  Replicant 2
                       Silicon      Silicon
Laboratory Device
Slurm      A              0.26         0.05
           B              1.00         1.00
           C              1.00         0.04
Zoidberg   A              0.00         1.00
           B              1.00         1.00
           C              0.98         0.79
Wernstrom  A              0.99         1.00
           B              0.18         0.26
           C              0.22         0.34

Two keys things here: The first key is using robot.loc[ _ , _ ] (specifying two arguments, one for the index and one for the column); this has to be something your MultiIndex-type index and your MultiIndex-type columns can understand.

The second key is the robots.columns.get_level_values(level=1), which gets the 4 column labels for level 1 (carbon/silicon) for the 4 columns displayed in the image of the DataFrame:

In [7]: robot.columns.get_level_values(level=1)
Out[7]: Index(['Silicon', 'Carbon', 'Silicon', 'Carbon'], dtype='object')

and it then filters which columns to show based on the given condition:

In [8]: robot.columns.get_level_values(level=1)=='Silicon'
Out[8]: array([ True, False,  True, False])

If you had more elements besides Silicon, you could use the | operator (not the & operator) like this:

robot.loc[:, (robot.columns.get_level_values(level=1)=='Silicon')|(robot.columns.get_level_values(level=1)=='Carbon')]

or a bit shorter:

lv = robot.columns.get_level_values(level=1)
robot.loc[:, (lv=='Silicon')|(lv=='Carbon')]

UPDATE: If you also want to filter values in the index, you can use robot.index.get_level_values() instead of robot.columns.get_level_values(). Here's an example:

lv = robot.columns.get_level_values(level=1)
ilv = robot.index.get_level_values(level=1)
robot.loc[(ilv=='A')|(ilv=='B'), (lv=='Silicon')]

We've replaced the : (which means all values of all levels of the MultiIndex) with a logical mask to filter indices, the same way we did to filter columns.

edited Dec 4, 2019 at 8:21

answered Dec 4, 2019 at 8:07

charlesreid1

4,9114 gold badges35 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Fire Over a year ago

Yes! This is what I am looking for, and thank you for the in depth explanation. I have seen the .loc function before, but didn't know it could be used on data frames. Is there any way to remove the C, and only have A & B for Devices?

charlesreid1 Over a year ago

Yea, the idea is the same but you just use robot.index.get_level_values() instead of robot.columns.get_level_values(), and pass that to the first arg of robot.loc[_, _]. See updated answer!

Fire Over a year ago

I'm sorry, I have one more question. If we keep A & B for Devices, but then restrict it to only giving us Silicon from Replicant 1, how would I go about that? I attempted to with your code, v = robot.columns.get_level_values(level=0) Level zero to indicate the Replicants, but when I plug that into code, w = robot.loc[(ilv=='C'), (v=='Replicant 1'),(lv=='Carbon')] It doesn't work. So I must be doing something incorrectly. Perhaps it cannot be done this way?

charlesreid1 Over a year ago

(v=='Replicant 1'),(lv=='Carbon') should be (v=='Replicant 1')&(lv=='Carbon'), the robot.loc[_,_] call should only have 2 arguments

Mohammad Nazari · Accepted Answer · 2019-12-04 08:12:30Z

1

your dataframe is MultiIndex , So you need to use the following code to select a row:

result = robot.iloc[(robot.index.get_level_values('Device') == 'A')|(robot.index.get_level_values('Device') == 'B')]

Now, if you only want column Silicon use the following code:

result.iloc[:, result.columns.get_level_values(1)== "Silicon"]

answered Dec 4, 2019 at 8:12

Mohammad Nazari

3,0952 gold badges22 silver badges32 bronze badges

Comments

gbruenjes · Accepted Answer · 2019-12-04 08:26:32Z

1

Use slicers like this:

robot.loc[(slice(None), ['A', 'B']), (slice(None), 'Silicon')]

                  Replicant 1  Replicant 2 
                       Silicon      Silicon
Laboratory Device                          
Slurm      A              0.26         0.05
           B              1.00         1.00
Zoidberg   A              0.00         1.00
           B              1.00         1.00
Wernstrom  A              0.99         1.00
           B              0.18         0.26

or:

idx = pd.IndexSlice
robot.loc[idx[:, ['A', 'B']], idx[:, 'Silicon']]

edited Dec 4, 2019 at 8:26

answered Dec 4, 2019 at 8:21

gbruenjes

4,2251 gold badge18 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to slice a Pandas DataFrame with a MultiIndex index and a MultiIndex column?

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related