How to convert dataframe to exact format needed for constant contact API?

Question

I have a dataframe with following data:

  email_addresses first_name last_name
0    [email protected]       test      test
1     [email protected]       Jane       Doe
2   [email protected]       John     Smith

I need to pass this data in the following format into the 'data' parameter of constant contacts API. Format needs to be like this:

update_contact = 
{
    "import_data": [{
        "email_addresses": ["[email protected]"],
        "first_name": "test",
        "last_name": "test"
    }, {
        "email_addresses": ["[email protected]"],
        "first_name": "Jane",
        "last_name": "Doe"
    }, {
        "email_addresses": ["[email protected]"],
        "first_name": "John",
        "last_name": "Smith"
    }],
    "lists": ["1"]
}

As you can see I need to pass in string 'import_data' and 'lists' with square brackets around the 'lists_id'.

I can pass the string while converting the dataframe to json like so:

update_contact = {"import_data" : update_contact.to_json(orient='records'),  "lists" : 1}

But the output is not what I am looking for. It comes out like this:

{'import_data': '[{"\\ufeffemail_addresses":"[email protected]","first_name":"test","last_name":"test"},{"\\ufeffemail_addresses":"[email protected]","first_name":"Jane","last_name":"Doe"},{"\\ufeffemail_addresses":"[email protected]","first_name":"John","last_name":"Smith"}]',
 'lists': 1}

The API I need to pass the 'update_contact' looks like this:

headers = {

        'Authorization': 'Bearer x',
        'X-Originating-Ip': 'x',
        'Content-Type': 'application/json',

        }

r = requests.post('https://api.constantcontact.com/v2/activities/addcontacts?api_key=x', headers=headers ,data = update_contact_now)

How do I get to_json to deliver me JSON that looks like this (for 100s of rows in dataframe)? :

update_contact = 
    {
        "import_data": [{
            "email_addresses": ["[email protected]"],
            "first_name": "test",
            "last_name": "test"
        }, {
            "email_addresses": ["[email protected]"],
            "first_name": "Jane",
            "last_name": "Doe"
        }, {
            "email_addresses": ["[email protected]"],
            "first_name": "John",
            "last_name": "Smith"
        }],
        "lists": ["1"]
    }

Edit:

Ran the line:

update_contact = {"import_data" : update_contact.to_dict(orient='records'),  "lists" : 1}

returned:

{'import_data': [{'first_name': 'test',
   'last_name': 'test',
   '\xef\xbb\xbfemail_addresses': '[email protected]'},
  {'first_name': 'Jane',
   'last_name': 'Doe',
   '\xef\xbb\xbfemail_addresses': '[email protected]'},
  {'first_name': 'John',
   'last_name': 'Smith',
   '\xef\xbb\xbfemail_addresses': '[email protected]'}],
 'lists': 1072830671}

Edit2:

after running lines:

df['email_addresses'] = df['email_addresses'].apply(lambda x: [x])
update_contact = {"import_data" : df.to_dict(orient='records'),  "lists" : [1]}
print (update_contact)

I get the result:

{'import_data': [{u'email_address': '[email protected]',
   u'first_name': u'bob',
   u'last_name': u'bob'},
  {u'email_address': '[email protected]',
   u'first_name': u'tim',
   u'last_name': u'joe'},
  {u'email_address': '[email protected]',
   u'first_name': u'sue1',
   u'last_name': u'sue1'},
  {u'email_address': '[email protected]',
   u'first_name': u'tim3',
   u'last_name': u'test'},
  {u'email_address': '[email protected]',
   u'first_name': u'Paul ',
   u'last_name': u'Ryan'},
  {u'email_address': 'TG_FN_121717@TG_LN_121717.com',
   u'first_name': u'TG_FN_121717',
   u'last_name': u'TG_LN_121717'},
  {u'email_address': 'AS_FN_121717@AS_LN_121717.com',
   u'first_name': u'AS_FN_121717',
   u'last_name': u'AS_LN_121717'},
  {u'email_address': '[email protected]',
   u'first_name': u'Adam',
   u'last_name': u'Apple'},
  {u'email_address': '[email protected]',
   u'first_name': u'Brenda',
   u'last_name': u'Banana'},
  {u'email_address': '[email protected]',
   u'first_name': u'Charlene',
   u'last_name': u'Cherry'},
  {u'email_address': '[email protected]',
   u'first_name': u'Donna',
   u'last_name': u'Daquiri'}],
 'lists': [1]}

Not sure how to drop the 'u'?

Edit3:

tried:

a = json.dumps(update_contact)

got this error:

