2

I am trying to get the following data from the json below, and include it in a Pandas dataframe:

  • email
  • 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

JamesF90
  • 21
  • 1

0 Answers0