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

Convert json containing nested list using json_normalize function and create dataframe

[
{
"ver": "1",
"dt": 1699439129329,
"od": "OBJECT_ID",
"bin": "Vbin",
"myname": "VME",
"msgss": [
{
"code": "TLHGH",
"Details": {
"no": 1,
"rep": 4
},
"pngds": [
{
"id": "ID",
"mpo": 16,
"mkg": "up",
}
] }
] },
{
"ver": "2",
"dt": 1699439129329,
"od": "OBJECT_ID",
"bin": "Vbin",
"myname": "VME",
"msgss": [
{
"code": "TLHGH",
"Details": {
"no": 1,
"rep": 4
},
"pngds": [
{
"id": "ID",
"mpo": 16,
"mkg": "up",
}
] }
] }
]

I need to parse above json and get the output details like below.

enter image description here

Tags:

2

Answers


  1. 0

    Use nested list comprehension for flatten values to list of dictionaries and call DataFrame constructor:

    L = [ { "ver": "1", "Datetime": 1699439129329, "od": "OBJECT_ID", "bin": "Vbin", "myname": "VME", "msgss": [ { "code": "TLHGH", "Details": { "no": 1, "rep": 4 }, "pngds": [ { "id": "ID", "mpo": 16, "mkg": "up", } ] } ] }, { "ver": "2", "Datetime": 1699439129329, "od": "OBJECT_ID", "bin": "Vbin", "myname": "VME", "msgss": [ { "code": "TLHGH", "Details": { "no": 1, "rep": 4 }, "pngds": [ { "id": "ID", "mpo": 16, "mkg": "up", } ] } ] } ]

    df = pd.DataFrame([{**x, **{'code': y['code']} ,**y.pop('Details'), **z} 
                   for x in L 
                   for y in x.pop('msgss') 
                   for z in y.pop('pngds')])
print (df)
  ver       Datetime         od   bin myname   code  no  rep  id  mpo mkg
0   1  1699439129329  OBJECT_ID  Vbin    VME  TLHGH   1    4  ID   16  up
1   2  1699439129329  OBJECT_ID  Vbin    VME  TLHGH   1    4  ID   16  up
    Login or Signup to reply.
  2. 0

    You can use pandas.json_normalize:

    df = pd.json_normalize(
    data,
    record_path=["msgss", "pngds"],
    meta=[
        "ver",
        "Datetime",
        "od",
        "bin",
        "myname",
        ["msgss", "code"],
        ["msgss", "Details", "no"],
        ["msgss", "Details", "rep"],
    ],
)

df.columns = [
    "id",
    "mpo",
    "mkg",
    "ver",
    "Datetime",
    "od",
    "bin",
    "myName",
    "code",
    "no",
    "rep",
]

       id  mpo mkg ver       Datetime         od   bin myName   code no rep
0  ID   16  up   1  1699439129329  OBJECT_ID  Vbin    VME  TLHGH  1   4
1  ID   16  up   2  1699439129329  OBJECT_ID  Vbin    VME  TLHGH  1   4
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search