Enhancement include or exclude keys in json normalize
State: closed

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


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

Comments:
> Just mulling this over a bit more. Should we be giving consideration to `use_keys` instead?
> 
> Most other IO methods work by _inclusion_ instead of exclusion (see `usecols` parameter) and I'm wondering if that wouldn't be more applicable here as well. Curious to hear your thoughts

Yes I thought about it too. I come from a usecase where I had a large nested `dict` with `n` keys and want to ignore certain keys from flattening at all levels. It is easier to mention the keys to ignore than include. 

What do you think?
How about `use_keys` and it can accept a Callable to determine whether or not something can be included? That callable could provide the exclusion behavior you are after and it would be consistent with `usecols` behavior for other read methods
Few things I thought about. I understand the consistency part but the `usekeys` is not fitting. My first intuition on seeing it would be inclusion. 

We can do two things
1. We can name the callable a `column_selector` of sorts
2. Or can have two keys for `use_keys` and `exclude_keys`
Option2 is wonky from an API perspective. What is the problem with the following though?

```python
use_keys = lambda x: x not in {'exclude1', 'exclude2'}
```

I'm not tied to the `use_keys` moniker per se but I don't think we should deviate far from the similar functionality in the rest of the IO interface
> Option2 is wonky from an API perspective. What is the problem with the following though?
> 
> ```python
> use_keys = lambda x: x not in {'exclude1', 'exclude2'}
> ```
> 
> I'm not tied to the `use_keys` moniker per se but I don't think we should deviate far from the similar functionality in the rest of the IO interface

Okay. This makes sense. Let me do this.
Sorry I didn't mean to actually change the name to `usecols` but rather to support the same things, namely a string, list of strings and a callable as input. This was previously only supporting a callable.

Does that make sense?
Should I change it back to use_key?

On Wed, Jul 17, 2019, 9:07 PM William Ayd <notifications@github.com> wrote:

> Sorry I didn't mean to actually change the name to usecols but rather to
> support the same things, namely a string, list of strings and a callable as
> input. This was previously only supporting a callable.
>
> Does that make sense?
>
> —
> You are receiving this because you modified the open/close state.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/pull/27262?email_source=notifications&email_token=ACNFXIBWRB3ABU4WOHGPJ6TP744EVA5CNFSM4H6TTAKKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD2EZTVY#issuecomment-512334295>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/ACNFXICK7EEDX3Q7544FI3LP744EVANCNFSM4H6TTAKA>
> .
>

Sure I think so. There isn’t really the concept of a column in JSON

Sent from my iPhone

> On Jul 17, 2019, at 8:44 AM, Bhavani Ravi <notifications@github.com> wrote:
> 
> Should I change it back to use_key?
> 
> On Wed, Jul 17, 2019, 9:07 PM William Ayd <notifications@github.com> wrote:
> 
> > Sorry I didn't mean to actually change the name to usecols but rather to
> > support the same things, namely a string, list of strings and a callable as
> > input. This was previously only supporting a callable.
> >
> > Does that make sense?
> >
> > —
> > You are receiving this because you modified the open/close state.
> > Reply to this email directly, view it on GitHub
> > <https://github.com/pandas-dev/pandas/pull/27262?email_source=notifications&email_token=ACNFXIBWRB3ABU4WOHGPJ6TP744EVA5CNFSM4H6TTAKKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD2EZTVY#issuecomment-512334295>,
> > or mute the thread
> > <https://github.com/notifications/unsubscribe-auth/ACNFXICK7EEDX3Q7544FI3LP744EVANCNFSM4H6TTAKA>
> > .
> >
> —
> You are receiving this because you commented.
> Reply to this email directly, view it on GitHub, or mute the thread.

Will do.

On Wed, Jul 17, 2019, 9:18 PM William Ayd <notifications@github.com> wrote:

> Sure I think so. There isn’t really the concept of a column in JSON
>
> Sent from my iPhone
>
> > On Jul 17, 2019, at 8:44 AM, Bhavani Ravi <notifications@github.com>
> wrote:
> >
> > Should I change it back to use_key?
> >
> > On Wed, Jul 17, 2019, 9:07 PM William Ayd <notifications@github.com>
> wrote:
> >
> > > Sorry I didn't mean to actually change the name to usecols but rather
> to
> > > support the same things, namely a string, list of strings and a
> callable as
> > > input. This was previously only supporting a callable.
> > >
> > > Does that make sense?
> > >
> > > —
> > > You are receiving this because you modified the open/close state.
> > > Reply to this email directly, view it on GitHub
> > > <
> https://github.com/pandas-dev/pandas/pull/27262?email_source=notifications&email_token=ACNFXIBWRB3ABU4WOHGPJ6TP744EVA5CNFSM4H6TTAKKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD2EZTVY#issuecomment-512334295
> >,
> > > or mute the thread
> > > <
> https://github.com/notifications/unsubscribe-auth/ACNFXICK7EEDX3Q7544FI3LP744EVANCNFSM4H6TTAKA
> >
> > > .
> > >
> > —
> > You are receiving this because you commented.
> > Reply to this email directly, view it on GitHub, or mute the thread.
>
> —
> You are receiving this because you modified the open/close state.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/pull/27262?email_source=notifications&email_token=ACNFXIC43LUESLDMYXQ6QJTP745N5A5CNFSM4H6TTAKKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD2E2YNI#issuecomment-512338997>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/ACNFXID7AGDPDDVMOOIS6QLP745N5ANCNFSM4H6TTAKA>
> .
>

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-10-19 16:00:42 UTC
I think the title is outdated, and we are finally not ignoring keys, but selecting them. Can you please update it, so we don't get confused?
@bhavaniravi can you also add a test case that uses `nested_input_data` and selects the `Name` key? That key in particular is nested a few levels deeper so would be good to validate

