Repository: pandas-dev/pandas
Pull request: Enhancement Add max_level param to json_normalize
- [x] closes #23843
- [x] closes #23861
- [x] tests added / passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry



Problem statement:
Configurable json_normalize with respect to number of levels and Keys to be flattened
#### Code Sample, a copy-pastable example if possible

```python
data = [{"CreatedBy":{"Name": "User001"}, 
        "Lookup":{"TextField":"Some text", "UserField":{"Id":"ID001", "Name": "Name001"}},
        "Image":{"a":"b"}}]
json_normalize(data).to_json(orient="records")
```
#### Current Output
```
[{"CreatedBy.Name":"User001","Image.a":"b",
"Lookup.TextField":"Some text",
"Lookup.UserField.Id":"ID001","Lookup.UserField.Name":"Name001"}]
```
#### Problem description

I want to flatten only specific keys and up to a specific a specific level. 
For eg., I want to flatten until level 1 and skip key image.

#### Expected Output

```
[{"CreatedBy.Name":"User001",
"Image:{a":"b}",
"Lookup.TextField":"Some text",
"Lookup.UserField":{Id":"ID001",
"Name":"Name001"}]
```

#### Output of ``pd.show_versions()``

INSTALLED VERSIONS
------------------
commit: None
pandas: 0.23.4
pytest: None
pip: 18.1
setuptools: 39.1.0
Cython: None
numpy: 1.15.1
scipy: None
pyarrow: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.7.3
pytz: 2018.4
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 3.0.0
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

Hints from issue discussion:
I thought for a moment that the `meta` parameter to `json_normalize` might help, but that was not to be.  Any kind of enhancement would need to be done to `json_normalize`.  It's "too late" once you get to the `.to_json`-`DataFrame` stage.

Interesting proposal.  My only concern is the extent of use cases for partial flattening.

cc @WillAyd @jreback 
I have rewritten the `nested_to_records` method for my use. 

Would love to contribute it back and extend it to `json_normalize` as well.

My use case is for exporting data for report generation.  
That's great!  Before we proceed, can you run tests on your machine to confirm that things don't break?
Definitely. Will do that. 
Yea I think it would be possible to add a `level` parameter here. There's a recursive function nested within which uses that as well, so may be able to wire those all together to control how deep the un-nesting of records goes.

Investigation and PRs certainly welcome
I have a query. If the `values` of the keys in record path are nested dictionaries itself, is it supposed . to get flattened? I am going through the code and got stuck here
@WillAyd I am a bit confused with the behavior of`json_normalize` method with `records_path`. While just passing a list of records flattens the dictionary, it doesn't flattens the same on sending `records_path` param. Is it the excepted behavior?

```
data = [{'CreatedBy': {'Name': 'User001'},
             'Lookup': [{'TextField': 'Some text',
                                'UserField': {'Id': 'ID001', 'Name': 'Name001'}},
                              {'TextField': 'Some text',
                               'UserField': {'Id': 'ID001', 'Name': 'Name001'}}
                        ],
            'Image': {'a': 'b'}
        }]

json_normalize(data, record_path=["Lookup"])
```
**Current Output**

```
   TextField                           UserField
0  Some text  {'Id': 'ID001', 'Name': 'Name001'}
1  Some text  {'Id': 'ID001', 'Name': 'Name001'}
```

**Expected Output**

```
   TextField              UserField.Id       Name
0  Some text               ID001             Name001
1  Some text               ID001             Name001
```
@bhavaniravi that seems like a bug to me. I'd suggest opening that as a separate issue and working that as a pre-cursor to this. Would serve the purpose of not only fixing the bug but helping grow familiarity with the various parameters so we can see how this should fit into the context of them
Actually there's already an issue open about this #22706
@WillAyd I think someone already gave a PR for the same. Should I be working on it too?

Commit message: remove unwanted file

Patch to review:
diff --git a/pandas/io/json/normalize.py b/pandas/io/json/normalize.py
index fa4e35b08bf6e..dd6efe0200e1f 100644
--- a/pandas/io/json/normalize.py
+++ b/pandas/io/json/normalize.py
@@ -25,9 +25,11 @@ def _convert_to_line_delimits(s):
     return convert_json_to_lines(s)
 
 
