解析Json文件规范化

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使用：

{'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': []}}]}

这是我目前用来解析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)

我得到了一个关于一些逗号丢失的错误，在我添加后，我得到了以下错误消息：

---

TypeError回溯（最近一次调用上次） 在里面 ----&燃气轮机；1 df=pd.json_normalize（数据，'offerType'，'linkDest'，'convertsOn'，'targets'，['offerID'，'approvalStatus'，'offerStatus'，'， 2[“跟踪链接”、“国家”、“平台”、“支付”[ 3“金额”、“货币” 4 ], 5“结束日期”、“dailyConversionCap”、“限制条件”、[

TypeError:_json_normalize（）为参数“errors”获取了多个值

我添加了错误class='ignore'，但仍然不起作用。 有人知道这个问题可能是什么以及如何解决吗

提前谢谢

Answer 1

或者，您可以使用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)

输出：

      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       

Answer 2

您没有正确设置输入。从文档中：

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

如果您将关键字添加到所有输入中（如下所示），您会注意到error=在其中出现了两次。有时，添加关键字有助于保持输入的准确性。通过重新安排你的输入，再尝试一次

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')