Accessing column of a list of nested json in a python dataframe

Question

  Identifier Properties  
   1       [{"$id":"2","SMName":"pia.redoabs.com","Type":"sms"},{"$id":"3","Name":"_18_Lucene41_0.doc","Type":"file"}]
   2       [{"$id":"2","SMName":"pred.redocad.com","Type":"sms"},{"$id":"3","Name":"_18_Nil41_0.doc","Type":"file"}]
   3      [{"$id":"2","SMName":"promomaster.com","Type":"sms"},{"$id":"3","Name":"_17_Litre41_0.doc","Type":"file"}]
   4      [{"$id":"2","SMName":"admaster.com","Type":"sms"},{"$id":"3","Name":"_k.pos","Type":"file"}]
   5      [{"$id":"2","SMName":"plan.com.com","Type":"sms"},{"$id":"3","Name":"_3_Lucene41_0.doc","Type":"file"}]
   6     [{"$id":"2","Name":"jm460","ETNDomain":"ent.ad.ent","Sid":"S-1-5-21-117609710-2111687655-839522115-432193","AadUserId":"7133971dffgh5r-b9b8-4af3-bbfd-85b1b56d1f6f","IsDomainJoined":true,"Type":"account","UserPrincipalName":"jmjklo460@ent.com"},{"$id":"3","Directory":"C:\\CR\\new_cbest_malware","Name":"ent_Survey.zip","hash":[{"$id":"4","Algorithm":"hsa1","Value":"cecce931f21697876efc80f5897a31481c396795","Type":"hash"},{"$id":"5","Algorithm":"MI5","Value":"12c216630a5f24faab06d463c9ce72a5","Type":"hash"},{"$id":"6","Algorithm":"TM345","Value":"cbb327b70a83fefeaf744458f2ed62021e529ce0ece36566761779f17d4c07a6","Type":"hash"}],"CreatedTimeUtc":"2022-08-22T17:42:02.4272869Z","Type":"file"},{"$ref":"4"},{"$ref":"5"},{"$ref":"6"},{"$id":"7","ProcessId":"54884","CommandLine":"\"7zG.exe\" a -i#7zMap23807:40278:7zEvent24942 -ad -saa -- \"C:\\CR\\CR_2\"","ElevationToken":"Default","CreationTimeUtc":"2022-10-03T17:59:35.2339055Z","ImageFile":{"$id":"8","Directory":"C:\\Program Files\\7-Zip","Name":"9zG.exe","FileHashes":[{"$id":"9","Algorithm":"HSA2","Value":"df22612647e9404a515d48ebad490349685250de","Type":"hash"},{"$id":"10","Algorithm":"MI5","Value":"04fb3ae7f05c8bc333125972ba907398","Type":"hash"},{"$id":"11","Algorithm":"hsa1","Value":"2fb898bacb587f2484c9c4aa6da2729079d93d1f923a017bb84beef87bf74fef","Type":"hash"}],"CreatedTimeUtc":"2020-09-21T16:34:33.1299959Z","Type":"file"},"ParentProcess":{"$id":"12","ProcessId":"13516","CreationTimeUtc":"2022-09-21T12:41:32.4609401Z","CreatedTimeUtc":"2022-09-21T12:41:32.4609401Z","Type":"process"},"CreatedTimeUtc":"2022-10-03T17:59:35.2339055Z","Type":"process"},{"$ref":"12"},{"$ref":"8"},{"$ref":"9"},{"$ref":"10"},{"$ref":"11"},{"$id":"13","DnsDomain":"ent.ad.ent.com","HostName":"ilch-l788441","OSFamily":"Windows","OSVersion":"20H2","Tags":[{"ProviderName":"tmdp","TagId":"VwanPov","TagName":"VwanPov","TagType":"UserDefined"},{"ProviderName":"dmpt","TagId":"Proxy Allow Personal Storage","TagName":"Proxy Allow Personal Storage","TagType":"UserDefined"},{"ProviderName":"dmpt","TagId":"Proxy Allow Webmail","TagName":"Proxy Allow Webmail","TagType":"UserDefined"},{"ProviderName":"dmpt","TagId":"proxy-allow-social-media","TagName":"proxy-allow-social-media","TagType":"UserDefined"}],"Type":"host","dmptDeviceId":"fa52ff90ab60ee6eac86ec60ed2ac748a33e29fa","FQDN":"ilch-567.ent.ad.ent.com","AadDeviceId":"e1d59b69-dd3f-4f33-96b5-db9233654c16","RiskScore":"Medium","HealthStatus":"Active","LastSeen":"2022-10-03T18:09:32.7812655","LastExternalIpAddress":"208.95.144.39","LastIpAddress":"10.14.126.52","AvStatus":"Updated","OnboardingStatus":"Onboarded","LoggedOnUsers":[{"AccountName":"jmjklo460","DomainName":"ENT"}]}]

