[
{
“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.
3
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
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