[Python] Converting all columns in spark df from decimal to float for pandas conversion

I want to create a function to transform the datatype of all spark dataframe columns from decimal to float.
I do not know my column names in advance, nor if and how many columns of the type of decimal are included. This excludes explicit casting of columns to prevent scaling limitations.
Other data type columns should not be affected.
NULLS might occur.

Reason behind all this madness: I need to convert the spark dataframe to pandas, to then be able to write an xlsx file. The transformation to pandas of decimal however results in an object type, which is stored in the xlsx file as text, not as a number.


Sample code:

df = spark.sql("select 'text' as txt, 1.1111 as one, 2.22222 as two, CAST(3.333333333333 AS FLOAT) as three")
df.printSchema()

>>
root
|-- txt: string (nullable = false)
|-- one: decimal(5,4) (nullable = false)
|-- two: decimal(6,5) (nullable = false)
|-- three: float (nullable = false)


Transform to Pandas:

df_pd = df.toPandas()
print(df_pd.dtypes)

>>
txt object
one object
two object
three float32
dtype: object


I need all of the decimal types to be of float type in df_pd.


Ideally I have something like this:

df = spark.sql("select 'text' as txt, 1.1111 as one, 2.22222 as two, 3.333333333333 as three")

insert magic

df.printSchema()

>>
root
|-- txt: string (nullable = false)
|-- one: float (nullable = false)
|-- two: float (nullable = false)
|-- three: float (nullable = false)


Thanks

Continue reading...