多个具有重复键但值不同且列中没有限制的字典

Question

这里是字典中具有无限键的数据集。行中的“详细信息”列可能具有不同的信息产品，具体取决于客户

ID  Name    Detail
1   Sara    [{"Personal":{"ID":"001","Name":"Sara","Type":"01","TypeName":"Book"},"Order":[{"ID":"0001","Date":"20200222","ProductID":"C0123","ProductName":"ABC", "Price":"4"}]},{"Personal":{"ID":"001","Name":"Sara","Type":"02","TypeName":"Food"},"Order":[{"ID":"0004","Date":"20200222","ProductID":"D0123","ProductName":"Small beef", "Price":"15"}]},{"Personal":{"ID":"001","Name":"Sara","Type":"02","TypeName":"Food"},"Order":[{"ID":"0005","Date":"20200222","ProductID":"D0200","ProductName":"Shrimp", "Price":"28"}]}]
2   Frank   [{"Personal":{"ID":"002","Name":"Frank","Type":"02","TypeName":"Food"},"Order":[{"ID":"0008","Date":"20200228","ProductID":"D0288","ProductName":"Salmon", "Price":"24"}]}]

我的预期产出是

ID Name Personal_ID Personal_Name Personal_Type Personal_TypeName Personal_Order_ID Personal_Order_Date Personal_Order_ProductID Personal_Order_ProductName Personal_Order_Price    
1  Sara 001         Sara          01            Book              0001              20200222            C0123                    ABC                          4    
2  Sara 001         Sara          02            Food              0004              20200222            D0123                    Small beef                   15
3  Sara 001         Sara          02            Food              0005              20200222            D0200                    Shrimp                       28
4  Frank 002        Frank         02            Food              0008              20200228            D0288                    Salmon                       24

Answer 1

因此，基本上，在细节列中有一个嵌套的JSON，您需要将其分解为df，然后与原始JSON合并

import pandas as pd
import json
from pandas import json_normalize

#create empty df to hold the detail information
detailDf = pd.DataFrame()
#We will need to loop over each row to read each JSON
for ind, row in df.iterrows():
    #Read the json, make it a DF, then append the information to the empty DF
    detailDf = detailDf.append(json_normalize(json.loads(row['Detail']), record_path = ('Order'), meta = [['Personal','ID'], ['Personal','Name'], ['Personal','Type'],['Personal','TypeName']]))

# Personally, you don't really need the rest of the code, as the columns Personal.Name
# and Personal.ID is the same information, but none the less.

# You will have to merge on name and ID
df = df.merge(detailDf, how = 'right', left_on = [df['Name'], df['ID']], right_on = [detailDf['Personal.Name'], detailDf['Personal.ID'].astype(int)])

#Clean up
df.rename(columns = {'ID_x':'ID', 'ID_y':'Personal_Order_ID'}, inplace = True)
df.drop(columns = {'Detail', 'key_1', 'key_0'}, inplace = True)

如果您仔细阅读我的评论，我建议使用detailDf作为您的最终df，因为合并确实是不必要的，而且这些信息已经在详细的JSON中了