r - 如何有条件地将具有多个值的两行合并在一起并在 R 中发生变异？

Question

使用不同的捕鱼方法捕获鱼。

我想根据 Species 合并行(如果它们是相同的鱼种)，如果它们被两个 Bottom fishing 捕获和 Trolling方法它会导致两行折叠成一行，改变 Method值到 Both .

例如 Caranx ignobilis会有新的Method Both 的值. Bait Released和 Kept列也应该在同一行上有值。

          Species                  Method       Bait     Released  Kept
        4 Caranx ignobilis         Both         NA       1         1

看起来很简单，但我一直在挠头几个小时并玩弄 case_when作为 tidyverse 的一部分包裹。

tibble 是先前使用 group_by 对数据进行子集设置的结果和 pivot_wider .

这是样本的样子:

# A tibble: 10 x 5
# Groups:   Species [9]
   Species                  Method          Bait Released  Kept
   <chr>                    <fct>          <int>    <int> <int>
 1 Aethaloperca rogaa       Bottom fishing    NA       NA     2
 2 Aprion virescens         Bottom fishing    NA       NA     1
 3 Balistidae spp.          Bottom fishing    NA       NA     1
 4 Caranx ignobilis         Trolling          NA       NA     1
 5 Caranx ignobilis         Bottom fishing    NA        1    NA
 6 Epinephelus fasciatus    Bottom fishing    NA        3    NA
 7 Epinephelus multinotatus Bottom fishing    NA       NA     5
 8 Other species            Bottom fishing    NA        1    NA
 9 Thunnus albacares        Trolling          NA       NA     1
10 Variola louti            Bottom fishing    NA       NA     1

数据:

fish_catch <- structure(list(Species = c("Aethaloperca rogaa", "Aprion virescens","Balistidae spp.", "Caranx ignobilis", "Caranx ignobilis", "Epinephelus fasciatus","Epinephelus multinotatus", "Other species", "Thunnus albacares","Variola louti"),
              Method = structure(c(1L, 1L, 1L, 2L, 1L, 1L,1L, 1L, 2L, 1L), .Label = c("Bottom fishing", "Trolling"), class = "factor"),Bait = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_,NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_,NA_integer_),
              Released = c(NA, NA, NA, NA, 1L, 3L, NA, 1L,NA, NA),
              Kept = c(2L, 1L, 1L, 1L, NA, NA, 5L, NA, 1L, 1L)), class = c("grouped_df","tbl_df", "tbl", "data.frame"), row.names = c(NA, -10L), groups = structure(list(Species = c("Aethaloperca rogaa", "Aprion virescens",
              "Balistidae spp.","Caranx ignobilis", "Epinephelus fasciatus", "Epinephelus multinotatus","Other species", "Thunnus albacares", "Variola louti"), .rows = list(1L, 2L, 3L, 4:5, 6L, 7L, 8L, 9L, 10L)), row.names = c(NA,-9L), class = c("tbl_df", "tbl", "data.frame"), .drop = FALSE)) 

我要走的路线，但后来我意识到它没有包含 Species或其他列

    mutate(Method = case_when(Method == "Bottom fishing" & Method == "Trolling" ~ "Both",
                                 Method == "Bottom fishing" ~ "Bottom fishing",
                                 Method == "Trolling" ~ "Trolling", TRUE ~ as.character(MethodCaught)))

Answer 1

这是使用 tidyverse 的一种方法.您可以 group_by(Species)并设置 Method如果该物种的方法中包括底钓和拖钓，则为“两者”。之后，您可以group_by物种和方法，并使用 fill替换 NA具有已知值。最后用slice为每个物种/方法保留一行。这假设您将为每个物种/方法分配 1 行 - 如果不是这种情况，请告诉我。

library(tidyverse)

fish_catch %>%
  group_by(Species) %>%
  mutate(Method = ifelse(all(c("Bottom fishing", "Trolling") %in% Method), "Both", as.character(Method))) %>%
  group_by(Species, Method) %>%
  fill(c(Bait, Released, Kept), .direction = "updown") %>%
  slice(1)

输出

# A tibble: 9 x 5
# Groups:   Species, Method [9]
  Species                  Method          Bait Released  Kept
  <chr>                    <chr>          <int>    <int> <int>
1 Aethaloperca rogaa       Bottom fishing    NA       NA     2
2 Aprion virescens         Bottom fishing    NA       NA     1
3 Balistidae spp.          Bottom fishing    NA       NA     1
4 Caranx ignobilis         Both              NA        1     1
5 Epinephelus fasciatus    Bottom fishing    NA        3    NA
6 Epinephelus multinotatus Bottom fishing    NA       NA     5
7 Other species            Bottom fishing    NA        1    NA
8 Thunnus albacares        Trolling          NA       NA     1
9 Variola louti            Bottom fishing    NA       NA     1