I have a dataframe with following data:
email_addresses first_name last_name
0 test@test.com test test
1 Jane@Doe.com Jane Doe
2 John@Smith.com 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": ["test@test.com"],
"first_name": "test",
"last_name": "test"
}, {
"email_addresses": ["Jane@Doe.com"],
"first_name": "Jane",
"last_name": "Doe"
}, {
"email_addresses": ["John@Smith.com"],
"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":"test@test.com","first_name":"test","last_name":"test"},{"\\ufeffemail_addresses":"Jane@Doe.com","first_name":"Jane","last_name":"Doe"},{"\\ufeffemail_addresses":"John@Smith.com","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": ["test@test.com"],
"first_name": "test",
"last_name": "test"
}, {
"email_addresses": ["Jane@Doe.com"],
"first_name": "Jane",
"last_name": "Doe"
}, {
"email_addresses": ["John@Smith.com"],
"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': 'test@test.com'},
{'first_name': 'Jane',
'last_name': 'Doe',
'\xef\xbb\xbfemail_addresses': 'Jane@Doe.com'},
{'first_name': 'John',
'last_name': 'Smith',
'\xef\xbb\xbfemail_addresses': 'John@Smith.com'}],
'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': 'bob.bob19955@gmail.com',
u'first_name': u'bob',
u'last_name': u'bob'},
{u'email_address': 'tim@joe.com',
u'first_name': u'tim',
u'last_name': u'joe'},
{u'email_address': 'sue1@sue1.com',
u'first_name': u'sue1',
u'last_name': u'sue1'},
{u'email_address': 'tim3@test.com',
u'first_name': u'tim3',
u'last_name': u'test'},
{u'email_address': 'paul@ryan.com',
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': 'adamapple000@mailinator.com',
u'first_name': u'Adam',
u'last_name': u'Apple'},
{u'email_address': 'brendabanana000@mailinator.com',
u'first_name': u'Brenda',
u'last_name': u'Banana'},
{u'email_address': 'charlenecherry000@mailinator.com',
u'first_name': u'Charlene',
u'last_name': u'Cherry'},
{u'email_address': 'donnadaquiri000@mailinator.com',
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)
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': ['test@test.com'],
'first_name': 'test',
'last_name': 'test'},
{'email_addresses': ['Jane@Doe.com'],
'first_name': 'Jane',
'last_name': 'Doe'},
{'email_addresses': ['John@Smith.com'],
'first_name': 'John',
'last_name': 'Smith'}],
'lists': [1]}