Pandas - replace, float and sum of columns

user_13174083 0
Viewed 736

I have an example:

import pandas as pd
import numpy as np

a=[['1','221,51','413,56','636,07'],['13','213','131.86','357,86'],['49','239.83','-238.83','10']]

df = pd.DataFrame(a)
repl_list = {',': '.'}
df.replace(repl_list, regex=True, inplace=True)


df[0] = df[0].astype(float)
df[1] = df[1].astype(float)
df[2] = df[2].astype(float)
df[3] = df[3].astype(float)

df['SUM'] = df.loc[df[0] >= 0, [0, 1, 2]].sum(axis=1)
df['SUM'] = df['SUM'].astype(float)
df['SUM'].fillna(0, inplace=True)
df['T/F'] = np.where((df['SUM'] >= df[3]), True, False)
a = df.loc[df['T/F'] == False]
print(df)

if sum [3] == SUM column, then it should be True, otherwise False

why with this code the result is:

      0       1       2       3     SUM    T/F
0   1.0  221.51  413.56  636.07  636.07  False
1  13.0  213.00  131.86  357.86  357.86   True
2  49.0  239.83 -238.83   10.00   50.00   True

Why in 2 shows true even though it should be false? The same problem in the first row - should be true.

How does this float converter work? When I add to a[] values ['49','239.83','-288.83','10'] output looks like:

      0       1       2       3           SUM    T/F
0   1.0  221.51  413.56  636.07  6.360700e+02  False
1  13.0  213.00  131.86  357.86  3.578600e+02   True
2  49.0  239.83 -238.83   10.00  5.000000e+01   True
3  49.0  239.83 -288.83   10.00  5.684342e-14  False

Why does it look like this? Where is mistake and what does this float sum look like? Please explain how it works and why there is an error.

True should be only if column '3' is equal to SUM - I don't understand why it doesn't work - what about the first row? It totally doesn't make sense

pandas dataframe precision python-3.x python
2 Answers

Because float precision if compare equal values is possible some values not matched.

Here is possible use numpy.isclose.html for compare equality:

df['T/F'] = np.isclose(df['SUM'], df[3])
print(df)
      0       1       2       3     SUM    T/F
0   1.0  221.51  413.56  636.07  636.07   True
1  13.0  213.00  131.86  357.86  357.86   True
2  49.0  239.83 -238.83   10.00   50.00  False

Then add condition for compare higher values and chain by | for bitwise OR:

df['T/F'] = np.isclose(df['SUM'], df[3]) | (df['SUM'] > df[3])
print(df)
      0       1       2       3     SUM   T/F
0   1.0  221.51  413.56  636.07  636.07  True
1  13.0  213.00  131.86  357.86  357.86  True
2  49.0  239.83 -238.83   10.00   50.00  True
user_29010020

That is due the floating point precision of both of the values in the columns 3 and SUM.

df[3][0]
# 636.07
df['SUM'][0]
# 636.0699999999999

To count this use np.round and set precision. 

df['T/F']=np.round(df['SUM'],2)>=np.round(df[3],2)
print(df)

      0       1       2       3     SUM   T/F
0   1.0  221.51  413.56  636.07  636.07  True
1  13.0  213.00  131.86  357.86  357.86  True
2  49.0  239.83 -238.83   10.00   50.00  True
user_124164530
Related