Code Sample, a copy-pastable example if possible
df = pd.DataFrame([[1,2,3,4]], columns=[5,6,7,8])
df.to_json('test.json', orient='table')
pd.read_json('test.json', orient='table')
KeyError: '[5 6 7 8] not in index'
Output of pd.show_versions()
Comment From: TomAugspurger
I thought that https://frictionlessdata.io/specs/table-schema/ required the field names to be strings, but that may not be the case.
Comment From: robmarkcole
There is an error with string column/index names too:
df = pd.DataFrame([['a', 'b'], ['c', 'd']],
index=['row 1', 'row 2'],
columns=['col 1', 'col 2'])
df.to_json(orient='table')
pd.read_json(_, orient='table')
...
ValueError: Mixing dicts with non-Series may lead to ambiguous ordering.
This issue should be renamed to read_json and orient='table' Fails
Comment From: WillAyd
@robmarkcole what version are you using? Your example ran fine for me on master.
Comment From: robmarkcole
@WillAyd error on 0.22.0. No error on 0.23.0.dev0
Comment From: WillAyd
General support for read_json with table='orient' was only just added for the v0.23 release so makes sense it doesn't work on 0.22. See #19039
Comment From: MichaMucha
Hi! Captain Obvious here, just wanted to say getting this in 0.23.0 as well
import pandas as pd
breaking_case = pd.DataFrame({
1: [1,2],
2: [3,4]}
)
pd.read_json(breaking_case.to_json(orient='table'), orient='table')
KeyError: '[1 2] not in index'
Comment From: WillAyd
@MichaMucha that's right this was never implemented, though not sure if it's valid JSON either. If interested investigation into the schema linked above to confirm or deny is welcome!
Comment From: MichaMucha
Thanks for the quick reply!
I read through the page and it seems that:
- a descriptor specifying columns is an "ordered dict" equivalent
- order of column descriptions in that dict implies column order
- each column is described by a few properties, one of those is name.
- technically speaking, { "name" : 1 } is valid JSON
I would reason that you can have numerical column names.
direct quotes from the spec (a "field" is a column in our case): A Table Schema is represented by a descriptor. The descriptor MUST be a JSON object (JSON is defined in RFC 4627). It MUST contain a property fields. fields MUST be an array where each entry in the array is a field descriptor (as defined below). The order of elements in fields array MUST be the order of fields in the CSV file. A field descriptor MUST be a JSON object that describes a single field.
Let me know what can I do if I can help more
Comment From: WillAyd
Thanks for the review! The table implementation is located in pandas/pandas/io/json/table_schema.py so if you poke around there you should see where this could be implemented. Assuming this is your first time, also be sure to read through the contributing guide:
https://pandas.pydata.org/pandas-docs/stable/contributing.html
Hope that helps but let me know if you have any questions
Comment From: MichaMucha
Thank you! Sorry took me a while to find time. Thanks for the links! I looked at the table implementation. Turns out table_schema.py is not to blame! The assignment happens at line 96 and leaves the type intact.
I dug a little deeper and it seems that this guy over here -
pandas/pandas/io/json/json.py:214 - JSONTableWriter._write
wants to write data in a row-oriented fashion.
Every row becomes a dict, this makes every column a key, and JSON needs keys to be strings.
When you read it back, you get strings obviously.
One workaround I can think about is to match field['name'] to the stringified columns found in datahere, and cast them to the type of field['name'].
This will cause ambiguity trouble if you have a data frame with a column "1" and 1 for example.
Another solution I can imagine is an error asking the user to provide string columns.
Let me know what you think. Thanks again for the contributing guide!
Comment From: WillAyd
@MichaMucha IIUC that supports the argument that numeric column names should not be allowed, given the column names are the keys in the JSON table schema and JSON keys need to be strings.
If that's the case and you are looking to contribute then I'd suggest perhaps raising a more descriptive ValueError when trying to write to the JSON table schema using non-string column names
Comment From: albertvillanova
@WillAyd I think the problem is with the JSON serialization of the DataFrame (the 'data' for orient='table') and not with 'schema' (TableSchema spec).
IIUC, the aim of orient='table' is to make round-trip JSON serialization-deserialization of pandas objects. As pandas DataFrame can have non-string column names (indeed, that is the default if column names are not passed explicitly at instantiation), then the column names SHOULD NOT be used as keys in the JSON object (JSON spec imposes that keys must be strings).
It is also the case for index names: they can be non-strings. Therefore, index names should not be used as keys in the JSON object.
Comment From: WillAyd
19129 should already cover that just needs community PR
Comment From: albertvillanova
Yes, but I was suggesting not just "raising a more descriptive ValueError" (sic), but changing the implementation of the JSON serialization for orient='table'.
Comment From: IsaacG
take
Comment From: chandra-teajunkie
Hi, take I would like to try working on this if this is still up.
Comment From: IsaacG
release