How to correctly parse nested json with Python where several keys are equal
I have a problem with flatten / json_normalize functions. There's a nested json with 6 "receipts" in it, but flattening this json gives me only 1 row with 1 receipt, which is also last, I need all 6 in my pandas dataframe.
[
{
"_index": "packets-2020-02-03",
"_type": "receipts_file",
"_score": null,
"_source": {
"layers": {
"frame": {
"frame.encap_type": "25",
"frame.time": "Feb 3, 2019 00:17:14.004011000 MSK",
"frame.offset_shift": "0.000000000",
"frame.time_epoch": "2575325034.004011000",
"frame.time_delta": "0.002843000",
"frame.time_delta_displayed": "0.002843000",
"frame.time_relative": "0.002852000",
"frame.number": "4",
"frame.len": "1294",
"frame.cap_len": "1294",
"frame.marked": "0",
"frame.ignored": "0",
"frame.protocols": "several"
},
"receipts": {
"receipts.command_length": "238",
"receipts.command_id": "0x00000005",
"receipts.sequence_number": "47207",
"receipts.data_coding": "0x00000000",
"receipts.data_coding_tree": {
"receipts.rps": "0x00000000",
"Receipt Type 1 Data Coding": {
"receipts.rps.rc_coding_group": "0x00000000",
"receipts.rps.text_compression": "0",
"receipts.rps.class_present": "0",
"receipts.rps.charset": "0x00000000"
},
"Receipt Type 2 Data Coding": {
"receipts.rps.rpk._coding_group": "0x00000000",
"receipts.rps.rpk._language": "0x00000000"
}
},
"receipts.rc_default_receipt_id": "0",
"receipts.rc_length": "117",
"receipts.receipt": "29831",
"receipts.opt_params": {
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003002",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "47912"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003001",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "98982"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003004",
"receipts.opt_param_len": "1",
"receipts.vendor_op": "00"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003000",
"receipts.opt_param_len": "4",
"receipts.vendor_op": "23080"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003003",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "29849"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x0000001e",
"receipts.opt_param_len": "9",
"receipts.receipted_receipt_id": "949BB6DE"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00000427",
"receipts.opt_param_len": "1",
"receipts.receipt_state": "2"
}
}
},
"receipts": {
"receipts.command_length": "241",
"receipts.command_id": "0x00000005",
"receipts.sequence_number": "47208",
"receipts.data_coding": "0x00000000",
"receipts.data_coding_tree": {
"receipts.rps": "0x00000000",
"Receipt Type 1 Data Coding": {
"receipts.rps.rc_coding_group": "0x00000000",
"receipts.rps.text_compression": "0",
"receipts.rps.class_present": "0",
"receipts.rps.charset": "0x00000000"
},
"Receipt Type 2 Data Coding": {
"receipts.rps.rpk._coding_group": "0x00000000",
"receipts.rps.rpk._language": "0x00000000"
}
},
"receipts.rc_default_receipt_id": "0",
"receipts.rc_length": "117",
"receipts.receipt": "98341",
"receipts.opt_params": {
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003002",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "38220"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003001",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "93813"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003004",
"receipts.opt_param_len": "1",
"receipts.vendor_op": "00"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003000",
"receipts.opt_param_len": "4",
"receipts.vendor_op": "98381"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003003",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "77371"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x0000001e",
"receipts.opt_param_len": "9",
"receipts.receipted_receipt_id": "6DED391C"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00000427",
"receipts.opt_param_len": "1",
"receipts.receipt_state": "2"
}
}
},
"receipts": {
"receipts.command_length": "238",
"receipts.command_id": "0x00000005",
"receipts.sequence_number": "47209",
"receipts.data_coding": "0x00000000",
"receipts.data_coding_tree": {
"receipts.rps": "0x00000000",
"Receipt Type 1 Data Coding": {
"receipts.rps.rc_coding_group": "0x00000000",
"receipts.rps.text_compression": "0",
"receipts.rps.class_present": "0",
"receipts.rps.charset": "0x00000000"
},
"Receipt Type 2 Data Coding": {
"receipts.rps.rpk._coding_group": "0x00000000",
"receipts.rps.rpk._language": "0x00000000"
}
},
"receipts.rc_default_receipt_id": "0",
"receipts.rc_length": "117",
"receipts.opt_params": {
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003002",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "38717"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003001",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "37788"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003004",
"receipts.opt_param_len": "1",
"receipts.vendor_op": "74818"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003000",
"receipts.opt_param_len": "4",
"receipts.vendor_op": "77812"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003003",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "39999"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x0000001e",
"receipts.opt_param_len": "9",
"receipts.receipted_receipt_id": "273A872F"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00000427",
"receipts.opt_param_len": "1",
"receipts.receipt_state": "2"
}
}
},
"receipts": {
"receipts.command_length": "242",
"receipts.command_id": "0x00000005",
"receipts.sequence_number": "47210",
"receipts.data_coding": "0x00000000",
"receipts.data_coding_tree": {
"receipts.rps": "0x00000000",
"Receipt Type 1 Data Coding": {
"receipts.rps.rc_coding_group": "0x00000000",
"receipts.rps.text_compression": "0",
"receipts.rps.class_present": "0",
"receipts.rps.charset": "0x00000000"
},
"Receipt Type 2 Data Coding": {
"receipts.rps.rpk._coding_group": "0x00000000",
"receipts.rps.rpk._language": "0x00000000"
}
},
"receipts.rc_default_receipt_id": "0",
"receipts.rc_length": "118",
"receipts.receipt": "69322",
"receipts.opt_params": {
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003002",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "83881"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003001",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "73188"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003004",
"receipts.opt_param_len": "1",
"receipts.vendor_op": "00"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003000",
"receipts.opt_param_len": "4",
"receipts.vendor_op": "78881"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003003",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "74388"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x0000001e",
"receipts.opt_param_len": "9",
"receipts.receipted_receipt_id": "949C60DF"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00000427",
"receipts.opt_param_len": "1",
"receipts.receipt_state": "2"
}
}
},
"receipts": {
"receipts.command_length": "238",
"receipts.command_id": "0x00000005",
"receipts.sequence_number": "47211",
"receipts.data_coding": "0x00000000",
"receipts.data_coding_tree": {
"receipts.rps": "0x00000000",
"Receipt Type 1 Data Coding": {
"receipts.rps.rc_coding_group": "0x00000000",
"receipts.rps.text_compression": "0",
"receipts.rps.class_present": "0",
"receipts.rps.charset": "0x00000000"
},
"Receipt Type 2 Data Coding": {
"receipts.rps.rpk._coding_group": "0x00000000",
"receipts.rps.rpk._language": "0x00000000"
}
},
"receipts.rc_default_receipt_id": "0",
"receipts.rc_length": "117",
"receipts.receipt": "12281",
"receipts.opt_params": {
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003002",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "12727"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003001",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "18828"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003004",
"receipts.opt_param_len": "1",
"receipts.vendor_op": "00"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003000",
"receipts.opt_param_len": "4",
"receipts.vendor_op": "38218"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003003",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "47718"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x0000001e",
"receipts.opt_param_len": "9",
"receipts.receipted_receipt_id": "949BD094"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00000427",
"receipts.opt_param_len": "1",
"receipts.receipt_state": "2"
}
}
},
"receipts": {
"receipts.command_length": "25",
"receipts.command_id": "0x80000004",
"receipts.command_status": "0x00000000",
"receipts.sequence_number": "35572",
"receipts.receipt_id": "949C23B8"
}
}
}
}
]
I tried to use this code:
import json
import pandas as pd
from flatten_json import flatten
i_file_name = 'example.json'
with open(i_file_name) as fd:
json_data = json.load(fd)
json_data = (flatten(d, '.') for d in json_data)
df = pd.DataFrame(json_data)
df.head()
and
import pandas as pd
i_file_name = 'example.json'
df = pd.read_json(i_file_name)
df = pd.json_normalize(df['_source'])
df.head()
They give me the same result: just 1 row, not 6. I tried to set record_path and meta with json_normalize but I couldn't figure out how to do that. I'm kinda new to json parsing and I couldn't find similar issues here. I know that I need to set the right keys, but I don't know how
EDIT:
Unfortanutely, StackOverflow has a limited support of tables in questions, so I'll just try to explain my expected output.
Right now I'm getting just one row with those columns:
- _index
- _type
- _score
- _source.layers.frame.*
- _source.source.receipts.*
where * means that there are several columns under the same level
receipts.* contain just 5 columns:
- command_length
- command_id
- command_status
- sequence_number
- receipt_id
and that 1 row that I'm getting contains values for these columns from the last "receipts"-level records:
"receipts": {
"receipts.command_length": "25",
"receipts.command_id": "0x80000004",
"receipts.command_status": "0x00000000",
"receipts.sequence_number": "35572",
"receipts.receipt_id": "949C23B8"
}
but there are also other "receipts"-level records, such as:
"receipts": {
"receipts.command_length": "238",
"receipts.command_id": "0x00000005",
"receipts.sequence_number": "47207",
"receipts.data_coding": "0x00000000",
"receipts.data_coding_tree": {
"receipts.rps": "0x00000000",
"Receipt Type 1 Data Coding": {
"receipts.rps.rc_coding_group": "0x00000000",
"receipts.rps.text_compression": "0",
"receipts.rps.class_present": "0",
"receipts.rps.charset": "0x00000000"
},
"Receipt Type 2 Data Coding": {
"receipts.rps.rpk._coding_group": "0x00000000",
"receipts.rps.rpk._language": "0x00000000"
}
},
"receipts.rc_default_receipt_id": "0",
"receipts.rc_length": "117",
"receipts.receipt": "29831",
"receipts.opt_params": {
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003002",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "47912"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003001",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "98982"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003004",
"receipts.opt_param_len": "1",
"receipts.vendor_op": "00"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003000",
"receipts.opt_param_len": "4",
"receipts.vendor_op": "23080"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00003003",
"receipts.opt_param_len": "10",
"receipts.vendor_op": "29849"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x0000001e",
"receipts.opt_param_len": "9",
"receipts.receipted_receipt_id": "949BB6DE"
},
"receipts.opt_param": {
"receipts.opt_param_tag": "0x00000427",
"receipts.opt_param_len": "1",
"receipts.receipt_state": "2"
}
}
},
that I want to see also as rows in pandas dataframe. So the current row, that I'm getting, should be the 6th in it.
I kind of understood that my json is somehow broken since it has 6 different keys with the same name (receipts), but maybe I can parse it differently so that I can import it to Pandas correctly
1 Answer
I've realized that I didn't answer my question but actually managed to solve it. I apologize for the following code, but if you want to solve a problem like that, it might help. I've decided that I'd rather show the world my silly code than to leave it without any solution.
Firstly, I did as I mentioned in my question:
import pandas as pd
i_file_name = 'example.json'
df = pd.read_json(i_file_name)
df = pd.json_normalize(df['_source'])
then I converted it to json and imported it again to Pandas:
df_json = df.to_json(orient='records')
df = pd.read_json(df_json, orient='columns')
then I melted some of the layers:
df_melt = pd.melt(df, id_vars=['layers.frame.frame.time',
'layers.frame.frame.number'
value_vars=['layers.receipts'])
after that, I created a new DataFrame with those melted values and saved index to later join 2 dataframes.
df_melt2 = pd.DataFrame(df_melt['value'].values.tolist(), index=df_melt)
then I joined 2 dataframes together and dropped no longer needed columns
df_melt_full = pd.concat([df_melt, df_melt2], axis=1)
df_melt_full = df_melt_full.drop(['value', 'variable'], axis=1)
after that, I melted it again (yeah, that's my code from February and I'm ashamed of it)
df_melt_full_melt = pd.melt(df_melt_full,
id_vars=['layers.frame.frame.time',
'layers.frame.frame.number']
)
and imported it again
df_normalized = pd.json_normalize(df_melt_full_melt['value'])
then, finally, I joined 2 dataframes together and got my problem solved
df_final = pd.concat([df_melt, df_normalized], axis=1)
answered on Stack Overflow Aug 24, 2020 by 
zot8
zot8User contributions licensed under CC BY-SA 3.0