Parse Json file normalize

Question

Im trying to parse a nested json file with json_normalize but I keep getting an error. I already tried to use different solutions that I found, like writing the hierarchy from the columns but so far all i got was error message This is a part of the json Im using:

{'offers': [{'offerType': 'mobile-app', 'linkDest': 'app-store', 'convertsOn': 'install-and-open', 'appInfo': {'appID': '1065767207', 'previewLink': 'https://itunes.apple.com/in/app/id1065767207?mt=8', 'appName': 'Zivame - One Stop Lingerie App', 'appCategory': 'Shopping', 'appIcon': 'https://cdn-iphone.apptap.com/img/398/1065767207/586427483.jpg'}, 'targets': [{'offerID': '9b5k58v8d43aftq236q17gy65', 'approvalStatus': 'approved', 'offerStatus': 'active', 'trackingLink': 'https://api.apptap.com/link/buy/iphone/1065767207/e1?clinkID=t8CTj4i9juYAKRjUoqBiHa6lDPI9enbFx9dWp-hlWov00cmXlnm0&pubID=m_73-YT52OdNcB6Uz_InRvD8VA&siteID=kPCipA&placementID={branch_placement_id}&trackingID={branch_tracking_id}', 'countries': ['IN'], 'platforms': ['iphone'], 'payout': {'amount': 0.6, 'currency': 'USD'}, 'endDate': None, 'dailyConversionCap': None, 'restrictions': {'allowIncent': False, 'deviceIDRequired': False, 'sourceAppIDRequired': False, 'minOSVersion': '9.0', 'allowedPlacements': None, 'blockedPlacements': []}}]}, {'offerType': 'mobile-app', 'linkDest': 'app-store', 'convertsOn': 'install-and-open', 'appInfo': {'appID': '1070734239', 'previewLink': 'https://itunes.apple.com/in/app/id1070734239?mt=8', 'appName': 'Housejoy', 'appCategory': 'Lifestyle', 'appIcon': 'https://cdn-iphone.apptap.com/img/345/1070734239/749793232.jpg'}, 'targets': [{'offerID': '1qfdu3blg6bey343yuxjwtpgm', 'approvalStatus': 'approved', 'offerStatus': 'active', 'trackingLink': 'https://api.apptap.com/link/buy/iphone/1070734239/e1?clinkID=t8CTj4i9jucFKR3XoqNsHa6lDPIyfnbFx9dWp-hlWov00cmXlnm0&pubID=m_73-YT52OdNcB6Uz_InRvD8VA&siteID=kPCipA&placementID={branch_placement_id}&trackingID={branch_tracking_id}', 'countries': ['IN'], 'platforms': ['iphone'], 'payout': {'amount': 0.94, 'currency': 'USD'}, 'endDate': None, 'dailyConversionCap': None, 'restrictions': {'allowIncent': False, 'deviceIDRequired': False, 'sourceAppIDRequired': False, 'minOSVersion': '9.0', 'allowedPlacements': None, 'blockedPlacements': []}}]}

This is the code that I`m currently using to try to parse the json:

df = pd.json_normalize(data, 'offerType','linkDest','convertsOn','targets',['offerID','approvalStatus','offerStatus', ['trackingLink','countries','platforms','payout',[ 'amount','currency' ], 'endDate','dailyConversionCap','restrictions',[ 'deviceIDRequired','sourceAppIDRequires','minOSVersion', 'allowedPlacements','blockedPlacements' ]]], errors='ignore') df.head(10)

I got an error regarding some commas that were missing, after I added I got the following error message:

TypeError Traceback (most recent call last) in ----> 1 df = pd.json_normalize(data, 'offerType','linkDest','convertsOn','targets',['offerID','approvalStatus','offerStatus', 2 ['trackingLink','countries','platforms','payout',[ 3 'amount','currency' 4 ], 5 'endDate','dailyConversionCap','restrictions',[

TypeError: _json_normalize() got multiple values for argument 'errors'

I added the errors='ignore' but still didn`t work. Does anybody know what this issue could be and how to fix it?

thanks in advance

Jonathan Leon · Accepted Answer · 2021-01-12 17:05:57Z

Alternatively you can use flatten_json

import requests data = requests.get('http://api.apptap.com/api/4/offers_feed?pubID=mk096uf7xn0w_branch&siteID=feed&countries=IN') data = json.loads(data.text) dic_flattened = (flatten(d, '.') for d in data['offers']) df = pd.DataFrame(dic_flattened)

Output:

 offerType linkDest convertsOn ... targets.1.restrictions.minOSVersion targets.1.restrictions.allowedPlacements targets.1.restrictions.blockedPlacements 0 mobile-app app-store install-and-open ... NaN NaN NaN 1 mobile-app app-store install-and-open ... NaN NaN NaN 2 mobile-app app-store install-and-open ... NaN NaN NaN 3 mobile-app app-store install-and-open ... None NaN [] 4 mobile-app app-store install-and-open ... None NaN [] .. ... ... ... ... ... ... ... 127 mobile-app app-store install-and-open ... NaN NaN NaN 128 mobile-app app-store install-and-open ... NaN NaN NaN 129 mobile-app app-store install-and-open ... 5.0 NaN [] 130 mobile-app app-store install-and-open ... 4.1 NaN [] 131 mobile-app app-store install-and-open ... NaN NaN NaN 9.0

I installed and it was successful but I got the message: ModuleNotFoundError: No module named 'flatten_json'
did you import as indicated in the post? from flatten_json import flatten. If so, did you have a virtual environment, and didn't install it there?
thanks, it worked but now I have another problem. I have a total of 126 rows and 24 columns where each row was a different offer with different information, now I have 126 columns with the exact same information as the first row spread in a total of 3130 column (each 24 columns it was suppose to be a row). Do you know how I can fix that?
doesn't sound like your dictionary is setup correctly. if it's not {'offers': [{...},{...},{...},{...}]}, it won't parse into rows. or make sure this row is exactly as written above dic_flattened = (flatten(data, '.') for d in data['offers'])

Jonathan Leon · Accepted Answer · 2021-01-10 23:26:58Z

You aren't setting up your inputs correctly. From the docs:

pandas.json_normalize(data, record_path=None, meta=None, meta_prefix=None, record_prefix=None, errors='raise', sep='.', max_level=None)

If you add the keywords to all your inputs (as seen below), you'll notice error= is in there twice. Sometimes it helps to add the keywords to keep your inputs straight. Give it another go by rearranging your inputs.

df = pd.json_normalize(data, record_path='offerType',meta='linkDest',meta_prefix='convertsOn',record_prefix='targets',errors=['offerID','approvalStatus','offerStatus', ['trackingLink','countries','platforms','payout',[ 'amount','currency' ], 'endDate','dailyConversionCap','restrictions',[ 'deviceIDRequired','sourceAppIDRequires','minOSVersion', 'allowedPlacements','blockedPlacements' ]]], errors='ignore')

Collectives™ on Stack Overflow

Parse Json file normalize

2 Answers 2

9 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Related