topic Interoperability Spark ↔ Pandas: can't convert Spark dataframe to Pandas dataframe via df.toPandas() when it contains datetime value in distant future in Data Engineering

Interoperability Spark ↔ Pandas: can't convert Spark dataframe to Pandas dataframe via df.toPandas() when it contains datetime value in distant future

MartinB — Sat, 11 Sep 2021 10:34:17 GMT

Hi,

I have multiple datasets in my data lake that feature valid_from and valid_to columns indicating validity of rows.

If a row is valid currently, this is indicated by valid_to=9999-12-31 00:00:00.

Example:

Loading this into a Spark dataframe works fine (Spark has no issue with timestamp 9999-12-31).

However, for analysis and visualization purpose, I would like to do further processing with Pandas instead of Spark. But when trying to convert the dataframe to Pandas an error occurs:

ArrowInvalid: Casting from timestamp[us, tz=Etc/UTC] to timestamp[ns] would result in out of bounds timestamp: 253379592300000000

Code for simulating the issue:

import datetime
import pandas as pd
 
 
df_spark_native = sc.parallelize([
    [1,   'Alice',   datetime.date(1985, 4, 13),   datetime.datetime(1985, 4, 13, 4,5)],
    [2,   'Bob',     datetime.date(9999, 1, 20),   datetime.datetime(9999, 4, 13, 4,5)],
    [3,   'Eve',     datetime.date(1500, 1, 20),   datetime.datetime(1500, 4, 13, 4,5)],
    [3,   'Dave',    datetime.date(   1, 1, 20),   datetime.datetime(   1, 4, 13, 4,5)]
]).toDF(('ID', 'Some_Text', 'Some_Date', 'Some_Timestamp'))
display( df_spark_native )
df_spark_native.printSchema()
 
 
df_spark_to_pandas = df_spark_native.toPandas()
display( df_spark_to_pandas )

To me, it appears, that under the hood, spark uses pyarrow to convert the dataframe to pandas.

Pyarrow already has some functionality for handling dates and timestamps that would otherwise cause out of range issue: parameter "timestamp_as_object" and "date_as_object" of pyarrow.Table.to_pandas(). However, Spark.toPandas() currently does not allow passing down parameters to pyarrow.

Re: Interoperability Spark ↔ Pandas: can't convert Spark dataframe to Pandas dataframe via df.toPandas() when it contains datetime value in distant future

Anonymous — Sat, 11 Sep 2021 20:15:46 GMT

Hello @Martin B.. It's nice to meet you. I'm Piper, one of the community moderators here. Thank you for your question and I'm sorry to hear about the issue. If no one comments soon, please be patient. The team will be back on Monday.

Re: Interoperability Spark ↔ Pandas: can't convert Spark dataframe to Pandas dataframe via df.toPandas() when it contains datetime value in distant future

MartinB — Tue, 28 Sep 2021 15:58:10 GMT

Hi @Piper Wilson , can the team help?

Re: Interoperability Spark ↔ Pandas: can't convert Spark dataframe to Pandas dataframe via df.toPandas() when it contains datetime value in distant future

Anonymous — Wed, 29 Sep 2021 15:46:56 GMT

@Martin B. - I apologize for my delayed response. I've pinged the team again. Thanks for your patience.

Re: Interoperability Spark ↔ Pandas: can't convert Spark dataframe to Pandas dataframe via df.toPandas() when it contains datetime value in distant future

shan_chandra — Wed, 06 Oct 2021 14:42:15 GMT

Currently, out of bound timestamps are not supported in pyArrow/pandas. Please refer to the below associated JIRA issue.

https://issues.apache.org/jira/browse/ARROW-5359?focusedCommentId=17104355&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17104355

https://issues.apache.org/jira/browse/ARROW-8967

Re: Interoperability Spark ↔ Pandas: can't convert Spark dataframe to Pandas dataframe via df.toPand

ThePhil — Fri, 31 Jan 2025 22:26:53 GMT

Be aware, that in Databricks 15.2 LTS this behavior is broken.
I cannot find the code, but most likely related to the following option:
https://github.com/apache/spark/commit/c1c710e7da75b989f4d14e84e85f336bc10920e0#diff-f9ddcc6cba651c6ebfd34e29ef049c38950cff45ba9f1e461cda315de9b0a56cR149

I was able to reproduce the issue locally when the latest pyarrow is installed, with this option enabled.