How do I flat a Nested Json file in Python?

I have a nested Json file with arrays.
I want to flat it so there won't be nested Jsons.

For example:

Code for Json:
https://jsonblob.com/4b255e51-7e9f-11e8-b89c-37203846213e

This Json has sub-Json and also array that contains Json.

The source is:

Output should be:

If there are arrays that contains a single Json they can be ignored. But if they have also sub-json they should be treated as above. Basically from my point of view each array is like a separated Json file.

I know that flating the Json can be done as:

from pandas.io.json import json_normalize
json_normalize(sample_object)


But this won't work with arrays.

Any idea how to make this work?

EDIT:

This is how arrays should be handled:
source:

Output:

Which means first Json in array stays as is {0}, {1} etc... but the sub-jsons are flatted. There are no columns of attributes_0_value ! Basically convert it to array with a single Json. No nesting (unless there is another array).

attributes_0_value

2 Answers
2

Try using this:

import pandas as pd
import json

response = {u'total': 1245, u'limit': 2, u'results': [{u'customer': {u'lastName': u'rtyrtyrt', u'userAccountId': None, u'id': 637, u'firstName': u'rtyrtyrty', u'email': u'ddfgdfg@dfsdfgdfg.ggg'}, u'shippingAddress': {u'city': u'rtyrtyrtyrty', u'vatNumber': None, u'firstName': u'rtyrtyrty', u'companyName': None, u'country': {u'defaultCulture': {u'languageName': u'English', u'code': u'en-GB', u'id': 2, u'name': u'English'}, u'onlineStoreActive': True, u'currency': {u'symbol': u'xa3', u'code': u'GBP', u'id': 2, u'currencyCulture': u'en-GB', u'numericCode': 826}, u'locale': None, u'isO2LetterCode': u'GB', u'vatPercentage': 20.0, u'continent': u'Europe ', u'isoNumericCode': u'826', u'invariantName': u'UNITED KINGDOM', u'id': 2, u'isO3LetterCode': u'GBR'}, u'stateProvince': None, u'lastName': u'rtyrtyrt', u'zipCode': u'5464556', u'email': u'ddfgdfg@dfsdfgdfg.ggg', u'addressLine2': None, u'addressLine1': u'tyrtyrty', u'phoneNumber': u'45644443456456546', u'addressName': None, u'id': 861}, u'orderDateUtc': u'0001-01-01 00:00', u'shoppingCardId': 0, u'paymentType': {u'code': u'SafeCharge', u'invariantName': u'Credit Card', u'id': 50}, u'orderNumber': u'0100000845', u'giftMessage': u'', u'storeId': 1, u'shippingService': {u'deletedOn': None, u'code': u'ROYALSTD', u'courier': None, u'updatedOn': u'2018-01-24 09:23', u'locale': None, u'createdOn': u'2018-01-24 09:23', u'storeId': 1, u'sortOrder': 1, u'invariantName': u'Royal Mail Standard', u'id': 1}, u'referenceOrderNumber': u'', u'totals': {u'shippingChargesNet': 3.95, u'orderLevelDiscount': 0.0, u'grandTotal': 8.95, u'vatPercentage': 20.0, u'shippingChargesDiscount': 0.0, u'shippingCharges': 3.95, u'units': 1, u'salesTaxPerc': 0.0, u'subTotal': 5.0, u'salesTax': 1.4916666666666667}, u'currency': {u'symbol': u'xa3', u'code': u'GBP', u'id': 2, u'currencyCulture': u'en-GB', u'numericCode': 826}, u'status': {u'invariantName': u'Waiting PackingList', u'id': 4, u'name': None}, u'billingAddress': {u'city': u'rtyrtyrtyrty', u'vatNumber': None, u'firstName': u'rtyrtyrty', u'companyName': None, u'country': {u'defaultCulture': {u'languageName': u'English', u'code': u'en-GB', u'id': 2, u'name': u'English'}, u'onlineStoreActive': True, u'currency': {u'symbol': u'xa3', u'code': u'GBP', u'id': 2, u'currencyCulture': u'en-GB', u'numericCode': 826}, u'locale': None, u'isO2LetterCode': u'GB', u'vatPercentage': 20.0, u'continent': u'Europe ', u'isoNumericCode': u'826', u'invariantName': u'UNITED KINGDOM', u'id': 2, u'isO3LetterCode': u'GBR'}, u'stateProvince': None, u'lastName': u'rtyrtyrt', u'zipCode': u'5464556', u'email': u'ddfgdfg@dfsdfgdfg.ggg', u'addressLine2': None, u'addressLine1': u'tyrtyrty', u'phoneNumber': u'456456456546', u'addressName': None, u'id': 861}, u'items': [{u'orderId': 844, u'discountEach': 0.0, u'cancellationId': 0, u'orderedQty': 1, u'giftMessage': u'', u'orderLevelDiscountEach': 0.0, u'historicalCategories': , u'giftFrom': u'', u'netShippingChargesEach': 3.95, u'promotionItemIds': , u'variantId': 11282, u'attributes': [{u'value': u'', u'key': u'ProductSeason'}], u'priceEach': 5.0, u'isGift': False, u'id': 939, u'giftTo': u''}], u'attributes': [{u'value': u'2', u'key': u'CustomerCultureId'}, {u'value': u'185.13.248.67', u'key': u'IpAddress'}, {u'value': u'UA', u'key': u'IpCountryCode'}, {u'value': u'OLS', u'key': u'OrderSource'}, {u'value': u'111790', u'key': u'SafeCharge_AuthCode'}, {u'value': u'UQBzAGQAaAB3ADgAMgB0AE4AagBHADUAegBpAHMAIwA7AC4ANgA3AFEAXwBMAGAAKwAqAHIAVgBGAEcAKQBFAD0ASQA8AC4ATgA0AD8ANQA+AFAAMwA=', u'key': u'SafeCharge_Token'}, {u'value': u'1512424599', u'key': u'SafeCharge_TransactionId'}, {u'value': u'1', u'key': u'StoreId'}], u'isGift': False, u'id': 844}, {u'customer': {u'lastName': u'dfgdfg', u'userAccountId': None, u'id': 638, u'firstName': u'dfgdfg', u'email': u'hfghfgh@dfdfg.fdg'}, u'shippingAddress': {u'city': u'fghfghhf', u'vatNumber': None, u'firstName': u'dfgdfg', u'companyName': None, u'country': {u'defaultCulture': {u'languageName': u'English', u'code': u'en-GB', u'id': 2, u'name': u'English'}, u'onlineStoreActive': True, u'currency': {u'symbol': u'xa3', u'code': u'GBP', u'id': 2, u'currencyCulture': u'en-GB', u'numericCode': 826}, u'locale': None, u'isO2LetterCode': u'GB', u'vatPercentage': 20.0, u'continent': u'Europe ', u'isoNumericCode': u'826', u'invariantName': u'UNITED KINGDOM', u'id': 2, u'isO3LetterCode': u'GBR'}, u'stateProvince': None, u'lastName': u'dfgdfg', u'zipCode': u'4564566', u'email': u'hfghfgh@dfdfg.fdg', u'addressLine2': None, u'addressLine1': u'fghfghfgh', u'phoneNumber': u'567567567', u'addressName': None, u'id': 862}, u'orderDateUtc': u'0001-01-01 00:00', u'shoppingCardId': 0, u'paymentType': {u'code': u'SafeCharge', u'invariantName': u'Credit Card', u'id': 50}, u'orderNumber': u'0100000846', u'giftMessage': u'', u'storeId': 1, u'shippingService': {u'deletedOn': None, u'code': u'ROYALSTD', u'courier': None, u'updatedOn': u'2018-01-24 09:23', u'locale': None, u'createdOn': u'2018-01-24 09:23', u'storeId': 1, u'sortOrder': 1, u'invariantName': u'Royal Mail Standard', u'id': 1}, u'referenceOrderNumber': u'', u'totals': {u'shippingChargesNet': 3.95, u'orderLevelDiscount': 0.0, u'grandTotal': 8.95, u'vatPercentage': 20.0, u'shippingChargesDiscount': 0.0, u'shippingCharges': 3.95, u'units': 1, u'salesTaxPerc': 0.0, u'subTotal': 5.0, u'salesTax': 1.4916666666666667}, u'currency': {u'symbol': u'xa3', u'code': u'GBP', u'id': 2, u'currencyCulture': u'en-GB', u'numericCode': 826}, u'status': {u'invariantName': u'Shipped', u'id': 6, u'name': None}, u'billingAddress': {u'city': u'fghfghhf', u'vatNumber': None, u'firstName': u'dfgdfg', u'companyName': None, u'country': {u'defaultCulture': {u'languageName': u'English', u'code': u'en-GB', u'id': 2, u'name': u'English'}, u'onlineStoreActive': True, u'currency': {u'symbol': u'xa3', u'code': u'GBP', u'id': 2, u'currencyCulture': u'en-GB', u'numericCode': 826}, u'locale': None, u'isO2LetterCode': u'GB', u'vatPercentage': 20.0, u'continent': u'Europe ', u'isoNumericCode': u'826', u'invariantName': u'UNITED KINGDOM', u'id': 2, u'isO3LetterCode': u'GBR'}, u'stateProvince': None, u'lastName': u'dfgdfg', u'zipCode': u'4563334566', u'email': u'hfghfgh@dfdfg.fdg', u'addressLine2': None, u'addressLine1': u'fghfghfgh', u'phoneNumber': u'567567567', u'addressName': None, u'id': 862}, u'items': [{u'orderId': 845, u'discountEach': 0.0, u'cancellationId': 0, u'orderedQty': 1, u'giftMessage': u'', u'orderLevelDiscountEach': 0.0, u'historicalCategories': , u'giftFrom': u'', u'netShippingChargesEach': 3.95, u'promotionItemIds': , u'variantId': 11282, u'attributes': [{u'value': u'', u'key': u'ProductSeason'}], u'priceEach': 5.0, u'isGift': False, u'id': 940, u'giftTo': u''}], u'attributes': [{u'value': u'2', u'key': u'CustomerCultureId'}, {u'value': u'115.11.118.67', u'key': u'IpAddress'}, {u'value': u'UA', u'key': u'IpCountryCode'}, {u'value': u'OLS', u'key': u'OrderSource'}, {u'value': u'111335', u'key': u'SafeCharge_AuthCode'}, {u'value': u'UQA1AEYASgBVAEgAcgBvAE8AWAAlAFMAaABcAGAAMwA0AG4ATABiAHAAcQBoAEkAawB6AHMANQBXAEgAUQApACQATwBpAEQAUABAAGcAKwBcADQAMwA=', u'key': u'SafeCharge_Token'}, {u'value': u'1512424624', u'key': u'SafeCharge_TransactionId'}], u'isGift': False, u'id': 845}], u'offset': 0}

sample_object = pd.DataFrame(response)['results'].to_dict()

def flatten_json(y):
out = {}

def flatten(x, name=''):
if type(x) is dict:
for a in x:
flatten(x[a], name + a + '_')
elif type(x) is list:
out[name[:-1]] = x
else:
out[name[:-1]] = x

flatten(y)
return out


flat = {k: flatten_json(v) for k, v in sample_object.items()}

with open('flat.json', 'w') as jsonfile:
jsonfile.write(json.dumps(flat))


I personally use this procedure.

First store the JSON data as string (or, load from url or file)

use nested_to_record() method from pandas

import json
from pandas.io.json.normalize import nested_to_record

json_dic = json.loads(json_str)
flat = nested_to_record(json_dic, sep='_')

for key in flat:
print key, flat[key]


Output:

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.