Python Extracting nested values from a json string using pandas

Question

Below is a nested json I am using:

{
    "9": {
        "uid": "9",
        "name": "pedro",
        "mail": "[email protected]",
        "roles": [
            "authenticated",
            "administrator"
        ],
        "user_status": "1"
    },
    "10": {
        "uid": "10",
        "name": "Rosa",
        "mail": "[email protected]",
        "roles": [
            "authenticated",
            "administrator"
        ],
        "user_status": "1"
    },
    "11": {
        "uid": "11",
        "name": "Tania",
        "mail": "[email protected]",
        "roles": [
            "authenticated",
            "administrator"
        ],
        "user_status": "1"
    }
}

Each first key is different from the rest. I need to extract the information between each of the keys, e.g. uid, name, mail, etc but not interested on the key id (9,10,11). Is there any way to achieve this without passing the key id on the code?

Below is what I’ve attempted thus far:

import json

outputuids = {
        "9": {
            "uid": "9",
            "name": "pedro",
            "mail": "[email protected]",
            "roles": [
                "authenticated",
                "administrator"
            ],
            "user_status": "1"
        },
        "10": {
            "uid": "10",
            "name": "Rosa",
            "mail": "[email protected]",
            "roles": [
                "authenticated",
                "administrator"
            ],
            "user_status": "1"
        },
        "11": {
            "uid": "11",
            "name": "Tania",
            "mail": "[email protected]",
            "roles": [
                "authenticated",
                "administrator"
            ],
            "user_status": "1"
        }
    }

    data1 = json.loads(outputuids)
    for i in data1:
       fuid=data1['9']['uid']
       fname=data1['9']['name']
       print (fuid + fname)

Answer 1

Pandas is overkill for this task. You can iterate over outputuids.values() to avoid having to explicitly refer to the keys of the dictionary:

result = []
keys_to_retain = {"uid", "name", "mail"}

for val in outputuids.values():
    result.append({k: v for k, v in val.items() if k in keys_to_retain})

print(result)

This outputs:

[
 {'uid': '9', 'name': 'pedro', 'mail': '[email protected]'},
 {'uid': '10', 'name': 'Rosa', 'mail': '[email protected]'},
 {'uid': '11', 'name': 'Tania', 'mail': '[email protected]'}
]