This is a dataframe with 2 columns "Identifier" & "Properties". The "Properties" column appears as a list of json.The aim is to create different columns for "sms" , "file" ,"ETNDomain" ,"UserPrincipalName" etc. Not all the rows have same information as it is seen above. The first 5 rows are similar while the 6th row is different Can we make the code dynamic to be able to extract any values ? Further I used kql to parse this data & it was relatively straightforward. But having little/no experience with json it would be great if someone can help ?

Answer 1

You can use something like that. However, since there are many different keys in the json data, most rows will be nan.

import numpy as np
import ast

df['Properties']=df['Properties'].astype(str)
df['Properties']=df['Properties'].apply(lambda x: ast.literal_eval(x) if x != 'nan' else np.nan)

df = df.explode('Properties') #df is your dataframe.
final = df[['Identifier']].join(pd.json_normalize(df['Properties']))

'''
|    |   Identifier |   $id | SMName           | Type   | Name               |   ETNDomain |   Sid |   AadUserId |   IsDomainJoined |   UserPrincipalName |   Directory |   hash |   CreatedTimeUtc |   $ref |   ProcessId |   CommandLine |   ElevationToken |   CreationTimeUtc |   ImageFile.$id |   ImageFile.Directory |   ImageFile.Name |   ImageFile.FileHashes |   ImageFile.CreatedTimeUtc |   ImageFile.Type |   ParentProcess.$id |   ParentProcess.ProcessId |   ParentProcess.CreationTimeUtc |   ParentProcess.CreatedTimeUtc |   ParentProcess.Type |   DnsDomain |   HostName |   OSFamily |   OSVersion |   Tags |   dmptDeviceId |   FQDN |   AadDeviceId |   RiskScore |   HealthStatus |   LastSeen |   LastExternalIpAddress |   LastIpAddress |   AvStatus |   OnboardingStatus |   LoggedOnUsers |
|---:|-------------:|------:|:-----------------|:-------|:-------------------|------------:|------:|------------:|-----------------:|--------------------:|------------:|-------:|-----------------:|-------:|------------:|--------------:|-----------------:|------------------:|----------------:|----------------------:|-----------------:|-----------------------:|---------------------------:|-----------------:|--------------------:|--------------------------:|--------------------------------:|-------------------------------:|---------------------:|------------:|-----------:|-----------:|------------:|-------:|---------------:|-------:|--------------:|------------:|---------------:|-----------:|------------------------:|----------------:|-----------:|-------------------:|----------------:|
|  0 |            1 |     2 | pia.redoabs.com  | sms    | nan                |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  0 |            1 |     2 | pia.redoabs.com  | sms    | nan                |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  1 |            2 |     3 | nan              | file   | _18_Lucene41_0.doc |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  1 |            2 |     3 | nan              | file   | _18_Lucene41_0.doc |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  2 |            3 |     2 | pred.redocad.com | sms    | nan                |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  2 |            3 |     2 | pred.redocad.com | sms    | nan                |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  3 |            4 |     3 | nan              | file   | _18_Nil41_0.doc    |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  3 |            4 |     3 | nan              | file   | _18_Nil41_0.doc    |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  4 |            5 |     2 | promomaster.com  | sms    | nan                |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  4 |            5 |     2 | promomaster.com  | sms    | nan                |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  5 |            6 |     3 | nan              | file   | _17_Litre41_0.doc  |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  5 |            6 |     3 | nan              | file   | _17_Litre41_0.doc  |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  5 |            6 |     3 | nan              | file   | _17_Litre41_0.doc  |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  5 |            6 |     3 | nan              | file   | _17_Litre41_0.doc  |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  5 |            6 |     3 | nan              | file   | _17_Litre41_0.doc  |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  5 |            6 |     3 | nan              | file   | _17_Litre41_0.doc  |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  5 |            6 |     3 | nan              | file   | _17_Litre41_0.doc  |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  5 |            6 |     3 | nan              | file   | _17_Litre41_0.doc  |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  5 |            6 |     3 | nan              | file   | _17_Litre41_0.doc  |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  5 |            6 |     3 | nan              | file   | _17_Litre41_0.doc  |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  5 |            6 |     3 | nan              | file   | _17_Litre41_0.doc  |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
|  5 |            6 |     3 | nan              | file   | _17_Litre41_0.doc  |         nan |   nan |         nan |              nan |                 nan |         nan |    nan |              nan |    nan |         nan |           nan |              nan |               nan |             nan |                   nan |              nan |                    nan |                        nan |              nan |                 nan |                       nan |                             nan |                            nan |                  nan |         nan |        nan |        nan |         nan |    nan |            nan |    nan |           nan |         nan |            nan |        nan |                     nan |             nan |        nan |                nan |             nan |
'''