针对多问题复杂数据集的简易清洗方法问询

原始数据集 #

df1 <- tibble::tribble(~City,   ~Year,  ~Coffee,    ~Tea,   ~Year,  ~Sugar, ~At,    ~Empty,
"NY",   "2020", "", "", "2020", "2",    "", "",
"NY",   "2019", "5",    "3",    "2019", "5",    "", "",
"City", "Year", "Coffee",   "Tea",  "Year", "Sugar",    "", "",
"ATL",  "2020", "", "", "2020", "2",    "", "",
"ATL",  "2019", "5",    "3",    "2019", "5",    "", "",
"Data input by: Alex",  "", "", "", "", "", "", "",
"BOS",  "Year", "", "Coffee",   "", "Tea",  "Sugar",    "",
"BOS",  "2020", "", "7",    "2020", "8",    "3",    "",
"BOS",  "2019", "", "7",    "2019", "7",    "2",    "",
"MS",   "Year", "Frappacino",   "Green Tea",    "", "Coffee",   "Sugar",    "",
"MS",   "2020", "5",    "6",    "2019", "8",    "3",    "",
"MS",   "2019", "5",    "5",    "2020", "8",    "3",    "",
"City", "Year", "Coffee",   "Tea",  "Year", "Sugar",    "At",   "",
"HW",   "2020", "500",  "300",  "2020", "200",  "", "",
"HW",   "2019", "450",  "320",  "2019", "180",  "", "",
"Data input by: Aleksanteri",   "", "", "", "", "", "", "",
"Kaupunki", "Vuosi",    "Kahvi",    "Tee",  "Vuosi",    "At",   "Sokeri",   "",
"HEL",  "2020", "7",    "4",    "2018", "", "4",    "",
"HEL",  "2019", "7",    "4",    "2019", "", "4",    "",
"HEL",  "2018", "6",    "3",    "2020", "", "5",    "")

数据集存在的问题 #

• 城市ATL的数据是NY的重复值，无法确定是否存在此类重复模式；
• 存在两行标注数据录入人员的冗余行；
• BOS的数据从Year列开始整体偏移一个单元格，且缺少第二个Year字段；
• MS的数据包含Frappacino和Green Tea字段，但无Tea数据；
• MS的咖啡数据年份颠倒（2020与2019数据错位）；
• HW的数据数值量级错误，需按X/100修正（如500需改为5）；
• HEL的列名使用芬兰语，需映射为英文：Vuosi=Year、Kaupunki=City、Kahvi=Coffee、Tee=Tea、Sokeri=Sugar，并确认数据无偏移；
• HEL的Sokeri（Sugar）数据年份与Kahvi（Coffee）、Tee（Tea）的年份颠倒；
• Empty列完全为空，可能为数据偏移预留列（本次无需处理）。

处理方案说明 #

不存在能一次性处理所有这类复杂问题的“一键式”简易方法，但可以通过模块化的脚本流程系统解决，减少重复劳动。以下是基于R语言的分模块处理思路：

1. 清理通用冗余内容 #

先过滤掉标注录入人员的行和重复的表头行：

library(dplyr)
library(stringr)

# 移除冗余行
df_clean <- df1 %>%
  filter(!str_detect(City, "^Data input by:|^City|^Kaupunki"))

2. 分城市针对性处理 #

因为不同城市的数据格式差异极大，需单独处理后再合并：

NY/ATL数据处理

提取数据并标记重复行（重复值需手动确认后决定保留或删除）：

ny_atl <- df_clean %>%
  filter(City %in% c("NY", "ATL")) %>%
  mutate(across(c(Coffee, Tea, Sugar), as.numeric)) %>%
  # 标记除城市列外完全重复的行
  mutate(is_duplicate = duplicated(.[,-1]))

BOS数据偏移修正

重新映射偏移的列：

bos <- df_clean %>%
  filter(City == "BOS") %>%
  # 修正列偏移：将Tea→Coffee，Sugar→Tea，At→Sugar
  rename(Coffee = Tea, Tea = Sugar, Sugar = At) %>%
  select(City, Year, Coffee, Tea, Sugar) %>%
  mutate(across(c(Coffee, Tea, Sugar), as.numeric))

MS数据年份与字段修正

修正咖啡数据的年份错位，并重命名特殊字段：

ms <- df_clean %>%
  filter(City == "MS") %>%
  rename(Frappacino = Coffee, Green_Tea = Tea, Coffee = Sugar) %>%
  # 交换2020和2019的Coffee数据
  mutate(Coffee = ifelse(Year == "2020", lag(Coffee), lead(Coffee))) %>%
  select(City, Year, Frappacino, Green_Tea, Coffee, Sugar) %>%
  mutate(across(c(Frappacino, Green_Tea, Coffee, Sugar), as.numeric))

HW数据量级修正

将数值除以100修正量级：

hw <- df_clean %>%
  filter(City == "HW") %>%
  mutate(across(c(Coffee, Tea, Sugar), ~as.numeric(.)/100))

HEL数据列名映射与年份修正

映射芬兰语列名，修正Sugar数据的年份颠倒：

hel <- df_clean %>%
  filter(City == "HEL") %>%
  # 映射芬兰语列名到英文
  rename(City = City, Year = Year, Coffee = Coffee, Tea = Tea, Sugar = At) %>%
  # 修正Sugar年份错位：交换2020和2018的Sugar值
  mutate(Sugar = case_when(
    Year == "2020" ~ filter(., Year == "2018")$Sugar,
    Year == "2018" ~ filter(., Year == "2020")$Sugar,
    TRUE ~ Sugar
  )) %>%
  mutate(across(c(Coffee, Tea, Sugar), as.numeric))

3. 合并处理后的数据 #

将各城市的处理结果合并为统一数据集：

final_df <- bind_rows(ny_atl, bos, ms, hw, hel) %>%
  arrange(City, Year)

关键提示 #

• 这类异构脏数据没有通用自动处理工具，必须针对每个问题点编写逻辑，但模块化流程能让操作更清晰；
• 涉及数据真实性的问题（如ATL的重复值），必须手动确认后再处理，脚本无法自动判断；
• 处理前务必备份原始数据，避免误操作导致数据丢失。

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