Round floats in pandas multiindex DataFrame

user_14567063 0
Viewed 601

I am given a pandas multiindex DataFrame indexed with floats. Consider the following example:

arrays = [[0.21,0.21,0.21,0.22,0.22,0.22,0.23,0.23,0.23],
          [0.81,0.8200000000000001,0.83,0.81,0.8200000000000001,0.83,0.81,0.8200000000000001,0.83]]
df = pd.DataFrame(np.random.randn(9, 2), index=arrays)

df

#               0           1
# 0.21  0.81    -2.234036   -0.145643
#       0.82    0.367248    -1.471617
#       0.83    -0.764520   0.686241
# 0.22  0.81    1.380429    1.546513
#       0.82    1.230707    1.826980
#       0.83    -1.198403   0.377323
# 0.23  0.81    -0.418367   -0.125763
#       0.82    0.682860    -0.119080
#       0.83    -1.802418   0.357573

I am given this DataFrame in this form. Now, if I want to retrieve the entry df.loc[(0.21, 0.82)] I get an error because the index doesn't really carry 0.82 but 0.8200000000000001. I don't know in advance where these problems occur in the index. How can I address this problem? My idea is to round both levels of the multiindex to the significant number of decimals, which is 2 in this case. But how can that be done? Is there a better solution?

multi-index pandas dataframe indexing python
2 Answers

You can use the rename function to apply a function to each value of your MultiIndex:

df = df.rename(index=lambda val: round(val, 2))

print(df.loc[(.21, .82)])
0    0.260015
1   -0.233822
Name: (0.21, 0.82), dtype: float64

However I'm not sure if having floats as specific keys due to https://docs.python.org/3/tutorial/floatingpoint.html (brief example)

>>> .1 + .1 + .1 == .3
False

Though I'm curious as to what others think of this. Because I'm not sure of the realistic possible issues you'd encounter.

You could always truncate your floats to strings and then access your dataframe via strings instead to ensure accuracy:

df = df.rename(index="{:.2f}".format)

print(df.loc[("0.21", "0.82")]) # note that the leading 0 is important here now
0    0.260015
1   -0.233822
Name: (0.21, 0.82), dtype: float64
user_142784480

Consider using integer numbers instead: multiply your floating-point numbers by 100 (or 1000) and convert to ints:

df.index = pd.MultiIndex.from_product([
             (df.index.levels[0] * 100).astype(int),
             (df.index.levels[1] * 100).astype(int)])

Integer numbers are precise, unlike floating-point numbers. Now, you can use df.loc[(21, 82)] to access your data.

user_44929320
Related