BUG: to_json/read_json with orient="table" does not preserve types with pd.NA #51375
Description
Pandas version checks
-
I have checked that this issue has not already been reported.
-
I have confirmed this bug exists on the latest version of pandas.
-
I have confirmed this bug exists on the main branch of pandas.
Reproducible Example
import pandas as pd
df = pd.DataFrame({"x": [pd.NA, pd.NA], "y": [1.0, 2.0]})
df2 = pd.read_json(df.to_json(orient="table"), orient="table")
print(df.to_json(orient="table"))
print(df.dtypes)
print(df2.dtypes)Issue Description
When I have a column consisting of pd.NA the data type is initially object. When I call .to_json(orient="table") the schema says that the type is "string" (which is probably OK??)
{"schema":{"fields":[{"name":"index","type":"integer"},{"name":"x","type":"string"},{"name":"y","type":"number"}],"primaryKey":["index"],"pandas_version":"1.4.0"},"data":[{"index":0,"x":null,"y":1.0},{"index":1,"x":null,"y":2.0}]}
When I read the produced json string back using .read_json the data dype in the resulting frame is float64
Full output of the example
{"schema":{"fields":[{"name":"index","type":"integer"},{"name":"x","type":"string"},{"name":"y","type":"number"}],"primaryKey":["index"],"pandas_version":"1.4.0"},"data":[{"index":0,"x":null,"y":1.0},{"index":1,"x":null,"y":2.0}]}
x object
y float64
dtype: object
x float64
y float64
dtype: object
Expected Behavior
The data types should be preserved when doing a roundtrip with orient="table" on both read and write.
Minor detail: The schema appears to say "pandas_version": 1.4.0 even though the details below says that the install version is 1.5.2. Would expect that 1.5.2 was present in the output.
Installed Versions
INSTALLED VERSIONS
commit : 8dab54d
python : 3.10.9.final.0
python-bits : 64
OS : Linux
OS-release : 5.10.16.3-microsoft-standard-WSL2
Version : #1 SMP Fri Apr 2 22:23:49 UTC 2021
machine : x86_64
processor :
byteorder : little
LC_ALL : None
LANG : C.UTF-8
LOCALE : en_US.UTF-8
pandas : 1.5.2
numpy : 1.24.1
pytz : 2022.7.1
dateutil : 2.8.2
setuptools : 66.0.0
pip : 22.3.1
Cython : None
pytest : 7.2.1
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : 8.8.0
pandas_datareader: None
bs4 : 4.11.1
bottleneck : 1.3.5
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : None
numexpr : 2.8.4
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 10.0.1
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : 1.9.3
snappy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
zstandard : None
tzdata : Non