pandas 通过解析json列创建新列

syqv5f0l  于 2023-03-11  发布在  其他
关注(0)|答案(1)|浏览(142)

我有一个json(Vals)列的 Dataframe :

Identity                                                                                                      Vals
                  2fc9d38d-0fe4-c7be       {"$id":"2","Address":"22.44","Location":{"Code":"TN"},"Asset":false,"Roles":["A"],"Type":"webfile"}
                        abd77d57ac29 {"$id":"3","Address":"40.1","Location":{"Code":"SS"},"Asset":false,"Roles":["Attacker"],"Type":"webfile"}
                           c7be-4a37                  {"$id":"4","AppId":11161,"SaasId":11161,"Name":"Office 365","InstanceId":0,"Type":"app"}
              916a-8051-8fd1721385ae                              {"$id":"3","Address":"213.85","Asset":false,"Roles":["tm"],"Type":"webfile"}
                   8051-8fd1721385ae                     {"$id":"4","Address":"198.137","Asset":false,"Roles":["Contextual"],"Type":"webfile"}
                        8fd1721385ae                             {"$id":"5","AppId":26324,"sId":26324,"Name":"MB","InstanceId":0,"Type":"app"}
                       58a51721385ae                      {"$id":"6","Address":".225.0","Asset":false,"Roles":["Contextual"],"Type":"webfile"}
964fb17e-a352-dbd4-d5b7-374172d811aa                                   {"$id":"2","Name":"AD561-SA","DisplayName":"AD561-SA","Type":"account"}

我想为“类型”创建一个新列来保存值-“webfile”,“app”等。运行下面的代码:

df_test["new_col"]=df_test['Vals'].apply(lambda x: x['Type'] if 'Type' in x else None)

但是出错了

TypeError: list indices must be integers or slices, not str

有人能帮忙吗?

nfs0ujit

nfs0ujit1#

由于您的Vals列包含JSON字符串,因此在提取Type字段之前必须先进行解码:

import json

df_test['Type'] = pd.json_normalize(df_test['Vals'].apply(json.loads))['Type']

输出:

>>> df_test[['Identity', 'Type']]
                               Identity     Type
0                    2fc9d38d-0fe4-c7be  webfile
1                          abd77d57ac29  webfile
2                             c7be-4a37      app
3                916a-8051-8fd1721385ae  webfile
4                     8051-8fd1721385ae  webfile
5                          8fd1721385ae      app
6                         58a51721385ae  webfile
7  964fb17e-a352-dbd4-d5b7-374172d811aa  account

相关问题