Divide subset of multiindex Dataframe by a value from dictionary

Question

I have multiindex DataFrame and a dictionary. I need to divide all numerical values in the DF by the number from the dictionary when the dictionary key corestponding with the 0 level index. Here is my code -- all I get is NaN values. Thanks for help.

import pandas as pd

data = {
    "ix0" : ["a", "a", "b", "b"], 
    "ix1" : ["a", "b", "a", "b"], 
    "col1" : [0, 1, 2, 3],
    "col2" : [3, 2, 1, 0]
}

dict = {
    "a" : 10,
    "b" : 100 
}

df = pd.DataFrame(data).set_index(["ix0", "ix1"])

print(df, "\n")

for letter in dict.keys():
    df.loc[letter] = df.loc[letter].divide(dict[letter])

print(df)

output:

         col1  col2
ix0 ix1            
a   a       0     3
    b       1     2
b   a       2     1
    b       3     0 

         col1  col2
ix0 ix1            
a   a     NaN   NaN
    b     NaN   NaN
b   a     NaN   NaN
    b     NaN   NaN

score 0 · Accepted Answer · answered Oct 18 '18 at 07:04

0

Dont use variable name dict, because python code word.

Loop by dictionary by .items() and for select in MultiIndex use slicers:

d = {
    "a" : 10,
    "b" : 100 
}

idx = pd.IndexSlice

for k, v in d.items():
    df.loc[idx[k, :], :] /=  v
    #same like
    #df.loc[idx[k, :], :] =  df.loc[idx[k, :], :] / v

print(df)
         col1  col2
ix0 ix1            
a   a    0.00  0.30
    b    0.10  0.20
b   a    0.02  0.01
    b    0.03  0.00

answered Oct 18 '18 at 07:04

jezrael

822,522
95
1,334
1,252

It works, thank you. BTW why my `for` loop doesnt work? – BoB Oct 18 '18 at 07:35
I think problem is with `df.loc[letter]` it not return `MultiIndex` DataFrame – jezrael Oct 18 '18 at 07:35

Divide subset of multiindex Dataframe by a value from dictionary

1 Answers1