Unify values in Python PANDAS-CodePudding

I'm an intermediary in Python, and recently I am developing some projects for the company that I work using Pandas to manipulate and automate some spreadsheets in Excel.

One of the spreadsheets contains 4800 values of multiples companies that are owing to my company, and what I need to do with the code is to send an e-mail to the company that are owing with the value that she owes, and some values are out of order, but are from the same company, example below:

COMPANY | VALUE
COMPANY 1 | US$ 1400
COMPANY 2 | US$ 2200
COMPANY 1 | US$ 900
COMPANY 3 | US$ 1500

What I need to do and I'm having issues, is to formulate a code that read those columns even out of order, unify the lines that are from the same company to send an e-mail to her, example below:

 Hello COMPANY 1! You owe US$ 2300, according to these lines below:

    COMPANY 1 | R$ 1400
    COMPANY 1 | R$ 900

Obs: I'm using smtplib to send the e-mails.

The code to send the e-mails automatically I already have, I just need to know how can I make the program read the spreadsheet and get the values of the same company together to send by e-mail, here's my entire code below:

import pandas as pd
import smtplib

list_months = ['Spreadsheet 1']

# localize the columns and lines
owing_clients = pd.read_excel(r'C:\Users\youruserhere\Documents\Owing Clients.xlsx')

# change these as per use
your_email = "email"
your_password = "password"

# establishing connection with gmail
server = smtplib.SMTP_SSL('smtp.gmail.com', 465)
server.ehlo()
server.login(your_email, your_password)

# getting the names and the emails
names = owing_clients['Debtor Name']
emails = owing_clients['Email']
values = owing_clients['Value']
debtorcode1 = owing_clients['Debtor Code']

# iterate through the records
for i in range(len(emails)):

    # mark the name, email and value of the debtor:
    name = names[i]
    email = emails[i]
    value = values[i]
    debtorcode1 = debtorcode[i]

    # the message to be emailed
    message = f"Subject: Debt\nHello {name}, you owe {value}, your debtor code is {debtorcode1}"
    # send email
    server.sendmail(your_email, [email], message)
    print(f'Your e-mail was successfully send!')

# Close the server
server.close()

Here's the spreadsheet for example:

I can execute the entire code and he sends the e-mails, but he sends one by one, and you know that is like the corporate bureaucracy, my company doesn't want one by one, they want me to unify all the values of a single company and send it. Thanks for the help.

CodePudding user response：

Preparing data:

import pandas as pd

data = {

    'Value': [1400, 2200, 900, 1500], 

    'Company': ['C1', 'C2', 'C1', 'C3'],

}

df = pd.DataFrame.from_dict(data)

    Value   Company
0   1400    C1
1   2200    C2
2   900     C1
3   1500    C3

Groupby

df.groupby('Company').sum()

Output

    Value
Company     
C1  2300
C2  2200
C3  1500