I am trying to create dataframe from the json which I fetched from Quickbooks APAgingSummary API, but I am getting an error "TypeError: object of type 'float' has no len()", when I am inserting json_normalize data in the form of list to pandas. I used the same code for creating Dataframe from Quickbooks AccountListDetail API Json and it was working fine.
This code was used for fetching data:
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
This is the 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'}]}}
this is the code where I am getting the error:
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)
this is the responseDf after 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...
each element of ColData contains list of dictionaries.
and This is the error:
---------------------------------------------------------------------------
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:\ProgramData\Anaconda3\lib\site-packages\pandas\core\frame.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:\ProgramData\Anaconda3\lib\site-packages\pandas\core\internals\construction.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:\ProgramData\Anaconda3\lib\site-packages\pandas\core\internals\construction.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\_libs\lib.pyx in pandas._libs.lib.to_object_array()
TypeError: object of type 'float' has no len()
Any help will be really appreciated.
CodePudding user response：
You got the error because there is NaN value on the ColData column in responseDf. NaN is considered float type and has no len(), hence the error.
To solve the problem, you can init the NaN with list of empty dict with .fillna(), as follows:
responseDf['ColData'] = responseDf['ColData'].fillna({i: [{}] for i in responseDf.index})
Put the codes immediately after the line with pd.json_normalize
The full set of codes will be:
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)
Then, you will get through the error and get the result of responseDf, as follows:
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