-def nested_to_record(ds, prefix="", sep=".", level=0):
+def nested_to_record(ds, prefix="", sep=".", level=0,
+                     max_level=None):
     """
-    A simplified json_normalize.
+
+    A simplified json_normalize
 
     Converts a nested dict into a flat dict ("record"), unlike json_normalize,
     it does not attempt to extract a subset of the data.
@@ -36,13 +38,19 @@ def nested_to_record(ds, prefix="", sep=".", level=0):
     ----------
     ds : dict or list of dicts
     prefix: the prefix, optional, default: ""
-    sep : string, default '.'
+    sep : str, default '.'
         Nested records will generate names separated by sep,
         e.g., for sep='.', { 'foo' : { 'bar' : 0 } } -> foo.bar
 
         .. versionadded:: 0.20.0
 
-    level: the number of levels in the jason string, optional, default: 0
+    level: int, optional, default: 0
+        The number of levels in the json string.
+
+    max_level: int, optional, default: None
+        The max depth to normalize.
+
+        .. versionadded:: 0.25.0
 
     Returns
     -------
@@ -65,10 +73,8 @@ def nested_to_record(ds, prefix="", sep=".", level=0):
     if isinstance(ds, dict):
         ds = [ds]
         singleton = True
-
     new_ds = []
     for d in ds:
-
         new_d = copy.deepcopy(d)
         for k, v in d.items():
             # each key gets renamed with prefix
@@ -79,16 +85,19 @@ def nested_to_record(ds, prefix="", sep=".", level=0):
             else:
                 newkey = prefix + sep + k
 
-            # only dicts gets recurse-flattend
+            # flatten if type is dict and
+            # current dict level  < maximum level provided and
             # only at level>1 do we rename the rest of the keys
-            if not isinstance(v, dict):
+            if (not isinstance(v, dict) or
+                    (max_level is not None and level >= max_level)):
                 if level != 0:  # so we skip copying for top level, common case
                     v = new_d.pop(k)
                     new_d[newkey] = v
                 continue
             else:
                 v = new_d.pop(k)
-                new_d.update(nested_to_record(v, newkey, sep, level + 1))
+                new_d.update(nested_to_record(v, newkey, sep, level + 1,
+                                              max_level))
         new_ds.append(new_d)
 
     if singleton:
@@ -100,42 +109,53 @@ def json_normalize(data, record_path=None, meta=None,
                    meta_prefix=None,
                    record_prefix=None,
                    errors='raise',
-                   sep='.'):
+                   sep='.',
+                   max_level=None):
     """
     Normalize semi-structured JSON data into a flat table.
 
     Parameters
     ----------
     data : dict or list of dicts
-        Unserialized JSON objects
-    record_path : string or list of strings, default None
+        Unserialized JSON objects.
+    record_path : str or list of str, default None
         Path in each object to list of records. If not passed, data will be
-        assumed to be an array of records
-    meta : list of paths (string or list of strings), default None
-        Fields to use as metadata for each record in resulting table
-    meta_prefix : string, default None
-    record_prefix : string, default None
+        assumed to be an array of records.
+    meta : list of paths (str or list of str), default None
+        Fields to use as metadata for each record in resulting table.
+    meta_prefix : str, default None
         If True, prefix records with dotted (?) path, e.g. foo.bar.field if
-        path to records is ['foo', 'bar']
+        meta is ['foo', 'bar'].
+    record_prefix : str, default None
+        If True, prefix records with dotted (?) path, e.g. foo.bar.field if
+        path to records is ['foo', 'bar'].
     errors : {'raise', 'ignore'}, default 'raise'
-
+        Configures error handling.
         * 'ignore' : will ignore KeyError if keys listed in meta are not
-          always present
+          always present.
         * 'raise' : will raise KeyError if keys listed in meta are not
-          always present
+          always present.
 
         .. versionadded:: 0.20.0
 
