Python and Pandas versions change how a number is interpreted after DataFrame read

user_14855024 0
Viewed 217

I have 2 environments:

Environment #1 (old):

  • Python 3.7.5
  • Pandas 0.23.4

Environment #2 (new):

  • Python 3.8.10
  • Pandas 1.3.4

When I load the same CSV file by doing pd.read_csv('name_of_my_csv_file.csv', delimiter=';', dtype=str) in both of the environments I noticed that Python or Pandas misinterpret some (not all of them, just about ~12 rows out of 50 000 rows) numbers. In Environment #1 (old) the misinterpretation of a number looks like this 7546.168415200001, where in reality the number in the Excel file is 7546.1684152. Environment #2 (new) interprets the number correctly, that is, as 7546.1684152.

>>> amount_old
7546.168415200001
>>>
>>> amount_new
7546.1684152
>>>
>>> # Types of both numbers from DataFrame
>>> type(amount_old)
<class 'numpy.float64'>
>>>
>>> type(amount_new)
<class 'numpy.float64'>
>>>

Based on this, I have 2 questions:

  1. What causes this difference?
  2. How do I make sure, that in Environment #2 (new) I get the same number as in Environment #1 (old)? That is, with the 00001 appended to it at the end? The reason I need the Environment #2 (new) match the Environment #1 (old)'s value is, that I have a test that compares the hash of the DataFrame, which, fails because of these different numbers. The hashes, in both cases, are created by this command: pd.util.hash_pandas_object(my_dataframe_from_excel). Then, the hashes are compared in the test which fails because even the slightest change in the number causes the hash to be different.

EDIT: I'm not using pd.read_excel() but pd.read_csv().

pandas python
2 Answers

Edit To ensure your dataframe is loaded without any interpretation, use dtype=object in your 2 environments:

Documentation of read_excel (Pandas 0.23.4) about dtype:

Use object to preserve data as stored in Excel and not interpret dtype

Use np.close:

import numpy as np

amount_old = 7546.168415200001
amount_new = 7546.1684152

>>> amount_old == amount_new
False

>>> np.isclose(amount_old, amount_new)
True

With dataframes:

df_old = pd.DataFrame({'amount': [7546.168415200001]})
df_new = pd.DataFrame({'amount': [7546.1684152]})

>>> df_old['amount'] == df_new['amount']
0    False
Name: amount, dtype: bool

>>> np.isclose(df_old['amount'], df_new['amount'])
array([ True])

# Or without np.isclose
>>> df_old['amount'].sub(df_new['amount']).abs() <= 1e-6
0    True
Name: amount, dtype: bool
user_152399510

So, at the end it seems like an issue with representation of float objects which, in the environment #1 (old) (described in my question above) was misinterpreted. The environment #2 (new)'s values are actually correct. That means, that we will need to adjust the tests to actually match the new environment's output instead of the old environment's output.

Thank you everyone for the help.

user_148550240
Related