Skip to content

Using .drop with Pandas to drop a single row by a value

Have code to send out emails, some rows have the same name of a person to send a email to, but each rows have a unique value. I’m trying to get it to send one email with all the values where there are duplicate cells in the name column with the same name, but all unique values in those rows.

So to this I tried making a duplicate data frame by pulling the the rows with duplicate rows to make it easier for if else statements. Since when I send out emails it sends the same values in different emails multiple times I tried making it so every time it sends out a email it would drop the row of the value it used.

At the end of the code instead of dropping a single row from a single value it drops every row with a value.

import smtplib
import pandas as pd
import ssl
import xlrd

your_name = "Name Here"
your_email = "Email you using here"
your_password = "password to email here"
cc = input("Email of person you want to cc in email: ")

# for gmail change to, 465
server = smtplib.SMTP_SSL('', 587)
server.login(your_email, your_password)

data = [['bob', '', 'howdy'], ['joe', 
'', 'hi'], ['bill', '', 'hey'], 
['bob', '', 'hola'],['bob', '',    
'hello'], ['josh', '', 'yo'], ['austin', 
'', 'cya']

df = pd.DataFrame(data, columns = ['Pending Action From', 'Email', 

all_names = email_list['Pending Action From']
all_emails = email_list['Email']
all_values = email_list['values']

# Takes duplicate row based off same name
duplicateRowsDF = email_list[email_list.duplicated(['Pending Action From'])]
duplicateRowsDF.to_excel('DuplicateQARList.xlsx', index=False)
duplicateRowsDF = pd.read_excel('DuplicateQARList.xlsx')

# removes duplicate row based off same name and keeps first same name
email_list.drop_duplicates(subset='Pending Action From', keep="first", inplace=True)

# email_list.to_excel('TestQARList.xlsx')

duplicate_values = duplicateRowsDF['value']
duplicate_names = duplicateRowsDF['Pending Action From']

 # Create the email to send
def full_email():
    full_email = ("From: {0} <{1}>n"
                  "To: {2} <{3}>n"
                  "Cc: {4}n"
                  "Subject: {5}nn"
                  .format(your_name, your_email, name, email, cc, 
subject, message))
# Create the email to send

# In the email field, you can add multiple other emails if you want
# all of them to receive the same text
    server.sendmail(your_email, [email], full_email)
    print('Email to {} successfully sent!nn'.format(email))
except Exception as e:
    print('Email to {} could not be sent :( because {}nn'.format(email, str(e)))

for idx in range(len(email_list)):

    email = all_emails[idx]
    name = all_names[idx]
    value = all_values[idx]

    for i in range(len(duplicateRowsDF)):

        duplicate_value = duplicate_values[i]
        duplicate_name = duplicate_names[i]

        if name == duplicate_name and value != duplicate_value and duplicate_names[i] == duplicate_names[i]:

            message = value, duplicate_values[i]

            email_list = email_list.drop(value)

# Close the smtp server

Example of data frames made, it takes duplicates with the same name in the pending action from row and puts those rows in another data frame.

It only took two, but its supposed to take the data from all name values that are duplicate. It sends one email same their are duplicates of the same person, but it takes all the separate values for that one person.



In this case, it appears you want to make a list of all row values for each, so rather than process them one-by-one and drop them, you can aggregate them and use pop if you want to remove each, or just operate on the grouped dataframe

df.groupby(['Pending Action From','Email'], as_index=False).agg(list)

    Pending Action From Email   values
0   austin [cya]
1   bill [hey]
2   bob [howdy, hola, hello]
3   joe [hi]
4   josh [yo]
9 People found this is helpful