Skip to content
Advertisement

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 smtp-mail.outlook.com to smtp.gmail.com, 465
server = smtplib.SMTP_SSL('smtp-mail.outlook.com', 587)
server.ehlo()
server.login(your_email, your_password)

data = [['bob', 'testemail@gmail.com', 'howdy'], ['joe', 
'testemail@gmail.com', 'hi'], ['bill', 'testemail@gmail.com', 'hey'], 
['bob', 'testemail@gmail.com', 'hola'],['bob', 'testemail@gmail.com',    
'hello'], ['josh', 'testemail@gmail.com', 'yo'], ['austin', 
'testemail@gmail.com', 'cya']

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



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"
                  "{6}"
                  .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
try:
    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]

            full_email()
            email_list = email_list.drop(value)



# Close the smtp server
server.close()

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.

Advertisement

Answer

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  testemail@gmail.com [cya]
1   bill    testemail@gmail.com [hey]
2   bob testemail@gmail.com [howdy, hola, hello]
3   joe testemail@gmail.com [hi]
4   josh    testemail@gmail.com [yo]
9 People found this is helpful
Advertisement