json_normalize throws TypeError with list record_path

[vuminhle]

Code Sample, a copy-pastable example if possible

from pandas.io.json import json_normalize

json_normalize({'A': {'B': [{'X': 1, 'Y': 2}, {'X': 3, 'Y': 4}]}}, ['A', 'B'])

Note: change from json_normalize({'A': {'B': [1, 2]}}, ['A', 'B']) to distinguish from #21608

Problem description

The above code throws a TypeError:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Python36\lib\site-packages\pandas\io\json\normalize.py", line 257, in json_normalize
    _recursive_extract(data, record_path, {}, level=0)
  File "C:\Python36\lib\site-packages\pandas\io\json\normalize.py", line 230, in _recursive_extract
    seen_meta, level=level + 1)
  File "C:\Python36\lib\site-packages\pandas\io\json\normalize.py", line 233, in _recursive_extract
    recs = _pull_field(obj, path[0])
  File "C:\Python36\lib\site-packages\pandas\io\json\normalize.py", line 180, in _pull_field
    result = result[spec]
TypeError: string indices must be integers

Expected Output

	X	Y
0	1	2
1	3	4

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit: None python: 3.6.4.final.0 python-bits: 64 OS: Windows OS-release: 10 machine: AMD64 processor: Intel64 Family 6 Model 62 Stepping 4, GenuineIntel byteorder: little LC_ALL: None LANG: None LOCALE: None.None

pandas: 0.23.1
pytest: 3.6.1
pip: 10.0.1
setuptools: 28.8.0
Cython: None
numpy: 1.14.2
scipy: None
pyarrow: None
xarray: None
IPython: 6.3.1
sphinx: None
patsy: None
dateutil: 2.7.2
pytz: 2018.4
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: 4.6.0
html5lib: 1.0.1
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[vuminhle]

Pull request here #21607

[gfyoung]

cc @jreback

Uncertain that we want to support this. Thoughts?

[vuminhle]

@gfyoung: Note that I created a minimal test case to trigger the bug.
The same bug triggers when the array contains objects.

>>> json_normalize({'A': {'B': [{'X': 1, 'Y': 2}, {'X': 3, 'Y': 4}]}}, ['A', 'B'])
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Python36\lib\site-packages\pandas\io\json\normalize.py", line 257, in json_normalize
    _recursive_extract(data, record_path, {}, level=0)
  File "C:\Python36\lib\site-packages\pandas\io\json\normalize.py", line 230, in _recursive_extract
    seen_meta, level=level + 1)
  File "C:\Python36\lib\site-packages\pandas\io\json\normalize.py", line 233, in _recursive_extract
    recs = _pull_field(obj, path[0])
  File "C:\Python36\lib\site-packages\pandas\io\json\normalize.py", line 180, in _pull_field
    result = result[spec]
TypeError: string indices must be integers

[jreback]

this is not legitimate input to json_normalize. simiar to #21608 I would raise an intelligent message here.

[vuminhle]

Why isn't it legitimate?

• It is a dict; the output is a data frame. It is different from that in json_normalize throws AttributeError when calling with array of values #21608 (which is a list of values).
• Is [{'X': 1, 'Y': 2}, {'X': 3, 'Y': 4}] a legitimate JSON to json_normalize? It's strange if it is but the above json is not.
• I saw this JSON pattern frequently. Here is an example http://opensource.adobe.com/Spry/samples/data_region/JSONDataSetSample.html#Example8

[jreback]

duplicate of #22804