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