从一列获取多个字典

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"}]}]
2   Sara    [{"Personal":{"ID":"001","Name":"Sara","Type":"02","TypeName":"Food"},"Order":[{"ID":"0004","Date":"20200222","ProductID":"D0123","ProductName":"Small beef", "Price":"15"}]}]
3   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  Frank 002        Frank         02            Food              0008              20200228            D0288                    Salmon                       24

Answer 1

使用^{}提取与Detail列中字典内键相关的值，然后使用apply(pd.Series)将此字典转换为dataframe，然后使用^{}将前缀添加到列标签中。然后使用^{}沿axis=1将personal和order数据帧连接在一起：

# step 1
personal = df['Detail'].str[0].str.get('Personal').apply(pd.Series).add_prefix('Personal_')

# step 2
order = df['Detail'].str[0].str.get('Order').str[0].apply(pd.Series).add_prefix('Personal_Order_')

# step 3
result = pd.concat([df[['ID', "Name"]], personal, order], axis=1)

---

步骤：

# step 1: personal
  Personal_ID Personal_Name Personal_Type Personal_TypeName
0         001          Sara            01              Book
1         001          Sara            02              Food
2         002         Frank            02              Food

# step 2: order
  Personal_Order_ID Personal_Order_Date Personal_Order_ProductID Personal_Order_ProductName Personal_Order_Price
0              0001            20200222                    C0123                        ABC                    4
1              0004            20200222                    D0123                 Small beef                   15
2              0008            20200228                    D0288                     Salmon                   24

# step 3: result
   ID   Name Personal_ID Personal_Name  ... Personal_Order_Date Personal_Order_ProductID Personal_Order_ProductName Personal_Order_Price
0   1   Sara         001          Sara  ...            20200222                    C0123                        ABC                    4
1   2   Sara         001          Sara  ...            20200222                    D0123                 Small beef                   15
2   3  Frank         002         Frank  ...            20200228                    D0288                     Salmon                   24