JSON table orient not roundtripping extension types

[navado]

Code Sample, a copy-pastable example if possible

import pandas
print(pandas._version.get_versions())
df = pandas.DataFrame(
    {
        1: ['v1','v2'],
        2: ['v3','v4']
    },dtype='string'
)

print(df.info())
for orient in ['table','split','records','values']:
    rdf = pandas.read_json(df.to_json(orient=orient), orient=orient)
    print(f'======{orient}======')
    print(rdf.info())

Problem description

string dtype not preserved with round trip serialization to JSON, so dataframes containing strings cannot be reused transparently

Expected Output

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2 entries, 0 to 1
Data columns (total 2 columns):
 #   Column  Non-Null Count  Dtype 
---  ------  --------------  ----- 
 0   1       2 non-null      string
 1   2       2 non-null      string
dtypes: string(2)
memory usage: 160.0 bytes
None

Actual output

======table======
<class 'pandas.core.frame.DataFrame'>
Int64Index: 2 entries, 0 to 1
Data columns (total 2 columns):
 #   Column  Non-Null Count  Dtype 
---  ------  --------------  ----- 
 0   1       0 non-null      object
 1   2       0 non-null      object
dtypes: object(2)
memory usage: 48.0+ bytes
None
======split======
<class 'pandas.core.frame.DataFrame'>
Int64Index: 2 entries, 0 to 1
Data columns (total 2 columns):
 #   Column  Non-Null Count  Dtype 
---  ------  --------------  ----- 
 0   1       2 non-null      object
 1   2       2 non-null      object
dtypes: object(2)
memory usage: 48.0+ bytes
None
======records======
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2 entries, 0 to 1
Data columns (total 2 columns):
 #   Column  Non-Null Count  Dtype 
---  ------  --------------  ----- 
 0   1       2 non-null      object
 1   2       2 non-null      object
dtypes: object(2)
memory usage: 160.0+ bytes
None
======values======
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2 entries, 0 to 1
Data columns (total 2 columns):
 #   Column  Non-Null Count  Dtype 
---  ------  --------------  ----- 
 0   0       2 non-null      object
 1   1       2 non-null      object
dtypes: object(2)
memory usage: 160.0+ bytes
None

Output of pd.show_versions()

{'dirty': False, 'error': None, 'full-revisionid': '29d6b0232aab9576afa896ff5bab0b994760495a', 'version': '1.0.1'}

[MarcoGorelli]

Thanks @navado

I don't believe DataFrame.to_json was ever intended to store metadata. Have you tried with to_parquet?

>>> df = pd.DataFrame({'a': ['a']}, dtype='string')                         
>>> df                                                                      
   a
0  a

>>> df.info()                                                               
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1 entries, 0 to 0
Data columns (total 1 columns):
 #   Column  Non-Null Count  Dtype 
---  ------  --------------  ----- 
 0   a       1 non-null      string
dtypes: string(1)
memory usage: 136.0 bytes

>>> df.to_parquet('temp.pq')                                                
>>> pd.read_parquet('temp.pq')                                              
   a
0  a

>>> pd.read_parquet('temp.pq').info()                                       
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1 entries, 0 to 0
Data columns (total 1 columns):
 #   Column  Non-Null Count  Dtype 
---  ------  --------------  ----- 
 0   a       1 non-null      string
dtypes: string(1)
memory usage: 136.0 bytes

[WillAyd]

Have you tried orient=“table”? That does store metadata about the dtypes so is the best bet for round tripping

That said, better inferencing is possible generally so I think would accept a PR to do this

[navado]

@WillAyd - yes, it's the first print in the issue.

@MarcoGorelli - to_parquet (and most of other to_smth) functions require dealing with storage when to_json is easily applicable to prepare dataframe for sending over the network.

In general it's kind'a unexpected that metadata is not stored in such flexible format as JSON. It's not a compatibility issue (or I'm not aware of such).

Additionally, there are several more issues with round trip serialization of JSON that I realized only now:

• Size of de-serialized dataframe is differ from the original and varies depending on the orientation (see table and split vs the rest)
• Columns numbering differs between orientation (see prints for values vs the rest)

[WillAyd]

Ah sorry misread that. At the very least orient="table" should be able to preserve this, so would certainly take a PR to fix that

Not sure on the rest just yet - we for most IO right now don't infer extension types like "string" automatically but it is something we have talked about as a team.

@jorisvandenbossche

[jorisvandenbossche]

As long as the string dtype is not the default, we can't infer it from a file format that has no type information (like json). There has been discussion about adding a keyword to specify you want the new dtypes, see #29752.

Specifically for the JSON table format: that format indeed includes some type information. However, pandas itself doesn't specify the spec, I think, but we follow https://frictionlessdata.io/specs/table-schema/ ? (no specialist here)
If that is the case, we still can't distinguish the two cases.

[WillAyd]

Table should work - we actually write that it is a string dtype just something off with the reader

[jorisvandenbossche]

So nullable boolean and integer might work, indeed (but only if nulls are present, otherwise we still can't know which type to use).

But as explained above, I don't see how we can differentiate string dtype vs object dtype with strings. Both end up in the json schema as type "string". The types are not pandas specific, but following the table-schema specification.

[navado]

Probably JSON serialization should be AVRO based or resembling AVRO? i.e. solve the issue by writing schema information to the file with the data?

[jorisvandenbossche]

An AVRO interface for pandas might be interesting, but I think that is a different discussion? (or would be a new format)

The current json output formats do not include schema information, except for the table schema. And for that, there is a specification to follow.

[navado]

@jorisvandenbossche Avro is definitely different topic, but saving schema in serialized JSON - should not be a big problem

[jorisvandenbossche]

Saving schema information in the JSON outputs is something that could certainly be discussed, but I just wanted point out that this is still something else / bigger than how the current issue is framed here as "table orient not correctly roundtripping extension types"

So if we want to discuss this further, that warrants a separate issue I think.