python-2.7pandasdataframeexacttarget

How to iterate over multiple lists with different values but same variables while placing all values in one Pandas DataFrame


Sorry for the long title, didn't know how to ask it:

I am working with ExactTarget Salesforce Marketing API, trying to iterate over multiple dictionary objects from the API call but some of them are nested and have the same name as the other API responses and I am getting confused on how to iterate over the same named variables into a dataframe.

This is the output of the API Call:

(ClickEvent){
   Client = 
      (ClientID){
         ID = 11111111
      }
   PartnerKey = None
   CreatedDate = 2016-07-12 00:40:17
   ModifiedDate = 2016-07-12 00:40:17
   ID = 11111111
   ObjectID = "11111111"
   SendID = 11111111
   SubscriberKey = "azfull@usa.net"
   EventDate = 2016-07-12 00:40:17
   EventType = "Click"
   TriggeredSendDefinitionObjectID = None
   BatchID = 1
   URLID = 11111111
   URL = aaa.com

I want to create a separate dataframe column for the "ID" under "ClientID" but I am running into the trouble of another variable already being named "ID". How can I iterate over "ClientID" and get the ID value plus also get the other values and place them in the dataframe?

My code has been able to place the data in the dataframe but I am not getting the particular Client ID. this is what output looks like now:

     BatchID                           ClientID         CreatedDate  \
0          1  (ClientID){\n   ID = 10914162\n } 2016-02-23 13:08:59   
1          1  (ClientID){\n   ID = 10914162\n } 2016-02-23 13:11:49   

As you can see only want the ID number not the other garbage under "ClientID"

Code:

import ET_Client
import pandas as pd



try:
    debug = False
    stubObj = ET_Client.ET_Client(False, debug)

    ## Modify the date below to reduce the number of results returned from the request
    ## Setting this too far in the past could result in a very large response size
    retrieveDate = '2016-07-11T13:00:00.000'

    #ET call for clicks
    print '>>>ClickEvents'
    getClickEvent = ET_Client.ET_ClickEvent()
    getClickEvent.auth_stub = stubObj   
    getResponse = getClickEvent.get()
    ResponseResults = getResponse.results
    #print ResponseResults

    Client = []
    partner_keys = []
    created_dates = []
    modified_date = []
    ID = []
    ObjectID = []
    SendID = []
    SubscriberKey = []
    EventDate = []
    EventType = []
    TriggeredSendDefinitionObjectID = []
    BatchID = []
    URLID = []
    URL = []



    for ClickEvent in ResponseResults:
        Client.append(str(ClickEvent['Client']))
        partner_keys.append(ClickEvent['PartnerKey'])
        created_dates.append(ClickEvent['CreatedDate'])
        modified_date.append(ClickEvent['ModifiedDate'])
        ID.append(ClickEvent['ID'])
        ObjectID.append(ClickEvent['ObjectID'])
        SendID.append(ClickEvent['SendID'])
        SubscriberKey.append(ClickEvent['SubscriberKey'])
        EventDate.append(ClickEvent['EventDate'])
        EventType.append(ClickEvent['EventType'])
        TriggeredSendDefinitionObjectID.append('TriggeredSendDefinitionObjectID')
        BatchID.append(ClickEvent['BatchID'])
        URLID.append(ClickEvent['URLID'])
        URL.append(ClickEvent['URL'])


    df = pd.DataFrame({'ClientID': Client, 'PartnerKey': partner_keys,
                   'CreatedDate' : created_dates, 'ModifiedDate': modified_date, 
                   'ID':ID, 'ObjectID': ObjectID,'SendID':SendID,'SubscriberKey':SubscriberKey,
                   'EventDate':EventDate,'EventType':EventType,'TriggeredSendDefinitionObjectID':TriggeredSendDefinitionObjectID,
                   'BatchID':BatchID,'URLID':URLID,'URL':URL})

    print df  

I have been trying this solution but not working:

for ClickEvent in ResponseResults():
    if 'ClientID' in ClickEvent:
        ID.append(ClickEvent['Client']:
            print Client

Thank you in advance.

-EDIT- The output of the API call above is exactly how the systems outputs it, how should I make it an actual JSON response?

Data frame I want to look like this:

BatchID                           ClientID         CreatedDate  \
0          1                           111111111     2016-02-23 13:08:59   
1          1                           111111111     2016-02-23 13:11:49

Just dont want other stuff in the "ClientID" portion of the data I submitted above. Hope this helps.


Solution

  • Instead of appending the entire Client object to your list :

    Client.append(str(ClickEvent['Client']))
    

    Have you tried storing just the ID field of the object? Maybe something like:

    Client.append(str(ClickEvent['Client']['ID']))