Pandas - Write parquet and keep column as Decimal

Admin 1
Viewed 5416

I'm trying to write a parquet file into s3 to run a Copy into Redshift/ Parquet file is being write correctly but I got an error when try to copy: Column type: DECIMAL(10,2), Parquet schema: optional doubl.

The error message is pretty clear but I'm not sure how to enforce a column to be Decimal type.

Is possible to do it? Keep column value as Decimal when write parquet?

from datetime import datetime
import pandas as pd
import decimal as D
import numpy as np

data = [
    {
        "name": "ted",
        "timestamp": datetime.now().isoformat(),
        "actual_data": "2019-06-28 00:00:00",
        "age": 34,
        "value": 0.10
    },
    {
        "name": "barney",
        "timestamp": datetime.now().isoformat(),
        "actual_data": "2019-06-28 10:00:00",
        "age": 35,        
    }
]


df = pd.DataFrame.from_dict(data,)
df['age'] = df['age'].astype(int)
df['value'] = df['value'].fillna(0)
df['value'] = df['value'].astype(np.float, 2)

# df_merged["id"] = df_merged["id"].astype(int)

print(df)

df.to_parquet(f"s3://awesome-parquet-test/data.parquet", index=False)
parquet pandas python
1 Answers

To write the column as decimal values to Parquet, they need to be decimal to start with.

The values in your dataframe (simplified a bit here for the example) are floats, so they are written as floats:

>>> df = pd.DataFrame({'value': [0.1, 0.0]})
>>> df
   value
0    0.1
1    0.0
>>> df['value'].dtype
dtype('float64')
>>> df.to_parquet("test_data.parquet", index=False)
>>> pq.read_metadata("test_data.parquet").schema
<pyarrow._parquet.ParquetSchema object at 0x7f07248334a8>
value: DOUBLE

But when we first convert the floats do decimals in Python, we can actually write decimals to Parquet:

>>> import decimal
>>> df['value_decimal'] = df['value'].astype(str).map(decimal.Decimal)
>>> df['value_decimal'].values
array([Decimal('0.1'), Decimal('0.0')], dtype=object)

>>> df.to_parquet("test_data.parquet", index=False)
>>> pq.read_metadata("test_data.parquet").schema
<pyarrow._parquet.ParquetSchema object at 0x7f073ae52898>
value: DOUBLE
value_decimal: FIXED_LEN_BYTE_ARRAY DECIMAL

For the conversion to decimals in Python: the reason that I first convert to strings is because otherwise the decimal would use the full precision of the floating point (resulting in more than 10 decimals)

user_6533640
Related