I am new to python and trying to create some scripts to essentially just rip some JSON data from API connections to CSV.
The particular JSON I am working with is nested a few layers deep and I have been trying to step through it 1 layer at a time so that I learn and understand the concepts (which I have been picking up from various posts etc.) but I have managed to get so far down and stuck at the last hurdle where I end up with a string that is essentially a list but isn’t being recognised as one when I try and query it.
The string ends up like this (as variable col_data):
"[{‘value’: ‘Checking’, ‘id’: ’35’}, {‘value’: ‘1201.00’}, {‘value’: ”}]"
My assumption is that it is not recognised as a list because the "{}" sections are not enclosed with double quotes.
I am trying to get to a point where I can query each of the respective values to produce a CSV file:
val1 = col_data[0][value]
The whole thing is recognised as a single item in a list, as opposed to the 3 items I would like.
I’m also aware the problem probably lies further up the stack with how I broke the original JSON response down to this point:
data = response.json()
rows = [('Account', 'Debit', 'Credit')]
row_data = data['Rows']['Row']
x = 0
for row in row_data:
rowx_data = row_data[x]
col_data = [rowx_data.get('ColData')]
col_data = col_data[0]
row = (
col_data,
'test',
'test'
)
rows.append(row)
x = x + 1
The original response looked liked below and I was only interested in the content of the rows:
{
"Header": {
"Time": "2023-06-12T13:55:49-07:00",
"ReportName": "TrialBalance",
"DateMacro": "this month-to-date",
"ReportBasis": "Accrual",
"StartPeriod": "2023-06-01",
"EndPeriod": "2023-06-12",
"SummarizeColumnsBy": "Total",
"Currency": "USD",
"Option": [
{
"Name": "NoReportData",
"Value": "false"
}
]
},
"Columns": {
"Column": [
{
"ColTitle": "",
"ColType": "Account"
},
{
"ColTitle": "Debit",
"ColType": "Money"
},
{
"ColTitle": "Credit",
"ColType": "Money"
}
]
},
"Rows": {
"Row": [
{
"ColData": [
{
"value": "Checking",
"id": "35"
},
{
"value": "1201.00"
},
{
"value": ""
}
]
},
{
"ColData": [
{
"value": "Savings",
"id": "36"
},
{
"value": "800.00"
},
{
"value": ""
}
]
},
{
"ColData": [
{
"value": "Accounts Receivable (A/R)",
"id": "84"
},
{
"value": "5281.52"
},
{
"value": ""
}
]
},
{
"ColData": [
{
"value": "Inventory Asset",
"id": "81"
},
{
"value": "596.25"
},
{
"value": ""
}
]
},
{
"ColData": [
{
"value": "Undeposited Funds",
"id": "4"
},
{
"value": "2062.52"
},
{
"value": ""
}
]
},
{
"ColData": [
{
"value": "Truck:Original Cost",
"id": "38"
},
{
"value": "13495.00"
},
{
"value": ""
}
]
},
{
"ColData": [
{
"value": "Accounts Payable (A/P)",
"id": "33"
},
{
"value": ""
},
{
"value": "1602.67"
}
]
},
{
"ColData": [
{
"value": "Mastercard",
"id": "41"
},
{
"value": ""
},
{
"value": "157.72"
}
]
},
{
"ColData": [
{
"value": "Arizona Dept. of Revenue Payable",
"id": "89"
},
{
"value": ""
},
{
"value": "0.00"
}
]
},
{
"ColData": [
{
"value": "Board of Equalization Payable",
"id": "90"
},
{
"value": ""
},
{
"value": "370.94"
}
]
},
{
"ColData": [
{
"value": "Loan Payable",
"id": "43"
},
{
"value": ""
},
{
"value": "4000.00"
}
]
},
{
"ColData": [
{
"value": "Notes Payable",
"id": "44"
},
{
"value": ""
},
{
"value": "25000.00"
}
]
},
{
"ColData": [
{
"value": "Opening Balance Equity",
"id": "34"
},
{
"value": "9337.50"
},
{
"value": ""
}
]
},
{
"ColData": [
{
"value": "Retained Earnings",
"id": "2"
},
{
"value": ""
},
{
"value": "1642.46"
}
]
},
{
"Summary": {
"ColData": [
{
"value": "TOTAL"
},
{
"value": "32773.79"
},
{
"value": "32773.79"
}
]
},
"type": "Section",
"group": "GrandTotal"
}
]
}
}
>Solution :
You’re pretty close, running your code as-is suggests that you’ve just missed the fact that your col_data variable actually contains a list of lists. Try this:
data = load_json('test.json')
rows = [('Account', 'Debit', 'Credit')]
row_data = data['Rows']['Row']
x = 0
for row in row_data:
rowx_data = row_data[x]
col_data = [rowx_data.get('ColData')]
col_data = col_data[0]
if col_data is not None:
row = (
col_data[0].get('value'),
col_data[1].get('value'),
col_data[2].get('value')
)
rows.append(row)
x = x + 1
Output:
[('Account', 'Debit', 'Credit'),
('Checking', '1201.00', ''),
('Savings', '800.00', ''),
('Accounts Receivable (A/R)', '5281.52', ''),
('Inventory Asset', '596.25', ''),
('Undeposited Funds', '2062.52', ''),
('Truck:Original Cost', '13495.00', ''),
('Accounts Payable (A/P)', '', '1602.67'),
('Mastercard', '', '157.72'),
('Arizona Dept. of Revenue Payable', '', '0.00'),
('Board of Equalization Payable', '', '370.94'),
('Loan Payable', '', '4000.00'),
('Notes Payable', '', '25000.00'),
('Opening Balance Equity', '9337.50', ''),
('Retained Earnings', '', '1642.46')]