Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# ---------------------------------------------------------------------
2# JSON normalization routines
3from __future__ import annotations
5from collections import (
6 abc,
7 defaultdict,
8)
9import copy
10from typing import (
11 TYPE_CHECKING,
12 Any,
13 overload,
14)
16import numpy as np
18from pandas._libs.writers import convert_json_to_lines
19from pandas.util._decorators import set_module
21from pandas.core.dtypes.common import is_scalar
23import pandas as pd
24from pandas import (
25 DataFrame,
26 Series,
27)
29if TYPE_CHECKING:
30 from collections.abc import Iterable
32 from pandas._typing import (
33 IgnoreRaise,
34 Scalar,
35 )
38def convert_to_line_delimits(s: str) -> str:
39 """
40 Helper function that converts JSON lists to line delimited JSON.
41 """
42 # Determine we have a JSON list to turn to lines otherwise just return the
43 # json object, only lists can
44 if not s[0] == "[" and s[-1] == "]":
45 return s
46 s = s[1:-1]
48 return convert_json_to_lines(s)
51@overload
52def nested_to_record(
53 ds: dict,
54 prefix: str = ...,
55 sep: str = ...,
56 level: int = ...,
57 max_level: int | None = ...,
58) -> dict[str, Any]: ...
61@overload
62def nested_to_record(
63 ds: list[dict],
64 prefix: str = ...,
65 sep: str = ...,
66 level: int = ...,
67 max_level: int | None = ...,
68) -> list[dict[str, Any]]: ...
71def nested_to_record(
72 ds: dict | list[dict],
73 prefix: str = "",
74 sep: str = ".",
75 level: int = 0,
76 max_level: int | None = None,
77) -> dict[str, Any] | list[dict[str, Any]]:
78 """
79 A simplified json_normalize
81 Converts a nested dict into a flat dict ("record"), unlike json_normalize,
82 it does not attempt to extract a subset of the data.
84 Parameters
85 ----------
86 ds : dict or list of dicts
87 prefix: the prefix, optional, default: ""
88 sep : str, default '.'
89 Nested records will generate names separated by sep,
90 e.g., for sep='.', { 'foo' : { 'bar' : 0 } } -> foo.bar
91 level: int, optional, default: 0
92 The number of levels in the json string.
94 max_level: int, optional, default: None
95 The max depth to normalize.
97 Returns
98 -------
99 d - dict or list of dicts, matching `ds`
101 Examples
102 --------
103 >>> nested_to_record(
104 ... dict(flat1=1, dict1=dict(c=1, d=2), nested=dict(e=dict(c=1, d=2), d=2))
105 ... )
106 {\
107'flat1': 1, \
108'dict1.c': 1, \
109'dict1.d': 2, \
110'nested.e.c': 1, \
111'nested.e.d': 2, \
112'nested.d': 2\
113}
114 """
115 singleton = False
116 if isinstance(ds, dict):
117 ds = [ds]
118 singleton = True
119 new_ds = []
120 for d in ds:
121 new_d = copy.deepcopy(d)
122 for k, v in d.items():
123 # each key gets renamed with prefix
124 if not isinstance(k, str):
125 k = str(k)
126 if level == 0:
127 newkey = k
128 else:
129 newkey = prefix + sep + k
131 # flatten if type is dict and
132 # current dict level < maximum level provided and
133 # only dicts gets recurse-flattened
134 # only at level>1 do we rename the rest of the keys
135 if not isinstance(v, dict) or (
136 max_level is not None and level >= max_level
137 ):
138 if level != 0: # so we skip copying for top level, common case
139 v = new_d.pop(k)
140 new_d[newkey] = v
141 continue
143 v = new_d.pop(k)
144 new_d.update(nested_to_record(v, newkey, sep, level + 1, max_level))
145 new_ds.append(new_d)
147 if singleton:
148 return new_ds[0]
149 return new_ds
152def _normalize_json(
153 data: Any,
154 key_string: str,
155 normalized_dict: dict[str, Any],
156 separator: str,
157) -> dict[str, Any]:
158 """
159 Main recursive function
160 Designed for the most basic use case of pd.json_normalize(data)
161 intended as a performance improvement, see #15621
163 Parameters
164 ----------
165 data : Any
166 Type dependent on types contained within nested Json
167 key_string : str
168 New key (with separator(s) in) for data
169 normalized_dict : dict
170 The new normalized/flattened Json dict
171 separator : str, default '.'
172 Nested records will generate names separated by sep,
173 e.g., for sep='.', { 'foo' : { 'bar' : 0 } } -> foo.bar
174 """
175 if isinstance(data, dict):
176 for key, value in data.items():
177 new_key = f"{key_string}{separator}{key}"
179 if not key_string:
180 new_key = new_key.removeprefix(separator)
182 _normalize_json(
183 data=value,
184 key_string=new_key,
185 normalized_dict=normalized_dict,
186 separator=separator,
187 )
188 else:
189 normalized_dict[key_string] = data
190 return normalized_dict
193def _normalize_json_ordered(data: dict[str, Any], separator: str) -> dict[str, Any]:
194 """
195 Order the top level keys and then recursively go to depth
197 Parameters
198 ----------
199 data : dict or list of dicts
200 separator : str, default '.'
201 Nested records will generate names separated by sep,
202 e.g., for sep='.', { 'foo' : { 'bar' : 0 } } -> foo.bar
204 Returns
205 -------
206 dict or list of dicts, matching `normalized_json_object`
207 """
208 top_dict_ = {k: v for k, v in data.items() if not isinstance(v, dict)}
209 nested_dict_ = _normalize_json(
210 data={k: v for k, v in data.items() if isinstance(v, dict)},
211 key_string="",
212 normalized_dict={},
213 separator=separator,
214 )
215 return {**top_dict_, **nested_dict_}
218def _simple_json_normalize(
219 ds: dict | list[dict],
220 sep: str = ".",
221) -> dict | list[dict] | Any:
222 """
223 An optimized basic json_normalize
225 Converts a nested dict into a flat dict ("record"), unlike
226 json_normalize and nested_to_record it doesn't do anything clever.
227 But for the most basic use cases it enhances performance.
228 E.g. pd.json_normalize(data)
230 Parameters
231 ----------
232 ds : dict or list of dicts
233 sep : str, default '.'
234 Nested records will generate names separated by sep,
235 e.g., for sep='.', { 'foo' : { 'bar' : 0 } } -> foo.bar
237 Returns
238 -------
239 frame : DataFrame
240 d - dict or list of dicts, matching `normalized_json_object`
242 Examples
243 --------
244 >>> _simple_json_normalize(
245 ... {
246 ... "flat1": 1,
247 ... "dict1": {"c": 1, "d": 2},
248 ... "nested": {"e": {"c": 1, "d": 2}, "d": 2},
249 ... }
250 ... )
251 {\
252'flat1': 1, \
253'dict1.c': 1, \
254'dict1.d': 2, \
255'nested.e.c': 1, \
256'nested.e.d': 2, \
257'nested.d': 2\
258}
260 """
261 normalized_json_object = {}
262 # expect a dictionary, as most jsons are. However, lists are perfectly valid
263 if isinstance(ds, dict):
264 normalized_json_object = _normalize_json_ordered(data=ds, separator=sep)
265 elif isinstance(ds, list):
266 normalized_json_list = [_simple_json_normalize(row, sep=sep) for row in ds]
267 return normalized_json_list
268 return normalized_json_object
271def _validate_meta(meta: str | list[str | list[str]] | None) -> None:
272 """
273 Validate that meta parameter contains only strings or lists of strings.
274 Parameters
275 ----------
276 meta : str or list of str or list of list of str or None
277 The meta parameter to validate.
278 Raises
279 ------
280 TypeError
281 If meta contains elements that are not strings or lists of strings.
282 """
283 if meta is None:
284 return
285 if isinstance(meta, str):
286 return
287 for item in meta:
288 if isinstance(item, list):
289 for subitem in item:
290 if not isinstance(subitem, str):
291 raise TypeError(
292 "All elements in nested meta paths must be strings. "
293 f"Found {type(subitem).__name__}: {subitem!r}"
294 )
295 elif not isinstance(item, str):
296 raise TypeError(
297 "All elements in 'meta' must be strings or lists of strings. "
298 f"Found {type(item).__name__}: {item!r}"
299 )
302@set_module("pandas")
303def json_normalize(
304 data: dict | list[dict] | Series,
305 record_path: str | list | None = None,
306 meta: str | list[str | list[str]] | None = None,
307 meta_prefix: str | None = None,
308 record_prefix: str | None = None,
309 errors: IgnoreRaise = "raise",
310 sep: str = ".",
311 max_level: int | None = None,
312) -> DataFrame:
313 """
314 Normalize semi-structured JSON data into a flat table.
316 This method is designed to transform semi-structured JSON data, such as nested
317 dictionaries or lists, into a flat table. This is particularly useful when
318 handling JSON-like data structures that contain deeply nested fields.
320 Parameters
321 ----------
322 data : dict, list of dicts, or Series of dicts
323 Unserialized JSON objects.
324 record_path : str or list of str, default None
325 Path in each object to list of records. If not passed, data will be
326 assumed to be an array of records.
327 meta : list of paths (str or list of str), default None
328 Fields to use as metadata for each record in resulting table.
329 meta_prefix : str, default None
330 If not None, prefix meta fields with this string.
331 record_prefix : str, default None
332 If not None, prefix record fields with this string.
333 errors : {'raise', 'ignore'}, default 'raise'
334 Configures error handling.
336 * 'ignore' : will ignore KeyError if keys listed in meta are not
337 always present.
338 * 'raise' : will raise KeyError if keys listed in meta are not
339 always present.
340 sep : str, default '.'
341 Nested records will generate names separated by sep.
342 e.g., for sep='.', {'foo': {'bar': 0}} -> foo.bar.
343 max_level : int, default None
344 Max number of levels(depth of dict) to normalize.
345 if None, normalizes all levels.
347 Returns
348 -------
349 DataFrame
350 The normalized data, represented as a pandas DataFrame.
352 See Also
353 --------
354 DataFrame : Two-dimensional, size-mutable, potentially heterogeneous tabular data.
355 Series : One-dimensional ndarray with axis labels (including time series).
357 Examples
358 --------
359 >>> data = [
360 ... {"id": 1, "name": {"first": "Coleen", "last": "Volk"}},
361 ... {"name": {"given": "Mark", "family": "Regner"}},
362 ... {"id": 2, "name": "Faye Raker"},
363 ... ]
364 >>> pd.json_normalize(data)
365 id name.first name.last name.given name.family name
366 0 1.0 Coleen Volk NaN NaN NaN
367 1 NaN NaN NaN Mark Regner NaN
368 2 2.0 NaN NaN NaN NaN Faye Raker
370 >>> data = [
371 ... {
372 ... "id": 1,
373 ... "name": "Cole Volk",
374 ... "fitness": {"height": 130, "weight": 60},
375 ... },
376 ... {"name": "Mark Reg", "fitness": {"height": 130, "weight": 60}},
377 ... {
378 ... "id": 2,
379 ... "name": "Faye Raker",
380 ... "fitness": {"height": 130, "weight": 60},
381 ... },
382 ... ]
383 >>> pd.json_normalize(data, max_level=0)
384 id name fitness
385 0 1.0 Cole Volk {'height': 130, 'weight': 60}
386 1 NaN Mark Reg {'height': 130, 'weight': 60}
387 2 2.0 Faye Raker {'height': 130, 'weight': 60}
389 Normalizes nested data up to level 1.
391 >>> data = [
392 ... {
393 ... "id": 1,
394 ... "name": "Cole Volk",
395 ... "fitness": {"height": 130, "weight": 60},
396 ... },
397 ... {"name": "Mark Reg", "fitness": {"height": 130, "weight": 60}},
398 ... {
399 ... "id": 2,
400 ... "name": "Faye Raker",
401 ... "fitness": {"height": 130, "weight": 60},
402 ... },
403 ... ]
404 >>> pd.json_normalize(data, max_level=1)
405 id name fitness.height fitness.weight
406 0 1.0 Cole Volk 130 60
407 1 NaN Mark Reg 130 60
408 2 2.0 Faye Raker 130 60
410 >>> data = [
411 ... {
412 ... "id": 1,
413 ... "name": "Cole Volk",
414 ... "fitness": {"height": 130, "weight": 60},
415 ... },
416 ... {"name": "Mark Reg", "fitness": {"height": 130, "weight": 60}},
417 ... {
418 ... "id": 2,
419 ... "name": "Faye Raker",
420 ... "fitness": {"height": 130, "weight": 60},
421 ... },
422 ... ]
423 >>> series = pd.Series(data, index=pd.Index(["a", "b", "c"]))
424 >>> pd.json_normalize(series)
425 id name fitness.height fitness.weight
426 a 1.0 Cole Volk 130 60
427 b NaN Mark Reg 130 60
428 c 2.0 Faye Raker 130 60
430 >>> data = [
431 ... {
432 ... "state": "Florida",
433 ... "shortname": "FL",
434 ... "info": {"governor": "Rick Scott"},
435 ... "counties": [
436 ... {"name": "Dade", "population": 12345},
437 ... {"name": "Broward", "population": 40000},
438 ... {"name": "Palm Beach", "population": 60000},
439 ... ],
440 ... },
441 ... {
442 ... "state": "Ohio",
443 ... "shortname": "OH",
444 ... "info": {"governor": "John Kasich"},
445 ... "counties": [
446 ... {"name": "Summit", "population": 1234},
447 ... {"name": "Cuyahoga", "population": 1337},
448 ... ],
449 ... },
450 ... ]
451 >>> result = pd.json_normalize(
452 ... data, "counties", ["state", "shortname", ["info", "governor"]]
453 ... )
454 >>> result
455 name population state shortname info.governor
456 0 Dade 12345 Florida FL Rick Scott
457 1 Broward 40000 Florida FL Rick Scott
458 2 Palm Beach 60000 Florida FL Rick Scott
459 3 Summit 1234 Ohio OH John Kasich
460 4 Cuyahoga 1337 Ohio OH John Kasich
462 >>> data = {"A": [1, 2]}
463 >>> pd.json_normalize(data, "A", record_prefix="Prefix.")
464 Prefix.0
465 0 1
466 1 2
468 Returns normalized data with columns prefixed with the given string.
470 >>> data = [
471 ... {
472 ... "state": "Florida",
473 ... "shortname": "FL",
474 ... "info": {"governor": "Rick Scott"},
475 ... "counties": [{"name": "Dade", "population": 12345}],
476 ... },
477 ... ]
478 >>> pd.json_normalize(
479 ... data,
480 ... "counties",
481 ... ["state", "shortname", ["info", "governor"]],
482 ... meta_prefix="meta.",
483 ... )
484 name population meta.state meta.shortname meta.info.governor
485 0 Dade 12345 Florida FL Rick Scott
487 Meta fields are prefixed with the given string.
488 """
489 _validate_meta(meta)
491 def _pull_field(
492 js: dict[str, Any], spec: list | str, extract_record: bool = False
493 ) -> Scalar | Iterable:
494 """Internal function to pull field"""
495 result = js
496 try:
497 if isinstance(spec, list):
498 for field in spec:
499 if result is None:
500 raise KeyError(field)
501 result = result[field]
502 else:
503 result = result[spec]
504 except KeyError as e:
505 if extract_record:
506 raise KeyError(
507 f"Key {e} not found. If specifying a record_path, all elements of "
508 f"data should have the path."
509 ) from e
510 if errors == "ignore":
511 return np.nan
512 else:
513 raise KeyError(
514 f"Key {e} not found. To replace missing values of {e} with "
515 f"np.nan, pass in errors='ignore'"
516 ) from e
518 return result
520 def _pull_records(js: dict[str, Any], spec: list | str) -> list:
521 """
522 Internal function to pull field for records, and similar to
523 _pull_field, but require to return list. And will raise error
524 if has non iterable value.
525 """
526 result = _pull_field(js, spec, extract_record=True)
528 # GH 31507 GH 30145, GH 26284 if result is not list, raise TypeError if not
529 # null, otherwise return an empty list
530 if not isinstance(result, list):
531 if pd.isnull(result):
532 result = []
533 else:
534 raise TypeError(
535 f"Path must contain list or null, "
536 f"but got {type(result).__name__} at {spec!r}"
537 )
538 return result
540 if isinstance(data, Series):
541 index = data.index
542 else:
543 index = None
545 if isinstance(data, list) and not data:
546 return DataFrame()
547 elif isinstance(data, dict):
548 # A bit of a hackjob
549 data = [data]
550 elif isinstance(data, abc.Iterable) and not isinstance(data, str):
551 # GH35923 Fix pd.json_normalize to not skip the first element of a
552 # generator input
553 data = list(data)
554 for i, item in enumerate(data):
555 if isinstance(item, dict):
556 continue
557 if is_scalar(item) and pd.isna(item):
558 data[i] = {}
559 else:
560 msg = (
561 "All items in data must be of type dict or NA-like, "
562 f"found {type(item).__name__}"
563 )
564 raise TypeError(msg)
565 else:
566 raise NotImplementedError
568 # check to see if a simple recursive function is possible to
569 # improve performance (see #15621) but only for cases such
570 # as pd.Dataframe(data) or pd.Dataframe(data, sep)
571 if (
572 record_path is None
573 and meta is None
574 and meta_prefix is None
575 and record_prefix is None
576 and max_level is None
577 ):
578 return DataFrame(_simple_json_normalize(data, sep=sep), index=index)
580 if record_path is None:
581 if any([isinstance(x, dict) for x in y.values()] for y in data):
582 # naive normalization, this is idempotent for flat records
583 # and potentially will inflate the data considerably for
584 # deeply nested structures:
585 # {VeryLong: { b: 1,c:2}} -> {VeryLong.b:1 ,VeryLong.c:@}
586 #
587 # TODO: handle record value which are lists, at least error
588 # reasonably
589 data = nested_to_record(data, sep=sep, max_level=max_level)
590 result = DataFrame(data, index=index)
591 if record_prefix is not None:
592 result = result.rename(columns=lambda x: f"{record_prefix}{x}")
593 return result
594 elif not isinstance(record_path, list):
595 record_path = [record_path]
597 if meta is None:
598 meta = []
599 elif not isinstance(meta, list):
600 meta = [meta]
602 _meta = [m if isinstance(m, list) else [m] for m in meta]
604 # Disastrously inefficient for now
605 records: list = []
606 lengths = []
608 meta_vals = defaultdict(list)
609 meta_keys = [sep.join(val) for val in _meta]
611 def _recursive_extract(data, path, seen_meta, level: int = 0) -> None:
612 if isinstance(data, dict):
613 data = [data]
614 if len(path) > 1:
615 for obj in data:
616 for val, key in zip(_meta, meta_keys, strict=True):
617 if level + 1 == len(val):
618 seen_meta[key] = _pull_field(obj, val[-1])
620 _recursive_extract(obj[path[0]], path[1:], seen_meta, level=level + 1)
621 else:
622 for obj in data:
623 recs = _pull_records(obj, path[0])
624 recs = [
625 nested_to_record(r, sep=sep, max_level=max_level)
626 if isinstance(r, dict)
627 else r
628 for r in recs
629 ]
631 # For repeating the metadata later
632 lengths.append(len(recs))
633 for val, key in zip(_meta, meta_keys, strict=True):
634 if level + 1 > len(val):
635 meta_val = seen_meta[key]
636 else:
637 meta_val = _pull_field(obj, val[level:])
638 meta_vals[key].append(meta_val)
639 records.extend(recs)
641 _recursive_extract(data, record_path, {}, level=0)
643 result = DataFrame(records)
645 if record_prefix is not None:
646 result = result.rename(columns=lambda x: f"{record_prefix}{x}")
648 # Data types, a problem
649 for k, v in meta_vals.items():
650 if meta_prefix is not None:
651 k = meta_prefix + k
653 if k in result:
654 raise ValueError(
655 f"Conflicting metadata name {k}, need distinguishing prefix "
656 )
657 # GH 37782
659 values = np.array(v, dtype=object)
661 if values.ndim > 1:
662 # GH 37782
663 values = np.empty((len(v),), dtype=object)
664 for i, val in enumerate(v):
665 values[i] = val
667 result[k] = values.repeat(lengths)
668 if index is not None:
669 result.index = index.repeat(lengths)
670 return result