How to send parts of dataframe to corresponding email that is in a same dataframe

Question

I have one dataframe that contain column email and other columns.

I need to send this data to corresponding email, for example line 0 need to be emailed to Tony@mail.com, line 1 need to be emailed to Sergio@mail.com and line 2 and 3 need to be send to Nico@mail.com

     Name            Email      Subject CreatedDate     DueDate FinalDeadLine
0    Tony    Tony@mail.com      Renewal  2019-12-15  2019-12-16    2019-12-25
1  Sergio  Sergio@mail.com  NewBusiness  2019-11-18  2019-11-22    2019-11-28
2    Nico    Nico@mail.com  Endorsement  2019-12-11  2019-12-13    2019-12-24
3    Nico    Nico@mail.com      Rewrite  2019-12-05  2019-12-07    2019-12-23

Using Splitting dataframe into multiple dataframes I am performing a view on a dataframe:

Example code:

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import pandas as pd

df = pd.DataFrame({
                'Name': ['Tony','Sergio','Nico','Nico']
                ,'Email': ['Tony@mail.com', 'Sergio@mail.com','Nico@mail.com','Nico@mail.com']
                ,'Subject':['Renewal', 'NewBusiness', 'Endorsement','Rewrite']
                ,'CreatedDate': ['2019-12-15','2019-11-18','2019-12-11','2019-12-05']
                ,'DueDate': ['2019-12-16','2019-11-22','2019-12-13','2019-12-07']
                ,'FinalDeadLine': ['2019-12-25','2019-11-28','2019-12-24','2019-12-23']
                })
print(df)

# sort the dataframe
# the parameter axis=1 refer to columns, while 0 refers to rows
df.sort_values(by='Email', axis=0, inplace=True)

# set the index to be this and don't drop
df.set_index(keys=['Email'], drop=False,inplace=True)

# get a list of emails
email_list=df['Email'].unique().tolist()

# now we can perform a lookup on a 'view' of the dataframe
nico = df.loc[df.Email=='Nico@mail.com']

# itrating through email_list and printing dataframe corresponding to each email
for e in email_list:
  d = df.loc[df.Email==e]
  #print(d)

But then how can I connect this to my send_mail function?

send_mail function:

user = "myemail@gmail.com"
pwd = "mypassword"
subject = "Test subject"
recipients = "recipients@gmail.com"

def send_email(user,pwd, recipients, subject):
    try:
        df_html = df.to_html()
        dfPart = MIMEText(df_html,'html')
    #Container
        msg = MIMEMultipart('alternative')
        msg['Subject'] = subject
        msg['From'] = user
        msg['To'] = ",".join(recipients)
        msg.attach(dfPart)

        server = smtplib.SMTP('smtp.gmail.com: 587')
        server.starttls()
        server.login(user, pwd)

        server.sendmail(user, recipients, msg.as_string())
        server.close()
        print("Mail sent succesfully!")
    except Exception as e:
        print(str(e))
        print("Failed to send email")
send_email(user,pwd,recipients,"Test Subject")

Or maybe there is a better and most efficient way of doing all this? Any good examples available online?

Yatish Kadam · Answer 1 · 2019-12-27T18:32:29.107

0

So once you are done with the manipulation of the dataframe.. you can use the apply function to do row wise manipulation using

axis = 1

an example of this is below..

import pandas as pd


df = pd.DataFrame()
df["A"] = [1,2,3,4,5]
df["B"] = ["A", "B", "C", "D", "E"]

# print (df)

def printA(d):
    print("row A", d["A"], "row B", d["B"])

df.apply(lambda x:printA(x), axis=1)

row A 1 row B A
row A 2 row B B
row A 3 row B C
row A 4 row B D
row A 5 row B E

coming to your code. modify the email function to accept a row as the input.

def send_email(user,pwd, recipients, subject):

would change to

def send_email(dataFrameRow):

then inside that function you can access each of the columns using the same columns names ..

dataFrameRow["Name"] , dataFrameRow["Subject"], dataFrameRow["Email"]

calling the function would be as simple as.. you can call this after sorting or after the set_index.. I am not really sure what your trying to achieve there.. but this should work for your code.

df.apply(lambda x: send_email(x), axis=1)

edited Dec 27 '19 at 18:32

answered Dec 27 '19 at 18:08

Yatish Kadam

454
2
11

@Serdia let me know if you want me to elaborate on your particular code.. or you can manage from here.. – Yatish Kadam Dec 27 '19 at 18:10
Thanks, but sorry, how and where would I implement your solution towards my question? – Serdia Dec 27 '19 at 18:19
@Serdia you need to send the email to ever email id right? – Yatish Kadam Dec 27 '19 at 18:20
Yes, I need to send an email with corresponding data in a dataframe to each email id – Serdia Dec 27 '19 at 18:22
you can probably call the function after the set index.. i dont see anything usefull being done after that.. – Yatish Kadam Dec 27 '19 at 18:24
ill add it to the above code.. Can i know where you are getting the password from? i dont see it in the dataframe – Yatish Kadam Dec 27 '19 at 18:24
I did not post my password here. It is confidential – Serdia Dec 28 '19 at 00:35

score 0 · Answer 2 · answered Dec 27 '21 at 03:50

    from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders
from smtplib import SMTP
import smtplib
import sys
import pandas as pd

df = pd.DataFrame({
                'Name': ['Tony','Sergio','Nico','Nico']
                ,'Email': ['Tony@mail.com', 'Sergio@mail.com','Nico@mail.com','Nico@mail.com']
                ,'Subject':['Renewal', 'NewBusiness', 'Endorsement','Rewrite']
                ,'CreatedDate': ['2019-12-15','2019-11-18','2019-12-11','2019-12-05']
                ,'DueDate': ['2019-12-16','2019-11-22','2019-12-13','2019-12-07']
                ,'FinalDeadLine': ['2019-12-25','2019-11-28','2019-12-24','2019-12-23']
                })
print(df)

df.sort_values(by='Email', axis=0, inplace=True)

df.set_index(keys=['Email'], drop=False,inplace=True)

email_list=df['Email'].unique().tolist()

for e in email_list:
    d = df.loc[df.Email==e]
    d.to_csv('Test.csv')
    
    email_user = 'mymail@gmail.com'
    email_password = 'XXXXXXXXXX'
    
    col_one_list = [e]
    recipients = col_one_list
    
    emaillist = [elem.strip().split(',') for elem in recipients]
    msg = MIMEMultipart()
    
    msg['Subject'] = 'Test'
    msg['From'] = 'mymail@gmail.com'
    
    filename = "Test.csv"
    attachment = open("Test.csv","rb")
    part = MIMEBase('application', 'octet-stream')
    part.set_payload((attachment).read())
    encoders.encode_base64(part)
    part.add_header('Content-Disposition',"attachment; filename=%s" % filename)
    
    msg.attach(part)
    
    server = smtplib.SMTP('smtp.gmail.com: 587')
    server.starttls()
    server.login(email_user,email_password)
    server.sendmail(msg['From'], emaillist , msg.as_string())
    server.quit()
    
print('Done')

How to send parts of dataframe to corresponding email that is in a same dataframe

2 Answers2