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 withcolumn_label:dtypepairs for dataframes or a string with the name of a dtype for series"values"is a json object withcolumn_label:value_arraypairs 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".')