'record_path'的字典不会在json_normalize中扁平化

Question

下面是json数据结构，我正试图将其转换为CSV

[{
"ASIN": "B0773V2Z6",
"Condition": "NewItem",
"EarliestAvailability": {
"TimepointType": "Immediately"
},
"FNSKU": "B0773V2Z6",
"InStockSupplyQuantity": "18",
"SellerSKU": "30237",
"SupplyDetail.member": [
{
"EarliestAvailableToPick": {
"TimepointType": "Immediately"
},
"LatestAvailableToPick": {
"TimepointType": "Immediately"
},
"Quantity": "1",
"SupplyType": "InStock"
},
{
"EarliestAvailableToPick": {
"TimepointType": "Immediately"
},
"LatestAvailableToPick": {
"TimepointType": "Immediately"
},
"Quantity": "1",
"SupplyType": "InStock"
}
],
"TotalSupplyQuantity": "18",            
}]

我已经尝试使用熊猫库中的json_normalize，如下所示

df = json_normalize(json_data, record_path="SupplyDetail.member", meta=["ASIN"], errors='ignore')

它给出以下结果

EarliestAvailableToPick              LatestAvailableToPick              ASIN
{'TimepointType': 'Immediately'}    {'TimepointType': 'Immediately'}    B0773V2Z6T
{'TimepointType': 'Immediately'}    {'TimepointType': 'Immediately'}    B0773V2Z6T

我需要的结果

EarliestAvailableToPick.TimepointType   LatestAvailableToPick.TimepointType   ASIN
'Immediately'                           'Immediately'                         B0773V2Z6T
'Immediately'                           'Immediately'                         B0773V2Z6T

我知道，如果字典是json的第一级，json_normalize会将其扁平化。但如果我们使用的是"record_path"，则不会将该路径下的字典扁平化！请帮助

Answer 1

这就是我为达到所需的解决方案所做的。

df = json_normalize(json_data, record_path="SupplyDetail.member", meta=["ASIN"], errors='ignore') re_data = df.to_json(orient='records') df_new = json_normalize(json.loads(re_data))

结果(df_new(为：

EarliestAvailableToPick.TimepointType LatestAvailableToPick.TimepointType Quantity SupplyType ASIN 'Immediately' 'Immediately' 1 InStock B0773V2Z6 'Immediately' 'Immediately' 1 InStock B0773V2Z6

Answer 2

作为一个快速而肮脏的修复程序，您可以执行以下操作：

df['EarliestAvailableToPick.TimepointType'] = df.EarliestAvailableToPick.map(lambda d: d['TimepointType'])
df['LatestAvailableToPick.TimepointType'] = df.LatestAvailableToPick.map(lambda d: d['TimepointType'])