Repository: pandas-dev/pandas
Pull request: BUG: non-iterable value in meta raise error in json_normalize
- [x] closes #31507 
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Problem statement:
json_normalize in 1.0.0 with meta path specified - expects iterable
#### Code Sample, a copy-pastable example if possible

```python
import json
from pandas.io.json import json_normalize

the_json = """                                                                  
[{"id": 99,                                                                     
  "data": [{"one": 1, "two": 2}]                                                
}]                                                                              
"""

print(json_normalize(json.loads(the_json),
                     record_path=['data'], meta=['id']))
```
#### Problem description

Through 0.25.3, this program generates a DataFrame with one row.  In 1.0.0 it fails with an exception:

```
Traceback (most recent call last):
  File "foo.py", line 11, in <module>
    record_path=['data'], meta=['id']))
  File "/home/dataczar/venvs/test/lib/python3.7/site-packages/pandas/util/_decorators.py", line 66, in wrapper
    return alternative(*args, **kwargs)
  File "/home/dataczar/venvs/test/lib/python3.7/site-packages/pandas/io/json/_normalize.py", line 327, in _json_normalize
    _recursive_extract(data, record_path, {}, level=0)
  File "/home/dataczar/venvs/test/lib/python3.7/site-packages/pandas/io/json/_normalize.py", line 314, in _recursive_extract
    meta_val = _pull_field(obj, val[level:])
  File "/home/dataczar/venvs/test/lib/python3.7/site-packages/pandas/io/json/_normalize.py", line 246, in _pull_field
    f"{js} has non iterable value {result} for path {spec}. "
TypeError: {'id': 99, 'data': [{'one': 1, 'two': 2}]} has non iterable value 99 for path ['id']. Must be iterable or null.
```

I don't see any documentation changes that suggest a backwards-incompatible change.  All my calls to `json_normalize` that don't use `meta` function as before.


#### Expected Output

Through 0.25.3, the output was:

```
   one  two  id
0    1    2  99
```


#### Output of ``pd.show_versions()``

From my virtualenv with pandas 1.0.0:

<details>
INSTALLED VERSIONS
------------------
commit           : None
python           : 3.7.4.final.0
python-bits      : 64
OS               : Linux
OS-release       : 4.2.0-042stab120.16
machine          : x86_64
processor        : x86_64
byteorder        : little
LC_ALL           : None
LANG             : en_GB.UTF-8
LOCALE           : en_GB.UTF-8

pandas           : 1.0.0
numpy            : 1.18.1
pytz             : 2019.3
dateutil         : 2.8.1
pip              : 19.0.3
setuptools       : 40.8.0
Cython           : None
pytest           : None
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : 4.5.0
html5lib         : None
pymysql          : None
psycopg2         : None
jinja2           : None
IPython          : 7.11.1
pandas_datareader: None
bs4              : None
bottleneck       : None
fastparquet      : None
gcsfs            : None
lxml.etree       : 4.5.0
matplotlib       : None
numexpr          : None
odfpy            : None
openpyxl         : None
pandas_gbq       : None
pyarrow          : None
pytables         : None
pytest           : None
pyxlsb           : None
s3fs             : None
scipy            : None
sqlalchemy       : 1.3.13
tables           : None
tabulate         : None
xarray           : None
xlrd             : None
xlwt             : None
xlsxwriter       : None
numba            : None
</details>

From my virtualenv with 0.25.x:

<details>
INSTALLED VERSIONS
------------------
commit           : None
python           : 3.7.4.final.0
python-bits      : 64
OS               : Linux
OS-release       : 4.2.0-042stab120.16
machine          : x86_64
processor        : x86_64
byteorder        : little
LC_ALL           : None
LANG             : en_GB.UTF-8
LOCALE           : en_GB.UTF-8

pandas           : 0.25.0
numpy            : 1.18.1
pytz             : 2019.3
dateutil         : 2.8.1
pip              : 19.0.3
setuptools       : 40.8.0
Cython           : None
pytest           : None
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : 4.4.1
html5lib         : 1.0.1
pymysql          : None
psycopg2         : None
jinja2           : None
IPython          : 7.7.0
pandas_datareader: None
bs4              : 4.8.0
bottleneck       : None
fastparquet      : None
gcsfs            : None
lxml.etree       : 4.4.1
matplotlib       : None
numexpr          : None
odfpy            : None
openpyxl         : None
pandas_gbq       : None
pyarrow          : None
pytables         : None
s3fs             : None
scipy            : None
sqlalchemy       : 1.3.7
tables           : None
xarray           : None
xlrd             : None
xlwt             : None
xlsxwriter       : None
</details>

Hints from issue discussion:
Looks buggy - investigation and PRs would certainly be welcome!
It looks like this may have been introduced with pull request #30145.
are you into fixing it? @tturocy otherwise, i could take this one

Before working on it, I would like to clarify the correct behaviour @WillAyd 
So checking #30145 it seems we should have iterable as values for records, which I do not think is correct, since `{["foo": 1]}` should also be a valid json. Then why only `Iterable` is allowed? i might miss discussions/information from #30145 

