Python Pandas loc Iterate DataFrame Email Report

Question

Current Situation: A spreadsheet/CSV is downloaded from the source. This sheet includes: [donor, name, date, amount, project] Different employees receive different project reports via email.

Steps:

1. Download report
2. Filter report by project
3. Email report to the responsible employee

This is a time-consuming, manual process.

I've played around with Python/Pandas and I'm starting to get it figured-out but I'm not sure how to loop through/iterate over the data to prepare the report. I then need to email it to the responsible person.

Sample Data:

ed = {'code': ['1000-1', '2000-3', '3000-1', '2500-2', '3500-1'], 'project': ['building fund', 'special projects', 'operational', 'travel', 'groups'], 'email': ['[email protected]', '[email protected]', '[email protected]', '[email protected]', 'fake@[email protected]']}
email = pd.DataFrame(ed)

d = {'donor': [111309, 111309, 110926, 110184, 1942, 1942, 110580, 110580, 110905, 110905, 110361, 110451, 110451, 111270, 106261, 109949], 'name': ['Johnny Appleseed', 'Johnny Appleseed', 'Davey Crockett', 'Daniel Boone', 'Harriet Tubman', 'Harriet Tubman', 'George Washington', 'George Washington', 'Abe Lincoln', 'Abe Lincoln', 'William Faulkner', 'Claude Debussy', 'Claude Debussy', 'Antonio Vivaldi', 'Keith Green', 'Samuel Adams'], 'date': ['1/1/21', '1/1/21', '1/1/21', '1/1/21', '1/1/21', '1/1/21', '1/1/21', '1/1/21', '1/1/21', '1/1/21', '1/1/21', '1/1/21', '1/1/21', '1/1/21', '1/1/21', '1/1/21'], 'amount': [50, 150, 150, 100, 100, 300, 750, 250, 100, 300, 250, 100, 300, 200, 100, 100], 'project': ['building fund', 'special projects', 'building fund', 'operational', 'operational', 'travel', 'groups', 'travel', 'building fund', 'building fund', 'special projects', 'operational', 'travel', 'groups', 'building fund', 'travel']}
data = pd.DataFrame(d)

Close to what I want to email (minus the index/row count):

I think I need to merge the two DFs so the email is joined to the data -- so I did this:

I will eventually need to run this and have it email the filtered report to the individual person responsible for it.

I don't want all the answers for the email (I am trying to learn this) so maybe a point in the right direction (library to use, basic steps, etc.)

Thanks!

John

Answer 1

It's easier to show an example, then you can decide if using groups as I'll show is better than say writing a function and using .apply() or groupby().apply() See comments inline.

merged = data.merge(email.reset_index(), how='left', on='project')

#group on project
df_grouped = merged.groupby(['project'])

#iterate through groups
for gkey in list(df_grouped.groups.keys()):
    # print(df_grouped.get_group(gkey))
    dft = df_grouped.get_group(gkey)
    recips = dft.email.unique().tolist() # list of recipients if there is more than one email per project
    # if only one recipient, can use
    recip = dft.email.iloc[0]
    print(recips, recip) # note one is list the other is a single value
    # do something with dataframe to prepare for sending via email
    # example: 
    # this puts the df in csv format or you can just write csv to file and email file
    csv_buffer = io.StringIO()
    dft.to_csv(csv_buffer)
    txt = csv_buffer.getvalue()
    print(txt)