Parse JSON response to populate data in the form of table

Question

I have JSON response in a <class 'dict'>. I want to iterate over the JSON response and form a table view. Below is the sample JSON response.

{'ResultSet': {'Rows': [{'Data': [{'VarCharValue': 'cnt'}, {'VarCharValue': 'id'}, {'VarCharValue': 'val'}]}, {'Data': [{'VarCharValue': '2000'}, {'VarCharValue': '1234'}, {'VarCharValue': 'ABC'}]},{'Data': [{'VarCharValue': '3000'}, {'VarCharValue': '5678'}, {'VarCharValue': 'DEF'}]}]}}

Expected Output format:

 cnt    id     val
2000   1234    ABC
3000   5678    DEF

There can only one row of data or there can be multiple rows of data for the column set (For provided sample data two rows are there).

I have sure how to iterate over the multiple rows, there can be n number of rows. — data_addict, Jun 08 '18 at 15:04
I see you added a #python hashtag to your question. If you are using Python, can you provide your python code so we can see where you are having problems? — Eric Milliot-Martinez, Jun 08 '18 at 15:06
@EricMilliot-Martinez, I want to achieve this requirement in python. — data_addict, Jun 08 '18 at 15:08
@user805 SO isn't a place for people to write code for you, we help troubleshoot errors you may be having. Try yourself first. — Sasha, Jun 08 '18 at 15:11
@user805 Since you haven't provided us with any of your code where you tried attempting to solve this, I suggest you google for some examples. A quick google brought me this and maybe this will help you: https://stackoverflow.com/questions/36862308/how-to-create-a-table-with-data-from-json-output-in-python — Eric Milliot-Martinez, Jun 08 '18 at 15:14

score 1 · Answer 1 · answered Jun 08 '18 at 15:25

I am not sure if you are using pandas but you can easily parse your response dict into a pandas.DataFrame with the following code

import pandas as pd

pd.DataFrame([[entr['VarCharValue'] for entr in r['Data']] for r in response['ResultSet']['Rows'][1:]],
             columns = [r['VarCharValue'] for r in response['ResultSet']['Rows'][0]['Data']])

score 1 · Accepted Answer · answered Jun 08 '18 at 15:41

I assume you want to use Pandas. Since pd.DataFrame accepts a list of dictionaries directly, you can restructure your input dictionary D as a list of dictionaries:

cols = [next(iter(i.values())) for i in D['ResultSet']['Rows'][0]['Data']]

d = [{col: j['VarCharValue'] for col, j in zip(cols, i['Data'])}
     for i in D['ResultSet']['Rows'][1:]]

df = pd.DataFrame(d)

print(df)

    cnt    id  val
0  2000  1234  ABC
1  3000  5678  DEF

You will probably want to convert at least the cnt series to numeric:

df['cnt'] = pd.to_numeric(df['cnt'])

Parse JSON response to populate data in the form of table

2 Answers2