如何用R自动拆分Office 365隔离数据CSV至多个分类文件？

已完成的数据导入流程 #

• 从Office 365管理员门户下载过去7天的CSV报告文件
• 执行以下R代码完成数据导入与预处理：

  # 定义CSV文件路径
  Office_365_Report_CSV = "C:/Users/absnd/Documents/2022-11-18office365latestquarantine.csv"
  
  # 加载data.table库
  require(data.table)
  
  # 读取CSV文件为data.table对象
  quarantine_data = fread(paste0(Office_365_Report_CSV), sep = ",", header = TRUE, check.names = FALSE)
  
  # 提取所需列
  Quarantine_Columns = quarantine_data[,c("Email date (UTC)","Recipients","Subject","Sender","Sender IP","Sender domain","Delivery action","Latest delivery location","Original delivery location","Internet message ID","Network message ID","Mail language","Original recipients","Additional actions","Threats","File threats","File hash","Detection technologies","Alert ID","Final system override","Tenant system override(s)","User system override(s)","Directionality","URLs","Sender tags","Recipient tags","Exchange transport rule","Connector","Context" )]
  

按主题拆分并导出CSV文件 #

添加以下R代码，实现按邮件主题分组，并根据Threats列值生成对应命名的CSV文件：

# 按邮件主题分组，同一主题的邮件归为一组
subject_groups = split(Quarantine_Columns, Quarantine_Columns$Subject)

# 获取当前日期，格式为YYYY-MM-DD
current_date = format(Sys.Date(), "%Y-%m-%d")

# 初始化不同威胁类型的文件计数器
phish_count = 1
phish_spam_count = 1

# 遍历每个主题分组
for (group in subject_groups) {
  # 获取当前分组的威胁类型（假设同一主题的威胁类型一致）
  threat_type = unique(group$Threats)[1]
  
  # 根据威胁类型生成文件名
  if (threat_type == "Phish") {
    filename = paste0(current_date, " Phishing ", phish_count, ".csv")
    phish_count = phish_count + 1
  } else if (threat_type == "Phish, Spam") {
    filename = paste0(current_date, " Phishing and Spam ", phish_spam_count, ".csv")
    phish_spam_count = phish_spam_count + 1
  } else {
    # 处理未定义的威胁类型，可按需调整命名规则
    filename = paste0(current_date, " Unknown Threat ", phish_count + phish_spam_count - 2, ".csv")
  }
  
  # 导出分组数据为CSV文件，保留所有提取列
  fwrite(group, filename, sep = ",", row.names = FALSE, quote = TRUE)
}

代码说明 #

• split()函数按Subject列对数据分组，确保同一主题的邮件聚合到一起
• 用Sys.Date()获取并格式化当前日期，作为文件名的固定前缀
• 针对两种指定威胁类型分别维护递增计数器，保证文件名唯一
• 使用fwrite()（data.table高效导出函数）完成文件保存，保留所有预处理后的列

内容的提问来源于stack exchange，提问作者albyrcoding