-    sep : string, default '.'
-        Nested records will generate names separated by sep,
-        e.g., for sep='.', { 'foo' : { 'bar' : 0 } } -> foo.bar
+    sep : str, default '.'
+        Nested records will generate names separated by sep.
+        e.g., for sep='.', { 'foo' : { 'bar' : 0 } } -> foo.bar.
 
         .. versionadded:: 0.20.0
 
+    max_level : int, default None
+        Max number of levels(depth of dict) to normalize.
+        if None, normalizes all levels.
+
+        .. versionadded:: 0.25.0
+
     Returns
     -------
     frame : DataFrame
 
+    Returns a JSON normalized Dataframe.
+
     Examples
     --------
 
@@ -149,30 +169,40 @@ def json_normalize(data, record_path=None, meta=None,
     1  NaN         NaN      Regner        NaN       Mose       NaN
     2  2.0  Faye Raker         NaN        NaN        NaN       NaN
 
+    >>> from pandas.io.json import json_normalize
+    >>> data = [{'id': 1,
+    ...          'name': "Cole Volk",
+    ...          'fitness': {'height': 130, 'weight': 60}},
+    ...         {'name': "Mose Reg",
+    ...          'fitness': {'height': 130, 'weight': 60}},
+    ...         {'id': 2, 'name': 'Faye Raker',
+    ...          'fitness': {'height': 130, 'weight': 60}}]
+    >>> json_normalize(data, max_level=1)
+      fitness.height  fitness.weight   id    name
+    0   130              60          1.0    Cole Volk
+    1   130              60          NaN    Mose Reg
+    2   130              60          2.0    Faye Raker
+
     >>> data = [{'state': 'Florida',
     ...          'shortname': 'FL',
-    ...          'info': {
-    ...               'governor': 'Rick Scott'
-    ...          },
+    ...          'info': {'governor': 'Rick Scott'},
     ...          'counties': [{'name': 'Dade', 'population': 12345},
-    ...                      {'name': 'Broward', 'population': 40000},
-    ...                      {'name': 'Palm Beach', 'population': 60000}]},
+    ...                       {'name': 'Broward', 'population': 40000},
+    ...                       {'name': 'Palm Beach', 'population': 60000}]},
     ...         {'state': 'Ohio',
     ...          'shortname': 'OH',
-    ...          'info': {
-    ...               'governor': 'John Kasich'
-    ...          },
+    ...          'info': {'governor': 'John Kasich'},
     ...          'counties': [{'name': 'Summit', 'population': 1234},
     ...                       {'name': 'Cuyahoga', 'population': 1337}]}]
     >>> result = json_normalize(data, 'counties', ['state', 'shortname',
-    ...                                           ['info', 'governor']])
+    ...                                            ['info', 'governor']])
     >>> result
-             name  population info.governor    state shortname
-    0        Dade       12345    Rick Scott  Florida        FL
-    1     Broward       40000    Rick Scott  Florida        FL
-    2  Palm Beach       60000    Rick Scott  Florida        FL
-    3      Summit        1234   John Kasich     Ohio        OH
-    4    Cuyahoga        1337   John Kasich     Ohio        OH
+             name  population    state shortname info.governor
+    0        Dade       12345   Florida    FL    Rick Scott
+    1     Broward       40000   Florida    FL    Rick Scott
+    2  Palm Beach       60000   Florida    FL    Rick Scott
+    3      Summit        1234   Ohio       OH    John Kasich
+    4    Cuyahoga        1337   Ohio       OH    John Kasich
 
     >>> data = {'A': [1, 2]}
     >>> json_normalize(data, 'A', record_prefix='Prefix.')
@@ -206,7 +236,8 @@ def _pull_field(js, spec):
             #
             # TODO: handle record value which are lists, at least error
             #       reasonably
-            data = nested_to_record(data, sep=sep)
+            data = nested_to_record(data, sep=sep,
+                                    max_level=max_level)
         return DataFrame(data)
     elif not isinstance(record_path, list):
         record_path = [record_path]
