Python或Powershell读取csv并排序和收集要在电子邮件中发送的数据

Question

下面的示例描述了我正在做的事情

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

我想收集动作为2且主管电子邮件相同的数据，然后向主管发送一封电子邮件，其中包含与之关联的每个帐户的行数据，暗示已触发动作2事件

发送的电子邮件基于操作。需要将给定主管针对给定操作的电子邮件的所有行分组，并为每个主管发送一封电子邮件，其中多行数据与操作2匹配

这是一个大约30000行的CSV，每个主管可能有5个以上的帐户，其中包含一个给定的事件

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

我假设我需要一个唯一的列表，列出所有主管的电子邮件，其中包含与之相关联的动作2。有没有这样做的示例代码

然后，我假设我将使用列表并对每个类型执行一个for-each语句，然后使用操作2收集给定主管的所有行，以获得我需要的特定列。然后用这些数据撰写电子邮件，发送电子邮件，然后转到列表中的下一个。。。有关于我将如何做到这一点的示例代码吗

我用熊猫来分类吗？ 向需要身份验证的SMTP网关发送电子邮件的好导入/库是什么

Powershell也可能是一种选择

谢谢， 弗雷德

Answer 1

使用^{}和^{}和^{}的PowerShell解决方案：

注:

• Send-MailMessage是为了简单起见而使用的，因为它与PowerShell一起提供，但它被认为是过时的，因为它不能保证到SMTP服务器的安全连接

• 一个official suggestion是使用第三方MailKit库this article有背景信息。
  另一个第三方已为此库创建了PowerShell包装：^{}

# 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

}

Answer 2

OP提出了两个问题；一个是关于使用pandas检索数据集中的数据，另一个是关于发送电子邮件

这是第一个问题的答案

由于我不确定我是否完全按照你的意图来解释你的问题，我提供了两种可能的解决方案

第一种解决方案

对于每个操作action，对于每个主管s，向主管s发送一封电子邮件，其中所有行与操作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)

使用伪函数send_email(to, body)进行测试，上述代码生成以下4封电子邮件：

====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
=============

第二种解决方案

将总体处理限制为操作值为2和的行，按主管电子邮件对这些行进行分区，并向每个不同的主管发送一封电子邮件，其中仅包含与之关联的行

此解释是由用户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)

这将产生以下三封电子邮件：

====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
=============