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

1

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

edited Jan 12, 2021 at 17:05

answered Jan 10, 2021 at 23:37

Jonathan Leon

5,6862 gold badges9 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Mariana Rodrigues Over a year ago

I installed and it was successful but I got the message: ModuleNotFoundError: No module named 'flatten_json'

Jonathan Leon Over a year ago

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?

Jonathan Leon Over a year ago

to be sure, it's this module github.com/amirziai/flatten

Mariana Rodrigues Over a year ago

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?

Jonathan Leon Over a year ago

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related