I am trying to get the following data from the json below, and include it in a Pandas dataframe:
- Referrer URL
The challenge I have is that 'email' is nested within the value of form fields in attributes, and referrer URL is nested in attributes too.
Apologies if this is a strange question, I am pretty new to Python. Ne
{'id': 14115353751810,
'toolGUID': '14115353751810',
'leadId': 2774S2598290580983086,
'activityDate': '2021-07-01T01:09:21Z',
'activityTypeId': 2,
'primaryAttributeValueId': 12345,
'primaryAttributeValue': 'exampleform2',
'attributes': [{'name': 'Client IP Address', 'value': '----'},
{'name': 'Form Fields',
'value': 'a:34:{s:6:"module";s:11:"leadCapture";s:6:"action";s:5:"save2";s:9:"FirstName";s:7:"examplefirst";s:8:"examplelast";s:3:"Guo";s:5:"Email";s:23:"example@example.com";s:7:"Company";s:14:"Example Co";s:7:"Country";s:5:"United States";s:10:"emailValid";s:2:"no";s:5:"segment";s:15:"Sport";s:5:"utmcampaign";s:0:"";s:10:"utmcontent";s:0:"";s:9:"utmmedium";s:0:"";s:9:"utmsource";s:0:"";s:7:"utmterm";s:0:"";s:12:"formLanguage";s:2:"en";}'},
{'name': 'Query Parameters', 'value': ''},
{'name': 'Referrer URL',
'value': 'https://example.com'},
{'name': 'User Agent',
'value': 'Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko'},
{'name': 'Webpage ID', 'value': 1168250}]}
Below is my code. Is Json normalize the right direction?
import pandas as pd
from pandas.io.json import json_normalize
import json # The module we need to decode JSON
act = mc.execute(method='get_lead_activities', activityTypeIds=['2'], nextPageToken=None,
sinceDatetime='2021-07-01', untilDatetime='2021-07-02',
batchSize=None, listId=None,)
df = pd.json_normalize(act, 'attributes', ['id','primaryAttributeValueId','primaryAttributeValue','activityDate'],
record_prefix='attributes_')
print(df)
Thank you James