Getting error to normalize nested list in Python

Question

I have a nested list with dictionary. The following is just first element of the list

    {'id': 'abcde',
     'authorization': None,
     'operation_type': 'xx',
     'method': 'card',
     'transaction_type': 'asd',
     'card': {'type': 'dd',
      'brand': 'vv',
      'address': {'line1': 'xxxxxxx',
       'line2': '',
       'line3': '',
       'state': 'xx',
       'city': 'xxx',
       'postal_code': '12345',
       'country_code': 'xx'},
      'card_number': '123456XXXXXX7890',
      'holder_name': 'name user,
      'expiration_year': '20',
      'expiration_month': '02',
      'allows_charges': True,
      'allows_payouts': True,
      'bank_name': 'abc bank',
      'bank_code': '000'},
     'status': 'fgh',
     'conciliated': True,
     'creation_date': '2018-09-23T23:58:17-05:00',
     'operation_date': '2018-09-23T23:58:17-05:00',
     'description': 'asdmdefdsa',
     'error_message': 'sdaskjflj',
     'order_id': 'ashdgjasdfhk',
     'amount': 418.0,
     'customer': {'name': 'abc',
      'last_name': 'xyz',
      'email': '[email protected]',
      'phone_number': '12345678',
      'address': None,
      'creation_date': '2018-09-23T23:58:18-05:00',
      'external_id': None,
      'clabe': None},
     'fee': {'amount': 0.56, 'tax': 0.91, 'currency': 'XXX'},
     'currency': 'XXX'},
{'id': 'abcde',
     'authorization': None,
     'operation_type': 'xx',
     'method': 'card',
     'transaction_type': 'asd',
     'card': {'type': 'dd',
      'brand': 'vv',
      'address': {'line1': 'xxxxxxx',
       'line2': '',
       'line3': '',
       'state': 'xx',
       'city': 'xxx',
       'postal_code': '12345',
       'country_code': 'xx'},
      'card_number': '123456XXXXXX7890',
      'holder_name': 'name user,
      'expiration_year': '20',
      'expiration_month': '02',
      'allows_charges': True,
      'allows_payouts': True,
      'bank_name': 'abc bank',
      'bank_code': '000'},
     'status': 'fgh',
     'conciliated': True,
     'creation_date': '2018-09-23T23:58:17-05:00',
     'operation_date': '2018-09-23T23:58:17-05:00',
     'description': 'asdmdefdsa',
     'error_message': 'sdaskjflj',
     'order_id': 'ashdgjasdfhk',
     'amount': 418.0,
     'customer': {'name': 'abc',
      'last_name': 'xyz',
      'email': '[email protected]',
      'phone_number': '12345678',
      'address': None,
      'creation_date': '2018-09-23T23:58:18-05:00',
      'external_id': None,
      'clabe': None},
     'fee': {'amount': 0.56, 'tax': 0.91, 'currency': 'XXX'},
     'currency': 'XXX'}

I want to normalize the data to dataframe. I wrote the code as: json_normalize(d). But I am getting following error:

--------------------------------------------------------------------------- KeyError Traceback (most recent call last) in () ----> 1 df = json_normalize(data)

/anaconda3/lib/python3.6/site-packages/pandas/io/json/normalize.py in json_normalize(data, record_path, meta, meta_prefix, record_prefix, errors, sep) 201 # TODO: handle record value which are lists, at least error 202 # reasonably --> 203 data = nested_to_record(data, sep=sep) 204 return DataFrame(data) 205 elif not isinstance(record_path, list):

/anaconda3/lib/python3.6/site-packages/pandas/io/json/normalize.py in nested_to_record(ds, prefix, sep, level) 86 else: 87 v = new_d.pop(k) ---> 88 new_d.update(nested_to_record(v, newkey, sep, level + 1)) 89 new_ds.append(new_d) 90

/anaconda3/lib/python3.6/site-packages/pandas/io/json/normalize.py in nested_to_record(ds, prefix, sep, level) 82 new_d[newkey] = v 83 if v is None: # pop the key if the value is None ---> 84 new_d.pop(k) 85 continue 86 else:

KeyError: 'address'

I understood that because address in None, the code is giving me error. But I don't know how to fix it. Any help in this regard will be highly appreciated. Thanks in advance. (Please note that the data is dummy data)

Answer 1

The dictionary is badly formatted. First of all, you have lines like the following:

'holder_name': 'name user,

where the value 'name user is not a valid string, since it's not enclosed by a single quote character on the right.

Second, in your code you have two elements of a list, that is, two dictionaries, each of them starting with {'id': ..., as opposed to a single element as claimed.

After fixing the values of 'holder_name in both dictionaries and making it a two-member list, you can proceed with using json_normalize and you would get an output like the following (with printed in stdout):

   amount authorization card.address.city card.address.country_code    ...        operation_type      order_id status transaction_type 0  
418.0          None               xxx                        xx       ...                    xx  ashdgjasdfhk    fgh              asd 1  
418.0          None               xxx                        xx       ...                    xx  ashdgjasdfhk    fgh              asd

[2 rows x 42 columns]