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
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