@bhavaniravi in general is probably better if you push your changes once they are in state for us to be reviewed. There are few reasons for it:
- Every push sends an email to the people subscribed to it, or to the whole project
- Every push triggers a build in the CI (and unnecessary builds can make the builds of other PRs slower)
- We don't know when you're done and we should review

None of the above is a big deal, feel free to push when you need to. But if there is no reason for pushing more than at the end, it's probably worth that you are aware of the previous things.
> @bhavaniravi in general is probably better if you push your changes once they are in state for us to be reviewed. There are few reasons for it:
> 
> * Every push sends an email to the people subscribed to it, or to the whole project
> * Every push triggers a build in the CI (and unnecessary builds can make the builds of other PRs slower)
> * We don't know when you're done and we should review
> 
> None of the above is a big deal, feel free to push when you need to. But if there is no reason for pushing more than at the end, it's probably worth that you are aware of the previous things.

Sure. Will keep that in mind.
> @bhavaniravi can you also add a test case that uses `nested_input_data` and selects the `Name` key? That key, in particular, is nested a few levels deeper so would be good to validate

@WillAyd I am kind of stuck at this point when the number of nesting levels is > 1 and when we use `use_keys` as an inclusion method then, should the key have a path something like `CreatedBy.Name.FirstName`. Mentioning just `CreatedBy` flattens only 1 level or just `Name` can confuse with `Name` key in other levels
```
{
Name: "PO001"
"CreatedBy": {"Name":{"FirstName":"Name001"}}}
}
json_normalize(use_keys="CreatedBy.Name.FirstName"
```
Am I thinking right?
So to summarize the two options given:

```json
[{"a": 1},
 {"level_0": {"a": 1}},
 {"level_0": {"level_1": {"a": 1}}}]
```

Option 1 assuming `use_keys="a"` would include all of those entries, so that the evaluation of the expression is done solely against the object key without any real consideration for its place in the hierarchy.

Option 2 as I think you mentioned is to require the full hierarchy, so you'd have to do something like `use_key="level_0.level_1.a"` to get the last level. 

Thinking out loud, Option 1 seems easier from a usage perspective whereas Option 2 is more explicit. I might have a slight preference for option 2 as its a more conservative approach and it's usually easier to expand API functionality than to limit it, conceding that I still think Option 1 is more user friendly.

Open to your thoughts on it for sure
@WillAyd I'm leaned towards being explicit about the levels too. The current implementation doesn't support that. Let me fix that and get back

@WillAyd It's getting way more complicated than I thought and I need your help sorting it out.

Currently, we support 3 types of `use_keys` param `function, list and str`. Each type of this param is converted into a callable which checks in or not in `_parse_use_keys` which is later called whether to flatten a key or not.

Now to incorporate levels into all these 3 types it is getting way to complicated.
1. `use_key="level_0.level_1.a"`
2. `use_key=["level_0.level_1.a", "key2.level1"]`
3. `use_key= lambda x: x not in ["level_0.level_1.a"]`


Hey @bhavaniravi - I've been on vacation for a few weeks so sorry for lack of response. I'll be back in a few days and can review what you have again in more detail then
@bhavaniravi just reading through your last comment again. Is your concern that having a user specify the entire path to a key is too rigid? If so agreed it's a little tough from a usage perspective but that goes back to some of the comments in https://github.com/pandas-dev/pandas/pull/27262#issuecomment-517493404

So maybe as a third option we could explicitly document that use_key only works on top level keys of the JSON structure for now, and maybe leave it to a future enhancement to have that work further down in the hierarchy. I don't think that requires a lot of extra effort on top of what you have here but is explicit about the limitations of this. Do you think that might be easier?
@bhavaniravi is this still active? Thoughts on previous comment?
@WillAyd In #27262 (comment) 

> Option 1 assuming use_keys="a" would include all of those entries, so that the evaluation of the expression is done solely against the object key without any real consideration for its place in the hierarchy.

whereas the current implementation only normalizes the `a` at `level=0`. since it looks at `level_0` key which is not in list and proceeds without normalizing.

I think we are both agreeing on users being explicit for [option 1 and 2,](https://github.com/pandas-dev/pandas/pull/27262#issuecomment-522227548) leaving the callable for future implementation. is that right?

If callable is a hangup then feel free to ignore for now, but I am under the impression the issue is more so dealing with nested levels. Can you be sure to add a test case like:

```python
data = {
  "key1": {
    "should_match": 1,
    "no_match": 2
  },
  "should_match": 3
}
```

And making sure that `should_match` gets found in all cases? Right now tests only make assertion about items at the top of the JSON hierarchy
@WillAyd Made those changes you asked for. I had to change and swap a few things to get it right. Now it allows use_keys to be level specific for eg., `Level0.Level1.Level2.Key`
@bhavaniravi - could you merge master whenever you have some time
@bhavaniravi closing as I think this has gone stale but certainly ping if you'd like to pick back up and can reopen