Pandas version checks

  • [X] I have checked that this issue has not already been reported.

  • [X] I have confirmed this bug exists on the latest version of pandas.

  • [ ] I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd

for ii in range(10000):
    df = pd.read_csv("data.csv", dtype={"ts": float})   # data.csv provided in an attached file
    pd.to_datetime(df["ts"], unit="s", errors="coerce")

Issue Description

I, sometimes, get the following error with pandas 2.2.2 (I don't have this error with pandas 2.1.4):

Exception has occurred: FloatingPointError overflow encountered in multiply File ".../main.py", line 218, in pd.to_datetime(df["ts"], unit="s", errors="coerce") FloatingPointError: overflow encountered in multiply

The error is not repeatable, hence the loop. I tried to reduce as much as possible the input file while keeping the raised error, this is why I provided a csv file with 200 rows, attached to this issue. I don't know if the issue is due to the read_csv (I got the same problem with read_parquet) or due to to_datetime. If the read_csv is outside the loop and I make a deepcopy at the beginning of each loop, I don't have the problem, so my hunch is that this is linked to the reading process (read_csv in the example).

Expected Behavior

I expect the loop content to have the same behaviour, works every time or fails every time.

Installed Versions

INSTALLED VERSIONS ------------------ commit : d9cdd2ee5a58015ef6f4d15c7226110c9aab8140 python : 3.11.8.final.0 python-bits : 64 OS : Linux OS-release : 5.15.0-105-generic Version : #115~20.04.1-Ubuntu SMP Mon Apr 15 17:33:04 UTC 2024 machine : x86_64 processor : x86_64 byteorder : little LC_ALL : None LANG : en_US.UTF-8 LOCALE : en_US.UTF-8 pandas : 2.2.2 numpy : 1.26.4 pytz : 2024.1 dateutil : 2.9.0.post0 setuptools : 65.5.0 pip : 24.0 Cython : None pytest : None hypothesis : None sphinx : None blosc : None feather : None xlsxwriter : None lxml.etree : None html5lib : None pymysql : None psycopg2 : None jinja2 : None IPython : None pandas_datareader : None adbc-driver-postgresql: None adbc-driver-sqlite : None bs4 : None bottleneck : None dataframe-api-compat : None fastparquet : None fsspec : None gcsfs : None matplotlib : None numba : None numexpr : None odfpy : None openpyxl : None pandas_gbq : None pyarrow : 16.0.0 pyreadstat : None python-calamine : None pyxlsb : None s3fs : None scipy : None sqlalchemy : None tables : None tabulate : None xarray : None xlrd : None zstandard : None tzdata : 2024.1 qtpy : None pyqt5 : None

data.csv

Comment From: SUCM

I also see this FloatingPointError: overflow encountered in multiply error from time to time and it's always with pd.to_datetime where errors=coerce or ignore

so far I have done code like below and it would always work even when encountering the random FloatingPointError confirmed by the prints. basically a retry would work

try:
    c=df["a"].copy()
    df["a"] = pd.to_datetime(df["a"], ... errors="coerce")
except FloatingPointError:
    print("pandas FloatingPointError on column a")
    df["a"] = pd.to_datetime(c, ... errors="coerce")

you may want to try using Python pickle to preserve Python objects. I find pandas's csv and excel read/write to be unreliable

as an example, having 2 columns of str, one with phone numbers where area codes all starts with 0 and the other column are empty string, then pandas to/read csv and excel would save them to file without problem but on reading back you would get 2 columns of timestamps and/or other non-str objects

Comment From: lbortolotti

I'm seeing the exact same issue as the OP. No csv read/write in my case, just a pd.to_datetime call that randomly fails, but if re-run on the exact same data then succeeds...

Comment From: lmuther8

I also have this issue occurring when reading in a parquet file to a df then attempting to do pd.to_datetime(df['col_name'],unit='s', utc=True, errors='ignore').

Comment From: ichatz

I also have the exact same behavior when using pd.to_datetime in combination with read_json.

Comment From: lmuther8

I converted the floats to integers and it got rid of the error as far as i can tell if that works for anyone else's use case.

Comment From: drSylvia

I have the same issue occuring when read from a csv file and then pd.to_datetime(x * 3600, unit="s", origin=origin, errors="coerce"). And this FloatingPointError: overflow encountered in multiply error from time to time and on different columns in the same dataframe.

Comment From: drSylvia

I converted the floats to integers and it got rid of the error as far as i can tell if that works for anyone else's use case.

Would this result in a loss of accuracy or precision?

Comment From: optiluca

Any updates on this? I've still got Pandas pinned to 2.1.4 because of this bug.

Comment From: LilMonk

Facing the same issue while converting from long to timestamp. pd.to_datetime(df['utc_long'], unit='s', utc=True)

Solutions that worked for me: 1. Convert the column data type to int explicitly (@lmuther8 suggestion):

df['utc_timestamp'] = charging_df['utc_long'].astype(int)
# Be careful with NA values. First fill NA values and then convert to int.
  1. Iterate over each record in the column and convert it to timestamp:
utc_timestamp_list = []
for utc in df["utc_long"]:
    try:
        utc_timestamp_list.append(pd.to_datetime(utc_long, unit="s", utc=True))
    except Exception as e:
        print(f"Error converting utc_long {utc}: {e}")
df["utc_timestamp"] = utc_timestamp_list

Comment From: jvahl

It looks like the error only occurs for inputs longer than 127.

This works fine:

import pandas as pd

for ii in range(10000):
    df = pd.read_csv("data.csv", dtype={"ts": float}).iloc[:127]
    pd.to_datetime(df["ts"], unit="s", errors="coerce")

While this leads to the FloatingPointError:

import pandas as pd

for ii in range(10000):
    df = pd.read_csv("data.csv", dtype={"ts": float}).iloc[:128]
    pd.to_datetime(df["ts"], unit="s", errors="coerce")

Comment From: Hnasar

@mroeschke and @jbrockmendel you two were listed as authors on #56037 could you take a look? This seems to be a nondeterministic FloatingPointError in the vectorized timestamp conversion for floats. I'm struggling to repro the examples above, but I'm consistently seeing it in my own code.

From the 2.2.0 what's new:

Bug in the results of to_datetime() with an floating-dtype argument with unit not matching the pointwise results of Timestamp (GH 56037)

If I reorder function calls, or do abitrary other things, sometimes the error disappears. It seems like memory corruption, and I'm not familiar with pandas to debug further.

Here's the Series I'm using:

(Pdb) ser
0      NaN
1      NaN
2      NaN
3      NaN
4      NaN
        ..
1789   NaN
1790   NaN
1791   NaN
1792   NaN
1793   NaN
Name: earliest_parent_ts, Length: 1794, dtype: float64
(Pdb) ser.dropna()
44      1.740506e+09
45      1.740506e+09
46      1.740506e+09
47      1.740506e+09
49      1.740505e+09
            ...     
1426    1.740529e+09
1430    1.740503e+09
1431    1.740503e+09
1432    1.740503e+09
1549    1.740587e+09
Name: earliest_parent_ts, Length: 102, dtype: float64

Basically if I call this twice:

pd.to_datetime(ser, utc=True, unit="s").dt  # If I remove .dt it works

then it pretty reliably fails, but only if I have some specific function calls before this (into my own code).

Batching the series into 127-sized chunks seems to workaround the issue somehow but I'm not super confident in it. Retrying doesn't help.

Could either of you take a closer look at the patch you landed? Thank you for your hard work and attention!

edit: Btw, here's the workaround I'm using

try:
    ser = pd.to_datetime(ser, utc=True, unit=unit)
except FloatingPointError:
    # Pandas 2.2 has a non-deterministic error with large arrays, so
    # chunk into 127-sized parts to workaround.
    # https://github.com/pandas-dev/pandas/issues/58419

    match ser:
        case pd.Series():
            parts = [
                pd.to_datetime(ser.iloc[i : i + 127], utc=True, unit=unit)
                for i in range(0, len(ser), 127)
            ]
            ser = pd.concat(parts)
        case pd.Index() | np.ndarray():
            parts = [
                pd.to_datetime(ser[i : i + 127], utc=True, unit=unit)
                for i in range(0, len(ser), 127)
            ]
            ser = parts[0].append(parts[1:])
        case _:
            raise

Comment From: snitish

take

Comment From: snitish

A simpler example (fails about 80% of the time on my machine) -

>>> for i in range(1000):
>>>     pd.to_datetime(pd.Series([np.nan]*10000 + [1712219033.0], dtype=np.float64), unit="s", errors="coerce")
FloatingPointError: overflow encountered in multiply

Comment From: snitish

Thanks for the analysis @Hnasar. The source of the bug is indeed due to accessing uninitialized memory containing garbage values. I've submitted a PR with a fix.