Retrieving index from multi-index based on column values in Pandas

Question

Say I have a table as such:

  Attr   |  Foo    |  Bar     
Name|Val | 1  | 2  | 3  | 4
-----------------------------
OFO |1   | F  | T  | F  | F
    |2   | T  | F  | F  | T 
-----------------------------
ARB |5   | T  | T  | F  | F
    |6   | F  | F  | F  | T

Where my rows are controlled by an index with level 0 = {OFO, ARB} and level 1 = {1,2,5,6} and columns are level 0 = {Foo,Bar} and level 1 = {1,2,3,4} I want to be able to pull the respective row and column index from each T entry, so one return would be:

((Foo,1),(OFO,1))

I'm struggling to come up with a good solution.

Scott Boston · Accepted Answer · 2017-06-06 19:04:09Z

1

You can start with:

df.stack([0,1]).reset_index(name='value').query('value == True')

Output:

   level_0  level_1 level_2  level_3 value
3      OFO        1     Foo        2  True
5      OFO        2     Bar        4  True
6      OFO        2     Foo        1  True
10     ARB        5     Foo        1  True
11     ARB        5     Foo        2  True
13     ARB        6     Bar        4  True

edited Jun 6, 2017 at 19:04

answered Jun 6, 2017 at 18:29

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

Comments

chaserchap · Accepted Answer · 2017-06-06 18:35:22Z

0

This was my solution:

def getIndex(table):
    mask = (pd.notnull(table))
    i, j = np.where(mask)
    return list(zip(table.index[i],table.columns[j]))

With significant help from this question: Get the row and column labels for selected values in a Pandas dataframe

answered Jun 6, 2017 at 18:35

chaserchap

4910 bronze badges

Collectives™ on Stack Overflow

Retrieving index from multi-index based on column values in Pandas

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related