Iterate multi-index rows and columns DataFrame

Question

I have a DataFrame (1000,1000) that has Multi-Index in rows label_y1, label_y2 and columns label_x1, label_x2. I want to iterate through all the rows and columns and set everything to zero except where the selected row and column match. Ideally, this works for a single column and row - both with Multi-Index -, but it could also work for more than one column and row.

The DataFrame looks like:

local or

label_columns1 = ['testing','done']
label_columns2 = ['A', 'B']
label_rows1 = ['testing','done']
label_rows2 = ['A', 'B']

local = pd.DataFrame([[1,2,3,4]], index=pd.MultiIndex.from_product([label_rows1,label_rows2]), columns=pd.MultiIndex.from_product([label_columns1, label_columns2 ]))

print(local)

       testing    done   
             A  B    A  B
row1 A       1  2    3  4
     B       1  2    3  4
row2 A       1  2    3  4
     B       1  2    3  4

In the case of the columns I solved the problem with the following code:

for col in local.columns:
    if col != ('done', 'A'):
        local[col].values[:] = 0

This yields:

print(local)

       testing    done   
             A  B    A  B
row1 A       0  0    3  0
     B       0  0    3  0
row2 A       0  0    3  0
     B       0  0    3  0

I am doing it similarly for rows. I have also tried with local.iterrrows() and loc the rows but it does not work. Any idea on how can I do this? What I need is this:

print (local)

           testing    done   
             A  B    A  B
row1 A       0  0    0  0
     B       0  0    0  0
row2 A       0  0    3  0
     B       0  0    0  0

This solves the problem, taking as input what shared below by @zhqiat : local.loc[:,~local.columns.isin([label1])] = 0 \\ local.loc[~local.index.isin([label2]),:] = 0 — Daniel
– Daniel, Commented Nov 5, 2018 at 11:05

zglin · Accepted Answer · 2018-10-20 18:31:49Z

2

You can apply the similar logic (although inefficiently to pull that together)

import pandas as pd    
label_columns1 = ['testing','done']
label_columns2 = ['A', 'B']
label_rows1 = ['testing','done']
label_rows2 = ['A', 'B']

local = pd.DataFrame([[1,2,3,4]], index=pd.MultiIndex.from_product([label_rows1,label_rows2]), columns=pd.MultiIndex.from_product([label_columns1, label_columns2 ]))


for col in local.columns:
    for row in local.index:
        if col != ('done', 'A'):
            local.loc[:,col] = 0
        if row != ('done', 'A'):
            local.loc[row,:] = 0


print(local)



          testing    done   
                A  B    A  B
testing A       0  0    0  0
        B       0  0    0  0
done    A       0  0    3  0
        B       0  0    0  0

Additional conditions would be implemented with or /a list-like of tuples.

An alternative method would be to use the location functions in pandas to set the values of non labels as such. Additional label conditions are implemented in the list passed into the isin function.

local.loc[~local.index.isin([('done','A')]),:]=0
local.loc[:,~local.index.isin([('done','A')])]=0

edited Oct 20, 2018 at 18:31

answered Oct 18, 2018 at 19:48

zglin

2,9192 gold badges18 silver badges26 bronze badges

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

4 Comments

Daniel Over a year ago

This is not what I asked or need. I need to change the values in the DataFrame to zero. Except by those that match the column and row/index.

zglin Over a year ago

Please be a bit more clear about what you are looking to do? Also consider creating a MVCE to help people along. Expected output in a toy example is also very helpful.

Daniel Over a year ago

I have now written it how I need it. Thanks your the comment, indeed is much clearer now.

Daniel Over a year ago

For some reason, the second option sets everything to zero, including the matching row I need. I know how .isin works, but is messing with the data.

Collectives™ on Stack Overflow

Iterate multi-index rows and columns DataFrame

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related