python - 如何在不丢失记录的情况下使用空列表对熊猫中的列进行 json_normalize
问题描述
我正在使用将此数据pd.json_normalize
中的字段展平"sections"
为行。"sections"
除了是空列表的行之外,它工作正常。
此 ID 被完全忽略,并且在最终展平的数据框中丢失。我需要确保数据中每个唯一 ID 至少有一行(某些 ID 可能有很多行,每个唯一 ID、每个唯一 ID 最多一行section_id
,question_id
并且answer_id
当我在数据中取消嵌套更多字段时):
{'_id': '5f48f708fe22ca4d15fb3b55',
'created_at': '2020-08-28T12:22:32Z',
'sections': []}]
样本数据:
sample = [{'_id': '5f48bee4c54cf6b5e8048274',
'created_at': '2020-08-28T08:23:00Z',
'sections': [{'comment': '',
'type_fail': None,
'answers': [{'comment': 'stuff',
'feedback': [],
'value': 10.0,
'answer_type': 'default',
'question_id': '5e59599c68369c24069630fd',
'answer_id': '5e595a7c3fbb70448b6ff935'},
{'comment': 'stuff',
'feedback': [],
'value': 10.0,
'answer_type': 'default',
'question_id': '5e598939cedcaf5b865ef99a',
'answer_id': '5e598939cedcaf5b865ef998'}],
'score': 20.0,
'passed': True,
'_id': '5e59599c68369c24069630fe',
'custom_fields': []},
{'comment': '',
'type_fail': None,
'answers': [{'comment': '',
'feedback': [],
'value': None,
'answer_type': 'not_applicable',
'question_id': '5e59894f68369c2398eb68a8',
'answer_id': '5eaad4e5b513aed9a3c996a5'},
{'comment': '',
'feedback': [],
'value': None,
'answer_type': 'not_applicable',
'question_id': '5e598967cedcaf5b865efe3e',
'answer_id': '5eaad4ece3f1e0794372f8b2'},
{'comment': "stuff",
'feedback': [],
'value': 0.0,
'answer_type': 'default',
'question_id': '5e598976cedcaf5b865effd1',
'answer_id': '5e598976cedcaf5b865effd3'}],
'score': 0.0,
'passed': True,
'_id': '5e59894f68369c2398eb68a9',
'custom_fields': []}]},
{'_id': '5f48f708fe22ca4d15fb3b55',
'created_at': '2020-08-28T12:22:32Z',
'sections': []}]
测试:
df = pd.json_normalize(sample)
df2 = pd.json_normalize(df.to_dict(orient="records"), meta=["_id", "created_at"], record_path="sections", record_prefix="section_")
在这一点上,我现在缺少我仍然需要的 ID“5f48f708fe22ca4d15fb3b55”行。
df3 = pd.json_normalize(df2.to_dict(orient="records"), meta=["_id", "created_at", "section__id", "section_score", "section_passed", "section_type_fail", "section_comment"], record_path="section_answers", record_prefix="")
我可以以某种方式更改它以确保每个 ID 至少有一行吗?我正在处理数百万条记录,并且不想稍后意识到我的最终数据中缺少一些 ID。我能想到的唯一解决方案是将每个数据帧标准化,然后再次将其加入原始数据帧。
解决方案
- 解决问题的最佳方法是修复
dict
- 如果
sections
是空的list
,用[{'answers': [{}]}]
for i, d in enumerate(sample):
if not d['sections']:
sample[i]['sections'] = [{'answers': [{}]}]
df = pd.json_normalize(sample)
df2 = pd.json_normalize(df.to_dict(orient="records"), meta=["_id", "created_at"], record_path="sections", record_prefix="section_")
# display(df2)
section_comment section_type_fail section_answers section_score section_passed section__id section_custom_fields _id created_at
0 NaN [{'comment': 'stuff', 'feedback': [], 'value': 10.0, 'answer_type': 'default', 'question_id': '5e59599c68369c24069630fd', 'answer_id': '5e595a7c3fbb70448b6ff935'}, {'comment': 'stuff', 'feedback': [], 'value': 10.0, 'answer_type': 'default', 'question_id': '5e598939cedcaf5b865ef99a', 'answer_id': '5e598939cedcaf5b865ef998'}] 20.0 True 5e59599c68369c24069630fe [] 5f48bee4c54cf6b5e8048274 2020-08-28T08:23:00Z
1 NaN [{'comment': '', 'feedback': [], 'value': None, 'answer_type': 'not_applicable', 'question_id': '5e59894f68369c2398eb68a8', 'answer_id': '5eaad4e5b513aed9a3c996a5'}, {'comment': '', 'feedback': [], 'value': None, 'answer_type': 'not_applicable', 'question_id': '5e598967cedcaf5b865efe3e', 'answer_id': '5eaad4ece3f1e0794372f8b2'}, {'comment': 'stuff', 'feedback': [], 'value': 0.0, 'answer_type': 'default', 'question_id': '5e598976cedcaf5b865effd1', 'answer_id': '5e598976cedcaf5b865effd3'}] 0.0 True 5e59894f68369c2398eb68a9 [] 5f48bee4c54cf6b5e8048274 2020-08-28T08:23:00Z
2 NaN NaN [{}] NaN NaN NaN NaN 5f48f708fe22ca4d15fb3b55 2020-08-28T12:22:32Z
df3 = pd.json_normalize(df2.to_dict(orient="records"), meta=["_id", "created_at", "section__id", "section_score", "section_passed", "section_type_fail", "section_comment"], record_path="section_answers", record_prefix="")
# display(df3)
comment feedback value answer_type question_id answer_id _id created_at section__id section_score section_passed section_type_fail section_comment
0 stuff [] 10.0 default 5e59599c68369c24069630fd 5e595a7c3fbb70448b6ff935 5f48bee4c54cf6b5e8048274 2020-08-28T08:23:00Z 5e59599c68369c24069630fe 20 True NaN
1 stuff [] 10.0 default 5e598939cedcaf5b865ef99a 5e598939cedcaf5b865ef998 5f48bee4c54cf6b5e8048274 2020-08-28T08:23:00Z 5e59599c68369c24069630fe 20 True NaN
2 [] NaN not_applicable 5e59894f68369c2398eb68a8 5eaad4e5b513aed9a3c996a5 5f48bee4c54cf6b5e8048274 2020-08-28T08:23:00Z 5e59894f68369c2398eb68a9 0 True NaN
3 [] NaN not_applicable 5e598967cedcaf5b865efe3e 5eaad4ece3f1e0794372f8b2 5f48bee4c54cf6b5e8048274 2020-08-28T08:23:00Z 5e59894f68369c2398eb68a9 0 True NaN
4 stuff [] 0.0 default 5e598976cedcaf5b865effd1 5e598976cedcaf5b865effd3 5f48bee4c54cf6b5e8048274 2020-08-28T08:23:00Z 5e59894f68369c2398eb68a9 0 True NaN
5 NaN NaN NaN NaN NaN NaN 5f48f708fe22ca4d15fb3b55 2020-08-28T12:22:32Z NaN NaN NaN NaN NaN
推荐阅读
- python - 通过在另一组向量之间自由移动值来最小化多个向量的零交叉点之间的差异的优化
- c - 返回 void 指针的函数的意外行为
- jenkins - 是否可以在 pipeline{} 指令之外获取 Git 哈希?
- sql - sql / postgresql - 月份范围内的计数值增加
- mysql - 仅计算分组查询中的第一个唯一组合
- sql - 雪花阶段文件路径,包括今天的日期
- powershell - 窗口标题栏去掉X的查询
- python - 如何使用打印的 Pandas Dataframe 输出?
- html - 设置最小高度时“裁剪”圆形 div 的底部
- jquery - BigCommerce 只需使用 jquery 提取客户地址?