使用pivot_longer处理数据框时出现重复行的问题求助

问题说明 #

用pivot_longer整理数据框后，每个植物物种都出现了多行，看起来像是重复，但用distinct()去重完全没效果。

数据样本 #

# 数据预览
# A tibble: 10 × 6
   latin_name           plant_type treatment       `ounces per plot` weight            `grams per plot`
   <chr>                <chr>      <chr>                       <dbl> <chr>                        <dbl>
 1 Agastache foeniculum wildflower tx._one_rate               0.021  wt_per_plot_one              0.609
 2 Agastache foeniculum wildflower tx._one_rate               0.021  wt_per_plot_two              1.22 
 3 Agastache foeniculum wildflower tx._one_rate               0.021  wt_per_plot_three            1.83 
 4 Agastache foeniculum wildflower tx._one_rate               0.021  wt_per_plot_four             2.44 
 5 Agastache foeniculum wildflower tx._two._rate              0.0430 wt_per_plot_one              0.609
 6 Agastache foeniculum wildflower tx._two._rate              0.0430 wt_per_plot_two              1.22 
 7 Agastache foeniculum wildflower tx._two._rate              0.0430 wt_per_plot_three            1.83 
 8 Agastache foeniculum wildflower tx._two._rate              0.0430 wt_per_plot_four             2.44 
 9 Agastache foeniculum wildflower tx._three._rate            0.0645 wt_per_plot_one              0.609
10 Agastache foeniculum wildflower tx._three._rate            0.0645 wt_per_plot_two              1.22 

我使用的代码 #

library(tidyverse)
seed_rate1 <- seed_rate1%>%
  pivot_longer(
    cols = starts_with("tx"),
    names_to = "treatment",
    values_to = "ounces per plot")%>%
  pivot_longer(
    cols = starts_with("wt"),
    names_to = "weight",
    values_to = "grams per plot")

完整数据（dput输出） #

seed_struct <- 
  structure(structure(
    list(
      latin_name = c(
        "Agastache foeniculum",
        "Agastache foeniculum",
        "Agastache foeniculum",
        "Agastache foeniculum",
        "Agastache foeniculum",
        "Agastache foeniculum",
        "Agastache foeniculum",
        "Agastache foeniculum",
        "Agastache foeniculum",
        "Agastache foeniculum",
        "Agastache foeniculum",
        "Agastache foeniculum"
      ),
      plant_type = c(
        "wildflower",
        "wildflower",
        "wildflower",
        "wildflower",
        "wildflower",
        "wildflower",
        "wildflower",
        "wildflower",
        "wildflower",
        "wildflower",
        "wildflower",
        "wildflower"
      ),
      treatment = c(
        "tx._one_rate",
        "tx._one_rate",
        "tx._one_rate",
        "tx._one_rate",
        "tx._two._rate",
        "tx._two._rate",
        "tx._two._rate",
        "tx._two._rate",
        "tx._three._rate",
        "tx._three._rate",
        "tx._three._rate",
        "tx._three._rate"
      ),
      `ounces per plot` = c(
        0.021,
        0.021,
        0.021,
        0.021,
        0.042975207,
        0.042975207,
        0.042975207,
        0.042975207,
        0.06446281,
        0.06446281,
        0.06446281,
        0.06446281
      ),
      weight = c(
        "wt_per_plot_one",
        "wt_per_plot_two",
        "wt_per_plot_three",
        "wt_per_plot_four",
        "wt_per_plot_one",
        "wt_per_plot_two",
        "wt_per_plot_three",
        "wt_per_plot_four",
        "wt_per_plot_one",
        "wt_per_plot_two",
        "wt_per_plot_three",
        "wt_per_plot_four"
      ),
      `grams per plot` = c(
        0.609,
        1.218,
        1.827,
        2.437,
        0.609,
        1.218,
        1.827,
        2.437,
        0.609,
        1.218,
        1.827,
        2.437
      )
    ),
    row.names = c(NA,-12L),
    class = c("tbl_df", "tbl", "data.frame")
  ))

解决建议 #

先搞清楚：你看到的不是“重复行” #

distinct()默认会对比所有列，只有完全一模一样的行才会被去重。你提供的12行数据里，treatment或weight列的值都不一样，所以每一行都是唯一的，distinct()自然没用。你觉得“重复”是因为同一个treatment下的ounces per plot值相同，同一个weight下的grams per plot值相同，但整行并不重复。

针对你的需求，分两种情况处理： #

情况1：不需要treatment和weight的所有组合

如果你只想保留treatment的信息（不需要weight相关列），直接去掉weight列再去重：

seed_rate1 %>%
  pivot_longer(
    cols = starts_with("tx"),
    names_to = "treatment",
    values_to = "ounces per plot"
  ) %>%
  select(-starts_with("wt")) %>% # 移除所有weight相关列
  distinct() # 现在可以去重，因为同一treatment的行完全相同

如果你只想保留weight的信息（不需要treatment列）：

seed_rate1 %>%
  pivot_longer(
    cols = starts_with("wt"),
    names_to = "weight",
    values_to = "grams per plot"
  ) %>%
  select(-starts_with("tx")) %>% # 移除所有treatment相关列
  distinct()

情况2：原始宽格式数据下，避免笛卡尔积

你用两次pivot_longer的操作，会把每个treatment和每个weight做全组合（笛卡尔积），这就是为什么会生成3个treatment × 4个weight = 12行。如果你的原始宽数据中，tx_*和wt_*是成对关联的（比如tx_one_rate对应wt_per_plot_one），那应该用一次pivot_longer完成转换，避免生成多余组合：

假设你的原始宽数据是这样的：

# 模拟原始宽格式数据
original_wide <- tibble(
  latin_name = "Agastache foeniculum",
  plant_type = "wildflower",
  tx_one_rate = 0.021,
  wt_per_plot_one = 0.609,
  tx_two_rate = 0.043,
  wt_per_plot_two = 1.22,
  tx_three_rate = 0.0645,
  wt_per_plot_three = 1.83,
  tx_four_rate = 0.086,
  wt_per_plot_four = 2.44
)

用一次pivot_longer匹配成对的列：

original_wide %>%
  pivot_longer(
    cols = -c(latin_name, plant_type),
    names_to = c(".value", "group"),
    # 用正则匹配列名的前缀（tx或wt）和后缀（one/two/three/four）
    names_pattern = "(tx.*rate|wt_per_plot)_(.*)"
  ) %>%
  # 重命名列名让结果更清晰
  rename(treatment = tx_one_rate, `grams per plot` = wt_per_plot)

这样就能得到每行对应一组treatment和weight的关联数据，不会生成多余的组合。

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