我试图从json中创建数据帧，我从Quickbooks APAgingSummary APITypeError:类型'float'的对象没有len()"，当我将json_normalize数据以列表的形式插入到pandas时。我使用相同的代码从Quickbooks AccountListDetail API Json创建Dataframe，它工作得很好。
这段代码用于获取数据:
base_url = 'https://sandbox-quickbooks.api.intuit.com'
url = f"{base_url}/v3/company/{auth_client.realm_id}/reports/AgedPayables?&minorversion=62"
auth_header = f'Bearer {auth_client.access_token}'
headers = {
'Authorization': auth_header,
'Accept': 'application/json'
}
response = requests.get(url, headers=headers)
responseJson = response.json()
responseJson
这是responseJson:
{'Header': {'Time': '2021-10-05T04:33:02-07:00',
'ReportName': 'AgedPayables',
'DateMacro': 'today',
'StartPeriod': '2021-10-05',
'EndPeriod': '2021-10-05',
'SummarizeColumnsBy': 'Total',
'Currency': 'USD',
'Option': [{'Name': 'report_date', 'Value': '2021-10-05'},
{'Name': 'NoReportData', 'Value': 'false'}]},
'Columns': {'Column': [{'ColTitle': '', 'ColType': 'Vendor'},
{'ColTitle': 'Current',
'ColType': 'Money',
'MetaData': [{'Name': 'ColKey', 'Value': 'current'}]},
{'ColTitle': '1 - 30',
'ColType': 'Money',
'MetaData': [{'Name': 'ColKey', 'Value': '0'}]},
{'ColTitle': '31 - 60',
'ColType': 'Money',
'MetaData': [{'Name': 'ColKey', 'Value': '1'}]},
{'ColTitle': '61 - 90',
'ColType': 'Money',
'MetaData': [{'Name': 'ColKey', 'Value': '2'}]},
{'ColTitle': '91 and over',
'ColType': 'Money',
'MetaData': [{'Name': 'ColKey', 'Value': '3'}]},
{'ColTitle': 'Total',
'ColType': 'Money',
'MetaData': [{'Name': 'ColKey', 'Value': 'total'}]}]},
'Rows': {'Row': [{'ColData': [{'value': 'Brosnahan Insurance Agency',
'id': '31'},
{'value': ''},
{'value': '241.23'},
{'value': ''},
{'value': ''},
{'value': ''},
{'value': '241.23'}]},
{'ColData': [{'value': "Diego's Road Warrior Bodyshop", 'id': '36'},
{'value': '755.00'},
{'value': ''},
{'value': ''},
{'value': ''},
{'value': ''},
{'value': '755.00'}]},
{'ColData': [{'value': 'Norton Lumber and Building Materials', 'id': '46'},
{'value': ''},
{'value': '205.00'},
{'value': ''},
{'value': ''},
{'value': ''},
{'value': '205.00'}]},
{'ColData': [{'value': 'PG&E', 'id': '48'},
{'value': ''},
{'value': ''},
{'value': '86.44'},
{'value': ''},
{'value': ''},
{'value': '86.44'}]},
{'ColData': [{'value': 'Robertson & Associates', 'id': '49'},
{'value': ''},
{'value': '315.00'},
{'value': ''},
{'value': ''},
{'value': ''},
{'value': '315.00'}]},
{'Summary': {'ColData': [{'value': 'TOTAL'},
{'value': '755.00'},
{'value': '761.23'},
{'value': '86.44'},
{'value': '0.00'},
{'value': '0.00'},
{'value': '1602.67'}]},
'type': 'Section',
'group': 'GrandTotal'}]}}
这是我得到错误的代码:
colHeaders = []
for i in responseJson['Columns']['Column']:
colHeaders.append(i['ColTitle'])
responseDf = pd.json_normalize(responseJson["Rows"]["Row"])
responseDf[colHeaders] = pd.DataFrame(responseDf.ColData.tolist(), index= responseDf.index)
这是json_normalize:
之后的响应ColData type group Summary.ColData
0 [{'value': 'Brosnahan Insurance Agency', 'id':... NaN NaN NaN
1 [{'value': 'Diego's Road Warrior Bodyshop', 'i... NaN NaN NaN
2 [{'value': 'Norton Lumber and Building Materia... NaN NaN NaN
3 [{'value': 'PG&E', 'id': '48'}, {'value': ''},... NaN NaN NaN
4 [{'value': 'Robertson & Associates', 'id': '49... NaN NaN NaN
5 NaN Section GrandTotal [{'value': 'TOTAL'}, {'value': '755.00'}, {'va...
ColData的每个元素包含一个字典列表。
和
---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
<ipython-input-215-6ce65ce2ac94> in <module>
6
7 responseDf = pd.json_normalize(responseJson["Rows"]["Row"])
----> 8 responseDf[colHeaders] = pd.DataFrame(responseDf.ColData.tolist(), index= responseDf.index)
9 responseDf
10
C:ProgramDataAnaconda3libsite-packagespandascoreframe.py in __init__(self, data, index, columns, dtype, copy)
507 if is_named_tuple(data[0]) and columns is None:
508 columns = data[0]._fields
--> 509 arrays, columns = to_arrays(data, columns, dtype=dtype)
510 columns = ensure_index(columns)
511
C:ProgramDataAnaconda3libsite-packagespandascoreinternalsconstruction.py in to_arrays(data, columns, coerce_float, dtype)
522 return [], [] # columns if columns is not None else []
523 if isinstance(data[0], (list, tuple)):
--> 524 return _list_to_arrays(data, columns, coerce_float=coerce_float, dtype=dtype)
525 elif isinstance(data[0], abc.Mapping):
526 return _list_of_dict_to_arrays(
C:ProgramDataAnaconda3libsite-packagespandascoreinternalsconstruction.py in _list_to_arrays(data, columns, coerce_float, dtype)
559 else:
560 # list of lists
--> 561 content = list(lib.to_object_array(data).T)
562 # gh-26429 do not raise user-facing AssertionError
563 try:
pandas_libslib.pyx in pandas._libs.lib.to_object_array()
TypeError: object of type 'float' has no len()
任何帮助都将是非常感激的。
您得到了错误，因为在responseDf的ColData列上有NaN值。NaN被认为是float类型，没有len()，因此出现错误。
为了解决这个问题，可以将NaN初始化为.fillna()的空字典列表，如下所示:
responseDf['ColData'] = responseDf['ColData'].fillna({i: [{}] for i in responseDf.index})
将代码紧接在pd.json_normalize
行之后完整的代码集将是:
colHeaders = []
for i in responseJson['Columns']['Column']:
colHeaders.append(i['ColTitle'])
responseDf = pd.json_normalize(responseJson["Rows"]["Row"])
## Add the code here
responseDf['ColData'] = responseDf['ColData'].fillna({i: [{}] for i in responseDf.index})
responseDf[colHeaders] = pd.DataFrame(responseDf.ColData.tolist(), index= responseDf.index)
然后，您将通过错误并获得responseDf的结果，如下所示:
print(responseDf)
ColData type group Summary.ColData Current 1 - 30 31 - 60 61 - 90 91 and over Total
0 [{'value': 'Brosnahan Insurance Agency', 'id': '31'}, {'value': ''}, {'value': '241.23'}, {'value': ''}, {'value': ''}, {'value': ''}, {'value': '241.23'}] NaN NaN NaN {'value': 'Brosnahan Insurance Agency', 'id': '31'} {'value': ''} {'value': '241.23'} {'value': ''} {'value': ''} {'value': ''} {'value': '241.23'}
1 [{'value': 'Diego's Road Warrior Bodyshop', 'id': '36'}, {'value': '755.00'}, {'value': ''}, {'value': ''}, {'value': ''}, {'value': ''}, {'value': '755.00'}] NaN NaN NaN {'value': 'Diego's Road Warrior Bodyshop', 'id': '36'} {'value': '755.00'} {'value': ''} {'value': ''} {'value': ''} {'value': ''} {'value': '755.00'}
2 [{'value': 'Norton Lumber and Building Materials', 'id': '46'}, {'value': ''}, {'value': '205.00'}, {'value': ''}, {'value': ''}, {'value': ''}, {'value': '205.00'}] NaN NaN NaN {'value': 'Norton Lumber and Building Materials', 'id': '46'} {'value': ''} {'value': '205.00'} {'value': ''} {'value': ''} {'value': ''} {'value': '205.00'}
3 [{'value': 'PG&E', 'id': '48'}, {'value': ''}, {'value': ''}, {'value': '86.44'}, {'value': ''}, {'value': ''}, {'value': '86.44'}] NaN NaN NaN {'value': 'PG&E', 'id': '48'} {'value': ''} {'value': ''} {'value': '86.44'} {'value': ''} {'value': ''} {'value': '86.44'}
4 [{'value': 'Robertson & Associates', 'id': '49'}, {'value': ''}, {'value': '315.00'}, {'value': ''}, {'value': ''}, {'value': ''}, {'value': '315.00'}] NaN NaN NaN {'value': 'Robertson & Associates', 'id': '49'} {'value': ''} {'value': '315.00'} {'value': ''} {'value': ''} {'value': ''} {'value': '315.00'}
5 [{}] Section GrandTotal [{'value': 'TOTAL'}, {'value': '755.00'}, {'value': '761.23'}, {'value': '86.44'}, {'value': '0.00'}, {'value': '0.00'}, {'value': '1602.67'}] {} None None None None None None