@@ -241,10 +272,12 @@ def _recursive_extract(data, path, seen_meta, level=0):
         else:
             for obj in data:
                 recs = _pull_field(obj, path[0])
+                recs = [nested_to_record(r, sep=sep,
+                                         max_level=max_level)
+                        if isinstance(r, dict) else r for r in recs]
 
                 # For repeating the metadata later
                 lengths.append(len(recs))
-
                 for val, key in zip(meta, meta_keys):
                     if level + 1 > len(val):
                         meta_val = seen_meta[key]
@@ -260,7 +293,6 @@ def _recursive_extract(data, path, seen_meta, level=0):
                                                "{err} is not always present"
                                                .format(err=e))
                     meta_vals[key].append(meta_val)
-
                 records.extend(recs)
 
     _recursive_extract(data, record_path, {}, level=0)
@@ -279,8 +311,5 @@ def _recursive_extract(data, path, seen_meta, level=0):
         if k in result:
             raise ValueError('Conflicting metadata name {name}, '
                              'need distinguishing prefix '.format(name=k))
-
-        # forcing dtype to object to avoid the metadata being casted to string
         result[k] = np.array(v, dtype=object).repeat(lengths)
-
     return result
diff --git a/pandas/tests/io/json/test_normalize.py b/pandas/tests/io/json/test_normalize.py
index a7407d843c6c9..5c04f0af2c41a 100644
--- a/pandas/tests/io/json/test_normalize.py
+++ b/pandas/tests/io/json/test_normalize.py
@@ -66,6 +66,20 @@ def author_missing_data():
          }]
 
 
+@pytest.fixture
+def deeply_nested_post_data():
+    return [{'CreatedBy': {'Name': 'User001'},
+             'Lookup': [{'TextField': 'Some text',
+                         'UserField': {'Id': 'ID001', 'Name': 'Name001'}},
+                        {'TextField': 'Some text',
+                         'UserField': {'Id': 'ID001', 'Name': 'Name001'}}
+                        ],
+             'Image': {'a': 'b'},
+             'tags': [{'foo': 'something', 'bar': 'else'},
+                      {'foo': 'something2', 'bar': 'else2'}]
+             }]
+
+
 @pytest.fixture
 def missing_metadata():
     return [
@@ -294,6 +308,42 @@ def test_missing_field(self, author_missing_data):
         expected = DataFrame(ex_data)
         tm.assert_frame_equal(result, expected)
 
+    @pytest.mark.parametrize("max_level", [0, 1])
+    def test_max_level_with_records_path(self,
+                                         deeply_nested_post_data,
+                                         max_level):
+
+        expected_data = {0: [{"TextField": "Some text",
+                              'UserField': {'Id': 'ID001',
+                                            'Name': 'Name001'},
+                              "CreatedBy": {"Name": "User001"},
+                              'Image': {'a': 'b'}},
+                             {"TextField": "Some text",
+                              'UserField': {'Id': 'ID001',
+                                            'Name': 'Name001'},
+                              "CreatedBy": {"Name": "User001"},
+                              'Image': {'a': 'b'}}],
+                         1: [{"TextField": "Some text",
+                              "UserField.Id": "ID001",
+                              "UserField.Name": "Name001",
+                              "CreatedBy": {"Name": "User001"},
+                              'Image': {'a': 'b'}},
+                             {"TextField": "Some text",
+                              "UserField.Id": "ID001",
+                              "UserField.Name": "Name001",
+                              "CreatedBy": {"Name": "User001"},
+                              'Image': {'a': 'b'}}]}
+
+        test_input = deeply_nested_post_data
+        expected_data = expected_data[max_level]
+        result = json_normalize(test_input,
+                                record_path=["Lookup"],
+                                meta=[["CreatedBy"], ["Image"]],
+                                max_level=max_level)
+        expected_df = DataFrame(data=expected_data,
+                                columns=result.columns.values)
+        tm.assert_equal(expected_df, result)
+
 
 class TestNestedToRecord:
 
@@ -301,7 +351,6 @@ def test_flat_stays_flat(self):
         recs = [dict(flat1=1, flat2=2),
                 dict(flat1=3, flat2=4),
                 ]
-
         result = nested_to_record(recs)
         expected = recs
         assert result == expected
@@ -370,10 +419,6 @@ def test_missing_meta(self, missing_metadata):
              'zip': 37643,
              'name': np.nan}
         ]
