Enhanced json normalize
State: closed

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


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

Comments:
Hello @bhavaniravi! Thanks for updating this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:





There are currently no PEP 8 issues detected in this Pull Request. Cheers! :beers: 

##### Comment last updated at 2019-04-30 08:23:34 UTC
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/23861?src=pr&el=h1) Report
> Merging [#23861](https://codecov.io/gh/pandas-dev/pandas/pull/23861?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/9feb3ad92cc0397a04b665803a49299ee7aa1037?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/23861/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/23861?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #23861      +/-   ##
==========================================
- Coverage   91.97%   91.96%   -0.01%     
==========================================
  Files         175      175              
  Lines       52368    52371       +3     
==========================================
  Hits        48164    48164              
- Misses       4204     4207       +3
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.52% <100%> (ø)` | :arrow_up: |
| #single | `40.69% <14.28%> (-0.16%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/23861?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/json/normalize.py](https://codecov.io/gh/pandas-dev/pandas/pull/23861/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2pzb24vbm9ybWFsaXplLnB5) | `97.02% <100%> (+0.09%)` | :arrow_up: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/23861/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `78.94% <0%> (-10.53%)` | :arrow_down: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/23861/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `96.9% <0%> (-0.12%)` | :arrow_down: |
| [pandas/util/testing.py](https://codecov.io/gh/pandas-dev/pandas/pull/23861/diff?src=pr&el=tree#diff-cGFuZGFzL3V0aWwvdGVzdGluZy5weQ==) | `90.71% <0%> (+0.1%)` | :arrow_up: |

------

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

Good start (left a couple of comments)!  Can you also add a mini-section to the `whatsnew` explaining what these parameters do and how you use them?
closing. if you want to continue, pls ping. needs to merge master and update to comments.
Yes I want to continue this to merging. I was waiting for #22706 to get merged.
@WillAyd. The test passes in local and fails in Python 3.5 of the build.

![image](https://user-images.githubusercontent.com/10116000/51579342-a4573200-1ee6-11e9-8a25-58ae7d51bc40.png)

If the change the order of columns it fails in local.  Can you help me fix this?

This could be indicative of the (somewhat) unpredictable nature of column ordering when passing in a `dict` mapping of values into `DataFrame`.  I bet if you look at the data, you'll see that the column-data mappings are correct, just that ordering is not consistent.
Can you merge master and address comments?
Sure will do the changes and give a PR.

On Tue, Feb 19, 2019, 7:11 PM Jeff Reback <notifications@github.com> wrote:

> *@jreback* requested changes on this pull request.
> ------------------------------
>
> In pandas/io/json/normalize.py
> <https://github.com/pandas-dev/pandas/pull/23861#discussion_r258041695>:
>
> > @@ -25,9 +25,11 @@ def _convert_to_line_delimits(s):
>      return convert_json_to_lines(s)
>
>
> you have an extra file above, doc/test.parquet pls remove
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/pull/23861#pullrequestreview-205214902>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/AJpboCXwQxqV-2xzc0Hiu96_EF9fmkT0ks5vO_8EgaJpZM4YvnaU>
> .
>

I wanted to add on to this from some discussion over in #21537 that this fix doesn't handle the case of nested records.

For example:

data = {'a': 1, 'b': 2, 'c': {'d': [{'a': 1}, {'b': 2}], 'e': [{'a': 1}, {'b': 2}]}}
pd.io.json.json_normalize(data, record_path=['c'])

Produces:

0 | 0
-- | --
0 | d
1 | e

Where (ideally) it should produce:

0 | d.a | d.b | e.a | e.b
-- | -- | -- | -- | --
0 | 1 | 2 | 1 | 2

This is mainly because of the following bit:

https://github.com/pandas-dev/pandas/blob/fca2a2752dac842eb11e08aa7a35c42b4eefbc84/pandas/io/json/normalize.py#L287-L290

Which only extracts keys from the records, rather than iterating over the items.  Does this seem within scope for this PR? I can try and add-on here, if that's helpful.
if you can merge master and update to comments
Happy to help make the updates if @bhavaniravi doesn't mind.  But I'd also be interested in adding a piece to account for the nested record structure.  Any suggestions there? I'm thinking adding a check along the lines of:

```
if isinstance(recs, dict):
     recs = nested_to_record(recs)
```

I have to check if this works, but wanted to check if this should be part of a separate PR or if it's in scope here.
Closing as stale but ping if you'd like to continue. Otherwise @bpben if you'd like to pick this up in another PR then by all means!
Sorry guys, A bit occupied. Can close it off by End of this week, If that
works.

regards
Bhavani Ravi :)
bhavaniravi.com <https://medium.com/@bhavaniravi>


On Tue, Mar 19, 2019 at 9:03 AM William Ayd <notifications@github.com>
wrote:

> Closing as stale but ping if you'd like to continue. Otherwise @bpben
> <https://github.com/bpben> if you'd like to pick this up in another PR
> then by all means!
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/pull/23861#issuecomment-474185256>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/AJpboAiz5byqUnbAiL8KqURXOWCuS1huks5vYFpzgaJpZM4YvnaU>
> .
>

No worries. Typically there’s no rush with these just wanted to make sure it wasn’t abandoned. I’ve reopened - thanks for the help!
@WillAyd any thoughts on whether my proposed edit might be in scope here or should I open another PR?
@bpben another PR - I already think the scope of this one might be too large to begin with so breaking up makes the most sense
pls merge master
Can you check test failures? Looks like your last commits also picked up unwanted files
I am trying to fix the test failures. `test_missing_meta` failing. I printed the outputs and found `nan` and `NaN` are not equal. Not sure if `assert_frame_equal` will take care of this.

![image](https://user-images.githubusercontent.com/10116000/56459006-885f1180-63ab-11e9-924c-b515df0adc25.png)


It looks like one of your values is the literal string nan whereas the other is the actual sentinel. Hope that helps will need to be fixed. 

Sent from my iPhone

> On Apr 20, 2019, at 8:03 AM, Bhavani Ravi <notifications@github.com> wrote:
> 
> I am trying to fix the test failures. test_missing_meta failing. I printed the outputs and found nan and NaN are not equal. Not sure if assert_frame_equal will take care of this.
> 
> 
> 
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub, or mute the thread.

Line number 328 is where I am having the issue 
```
meta_values_ = np.array(v).repeat(lengths)
```
I'm printing v, and the result from above line
```
meta_values ===             ['Alice' 'nan']
type of real value v ===        <class 'float'>
np.array(v).repeat([lengths]) ===  <class 'numpy.str_'>
```
Seems like a numpy issue

```
>>> type(np.array(["a", np.nan]).repeat(2)[2])
<class 'numpy.str_'>
>>> type(np.array([1, np.nan]).repeat(2)[2])
<class 'numpy.float64'>
```

[Raised a question in StackOverflow too](https://stackoverflow.com/questions/55775600/numpy-repeat-converts-nan-to-str)
you have included get-pip somehow
Not sure why this test case `test_missing` is failing. @WillAyd Can you help me figure this out. https://dev.azure.com/pandas-dev/pandas/_build/results?buildId=10829
Try merging master one more time - this issue was just fixed recently. 

Sent from my iPhone

> On Apr 23, 2019, at 1:09 AM, Bhavani Ravi <notifications@github.com> wrote:
> 
> Not sure why this test case test_missing is failing. @WillAyd Can you help me figure this out. https://dev.azure.com/pandas-dev/pandas/_build/results?buildId=10829
> 
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub, or mute the thread.

> Sorry didn't see latest updates on the review side. I still really think it would be better if we just went forward with `max_level` here and did `ignore_keys` as a follow up - otherwise we've kind of implicitly bundled this together which slows down reviews and makes code more error prone.
> 
> Do you have any objection to splitting these up into separate PRs? If not can you focus on `max_level` in this one and add a whatsnew note?

@WillAyd I understand. Should I close this and create 2 new PRs. I'm just not sure how to proceed from here.
You can keep this PR and just focus on the max_level piece - can come back to the second enhancement after that in another PR
@WillAyd sure, let's do that. I have a whole weekend ahead of me. So what's next? 
@bhavaniravi try stripping out anything that isn't relevant for `max_level` to work
@bhavaniravi any interest in continuing here? I think these changes are great just need to simplify a bit to get through. Let me know if you can address comments above