Need to Convert the excel data to the json data using python

by

I tried using pandas but the json which I require is not getting generated. Need help on how this could be done.

Attached excel screenshot. I want the Json to be generated like below.

enter image description here

{
   "contents": [{
        "Program": "Gravity",
        "Service": "UCL",
        "Stories": {
            "Total": "",
            "Resolved": "",
            "Open": ""
        },
        "Sub Tasks": {
            "Total": "",
            "Resolved": "",
            "Open": ""
        },
        "Bugs": {
            "Total": "",
            "Resolved": "",
            "Open": ""
        }
    },
    {
        "Program": "Gravity",
        "Service": "Topology",
        "Stories": {
            "Total": "",
            "Resolved": "",
            "Open": ""
        },
        "Sub Tasks": {
            "Total": "",
            "Resolved": "",
            "Open": ""
        },
        "Bugs": {
            "Total": "",
            "Resolved": "",
            "Open": ""
        }
    }]
}

I tried using pandas but not getting required json. Getting below json

[{
    "Program": null,
    "Services": null,
    "Stories": "Total",
    "Unnamed: 3": "Resolved",
    "Unnamed: 4": "Open",
    "Sub Tasks": "Total",
    "Unnamed: 6": "Resolved",
    "Unnamed: 7": "Open",
    "Bugs": "Total",
    "Unnamed: 9": "Resolved",
    "Unnamed: 10": "Open"
}, {
    "Program": "Gravity",
    "Services": "UCL",
    "Stories": null,
    "Unnamed: 3": null,
    "Unnamed: 4": null,
    "Sub Tasks": null,
    "Unnamed: 6": null,
    "Unnamed: 7": null,
    "Bugs": null,
    "Unnamed: 9": null,
    "Unnamed: 10": null
}, {
    "Program": "Gravity",
    "Services": "Topology",
    "Stories": null,
    "Unnamed: 3": null,
    "Unnamed: 4": null,
    "Sub Tasks": null,
    "Unnamed: 6": null,
    "Unnamed: 7": null,
    "Bugs": null,
    "Unnamed: 9": null,
    "Unnamed: 10": null
}]

Leave a Comment