[Python] Why can't calculated values match exactly the precalculated ones? [duplicate]

I can't understand the cause of this error. I'm trying to recreate the column by calculating it using the specific formula, but I see troubles.

I have a dataset with columns 'A', 'B', and 'C'. Column 'C' was previously calculated perhaps using the formula 'A' - 'B'. This column already exists in the dataset. Now, I'm trying to recalculate column 'C' myself with the same formula, but my new results differ slightly from the original values. For example:

C_new C

0 0.559999 0.559999
1 0.859998 0.859998
2 0.569999 0.569999
3 -0.020000 -0.020000
4 0.979997 0.979998
... ... ...
948152 2.279995 2.279995
948153 2.269995 2.269995
948154 2.099995 2.099995
948155 2.299995 2.299995
948156 1.769996 1.769996


Notice, for instance, row at index 4: there's a tiny discrepancy.

After examining the differences between the original and recalculated columns ('C' - 'C_new') more closely (using pd.set_option('display.float_format', lambda x: f'{x:.18f}')), I observe the following interesting pattern:

C_new = df1['A'] - df1['B']
diffs = df1['C'] - C_new


0 -0.000000000000001221
1 0.000000000000000888
2 -0.000000000000000999
3 -0.000000000000000427
4 0.000000999999999141
...
948152 -0.000000000000001332
948153 -0.000000000000001332
948154 0.000000000000000000
948155 0.000000000000000888
948156 0.000000000000000888
Length: 948157, dtype: float64


Digging deeper, I counted how many rows exhibit differences around the magnitude of 0.000001. When I set a tolerance of 1e-7 and searched for differences close to 1e-6:

tolerance = 1e-7
print(diffs[np.abs(diffs - 1e-6) < tolerance])


4 0.000000999999999141
21 0.000001000000000168
25 0.000001000000000251
26 0.000000999999999696
27 0.000001000000000473
...
948083 0.000001000000001472
948087 0.000001000000001472
948093 0.000000999999998363
948123 0.000000999999999918
948126 0.000000999999999918
Length: 114311, dtype: float64


I would have expected the error distribution to range more evenly, perhaps from 0.000001 to 0.000009, instead of clustering around exactly 0.000001 or -0.000001.

I realize there are many unknowns, such as how exactly the original 'C' column was computed or the numerical precision originally used in columns 'A' and 'B'. Could anyone explain why I'm observing this discrepancy? Is it purely a result of floating-point rounding errors? Moreover, why does this peculiar error distribution occur, clustering closely on -0.000001 and 0.000001, rather than being evenly spread out?

*Edit

I tried to reproduce calculating first in float32 then saving to csv using pandas and then load the csv and calculating C again but as float64.

import numpy as np
import pandas as pd

# Original float64 calculation
A32 = np.array([22.5443], dtype=np.float32)
B32 = np.array([30.12434630], dtype=np.float32)

C_exact = (A32- B32)
df = pd.DataFrame({
'C_exact': C_exact,
'A': A32,
'B': B32
})
df.to_csv('test.csv')
df_load = pd.read_csv('test.csv')
# Recalculate directly in float64 again
C_recomputed = (df_load['A'] - df_load['B'])

difference = df_load['C_exact'].values - C_recomputed
print(f"Exact : {df_load['C_exact'].values[0]:.18f}")
print(f"Recomputed: {C_recomputed[0]:.18f}")
print(f"Difference: {difference[0]:.18f}")


and I get

Exact : -7.580045700000000330
Recomputed: -7.580045999999999395
Difference: 0.000000299999999065


It's not 0.000001 like before.

Continue reading...