And IMO, there is sort of inconsistency now in behaviour, because if we do not specify anything, then that `id` in this case can be parsed.
@charlesdong1991 keep in mind that json normalizing and json parsing are not the same. I think your question is if `record_path` needs to point to something "iterable", in which case I believe the answer is yes. That same stipulation doesn't need to apply to `meta` though
Ah, yeah, thanks for your quick reply @WillAyd , my doubt was indeed why both have to be `iterable`, and it's clear to me now! 
Thank you for picking this up @charlesdong1991 !  
I am having the same issue: 


@charlesdong1991  Any status on this issue?  We've had to revert have to 0.25.3.  Is there a workaround?

I had the same issue, I added the code from @charlesdong1991 pull request and it is working for me
thanks for all your comments!!

The PR is underway, and the issue which causes this problem is for `meta` the values of fields are not necessarily `Iterables`, but a patch added before changed this. As the PR in pandas usually takes quite a while to get merged (due to code quality/cleanness requirement and large amount of PRs here), so it will take longer to be released, and I hope this fix patch could be included in `1.0.2`.

If it is big issue @sboagibm in your use case, as @keua said, use the current change in this PR could work around it or roll back to earlier version, and then wait for the new release. Thanks!

Commit message: add ignore type

Patch to review:
diff --git a/doc/source/whatsnew/v1.0.2.rst b/doc/source/whatsnew/v1.0.2.rst
index 462f243f14494..61d730f7a285b 100644
--- a/doc/source/whatsnew/v1.0.2.rst
+++ b/doc/source/whatsnew/v1.0.2.rst
@@ -75,6 +75,7 @@ Bug fixes
 **I/O**
 
 - Using ``pd.NA`` with :meth:`DataFrame.to_json` now correctly outputs a null value instead of an empty object (:issue:`31615`)
+- Bug in :meth:`pandas.json_normalize` when value in meta path is not iterable (:issue:`31507`)
 - Fixed pickling of ``pandas.NA``. Previously a new object was returned, which broke computations relying on ``NA`` being a singleton (:issue:`31847`)
 - Fixed bug in parquet roundtrip with nullable unsigned integer dtypes (:issue:`31896`).
 
diff --git a/pandas/io/json/_normalize.py b/pandas/io/json/_normalize.py
index 4b153d3cb69bf..2a7210fa56ff0 100644
--- a/pandas/io/json/_normalize.py
+++ b/pandas/io/json/_normalize.py
@@ -3,11 +3,12 @@
 
 from collections import defaultdict
 import copy
-from typing import Any, DefaultDict, Dict, Iterable, List, Optional, Union
+from typing import DefaultDict, Dict, Iterable, List, Optional, Union
 
 import numpy as np
 
 from pandas._libs.writers import convert_json_to_lines
+from pandas._typing import Scalar
 from pandas.util._decorators import deprecate
 
 import pandas as pd
