Fix JSON orient='table' issues with numeric column names
State: closed

closes #19129
closes #22525
- [ ] tests added / passed
- [ ] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [ ] whatsnew entry


Labels:
name: IO JSON
description: read_json, to_json, json_normalize

Comments:
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25488?src=pr&el=h1) Report
> Merging [#25488](https://codecov.io/gh/pandas-dev/pandas/pull/25488?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/db978c716369064421c5ca71bd26002e0021e0d1?src=pr&el=desc) will **increase** coverage by `<.01%`.
> The diff coverage is `100%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/25488/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/25488?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #25488      +/-   ##
==========================================
+ Coverage   91.75%   91.75%   +<.01%     
==========================================
  Files         173      173              
  Lines       52955    52960       +5     
==========================================
+ Hits        48588    48594       +6     
+ Misses       4367     4366       -1
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.32% <100%> (ø)` | :arrow_up: |
| #single | `41.71% <0%> (-0.01%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/25488?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/json/json.py](https://codecov.io/gh/pandas-dev/pandas/pull/25488/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2pzb24vanNvbi5weQ==) | `93.28% <100%> (+0.06%)` | :arrow_up: |
| [pandas/io/json/table\_schema.py](https://codecov.io/gh/pandas-dev/pandas/pull/25488/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2pzb24vdGFibGVfc2NoZW1hLnB5) | `98.31% <100%> (+0.01%)` | :arrow_up: |
| [pandas/util/testing.py](https://codecov.io/gh/pandas-dev/pandas/pull/25488/diff?src=pr&el=tree#diff-cGFuZGFzL3V0aWwvdGVzdGluZy5weQ==) | `87.66% <0%> (+0.09%)` | :arrow_up: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25488?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/25488?src=pr&el=footer). Last update [db978c7...e7abdb1](https://codecov.io/gh/pandas-dev/pandas/pull/25488?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25488?src=pr&el=h1) Report
> Merging [#25488](https://codecov.io/gh/pandas-dev/pandas/pull/25488?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/c79b7bb91236a2e4cde3782d23109ac1f5e76dce?src=pr&el=desc) will **decrease** coverage by `<.01%`.
> The diff coverage is `100%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/25488/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/25488?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #25488      +/-   ##
==========================================
- Coverage      92%   91.99%   -0.01%     
==========================================
  Files         175      175              
  Lines       52371    52381      +10     
==========================================
+ Hits        48184    48190       +6     
- Misses       4187     4191       +4
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.55% <100%> (ø)` | :arrow_up: |
| #single | `40.73% <5.88%> (-0.15%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/25488?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/json/json.py](https://codecov.io/gh/pandas-dev/pandas/pull/25488/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2pzb24vanNvbi5weQ==) | `93.3% <100%> (+0.06%)` | :arrow_up: |
| [pandas/io/json/table\_schema.py](https://codecov.io/gh/pandas-dev/pandas/pull/25488/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2pzb24vdGFibGVfc2NoZW1hLnB5) | `98.38% <100%> (+0.08%)` | :arrow_up: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/25488/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `75% <0%> (-12.5%)` | :arrow_down: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/25488/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `96.9% <0%> (-0.12%)` | :arrow_down: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/25488?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/25488?src=pr&el=footer). Last update [c79b7bb...1465e39](https://codecov.io/gh/pandas-dev/pandas/pull/25488?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

Hello, here my explanation to justify this PR.

As I already commented in #19129, IIUC, the only aim of orient='target' is to get round-trip JSON serialization/deserialization of pandas objects. Thus, the 'schema' was added following the "Table Schema" spec in https://frictionlessdata.io/specs/table-schema/.

This spec is ONLY for the 'schema', not for the table itself. And this spec does not prohibit the column names to be non-strings. Indeed, when serializing pandas DataFrames with non-string column names, their 'schema' is totally valid, and the issues arise just because of the JSON serialization of the table itself (the 'data').

In order to serialize the data itself, among all the possible orient values, it was chosen orient='records' and this is the cause of all the issues. Why? Because orient='records' serializes each DataFrame row as a JSON object (roughly equivalent to a Python dict), using the column names as the JSON object names (dict keys). The choice of using column names as dict keys (JSON object names) is not right for several reasons:
- non-string column names: 
DataFrame column names are not restricted to be strings. Indeed, non-string column names are the default if no explicit column names are passed to DataFrame instantiation; however, JSON spec imposes that JSON object names must be strings. 
- order of the columns:
DataFrame columns have a specific order; however, when representing each record as a Python dict, we lose the order of the columns.
- non-unique column names
a DataFrame can have non-unique column names; however a Python dict cannot have non-unique keys.

In order to address all theses issues, this PR proposes to use another representation of each DataFrame row: instead of using a Python dict, we should use a Python list. The Python list:
- can have non-unique items
- conserves the order of its items
- and its JSON equivalent is a JSON array, which does not impose any restriction on the types of its values

And in pandas, this can be easily made by choosing the orient='values' (instead of orient='records').

Finally, I would like to point out that there exists a Tabular Data Resource spec (https://frictionlessdata.io/specs/tabular-data-resource/) and it clearly establishes (in its section JSON Tabular Data):

> JSON Tabular Data MUST be an `array` where each item in the array MUST be:
> - EITHER: an array where each entry in the array is the value for that cell in the table
> - OR: an object where each key corresponds to the header for that row and the value corresponds to the cell value for that row for that header

This PR proposes to use the first option (instead of the second one) for all the previously mentioned reasons.
> This PR proposes to use the first option (instead of the second one) for all the previously mentioned reasons.

OK I see your point here though so thanks for clarifying. Will review in more detail later and give you feedback just wanted to reach out in the interim
> What do people think about leaving `orient` just for the data key, and adding a new keyword `schema=True/False` to control whether we include the `schema` key at the outer level? I haven't really thought through the deprecation cycle.

What do you see as the advantage of doing this? I would actually be against this as we already have quite a few JSON formats, so adding another that doesn't follow the table spec doesn't add a lot of value I think.


I’ll need to read the spec again, but it may not make any claims for how the data should be represented. 

> On Mar 4, 2019, at 22:25, William Ayd <notifications@github.com> wrote:
> 
> What do people think about leaving orient just for the data key, and adding a new keyword schema=True/False to control whether we include the schema key at the outer level? I haven't really thought through the deprecation cycle.
> 
> What do you see as the advantage of doing this? I would actually be against this as we already have quite a few JSON formats, so adding another that doesn't follow the table spec doesn't add a lot of value I think.
> 
> —
> You are receiving this because you commented.
> Reply to this email directly, view it on GitHub, or mute the thread.

can you merge master
can you merge master
Not a bad idea but this change is definitely breaking and comes with quite a few implications. Given it's gone somewhat stale over the past month and a half going to close out, but feel free to ping back @albertvillanova if it's something you want to drive forward