Python or Powershell -- reading csv and sorting and gathering data to be sent in an email

user_6764418 0
Viewed 93

Example csv below to describe what I'm looking at doing

ID,Name,SupervisorName,SupervisorEmail,Action,Column5,Column6,Column7
123,Tim Jones,Sam Burk,sburk@somerandom.com,2,data,data,data
124,Mark Smith,Sam Burk,sburk@somerandom.com,2,data,data,data
125,Jill Jones,Tim Jones,tjones@somerandom.com,2,data,data,data
126,Sam Harris,Sam Burk,sburk@somerandom.com,3,data,data,data
127,Jana Lester,Lisa Jones,ljones@somerandom.com,2,data,data,data

I'm wanting to gather the data where the Action is 2 and the supervisor email is all the same and then send an email to the supervisor with the row data for each account they are associated with implying that an Action 2 event has been triggered.

Email being sent is based on the action. Need to group all of the rows for a given Supervisor's email for the given action and send one email per supervisor with multiple row data matching action 2.

It is a CSV of about 30k rows and each supervisor may have 5+ accounts that have a given event.

I want the output in the email to be something like:

Dear Sam Burk,

The below accounts have had the Action 2 performed on them. Please contact us with any concerns.

ID    Name         Action
123   Tim Jones     2
124   Mark Smith    2
126   Sam Harris    2

Regards,
Corporate Events

I'm assuming I would need a unique list of all supervisor emails that have the action of 2 associated to them. Any example code to do this?

I'm then assuming I would use the list and do a for each type statement to then gather all of the rows for the given supervisor with action 2 for the specific columns I need. Then compose the email with that data, send the email then go to the next one in the list... Any example code on how I would do this?

Do I use pandas to sort this? What is a good import / library to send emails to a SMTP gateway that requires authentication?

Powershell may be an option as well.

Thanks, Fred

csv powershell sorting email python
2 Answers

A PowerShell solution that uses Import-Csv and Group-Object and Send-MailMessage:

Note:

  • Send-MailMessage is used for simplicity, because it comes with PowerShell, but it is considered obsolete, because it does not guarantee secure connections to SMTP servers.

  • An official suggestion is to use the third-party MailKit library instead; this article has background information.
    A different third party has created a PowerShell wrapper for this library: Send-MailKitMessage.

# Fill in the appropriate values for your environment here.
$csvFile = 'file.csv' 
$smtpServer = 'smtp.example.org'
$senderEmail = 'admin@example.org'

Import-Csv $csvFile | Group-Object SupervisorEmail | ForEach-Object {

  # Compose the email body.
  $body = @"
Dear $($_.Group[0].SupervisorName)

The below accounts have had the Action 2 performed on them. Please contact us with any concerns.

$($_.Group | Select-Object ID, Name, Action | Out-String)

Regards,
Corporate Events
"@

  # Uncomment the following line to print the value of $body for diagnostic purposes:
  # Write-Verbose -Verbose $body

  # Define the parameters for the command that sends the email...
  $params = @{
    SmtpServer = smtpServer
    From = $senderEmail
    To = $_.Group[0].SupervisorEmail
    Subject = 'Action 2 Notification'
    Body = $body
  }

  # ... and send it.
  Send-MailMessage @params

}
user_453750

The OP asks two questions; one about retrieving data in a dataset using pandas, and one about sending emails.

Here is an answer to the first question.

Since I am not sure I interpret your question entirely the way you intended it, I provide two possible solutions.

First solution

For every action action, for every supervisor s, send an email to supervisor s with all rows matching action action.

import pandas as pd

df = pd.read_csv('emaildata.csv', header=0)
#     ID         Name SupervisorName  ... Column5  Column6 Column7
# 0  123    Tim Jones       Sam Burk  ...    data     data    data
# 1  124   Mark Smith       Sam Burk  ...    data     data    data
# 2  125   Jill Jones      Tim Jones  ...    data     data    data
# 3  126   Sam Harris       Sam Burk  ...    data     data    data
# 4  127  Jana Lester     Lisa Jones  ...    data     data    data

actions = set(df['Action'])
# {2, 3}

for action in actions:
  dfa = df[df['Action'] == action]
  email_partial_body = 'The below accounts have had the Action {} performed on them. Please contact us with any concerns.\n\n'.format(action) + str(dfa) + '\n\nRegards,\nCorporate Events'
  for i,row in  dfa.drop_duplicates(subset=['SupervisorEmail']).iterrows():
    email_full_body = ''.join(['Dear ', row['SupervisorName'], ',\n\n', email_partial_body])
    email_to = row['SupervisorEmail']
    send_email(email_to, email_full_body)

