How to extract data from a nested json-CodePudding

I would like to extract all the data from the 'Options' level of this Json file. The problem is that I only manage to get a part of the data. I attach more information:

[
{
    "id": "62af9e713fb3db3e96905790",
    "idModel": "62458249eed0bf2860e472a1",
    "modelType": "board",
    "fieldGroup": "b0cddfb41bc9462347387c37077a2de5338a50c797e04f3cf0adb6ae2344e83c",
    "display": {
        "cardFront": true
    },
    "name": "Priority",
    "pos": 16384,
    "options": [
        {
            "id": "62af9e713fb3db3e96905791",
            "idCustomField": "62af9e713fb3db3e96905790",
            "value": {
                "text": "Highest"
            },
            "color": "red",
            "pos": 16384
        },
        {
            "id": "62af9e713fb3db3e96905792",
            "idCustomField": "62af9e713fb3db3e96905790",
            "value": {
                "text": "High"
            },
            "color": "orange",
            "pos": 32768
        },
        {
            "id": "62af9e713fb3db3e96905793",
            "idCustomField": "62af9e713fb3db3e96905790",
            "value": {
                "text": "Medium"
            },
            "color": "yellow",
            "pos": 49152
        },
        {
            "id": "62af9e713fb3db3e96905794",
            "idCustomField": "62af9e713fb3db3e96905790",
            "value": {
                "text": "Low"
            },
            "color": "sky",
            "pos": 65536
        },
        {
            "id": "62af9e713fb3db3e96905795",
            "idCustomField": "62af9e713fb3db3e96905790",
            "value": {
                "text": "Lowest"
            },
            "color": "blue",
            "pos": 81920
        },
        {
            "id": "62af9e713fb3db3e96905796",
            "idCustomField": "62af9e713fb3db3e96905790",
            "value": {
                "text": "Not sure"
            },
            "color": "none",
            "pos": 98304
        }
    ],
    "type": "list",
    "isSuggestedField": false
},
{
    "id": "62af9e79b90b617ab506e07f",
    "idModel": "62458249eed0bf2860e472a1",
    "modelType": "board",
    "fieldGroup": "e8620327b22fc5e743a35720260ad7f03a0edaef6e64e3c47f37f04f09ce4747",
    "display": {
        "cardFront": true
    },
    "name": "Status",
    "pos": 32768,
    "options": [
        {
            "id": "62af9e79b90b617ab506e080",
            "idCustomField": "62af9e79b90b617ab506e07f",
            "value": {
                "text": "To do"
            },
            "color": "orange",
            "pos": 16384
        },
        {
            "id": "62af9e79b90b617ab506e081",
            "idCustomField": "62af9e79b90b617ab506e07f",
            "value": {
                "text": "In progress"
            },
            "color": "sky",
            "pos": 32768
        },
        {
            "id": "62af9e79b90b617ab506e082",
            "idCustomField": "62af9e79b90b617ab506e07f",
            "value": {
                "text": "Done"
            },
            "color": "blue",
            "pos": 49152
        },
        {
            "id": "62af9e79b90b617ab506e083",
            "idCustomField": "62af9e79b90b617ab506e07f",
            "value": {
                "text": "In review"
            },
            "color": "pink",
            "pos": 65536
        },
        {
            "id": "62af9e79b90b617ab506e084",
            "idCustomField": "62af9e79b90b617ab506e07f",
            "value": {
                "text": "Approved"
            },
            "color": "purple",
            "pos": 81920
        },
        {
            "id": "62af9e79b90b617ab506e085",
            "idCustomField": "62af9e79b90b617ab506e07f",
            "value": {
                "text": "Not sure"
            },
            "color": "none",
            "pos": 98304
        }
    ],
    "type": "list",
    "isSuggestedField": false
},
{
    "id": "62af9e9aec64591850a2affc",
    "idModel": "62458249eed0bf2860e472a1",
    "modelType": "board",
    "fieldGroup": "6a3e7236de9b7cad94edc4eca26ef6d0950c7ce7c7de9eda04b8639f32e81756",
    "display": {
        "cardFront": true
    },
    "name": "Risk2",
    "pos": 49152,
    "options": [
        {
            "id": "62af9e9aec64591850a2affd",
            "idCustomField": "62af9e9aec64591850a2affc",
            "value": {
                "text": "Vermelho"
            },
            "color": "red",
            "pos": 16384
        },
        {
            "id": "62af9e9aec64591850a2affe",
            "idCustomField": "62af9e9aec64591850a2affc",
            "value": {
                "text": "Laranja"
            },
            "color": "orange",
            "pos": 32768
        },
        {
            "id": "62af9e9aec64591850a2afff",
            "idCustomField": "62af9e9aec64591850a2affc",
            "value": {
                "text": "Amarelo"
            },
            "color": "yellow",
            "pos": 49152
        },
        {
            "id": "62af9e9aec64591850a2b000",
            "idCustomField": "62af9e9aec64591850a2affc",
            "value": {
                "text": "Cian"
            },
            "color": "sky",
            "pos": 65536
        },
        {
            "id": "62af9e9aec64591850a2b001",
            "idCustomField": "62af9e9aec64591850a2affc",
            "value": {
                "text": "Azul"
            },
            "color": "blue",
            "pos": 81920
        },
        {
            "id": "62af9e9aec64591850a2b002",
            "idCustomField": "62af9e9aec64591850a2affc",
            "value": {
                "text": "Cinza"
            },
            "color": "none",
            "pos": 98304
        }
    ],
    "type": "list",
    "isSuggestedField": false
},
{
    "id": "62af9e9b3d330332322d0b51",
    "idModel": "62458249eed0bf2860e472a1",
    "modelType": "board",
    "fieldGroup": "cbe95065aa1ceca2e0fe02597b0603b64ada5a3290a3fd182c67713972f62fa5",
    "display": {
        "cardFront": true
    },
    "name": "Effort",
    "pos": 65536,
    "type": "number",
    "isSuggestedField": false
},
{
    "id": "62af9ea5d702f48beebb20a1",
    "idModel": "62458249eed0bf2860e472a1",
    "modelType": "board",
    "fieldGroup": "32e07b2bfb57633e77f8d8ce19d227a98965b1147d6732b76dd7a6a4521efdf6",
    "display": {
        "cardFront": true
    },
    "name": "Date",
    "pos": 81920,
    "type": "date",
    "isSuggestedField": false
},
{
    "id": "62af9eae84137552f0b773b7",
    "idModel": "62458249eed0bf2860e472a1",
    "modelType": "board",
    "fieldGroup": "e5863f32271bf8a895f8d87c14223f0721b5bedfe41faed6040aa8c772593a8f",
    "display": {
        "cardFront": true
    },
    "name": "Number",
    "pos": 98304,
    "type": "number",
    "isSuggestedField": false
},
{
    "id": "62af9eb55a33596436b9c7fa",
    "idModel": "62458249eed0bf2860e472a1",
    "modelType": "board",
    "fieldGroup": "516f920dcdac8fe5232e2b6c243fd54fde96b21f5540e4711c6a8c69e4a3a0f1",
    "display": {
        "cardFront": true
    },
    "name": "Text",
    "pos": 114688,
    "type": "text",
    "isSuggestedField": false
},
{
    "id": "62b4365ca419438696c67670",
    "idModel": "62458249eed0bf2860e472a1",
    "modelType": "board",
    "fieldGroup": "e6ada93711c6506e4654e91f01a6bd417226703a20f7da551c66086d52e36579",
    "display": {
        "cardFront": true
    },
    "name": "Checkbox_field",
    "pos": 131072,
    "type": "checkbox",
    "isSuggestedField": false
},
{
    "id": "62c22641b4b30a7a955300f4",
    "idModel": "62458249eed0bf2860e472a1",
    "modelType": "board",
    "fieldGroup": "a20f7a7e943e379714813aba0b53e2093e77c5736f18c61d08c193176bbbeafc",
    "display": {
        "cardFront": true
    },
    "name": "Risk",
    "pos": 147456,
    "options": [
        {
            "id": "62c22641b4b30a7a955300f5",
            "idCustomField": "62c22641b4b30a7a955300f4",
            "value": {
                "text": "Highest"
            },
            "color": "red",
            "pos": 16384
        },
        {
            "id": "62c22641b4b30a7a955300f6",
            "idCustomField": "62c22641b4b30a7a955300f4",
            "value": {
                "text": "High"
            },
            "color": "orange",
            "pos": 32768
        },
        {
            "id": "62c22641b4b30a7a955300f7",
            "idCustomField": "62c22641b4b30a7a955300f4",
            "value": {
                "text": "Medium"
            },
            "color": "yellow",
            "pos": 49152
        },
        {
            "id": "62c22641b4b30a7a955300f8",
            "idCustomField": "62c22641b4b30a7a955300f4",
            "value": {
                "text": "Low"
            },
            "color": "sky",
            "pos": 65536
        },
        {
            "id": "62c22641b4b30a7a955300f9",
            "idCustomField": "62c22641b4b30a7a955300f4",
            "value": {
                "text": "Lowest"
            },
            "color": "blue",
            "pos": 81920
        },
        {
            "id": "62c22641b4b30a7a955300fa",
            "idCustomField": "62c22641b4b30a7a955300f4",
            "value": {
                "text": "Not sure"
            },
            "color": "none",
            "pos": 98304
        }
    ],
    "type": "list",
    "isSuggestedField": false
},
{
    "id": "62c227a0a9f6c20867a1f767",
    "idModel": "62458249eed0bf2860e472a1",
    "modelType": "board",
    "fieldGroup": "2a9bc4c06cef764be97bdd47ab397c597d2e1d8f4d54b48c84eafae36ed21e63",
    "display": {
        "cardFront": true
    },
    "name": "Risk3",
    "pos": 163840,
    "options": [
        {
            "id": "62c227a0a9f6c20867a1f768",
            "idCustomField": "62c227a0a9f6c20867a1f767",
            "value": {
                "text": "Alto"
            },
            "color": "none",
            "pos": 16384
        }
    ],
    "type": "list",
    "isSuggestedField": false
},
{
    "id": "62c2996148e38f2bf09e1088",
    "idModel": "62458249eed0bf2860e472a1",
    "modelType": "board",
    "fieldGroup": "dc10651a093834befed19308dd27fbdb6f552375b4e20e9b1e49fbd5dce97c2d",
    "display": {
        "cardFront": true
    },
    "name": "Dropdown",
    "pos": 180224,
    "options": [
        {
            "id": "62c2996148e38f2bf09e1089",
            "idCustomField": "62c2996148e38f2bf09e1088",
            "value": {
                "text": "Jhon"
            },
            "color": "red",
            "pos": 16384
        },
        {
            "id": "62c2996148e38f2bf09e108a",
            "idCustomField": "62c2996148e38f2bf09e1088",
            "value": {
                "text": "Paul"
            },
            "color": "green",
            "pos": 32768
        },
        {
            "id": "62c2996148e38f2bf09e108b",
            "idCustomField": "62c2996148e38f2bf09e1088",
            "value": {
                "text": "Ringo"
            },
            "color": "sky",
            "pos": 49152
        },
        {
            "id": "62c2996148e38f2bf09e108c",
            "idCustomField": "62c2996148e38f2bf09e1088",
            "value": {
                "text": "George"
            },
            "color": "none",
            "pos": 65536
        }
    ],
    "type": "list",
    "isSuggestedField": false
},
{
    "id": "62c6fecad63fb933eff94547",
    "idModel": "62458249eed0bf2860e472a1",
    "modelType": "board",
    "fieldGroup": "eeca8512098168e56c82890d9c242f4a3ced7f510c3d9599eacb9cb796c5dbf7",
    "display": {
        "cardFront": true
    },
    "name": "test de dropdown",
    "pos": 196608,
    "options": [
        {
            "id": "62c6fecad63fb933eff94548",
            "idCustomField": "62c6fecad63fb933eff94547",
            "value": {
                "text": "pam"
            },
            "color": "green",
            "pos": 16384
        },
        {
            "id": "62c6fecad63fb933eff94549",
            "idCustomField": "62c6fecad63fb933eff94547",
            "value": {
                "text": "pum"
            },
            "color": "red",
            "pos": 32768
        }
    ],
    "type": "list",
    "isSuggestedField": false
}

]

My code:

import pandas as pd
import json

with open('response.json') as f:
    data = json.load(f)
    df = pd.DataFrame(data)
    df1 = pd.DataFrame(data[0]['options'])
    df2 = pd.DataFrame(data[1]['options'])
    df3 = pd.DataFrame(data[2]['options'])
    df_concat = pd.concat([df1, df2, df3])
    print(df_concat)

And this json data is missing:

I've also tried json_normalize (it improved the look of what I got, but I'm still missing the same json data).

What am I doing wrong?

CodePudding user response：

The problem seems to be that some of the elements in your json do not have an options field. You could use a simple approach such as:

opt_list = []
for elem in data:
    print(elem['id'])
    if 'options' in elem:
        opt_list.extend(elem['options'])
opt_df = pd.DataFrame(opt_list)