-        ex_data = [
-            ['Massillon', 9562, 'OH', 'Morris St.', 44646, 'Alice'],
-            ['Elizabethton', 8449, 'TN', 'Spring St.', 37643, np.nan]
-        ]
         columns = ['city', 'number', 'state', 'street', 'zip', 'name']
         expected = DataFrame(ex_data, columns=columns)
         tm.assert_frame_equal(result, expected)
@@ -460,3 +505,84 @@ def test_nonetype_multiple_levels(self):
             'location.country.state.town.info.y': -33.148521423339844,
             'location.country.state.town.info.z': 27.572303771972656}
         assert result == expected
+
+    def test_with_max_level_none(self):
+        # GH23843: Enhanced JSON normalize
+        data = [{
+            'CreatedBy': {'Name': 'User001'},
+            'Lookup': {'TextField': 'Some text',
+                       'UserField': {'Id': 'ID001', 'Name': 'Name001'}},
+            'Image': {'a': 'b'}
+        }]
+        expected_output = [{
+            'CreatedBy.Name': 'User001',
+            'Lookup.TextField': 'Some text',
+            'Lookup.UserField.Id': 'ID001',
+            'Lookup.UserField.Name': 'Name001',
+            'Image.a': 'b'
+        }]
+        output = nested_to_record(data)
+        assert output == expected_output
+
+    def test_with_max_level_zero(self):
+        data = [{
+            'CreatedBy': {'Name': 'User001'},
+            'Lookup': {'TextField': 'Some text',
+                       'UserField': {'Id': 'ID001', 'Name': 'Name001'}},
+            'Image': {"a": 'b'}
+        }]
+        output = nested_to_record(data, max_level=0)
+        assert output == data
+
+    def test_with_max_level_one(self):
+        data = [{
+            'CreatedBy': {'Name': 'User001'},
+            'Lookup': {'TextField': 'Some text',
+                       'UserField': {'Id': 'ID001', 'Name': 'Name001'}},
+            'Image': {'a': 'b'}
+        }]
+        expected_output = [{
+            'CreatedBy.Name': 'User001',
+            'Lookup.TextField': 'Some text',
+            'Lookup.UserField': {'Id': 'ID001', 'Name': 'Name001'},
+            'Image.a': 'b'
+        }]
+        output = nested_to_record(data, max_level=1)
+        assert output == expected_output
+
+    def test_with_large_max_level(self):
+        data = [
+            {'CreatedBy': {
+                "user": {
+                    "name": {"firstname": "Leo",
+                             "LastName": "Thomson"},
+                    "family_tree": {
+                        "father": {
+                            "name": "Father001",
+                            "father": {
+                                "Name": "Father002",
+                                "father": {
+                                    "name": "Father003",
+                                    "father": {
+                                        "Name": "Father004",
+                                    },
+                                },
+                            }
+                        }
+                    }
+                }
+            }}
+        ]
+        expected_output = [
+            {'CreatedBy.user.name.firstname': 'Leo',
+             'CreatedBy.user.name.LastName': 'Thomson',
+             'CreatedBy.user.family_tree.father.name': 'Father001',
+             'CreatedBy.user.family_tree.father.father.Name': 'Father002',
+             'CreatedBy.user.family_tree.father.father.father.name':
+                 'Father003',
+             'CreatedBy.user.family_tree.father.father.father.father.Name':
+                 'Father004'}
+        ]
+
+        output = nested_to_record(data, max_level=100)
+        assert output == expected_output


Code review comments:
diff_hunk: @@ -66,6 +66,20 @@ def author_missing_data():
          }]
 
 
+@pytest.fixture
+def deeply_nested_post_data():
path: pandas/tests/io/json/test_normalize.py
text: @user1:
Is this just used in the one test? If so, we prefer to define it in this test.