u'[{"error_key":"json.field.invalid","error_message":"#/import_data/0/email_address: Property was found but is not permitted at this location."},{"error_key":"json.field.missing","error_message":"#/import_data/0/email_addresses: Property is required but not found."},{"error_key":"json.field.invalid","error_message":"#/import_data/1/email_address: Property was found but is not permitted at this location."},{"error_key":"json.field.missing","error_message":"#/import_data/1/email_addresses: Property is required but not found."},{"error_key":"json.field.invalid","error_message":"#/import_data/2/email_address: Property was found but is not permitted at this location."},{"error_key":"json.field.missing","error_message":"#/import_data/2/email_addresses: Property is required but not found."},{"error_key":"json.field.invalid","error_message":"#/import_data/3/email_address: Property was found but is not permitted at this location."},{"error_key":"json.field.missing","error_message":"#/import_data/3/email_addresses: Property is required but not found."},{"error_key":"json.field.invalid","error_message":"#/import_data/4/email_address: Property was found but is not permitted at this location."},{"error_key":"json.field.missing","error_message":"#/import_data/4/email_addresses: Property is required but not found."},{"error_key":"json.field.invalid","error_message":"#/import_data/5/email_address: Property was found but is not permitted at this location."},{"error_key":"json.field.missing","error_message":"#/import_data/5/email_addresses: Property is required but not found."},{"error_key":"json.field.invalid","error_message":"#/import_data/6/email_address: Property was found but is not permitted at this location."},{"error_key":"json.field.missing","error_message":"#/import_data/6/email_addresses: Property is required but not found."},{"error_key":"json.field.invalid","error_message":"#/import_data/7/email_address: Property was found but is not permitted at this location."},{"error_key":"json.field.missing","error_message":"#/import_data/7/email_addresses: Property is required but not found."},{"error_key":"json.field.invalid","error_message":"#/import_data/8/email_address: Property was found but is not permitted at this location."},{"error_key":"json.field.missing","error_message":"#/import_data/8/email_addresses: Property is required but not found."},{"error_key":"json.field.invalid","error_message":"#/import_data/9/email_address: Property was found but is not permitted at this location."},{"error_key":"json.field.missing","error_message":"#/import_data/9/email_addresses: Property is required but not found."},{"error_key":"json.field.invalid","error_message":"#/import_data/10/email_address: Property was found but is not permitted at this location."},{"error_key":"json.field.missing","error_message":"#/import_data/10/email_addresses: Property is required but not found."},{"error_key":"json.field.invalid","error_message":"#/import_data/11/email_address: Property was found but is not permitted at this location."},{"error_key":"json.field.missing","error_message":"#/import_data/11/email_addresses: Property is required but not found."}]'

Final Edit:

These lines of code worked:

update_contact.rename(columns={'email_address': 'email_addresses'}, inplace=True)
    df['email_addresses'] = df['email_addresses'].apply(lambda x: [x])
    update_contact = {"import_data" : df.to_dict(orient='records'),  "lists" : [1]}
    a = json.dumps(update_contact,ensure_ascii=False)
    print (update_contact)

jezrael · Accepted Answer · 2018-03-03T17:58:05.023

1

I think you need to_dict for dictionary and last convert to json:

#remove \xef\xbb\xb
#https://github.com/pandas-dev/pandas/issues/4793
df = pd.read_csv(file, encoding="utf-8-sig")
#df.columns = df.columns.str.strip()

df['email_addresses'] = df['email_addresses'].apply(lambda x: [x])
update_contact = {"import_data" : df.to_dict(orient='records'),  "lists" : [1]}
print (update_contact)
{'import_data': [{'email_addresses': ['[email protected]'], 
                  'first_name': 'test', 
                  'last_name': 'test'}, 
                 {'email_addresses': ['[email protected]'], 
                  'first_name': 'Jane', 
                  'last_name': 'Doe'},
                 {'email_addresses': ['[email protected]'], 
                  'first_name': 'John', 
                  'last_name': 'Smith'}],
  'lists': [1]}

edited Mar 03 '18 at 17:58

answered Mar 03 '18 at 17:36

jezrael

822,522
95
1,334
1,252

Spoke to soon, after clearning memory. I reran your line. see edit for issue: – RustyShackleford Mar 03 '18 at 17:38
the issue is also I need to put square brackets around the email addresses and list_ids – RustyShackleford Mar 03 '18 at 17:45
your answer brings me very close, how do I drop the 'u' in the edit2 I made above? – RustyShackleford Mar 03 '18 at 18:06
@RustyShackleford - I think need [this](https://stackoverflow.com/a/18337754/2901002) – jezrael Mar 03 '18 at 18:12
literally just tried that line and passed into API got error in edit: – RustyShackleford Mar 03 '18 at 18:12
do you think the error in the edit, could be because of placement of the column headers? – RustyShackleford Mar 03 '18 at 18:14
tried your link, but getting same error. I think it has to do with placement – RustyShackleford Mar 03 '18 at 18:16
@RustyShackleford What about `json.dumps(update_contact, ensure_ascii=False)` ? – jezrael Mar 03 '18 at 18:20
yep exactly that line. I also inserted the missing email address and got same error message – RustyShackleford Mar 03 '18 at 18:27
@RustyShackleford - Not easy for me, because cannot test. the simpliest should be switch to `python 3`. – jezrael Mar 03 '18 at 18:30
the issues was I had to convert 'email_address' to 'email_addresses' and it worked! – RustyShackleford Mar 03 '18 at 19:26

How to convert dataframe to exact format needed for constant contact API?

1 Answers1