Error converting dbf to Pandas dataframe using simpledbf

user_19061285 0
Viewed 147

I am attempting to convert a dbf file (from an ESRI shapefile) to pandas dataframe but receive this error:

ValueError: year 0 is out of range

I am using the following code:

from simpledbf import Dbf5

faunadbf = Dbf5('Listed Fauna1.dbf')

DFfauna = faunadbf.to_dataframe()

The error appears to be due to 0000 values in the STARTDATE and ENDDATE as it is OK if I delete them however I need to keep those records. How do I convert them so that Pandas will accept 0 date values?

Fauna and Flora dbf files

dbf pandas python
2 Answers

One way to get the records from the dbf files with the 00000 date fields already set to NaN is to use the dbf library1:

table = dbf.Table(
        'Listed_Fauna.dbf',
        default_data_types={'D':(datetime.date, lambda: float('NaN'))
        )
table.open()
for rec in table:
    print(rec.record_id, rec.startdate, rec.enddate)

which produces:

(5595479, nan, nan)
(5595472, nan, nan)
(8581585, datetime.date(2016, 12, 12), datetime.date(2016, 12, 12))
....
(8906882, datetime.date(2017, 11, 22), nan)
....

Instead of printing the records in the loop above, you would add them to a pd.Dataframe instead.

I do not know if this method or @Parfait's2 is more performant.

1Disclosure: I am the author of the dbf library.

2Thanks go to @Parfait for their help with acceptable date values in Pandas.

user_2088800

Essentially, you are facing two fundamental issues:

  1. According to ISO 8601 (international standard for date and datetimes), year 0000 AD actually equates to year 1 BC. However, Python's minimum datetime is January 1 at midnight in year 1 AD. So year 1 BC cannot be assigned.

  2. Due to the 64-bit integer limitation. Pandas default dtype, datetime64[ns], does not allow dates earlier than '1677-09-21 00:12:43.145224193'.

    However, this second item can be rectified by using the Period dtype.

    import pandas as pd

dates_df = pd.DataFrame({
    "DATE": [
        pd.Period(d, freq="S") for d in [
             "0001-01-01 12:00:00", 
             "1000-01-01 15:00:00", 
             "2000-01-01 18:00:00"
        ]
      ]
})

dates_df
#                   DATE
# 0     1-01-01 12:00:00
# 1  1000-01-01 15:00:00
# 2  2000-01-01 18:00:00

dates_df.dtypes
# STARTDATE    period[S]
# dtype: object

Specifically for your use case, source code to_dataframe() of simpledbf indicate a simple call to the constructor, pandas.DataFrame(), using dbf records and columns:

def to_dataframe(self, chunksize=None, na='nan'):
    ...
    results = list(self._get_recs()) 
    df = pd.DataFrame(results, columns=self.columns)

Therefore, consider retrieving the underlying data of _get_recs and convert dtype before DataFrame() call. Specifically, convert the generator to a list and then again to a dictionary via list/dict comprehension. Finally, convert to Period dtype for the STARTDATE and ENDDATE values:

from simpledbf import Dbf5 

fauna_dbf = Dbf5('Listed Fauna1.dbf')

# CONVERT GENERATOR TO LIST
fauna_recs = list(fauna_dbf._get_recs())
fauna_cols = fauna_dbf.columns

# CONVERT TO LIST OF DICTS WITH COLUMN NAMES AS KEYS
fauna_dict = [
    {col: vals} for col, vals in zip(fauna_cols, fauna_recs)
]

# CONVERT DATE VALS TO PERIOD DTYPES
fauna_dict["STARTDATE"] = [
    (pd.NaT if str(d).startswith("0000") else pd.Period(d, freq="D"))
    for d in fauna_dict["STARTDATE"]
]
fauna_dict["ENDDATE"] = [
    (pd.NaT if str(d).startswith("0000") else pd.Period(d, freq="D"))
    for d in fauna_dict["ENDDATE"]
]

# BIND RECORDS TO DATA FRAME
DFfauna = pd.DataFrame(fauna_dict)

Note: Above has not been tested.

user_14224510
Related