Can I retrieve specific integer datatypes for pandas dataframe columns from a plaintext file?

Viewed 61

I want to store a schema for a set of dataframes in a text file so they can be easily edited and loaded at runtime without defining a lot of dataframes and labels in my code (a few of them should also get stored with some initial values, so I can't have only column labels available in the file). I need to specify integer datatypes for some columns rather than a generic integer type. The only pandas functionality I'm aware of that might work is to store and retrieve the dataframes as JSON using pandas.read_json(orient='table'), but this only handles a generic "integer" type that's converted to int64 on the backend, which doesn't work for me. So for a two-column dataframe with types int8 and Int16 I'd like to be able to do this

import pandas as pd

df = pd.read_json('df.json')
print(df, '\n')
print(df.dtypes)

and get this output

Empty DataFrame
Columns: [one, two]
Index: []

one     int8
two    Int16
dtype: object

but read_json only handles int64. I can define the table I want, serialize it, and read it back like this

df = pd.DataFrame(columns=['one','two']).astype({'one':'int8', 'two':'Int16'})
print(df.to_json(orient='table'), '\n')
print(pd.read_json(df.to_json(orient='table'), orient='table').dtypes)

which, after some parsing, results in

{
  "schema":{
    "fields":[
      {"name":"index","type":"string"},
      {"name":"one","type":"integer"},
      {"name":"two","type":"integer"}
    ],
    "primaryKey":["index"],
    "pandas_version":"0.20.0"
  },
  "data":[]
}

one    int64
two    int64
dtype: object

If I try and change the "integer" types to, say, "int8", like this

pd.read_json(df.to_json(orient='table').replace('"integer"', '"int8"'), orient='table').dtypes

I get a traceback as below (showing first and last calls only)

ValueError                                Traceback (most recent call last)
<ipython-input-35-dc5f6a6f42b4> in <module>
----> 1 pd.read_json(df.to_json(orient='table').replace('"integer"', '"int8"'), orient='table').dtypes

c:\python39\lib\site-packages\pandas\io\json\_table_schema.py in convert_json_field_to_pandas_type(field)
    187             return "object"
    188
--> 189     raise ValueError(f"Unsupported or invalid field type: {typ}")
    190
    191

ValueError: Unsupported or invalid field type: int8

The function convert_json_field_to_pandas_type in the traceback doesn't seem to handle numeric types beyond "integer" and "number", which it converts to "int64" and "float64" respectively.

What's the most succinct workaround here?

Edit


If more context is helpful, my current solution is to load a custom json file with this format:

{
    "dataframe_name":{
        "df_or_s":"dataframe",
        "type":{
            "column_name":"dtype",
            "column_name":"dtype"
        },
        "values":{
            "column_name":[],
            "column_name":[]
        },
        "index":[]
    },
    "series_name":{
        "df_or_s":"series",
        "type":"dtype",
        "values":[],
        "index":[]
    }
}

where

  • "df_or_s" must be either "dataframe" or "series"
  • "type" is a json object with column_label:dtype pairs for dataframes or a string with the name of a dtype for series
  • "values" is a json object with column_label:value_array pairs for dataframes or an array of values for series

I then reconstruct the tables as instance attributes of a class like this:

from json import load as json_load
from pandas import DataFrame
from pandas import Series

class demo:

    def __init__(self, schema='schema.json'):

        if type(schema) == str:

            with open(schema) as f:
                dict_from_json = json_load(f)

            for k,v in dict_from_json.items():

                if v['df_or_s'] == 'dataframe':
                    if len(v['index']) != 0:
                        r = DataFrame(data=v['values'], columns=v['type'].keys(), index=v['index'])
                    else:
                        r = DataFrame(data=v['values'], columns=v['type'].keys())
                    self.__setattr__(k,r.astype(v['type']))

                elif v['df_or_s'] == 'series':
                    if len(v['index']) != 0:
                        r = Series(v['values'], index=v['index'], dtype=v['type'])
                    else:
                        r = Series(v['values'], dtype=v['type'])
                    self.__setattr__(k,r)

                else:
                    raise ValueError('unrecognized value for "'+v['df_or_s']+'" found for name "df_or_s" in tables.json. Must be "dataframe" or "series".')


0 Answers
Related