Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Pandas conversion from JSON to Dataframe with data with multiple attributes

I have json data which compiled by attributes(column) and its elements(data) for each.

[
    {
        "attribute_id": "a1",
        "attribute_name": "Profailkir Idkir",
        "elements":[
             {
                "formValues": [
                    "0000a634940"
                ],
                "id": "0000a634940"
            },
            {
                "formValues": [
                    "0000c84724"
                ],
                "id": "0000c84724"
            }]
     },
     {
        "attribute_id": "a2",
        "attribute_name": "Profailkir Nama",
        "elements":[
             {
                "formValues": [
                    "'AFIFAH BINTI KHAIRUL JUBRI"
                ],
                "id": "'AFIFAH BINTI KHAIRUL JUBRI"
            },
            {
                "formValues": [
                    "'AINUN JARIAH BINTI HASHIM"
                ],
                "id": "'AINUN JARIAH BINTI HASHIM"
            }]
     }
]

but when i tried using

import pandas as pd
df = pd.DataFrame(data)
print(df)

the result is become appended only, not extracting the formValues.

enter image description here

How can I convert into Dataframe in below reading format?

Profailkir Idkir Profailkir Nama
0 0000a634940 ‘AFIFAH BINTI KHAIRUL JUBRI
1 0000c84724 ‘AINUN JARIAH BINTI HASHIM
Tags:

2

Answers


  1. 0

    Assuming you want the id values from each element, you could use a dictionary comprehension on your data to reshape it:

    df = pd.DataFrame({ e['attribute_name'] : [ v['id'] for v in e['elements'] ] for e in data })

    Output:

      Profailkir Idkir              Profailkir Nama
0      0000a634940  'AFIFAH BINTI KHAIRUL JUBRI
1       0000c84724   'AINUN JARIAH BINTI HASHIM

    If you want the formValues (and they are of the same length for each attribute), you can modify the code slightly:

    df = pd.DataFrame({ e['attribute_name'] : sum([v['formValues'] for v in e['elements'] ], []) for e in data })

    For your sample data, the result is the same.

    Login or Signup to reply.
  2. 0

    Try it this way, I hope I got it right

    res = []
def add_entry(res, id, t1, t2):
    # append to results list
    res.append({'attribute_id': id, 'attribute_name': t1, 'elements id': t2})
    return res

i=0
a = []
b = []
for x in data:
    for i in x:
        a = []
        b = []
        m=i["attribute_id"]
        t1=i["attribute_name"]
        for z in i["elements"]:
            a.append(z["id"])
        t2=a
        args = [m, t1, t2]
        res = add_entry(res, *args)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search