Testing using a dummy function send_email(to, body), the above code produces the following 4 emails:

====EMAIL====
TO: sburk@somerandom.com
====
Dear Sam Burk,

The below accounts have had the Action 2 performed on them. Please contact us with any concerns.

    ID         Name SupervisorName        SupervisorEmail  Action Column5 Column6 Column7
0  123    Tim Jones       Sam Burk   sburk@somerandom.com       2    data    data    data
1  124   Mark Smith       Sam Burk   sburk@somerandom.com       2    data    data    data
2  125   Jill Jones      Tim Jones  tjones@somerandom.com       2    data    data    data
4  127  Jana Lester     Lisa Jones  ljones@somerandom.com       2    data    data    data

Regards,
Corporate Events
=============

====EMAIL====
TO: tjones@somerandom.com
====
Dear Tim Jones,

The below accounts have had the Action 2 performed on them. Please contact us with any concerns.

    ID         Name SupervisorName        SupervisorEmail  Action Column5 Column6 Column7
0  123    Tim Jones       Sam Burk   sburk@somerandom.com       2    data    data    data
1  124   Mark Smith       Sam Burk   sburk@somerandom.com       2    data    data    data
2  125   Jill Jones      Tim Jones  tjones@somerandom.com       2    data    data    data
4  127  Jana Lester     Lisa Jones  ljones@somerandom.com       2    data    data    data

Regards,
Corporate Events
=============

====EMAIL====
TO: ljones@somerandom.com
====
Dear Lisa Jones,

The below accounts have had the Action 2 performed on them. Please contact us with any concerns.

    ID         Name SupervisorName        SupervisorEmail  Action Column5 Column6 Column7
0  123    Tim Jones       Sam Burk   sburk@somerandom.com       2    data    data    data
1  124   Mark Smith       Sam Burk   sburk@somerandom.com       2    data    data    data
2  125   Jill Jones      Tim Jones  tjones@somerandom.com       2    data    data    data
4  127  Jana Lester     Lisa Jones  ljones@somerandom.com       2    data    data    data

Regards,
Corporate Events
=============

====EMAIL====
TO: sburk@somerandom.com
====
Dear Sam Burk,

The below accounts have had the Action 3 performed on them. Please contact us with any concerns.

    ID        Name SupervisorName       SupervisorEmail  Action Column5 Column6 Column7
3  126  Sam Harris       Sam Burk  sburk@somerandom.com       3    data    data    data

Regards,
Corporate Events
=============

Second solution

Limit overall processing to rows with Action value 2 and, partition those by supervisor email, and send an email to each distinct supervisor with only those rows associated with them.

This interpretation was suggested by user mklement0.

import pandas as pd

df = pd.read_csv('emaildata.csv', header=0)

action = 2

for supervisor_email_address, subdf in df.groupby(by='SupervisorEmail'):
  supervisor_name = subdf['SupervisorName'].iloc[0]
  email_body = ''.join(
    ['Dear ',
     supervisor_name,
     ',\n\nThe below accounts have had the Action {} performed on them. Please contact us with any concerns.\n\n'.format(action),
     str(subdf),
     '\n\nRegards,\nCorporate Events'])
  send_email(supervisor_email_address, email_body)

This gives the following three emails:

====EMAIL====
TO: ljones@somerandom.com
====
Dear Lisa Jones,

The below accounts have had the Action 2 performed on them. Please contact us with any concerns.

    ID         Name SupervisorName  ... Column5  Column6 Column7
4  127  Jana Lester     Lisa Jones  ...    data     data    data

[1 rows x 8 columns]

Regards,
Corporate Events
=============

====EMAIL====
TO: sburk@somerandom.com
====
Dear Sam Burk,

The below accounts have had the Action 2 performed on them. Please contact us with any concerns.

    ID        Name SupervisorName  ... Column5  Column6 Column7
0  123   Tim Jones       Sam Burk  ...    data     data    data
1  124  Mark Smith       Sam Burk  ...    data     data    data
3  126  Sam Harris       Sam Burk  ...    data     data    data

[3 rows x 8 columns]

Regards,
Corporate Events
=============

====EMAIL====
TO: tjones@somerandom.com
====
Dear Tim Jones,

The below accounts have had the Action 2 performed on them. Please contact us with any concerns.

    ID        Name SupervisorName  ... Column5  Column6 Column7
2  125  Jill Jones      Tim Jones  ...    data     data    data

[1 rows x 8 columns]

Regards,
Corporate Events
=============
user_30807230
Related