BUG: JSON serialization with orient split fails roundtrip with MultiIndex

[datapythonista]

When saving a DataFrame to JSON with orient='split' and then loading it again, the loaded dataframe is different from the original if columns are a multiindex.

>>> df = DataFrame([[1, 2], [3, 4]],
...                columns=pd.MultiIndex.from_arrays([["2022", "2022"], ['JAN', 'FEB']]))
>>> df
  2022    
   JAN FEB
0    1   2
1    3   4

>>> read_json(df.to_json(orient='split'), orient='split')
  2022 JAN
  2022 FEB
0    1   2
1    3   4

The problem seems to be that the JSON stores the format as {"columns":[["2022","JAN"],["2022","FEB"]], ...}, but when creating the loaded DataFrame the columns value is passes as that, and DataFrame(data, columns=[["2022","JAN"],["2022","FEB"]]) produces the incorrect result.

We can fix this by either changing how data is stored in the JSON, or how the dataframe is created. Personally, I think it makes more sense to store the data in the JSON in the way expected by the dataframe constructor.

CC: @MarcoGorelli

[labibdotc]

take

[datapythonista]

A test for this is being added in #50370. Once that PR is merged, you'll need to remove the xfail of the test here.

[labibdotc]

Sweet. On it!

[labibdotc]

Okay, run-down of what I have so far to make sure I am not missing anything:

Program expectation:

>>> read_json(df.to_json(orient='split'), orient='split')
  2022    
   JAN FEB
0    1   2
1    3   4

Program behavior:

>>> read_json(df.to_json(orient='split'), orient='split')
  2022 JAN
  2022 FEB
0    1   2
1    3   4

Unit focus

from_array

# returns class instance of type multiIndex which stores the following:
        [('2022', 'JAN'),
            ('2022', 'FEB')],
# That's great!

to_json

# returns json object ready to be read as an orient "split". It contains the following:
{"columns":[["2022","JAN"],["2022","FEB"]],"index":[0,1],"data":[[1,2],[3,4]]}
# That's great!
# as it aligns with the read_json expectation in docs:
{{\
"columns":["col 1","col 2"],\
"index":["row 1","row 2"],\
"data":[["a","b"],["c","d"]]\
}}\
'
    >>> pd.read_json(_, orient='split')
          col 1 col 2
    row 1     a     b
    row 2     c     d

read_json (_get_object_parser under the hood)

# This is potentially a place where things go wrong as components leading up to here seems fine so far
# This is the step where the to_json object become tabular
# read_json() -> JsonReader.read() # when orient is "split" -> returns _get_object_parser(<to_json object from before>)
# obj produced by calling DataFrame constructor again using json objects we have, produces:
  2022 JAN
  2022 FEB
0    1   2
1    3   4

# this is the problem

[datapythonista]

Not exactly. In my opinion we should change to_json, and instead of {"columns":[["2022","JAN"],["2022","FEB"]],"index":[0,1],"data":[[1,2],[3,4]]} save {"columns":[["2022","2022"],["JAN","FEB"]],"index":[0,1],"data":[[1,2],[3,4]]}.

I think this will make everything consistent, and read_json will work fine as is.

[labibdotc]

Hi, I have couple of questions before filing my pull request:

• Where should I add my tests? pandas/tests/io/json/test_pandas.py: result = data.to_json(orient="split", index=False) is what I am looking at.
• How do I show that I removed xfail of a test? Do I run pytest pandas and provide terminal-log in my pull request?
• Also, should I create v2.0.1.rst or do I update the latest (i..e. v2.0.0.rst) in docs including my updates for this commit?

[datapythonista]

Ideally, we need #50370 merged before you can work on this. Once that PR is merged, the test will be added, and you can remove its xfail decorator in your PR to show that the test is now passing.

You should just add a bullet point to the v2.0.0.rst file.

[labibdotc]

Sounds good. I will take another issue for now, and will keep on checking back until it merges.

[datapythonista]

@labibdotc #50370 has been now merged. If you merge main into your branch (or start a new branch with an updated main), you can work in this issue without trouble now. Let me know if you've got any question.

[labibdotc]

@datapythonista, How exactly "removing an xfail decorator" works on my part? Does it happen automatically by running the tests on my modified code?
Also to confirm, pandas/tests/io/json/test_pandas.py is the only test file I am running?