Build table from JSON in Python

Question

I am trying to transform a JSON text into a standard data table using Python, however I have little experience with this and as I search for solutions online I find I am having difficulty implementing any.

I was trying to use ast.literal_eval but kept getting an error that I have been unable to solve.

raise ValueError('malformed node or string: ' + repr(node))

JSON:

{
    "duration": 202.0,
    "session_info": {
        "activation_uuid": "ab90d941-df9d-42c5-af81-069eb4f71515",
        "launch_uuid": "11101c41-2d79-42cc-bf6d-37be46802fc8"
    },
    "timestamp": "2019-01-18T11:11:26.135Z",
    "source_page_view_reference": {
        "page_uuid": "1bede017-7b77-461d-82ef-a6bbcfdae4d7",
        "page_id": "/group/More",
        "page_name": "More",
        "view_uuid": "9580f3c5-1116-432a-83bc-9d0b5337f661",
        "page_type": "Native"
    },
    "analytics_sdk": {
        "component_id": "datasdk",
        "component_version": "1.0.52"
    },
    "treatment_id": "mockTreat",
    "client_event_id": "2b3cd878-6932-410b-b1ad-bc40ae888fdc",
    "campaign_id": "mockCamp"
}

Desired Table Format (values trimmed to fit for display purposes):

Duration | session_info.activation_uuid | session_info.launch_uuid | timestamp  | etc
   202.0 |  ab90d941-df9d-42c5-af81-069 | 11101c41-2d79-42cc-bf6d- | 2019-01-18 | etc

Any direct help, or simply good resources to learn up on this would be greatly appreciated. I've had trouble finding items that speak directly to what I am looking to do to create a table from a series of similar JSONs.

Please clarify what do you mean by: "a standard data table". What is it: a) a table in a database (which?), b) python data structure?, c) other? — Alex Yu
– Alex Yu, Commented Feb 21, 2019 at 23:40
That's what I was going to say too - specifically use json_normalize. See pandas docs. — jcragun
– jcragun, Commented Feb 21, 2019 at 23:41

OneCricketeer · Accepted Answer · 2019-02-21 23:50:48Z

pandas is almost always used when interacting with tables. And it can parse a dictionary

In [0]: import pandas

In [1]: from pandas.io.json import json_normalize

In [2]: d = {'duration': 202.0,
   ...:  'session_info':
   ...:     {'activation_uuid': 'ab90d941-df9d-42c5-af81-069eb4f71515',
   ...:      'launch_uuid': '11101c41-2d79-42cc-bf6d-37be46802fc8'},
   ...:  'timestamp': '2019-01-18T11:11:26.135Z',
   ...:  'source_page_view_reference':
   ...:     {'page_uuid': '1bede017-7b77-461d-82ef-a6bbcfdae4d7',
   ...:      'page_id': '/group/More',
   ...:      'page_name': 'More',
   ...:      'view_uuid': '9580f3c5-1116-432a-83bc-9d0b5337f661',
   ...:      'page_type': 'Native'},
   ...:  'analytics_sdk':
   ...:     {'component_id': 'datasdk',
   ...:      'component_version': '1.0.52'},
   ...:  'treatment_id': 'mockTreat',
   ...:  'client_event_id': '2b3cd878-6932-410b-b1ad-bc40ae888fdc',
   ...:  'campaign_id': 'mockCamp'}

In [4]: json_normalize(d)
Out[4]:
  analytics_sdk.component_id analytics_sdk.component_version campaign_id                       client_event_id  duration  ... source_page_view_reference.page_type  source_page_view_reference.page_uuid  source_page_view_reference.view_uuid                 timestamp treatment_id
0                    datasdk                          1.0.52    mockCamp  2b3cd878-6932-410b-b1ad-bc40ae888fdc     202.0  ...                               Native  1bede017-7b77-461d-82ef-a6bbcfdae4d7  9580f3c5-1116-432a-83bc-9d0b5337f661  2019-01-18T11:11:26.135Z    mockTreat

[1 rows x 14 columns]

To load JSON string into a dictionary, use json.loads

Or use pandas.read_json

lennhv · Accepted Answer · 2019-02-22 00:22:08Z

You can also do it in the following way, which is something similar to what pandas do internally.

import json

jsondata='''{
    "duration": 202.0,
    "session_info": {
        "activation_uuid": "ab90d941-df9d-42c5-af81-069eb4f71515",
        "launch_uuid": "11101c41-2d79-42cc-bf6d-37be46802fc8"
    },
    "timestamp": "2019-01-18T11:11:26.135Z",
    "source_page_view_reference": {
        "page_uuid": "1bede017-7b77-461d-82ef-a6bbcfdae4d7",
        "page_id": "/group/More",
        "page_name": "More",
        "view_uuid": "9580f3c5-1116-432a-83bc-9d0b5337f661",
        "page_type": "Native"
    },
    "analytics_sdk": {
        "component_id": "datasdk",
        "component_version": "1.0.52"
    },
    "treatment_id": "mockTreat",
    "client_event_id": "2b3cd878-6932-410b-b1ad-bc40ae888fdc",
    "campaign_id": "mockCamp"
}'''

data=json.loads(jsondata)

table=[[],[]]
def dictList(d, column_name=''):
    for k, v in d.items():
        if isinstance(v, dict):
            dictList(v, column_name=k)
            continue
        if column_name:
            column_name+='.'
        column_name +=k
        table[0].append(column_name)
        table[1].append(v)

dictList(data)

for row in table:
    print (row)

Collectives™ on Stack Overflow

Build table from JSON in Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related