CODEWITHSUNDEEP
pythonjsonpandas
Anonymous_hacker
Anonymous_hacker

Reputation: 37

Extracting fields from json

"Repo": {
    "D_Repo": {
        "fields": [
            {
                "Out": " ",
                "number": {
                    "Bks": "[number is valid]",
                    "Ban": "[VALID]"
                },
                "st_ID": 2
                "sign": "No Match"
            }
        ],
        "RID": 2546,
        "result": "OK"
    }

I have read the json using pd.read_json and Repo is a column in a dataframe. So how to access nested columns "bks, Ban, Out, RID and Decision" and like this there are 70 records inside "info". In above code I have displayed only one record. Can anybody help out with logic and code snippet?

Upvotes: 1

Views: 89

Answers (1)

iamklaus
iamklaus

Reputation: 3770

hope this helps!! (assuming d is what you have)

   d = {"Repo": {
        "DE_Repo": {
            "info": [
                {
                    "Out": " ",
                    "Value": {
                        "Bks": "[number is valid]",
                        "Ban": "[VALID]"
                    },
                    "ID": 2,
                    "Remark": "No Match"
                },
                {
                    "Out": " ",
                    "Value": {
                        "Bks": "[number is valid]",
                        "Ban": "[VALID]"
                    },
                    "ID": 2,
                    "Remark": "No Match"
                }
            ],
            "RID": 2546,
            "Decision": "Approved"
        }}}


repo_data = d['Repo']['DE_Repo']

values = []



 for keys, val in repo_data.items():
    if type(val) == list:
        for i in range(len(val)):
            for key,data in val[i].items():
                if type(data) == dict:
                    for subKey, subValue in data.items():
                        values.append([subKey,subValue])
                else:
                    values.append([key,data])
    else:
        values.append([keys,val])





extracted_points = pd.DataFrame(values,columns=['Key','Value'])

Output

           0                  1
0        Out                   
1        Bks  [number is valid]
2        Ban            [VALID]
3         ID                  2
4     Remark           No Match
5        Out                   
6        Bks  [number is valid]
7        Ban            [VALID]
8         ID                  2
9     Remark           No Match
10       RID               2546
11  Decision           Approved

Upvotes: 1

Related Questions