I receive this json from an API call:

data = {'List': [{'id': 12403,
   'name': 'myname',
   'code': 'mycode',
   'description': '',
   'createdBy': '',
   'createdDate': '24-Jun-2008 15:03:59 CDT',
   'lastModifiedBy': '',
   'lastModifiedDate': '24-Jun-2008 15:03:59 CDT'}]}

I want to handle this data and move it into a dataframe. When I attempt this with json_normalize it's basically putting my list value into a single cell in my dataframe.

My attempt:

import pandas as pd

df = pd.json_normalize(data)

Current output:

    List
0   [{'id': 12403, 'name': 'myname', 'code': 'mycode...

Desired output:

Question

What's the best way to work with a list value from JSON to pandas dataframe?

Update

{
    "Count": 38,
    "Items": [
        {
            "Actions": [
                "edit_",
                "remove_",
                "attachments_",
                "cancel",
                "continue",
                "auditTrail",
                "offline_",
                "changeUser",
                "linkRecord",
                "resendNotification"
            ],
            "Columns": [
                {
                    "Label": "Workflow Name",
                    "Name": "__WorkflowName__",
                    "Value": "VOAPTSQA00000735"
                },
                {
                    "Label": "Workflow Description",
                    "Name": "__WorkflowDescription__",
                    "Value": "Vendor Outsourcing Contract Request (APTSQA | SAP Integration)"
                },
                {
                    "Label": "Current Assignee",
                    "Name": "__CurrentAssignee__",
                    "Value": "Vendor Outsourcing Integration User"
                },
                {
                    "Label": "Last Updated",
                    "Name": "__DateLastUpdated__",
                    "Value": "9/7/2022 12:22:14 PM"
                },
                {
                    "Label": "Created",
                    "Name": "__DateCreated__",
                    "Value": "9/7/2022 12:20:55 PM"
                },
                {
                    "Label": "Date Signed",
                    "Name": "__DateSigned__",
                    "Value": ""
                },
                {
                    "Label": "Completed",
                    "Name": "__DateCompleted__",
                    "Value": ""
                },
                {
                    "Label": "Status",
                    "Name": "__Status__",
                    "Value": "In RFP"
                },
                {
                    "Label": "Document ID",
                    "Name": "__DocumentIdentifier__",
                    "Value": ""
                },
                {
                    "Label": "End Date",
                    "Name": "__EndDate__",
                    "Value": "12/31/2033 12:00:00 AM"
                },
                {
                    "Label": "Stage Progress",
                    "Name": "__FormProgress__",
                    "Value": "0"
                },
                {
                    "Label": "Next Signer",
                    "Name": "__NextSigner__",
                    "Value": ""
                }
            ],
            "ResultSetId": "784a1b83-4d83-4b80-87a3-9c1293baa7d8",
            "TaskId": "784a1b83-4d83-4b80-87a3-9c1293baa7d8",
            "TokenId": "cdd53c33-803d-4a63-9abd-47b733b55e89"
        } 

Adding context for my comment about nested list of key pair values. Here when I normalize the json, I get the list of Columns all as one value in a cell.

CodePudding user response：

The values of interest are under the List key, so slice it:

df = pd.json_normalize(data['List'])

output:

      id    name    code description createdBy               createdDate lastModifiedBy          lastModifiedDate
0  12403  myname  mycode                        24-Jun-2008 15:03:59 CDT                 24-Jun-2008 15:03:59 CDT