@@ -226,14 +227,26 @@ def _json_normalize(
     Returns normalized data with columns prefixed with the given string.
     """
 
-    def _pull_field(js: Dict[str, Any], spec: Union[List, str]) -> Iterable:
+    def _pull_field(js: Dict[str, Scalar], spec: Union[List, str]) -> Scalar:
+        """Internal function to pull field"""
         result = js  # type: ignore
         if isinstance(spec, list):
             for field in spec:
-                result = result[field]
+                result = result[field]  # type: ignore
         else:
-            result = result[spec]
+            result = result[spec]  # type: ignore
+        return result
+
+    def _pull_records(js: Dict[str, Scalar], spec: Union[List, str]) -> Iterable:
+        """
+        Interal function to pull field for records, and similar to
+        _pull_field, but require to return Iterable. And will raise error
+        if has non iterable value.
+        """
+        result = _pull_field(js, spec)
 
+        # GH 31507 GH 30145, if result is not Iterable, raise TypeError if not
+        # null, otherwise return an empty list
         if not isinstance(result, Iterable):
             if pd.isnull(result):
                 result = []  # type: ignore
@@ -242,7 +255,6 @@ def _pull_field(js: Dict[str, Any], spec: Union[List, str]) -> Iterable:
                     f"{js} has non iterable value {result} for path {spec}. "
                     "Must be iterable or null."
                 )
-
         return result
 
     if isinstance(data, list) and not data:
@@ -292,7 +304,7 @@ def _recursive_extract(data, path, seen_meta, level=0):
                 _recursive_extract(obj[path[0]], path[1:], seen_meta, level=level + 1)
         else:
             for obj in data:
-                recs = _pull_field(obj, path[0])
+                recs = _pull_records(obj, path[0])
                 recs = [
                     nested_to_record(r, sep=sep, max_level=max_level)
                     if isinstance(r, dict)
diff --git a/pandas/tests/io/json/test_normalize.py b/pandas/tests/io/json/test_normalize.py
index 91b204ed41ebc..b7a9918ff46da 100644
--- a/pandas/tests/io/json/test_normalize.py
+++ b/pandas/tests/io/json/test_normalize.py
@@ -486,6 +486,16 @@ def test_non_interable_record_path_errors(self):
         with pytest.raises(TypeError, match=msg):
             json_normalize([test_input], record_path=[test_path])
 
+    def test_meta_non_iterable(self):
+        # GH 31507
+        data = """[{"id": 99, "data": [{"one": 1, "two": 2}]}]"""
+
+        result = json_normalize(json.loads(data), record_path=["data"], meta=["id"])
+        expected = DataFrame(
+            {"one": [1], "two": [2], "id": np.array([99], dtype=object)}
+        )
+        tm.assert_frame_equal(result, expected)
+
 
 class TestNestedToRecord:
     def test_flat_stays_flat(self):


Code review comments:
diff_hunk: @@ -226,14 +227,26 @@ def _json_normalize(
     Returns normalized data with columns prefixed with the given string.
     """
 
-    def _pull_field(js: Dict[str, Any], spec: Union[List, str]) -> Iterable:
+    def _pull_field(js: Dict[str, Scalar], spec: Union[List, str]) -> Scalar:
path: pandas/io/json/_normalize.py
text: @user1:
```suggestion
    def _pull_field(js: Dict[str, Any], spec: Union[List, str]) -> Union[Scalar, Iterable]:
```

diff_hunk: @@ -226,14 +227,26 @@ def _json_normalize(
     Returns normalized data with columns prefixed with the given string.
     """
 
-    def _pull_field(js: Dict[str, Any], spec: Union[List, str]) -> Iterable:
+    def _pull_field(js: Dict[str, Scalar], spec: Union[List, str]) -> Scalar:
+        """Internal function to pull field"""
         result = js  # type: ignore
         if isinstance(spec, list):
             for field in spec:
-                result = result[field]
+                result = result[field]  # type: ignore
path: pandas/io/json/_normalize.py
text: @user1:
```suggestion
                result = result[field]
```

diff_hunk: @@ -226,14 +227,26 @@ def _json_normalize(
     Returns normalized data with columns prefixed with the given string.
     """
 
-    def _pull_field(js: Dict[str, Any], spec: Union[List, str]) -> Iterable:
+    def _pull_field(js: Dict[str, Scalar], spec: Union[List, str]) -> Scalar:
+        """Internal function to pull field"""
         result = js  # type: ignore
         if isinstance(spec, list):
             for field in spec:
-                result = result[field]
+                result = result[field]  # type: ignore
         else:
-            result = result[spec]
+            result = result[spec]  # type: ignore
+        return result
+
+    def _pull_records(js: Dict[str, Scalar], spec: Union[List, str]) -> Iterable:
path: pandas/io/json/_normalize.py
text: @user1:
```suggestion
    def _pull_records(js: Dict[str, Any], spec: Union[List, str]) -> Iterable:
```

diff_hunk: @@ -226,14 +227,26 @@ def _json_normalize(
     Returns normalized data with columns prefixed with the given string.
     """
 
-    def _pull_field(js: Dict[str, Any], spec: Union[List, str]) -> Iterable:
+    def _pull_field(js: Dict[str, Scalar], spec: Union[List, str]) -> Scalar:
+        """Internal function to pull field"""
         result = js  # type: ignore
         if isinstance(spec, list):
             for field in spec:
-                result = result[field]
+                result = result[field]  # type: ignore
         else:
-            result = result[spec]
+            result = result[spec]  # type: ignore
path: pandas/io/json/_normalize.py
text: @user1:
```suggestion
            result = result[spec]
```

diff_hunk: @@ -226,14 +227,26 @@ def _json_normalize(
     Returns normalized data with columns prefixed with the given string.
     """
 
-    def _pull_field(js: Dict[str, Any], spec: Union[List, str]) -> Iterable:
+    def _pull_field(js: Dict[str, Scalar], spec: Union[List, str]) -> Scalar:
+        """Internal function to pull field"""
         result = js  # type: ignore
         if isinstance(spec, list):
             for field in spec:
-                result = result[field]
+                result = result[field]  # type: ignore
path: pandas/io/json/_normalize.py
text: @user1:
Can you advise specifically what the error is? I realize we want to get this in for 1.0.2 so not going to block, but I still think this code is suspect (not from your change per se - just a historical artifact) so I'd hate to suppress a warning about another bug that this fix could be introducing

@user1:
Took a look at this locally; I think if you revert some of the other changes here you won't need the ignore. Suggested separately

@author:
yeah, the error is:
```
error: Incompatible types in assignment (expression has type "Union[str, int, float, bool, Any, Any, Any, Any]", variable has type "Dict[str, Union[str, int, float, bool, Any, Any, Any, Any]]")
```
as said, this is because `result = result[field]`, and `result` is a `Dict` while the value of `result` is not, so there is a conflict. And having `Any` instead of `Scalar` could fix the issue