Python Pandas loc Iterate DataFrame Email Report

  email, pandas, python, report

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:

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

data = pd.DataFrame({'donor': {0: 111309,
  1: 111309,
  2: 110926,
  3: 110184,
  4: 1942,
  5: 1942,
  6: 110580,
  7: 110580,
  8: 110905,
  9: 110905,
  10: 110361,
  11: 110451,
  12: 110451,
  13: 111270,
  14: 106261,
  15: 109949},
 'name': {0: 'Johnny Appleseed',
  1: 'Johnny Appleseed',
  2: 'Davey Crockett',
  3: 'Daniel Boone',
  4: 'Harriet Tubman',
  5: 'Harriet Tubman',
  6: 'George Washington',
  7: 'George Washington',
  8: 'Abe Lincoln',
  9: 'Abe Lincoln',
  10: 'William Faulkner',
  11: 'Claude Debussy',
  12: 'Claude Debussy',
  13: 'Antonio Vivaldi',
  14: 'Keith Green',
  15: 'Samuel Adams'},
 'date': {0: '1/1/21',
  1: '1/1/21',
  2: '1/1/21',
  3: '1/1/21',
  4: '1/1/21',
  5: '1/1/21',
  6: '1/1/21',
  7: '1/1/21',
  8: '1/1/21',
  9: '1/1/21',
  10: '1/1/21',
  11: '1/1/21',
  12: '1/1/21',
  13: '1/1/21',
  14: '1/1/21',
  15: '1/1/21'},
 'amount': {0: 50,
  1: 150,
  2: 150,
  3: 100,
  4: 100,
  5: 300,
  6: 750,
  7: 250,
  8: 100,
  9: 300,
  10: 250,
  11: 100,
  12: 300,
  13: 200,
  14: 100,
  15: 100},
 'project': {0: 'building fund',
  1: 'special projects',
  2: 'building fund',
  3: 'operational',
  4: 'operational',
  5: 'travel',
  6: 'groups',
  7: 'travel',
  8: 'building fund',
  9: 'building fund',
  10: 'special projects',
  11: 'operational',
  12: 'travel',
  13: 'groups',
  14: 'building fund',
  15: 'travel'}})

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

Sample Report Output

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

Merged Data

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

Source: Python Questions

LEAVE A COMMENT