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
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.
- 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.