ENH: Added to_json_schema
State: closed

Lays the groundwork for (but doesn't close) https://github.com/pandas-dev/pandas/issues/14386
This handles the schema part of the request there. We'll still need to
do the work to publish the data to the frontend, but that can be done
as a followup.

Usage:

```python
In [4]: df = pd.DataFrame(
   ...:     {'A': [1, 2, 3],
   ...:      'B': ['a', 'b', 'c'],
   ...:      'C': pd.date_range('2016-01-01', freq='d', periods=3),
   ...:      }, index=pd.Index(range(3), name='idx'))
   ...: df
   ...:
Out[4]:
     A  B          C
idx
0    1  a 2016-01-01
1    2  b 2016-01-02
2    3  c 2016-01-03

In [5]:

In [5]: pd.to_json_schema(df)
Out[5]:
{'fields': [{'name': 'idx', 'type': 'integer'},
  {'name': 'A', 'type': 'integer'},
  {'name': 'B', 'type': 'string'},
  {'name': 'C', 'type': 'date'}],
 'primary_key': 'idx'}
```

---

I think this is useful enough on its own to be part of the public API, so I've documented as such.
I've included a placeholder `publish_tableschema` that will *not* be included in the final commit.
It's just to make @rgbkrk's life easier for prototyping the nteract frontend. I think the proper solution for publishing the schema + data will have to wait on https://github.com/ipython/ipython/issues/10090

Labels:
name: Enhancement
name: IO Data
description: IO issues that don't fit into a more specific label
name: Dtype Conversions
description: Unexpected or buggy dtype conversions

Comments:
@pwalsh one data type question for you, what would a good JSON table schema type be for `timedelta`? I had thought [duration](http://specs.frictionlessdata.io/json-table-schema/#duration), but IIUC that's not quite the same. A timedelta can always be converted to a total number of seconds, whereas a duration can't.

At the moment I don't attempt to distinguish between JSON Table Schema `object` or `array` types and `any`. Since pandas doesn't handle nested data all that well, this may not be a big deal. We can revisit in the future though.
One more thing: this could also be the start for https://github.com/pandas-dev/pandas/issues/9146, a roundtrip orient for JSON. The spec allows for additional properties at the "table" and "field" level. So we could have a `pandas_dtype` property on each field, and more information about the table,

```python
{
        'type': 'DataFrame',
        'version': pd.__version__,
        'orient': 'records',
        'date_unit': 'ms'
}
```
isn't this just another json format orient?

or maybe need an argument schema=True in to_json()
> isn't this just another json format orient?

yes and no. This is just the schema, not the values. And this returns a dict instead of a serialized string.

I've put the doc section in `io.rst`, since it's related to IO, while not actually writing the data.
this is very odd for a top level method
This is great to see so we at least have standardized types for us to work with on the front-end.

It didn't occur to me that we would need to carve out the top level format for publishing both the schema and the data. I like the `{ schema: schema, data: data }` approach so far, set with a mimetype for the table schema. I'd then want to push on the R kernel to publish the same format for R data frames.

The matching data format is `orient='rows'` with `to_json` right?
> this is very odd for a top level method

Agreed. It probably belongs on `DataFrames/Series`, but I don't think it should have a `to_*` name, since that implies it's writing out the whole dataset, not just the schema.

> The matching data format is orient='rows' with to_json right?

`orient='records'`, with a `.reset_index` first if you want to include the index.
I don't think this should be a method at all
just an option in to_json
@TomAugspurger I'd use duration for timedelta. I'm going to have to research it a bit more if you think there is some inconsistency here.
Hey @holdenk - could we support the same schema + data output for Spark DataFrames?
We probably could, in fact the schema is already transfered between the JVM and PySpark using JSON so we might be able to just normalize on that format for interchange inside of PySpark its self.
I like @jreback's suggestion to add a `schema`argument to `to_json` method. I also like @rgbkrk's suggestion to return both the schema and data as `{ schema: schema, data: data }`. Lastly (I'm not sure if this should be implemented in pandas) we want to consume this as a mime bundle on the front-end, so the result should look something like:

```json
{
    "application/tableschema+json": {
        "schema": schema,
        "data": data
    }
}
BTW, I have published a jupyterlab/notebook extension that will render JSON Table Schema: https://github.com/gnestor/jupyterlab_table

This is more of a WIP until some standards are in place (e.g. a mimetype for JSON Table Schema, pandas compatibility, etc.).
Related media types have been registered over the new year:

- http://www.iana.org/assignments/media-types/application/vnd.tableschema+json
- http://www.iana.org/assignments/media-types/application/vnd.dataresource+json
> I don't think this should be a method at all just an option in to_json

OK, coming around on this. One problem: right now `to_json` returns the serialized data as a `str`. Does anyone (@rgbkrk maybe?) know if there's a way to tell `IPython.display.*` that data has already been serialized?

```python
import json
import IPython

# passing a python dict, which IPython serializes
IPython.display.display({"application/json": {"A": [1, 2, 3]}}, raw=True)

# Any way to do this?
IPython.display.display({"application/json": json.dumps({"A": [1, 2, 3]})}, raw=True)
```

or we could potentially include the mime-type in the already serialized data.
@minrk ^^ 

usually I pass a direct dict to IPython.display.display with raw=True. I'm not sure how I'd pass something already encoded since this data would be part of an overall JSON object.
Pardon me typing on mobile, I see now I'm repeating things already said.
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/14904?src=pr&el=h1) Report
> Merging [#14904](https://codecov.io/gh/pandas-dev/pandas/pull/14904?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/7ae4fd10b1581d77b4cfbf594e3b444af7456876?src=pr&el=desc) will **decrease** coverage by `-0.06%`.
> The diff coverage is `87.58%`.


```diff
@@            Coverage Diff             @@
##           master   #14904      +/-   ##
==========================================
- Coverage   91.07%   91.01%   -0.06%     
==========================================
  Files         136      137       +1     
  Lines       49167    49228      +61     
==========================================
+ Hits        44777    44806      +29     
- Misses       4390     4422      +32
```


| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/14904?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/json/json.py](https://codecov.io/gh/pandas-dev/pandas/compare/7ae4fd10b1581d77b4cfbf594e3b444af7456876...07d90cbd910be091bd2c2c087f5fb3f85dcb1fbc?src=pr&el=tree#diff-cGFuZGFzL2lvL2pzb24vanNvbi5weQ==) | `90.27% <100%> (+1.04%)` | :white_check_mark: |
| [pandas/io/json/__init__.py](https://codecov.io/gh/pandas-dev/pandas/compare/7ae4fd10b1581d77b4cfbf594e3b444af7456876...07d90cbd910be091bd2c2c087f5fb3f85dcb1fbc?src=pr&el=tree#diff-cGFuZGFzL2lvL2pzb24vX19pbml0X18ucHk=) | `100% <100%> (ø)` | :white_check_mark: |
| [pandas/core/config_init.py](https://codecov.io/gh/pandas-dev/pandas/compare/7ae4fd10b1581d77b4cfbf594e3b444af7456876...07d90cbd910be091bd2c2c087f5fb3f85dcb1fbc?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvY29uZmlnX2luaXQucHk=) | `95.12% <100%> (-0.16%)` | :x: |
| [pandas/util/testing.py](https://codecov.io/gh/pandas-dev/pandas/compare/7ae4fd10b1581d77b4cfbf594e3b444af7456876...07d90cbd910be091bd2c2c087f5fb3f85dcb1fbc?src=pr&el=tree#diff-cGFuZGFzL3V0aWwvdGVzdGluZy5weQ==) | `81.11% <13.33%> (-0.96%)` | :x: |
| [pandas/core/generic.py](https://codecov.io/gh/pandas-dev/pandas/compare/7ae4fd10b1581d77b4cfbf594e3b444af7456876...07d90cbd910be091bd2c2c087f5fb3f85dcb1fbc?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ2VuZXJpYy5weQ==) | `96.25% <91.3%> (-0.07%)` | :x: |
| [pandas/io/json/table_schema.py](https://codecov.io/gh/pandas-dev/pandas/compare/7ae4fd10b1581d77b4cfbf594e3b444af7456876...07d90cbd910be091bd2c2c087f5fb3f85dcb1fbc?src=pr&el=tree#diff-cGFuZGFzL2lvL2pzb24vdGFibGVfc2NoZW1hLnB5) | `95.58% <95.58%> (ø)` | |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/compare/7ae4fd10b1581d77b4cfbf594e3b444af7456876...07d90cbd910be091bd2c2c087f5fb3f85dcb1fbc?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `25% <0%> (-58.34%)` | :x: |
| [pandas/computation/pytables.py](https://codecov.io/gh/pandas-dev/pandas/compare/7ae4fd10b1581d77b4cfbf594e3b444af7456876...07d90cbd910be091bd2c2c087f5fb3f85dcb1fbc?src=pr&el=tree#diff-cGFuZGFzL2NvbXB1dGF0aW9uL3B5dGFibGVzLnB5) | `90.6% <0%> (-0.99%)` | :x: |
| [pandas/tools/merge.py](https://codecov.io/gh/pandas-dev/pandas/compare/7ae4fd10b1581d77b4cfbf594e3b444af7456876...07d90cbd910be091bd2c2c087f5fb3f85dcb1fbc?src=pr&el=tree#diff-cGFuZGFzL3Rvb2xzL21lcmdlLnB5) | `91.78% <0%> (-0.35%)` | :x: |
| ... and [11 more](https://codecov.io/gh/pandas-dev/pandas/pull/14904?src=pr&el=tree-more) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/14904?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/14904?src=pr&el=footer). Last update [7ae4fd1...9fac34c](https://codecov.io/gh/pandas-dev/pandas/compare/7ae4fd10b1581d77b4cfbf594e3b444af7456876...9fac34ce6c646407111c09a942cbd195b6cbf590?src=pr&el=footer&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).
@pwalsh what would the media type and structure be for the combined schema plus data:

```js
{
  schema: {...jsonTableSchemaHere},
  data: [...rows],
}
```
@rgbkrk that would be http://www.iana.org/assignments/media-types/application/vnd.dataresource+json
Would it end up like this then, based on that spec?

```js
{
  "resources": [{
    "format": "json",
    "data": [...],
    "schema": "table-schema"
  }],
  "schemas": {
    "table-schema": // inline here?
  }
}
```
@rgbkrk 

No - in this case, the top-level object is an object within your resources array, with the data inlined, and the schema inlined, exactly as https://github.com/pandas-dev/pandas/pull/14904#issuecomment-272664746
Ok cool, thanks for clarifying for me.

```js
{
  "resources": [
    {
      "schema": {...jsonTableSchemaHere},
      "data": [...rows],
    }
  ]
}
```
Thanks for the feedback all. We've split off the timedelta / duration formatting to https://github.com/pandas-dev/pandas/pull/15136 (comment there if you have any issues with that format; seems like there's some leeway in the spec).

Today I'll clean up the hacked time delta formatting in `to_json`, fix the mimetype, and write up docs / tests.

Only other major outstanding issue is the serialization before sending things off to be published by IPython. For reference, all of our `to_json` conversion goes from Python object to string, so pandas will already do the serialization.

Digging around, I didn't see any way for IPython to do this already. It's probably not worth the effort to change IPython / jupyter to accept already serialized messages. We'll just have one  extra de-serialize / serialize.
It seems like the kind of thing where IPython should accept a serializer. I've done hacky serialize -> deserialize -> send before. Alternatively, could we reuse any of this with `to_dict` and use that with IPython?

@carreau & @takluyver - any ideas about how we could accommodate libraries that use their own serializer for JSON if we end up sending raw JSON?
> @carreau & @takluyver - any ideas about how we could accommodate libraries that use their own serializer for JSON if we end up sending raw JSON

Not from the top of my head, I haven't touched that in a while.
One thing I would be happy with is  experimental API with provisional warnings/private methods.
What about a convention for mimetype (trailing or leading prefix) telling IPython to just trip this, and consider the value as already serialized?
I'm not quite sure what you mean from a quick scan of this conversation, but is it sufficient to define an `_ipython_display_()` method? If that exists, IPython uses that to tell the object to display itself, rather than going through the normal collection of mimetypes.
I think the issue now is how to publish something that has already been JSON serialized,

```python
import json
import IPython

# passing a python dict, which IPython serializes
IPython.display.display({"application/json": {"A": [1, 2, 3]}}, raw=True)

# Any way to do this? Where the "payload" as already been serialized?
IPython.display.display({"application/mymimetype": json.dumps({"A": [1, 2, 3]})}, raw=True)
```

ATM, I have to deserialize the serialized JSON, pass it to display, and presumably IPython serializes it somewhere down the line. Ideally we could avoid that unnecessary deserialize and serialize.
In the current state of things, I don't think there's a good answer for already serialized JSON, because one of two things is going to happen:

1. return serialized string which gets double-serialized by IPython
2. deserialize JSON yourself, returning JSONable data, which gets reserialized by IPython

The other option, which might work best in the end, is to make the *final* JSON serialization used by IPython extensible (much like pickle registries), and then put `self` in the field, trusting the final JSON serializer to deal with it. Once such APIs exist, it would look something like this:

```python
IPython.register_json_serializer(MyType, my_to_json)
IPython.display.display({"application/json": my_object)}, raw=True)
```

[Here is a notebook](https://nbviewer.jupyter.org/gist/minrk/10c74974836d8d691869e1b316765177) illustrating how such a thing might work, and what would currently need to be worked around in the absence of public APIs.
Hi. Just to note that we are renaming `JSON Table Schema` -> `Table Schema` for v1 one (should land this week).