r - 获取字符串中最小的数字

Question

我有一个由捕获不同事件的列组成的数据框。受访者填写了他们经历这些事件的年龄。对于他们经历过多次的任何给定事件，他们用分号分隔经历的年龄(例如，如果他们在 5、6、7 岁经历过该事件，他们将在该特定列中输入 5;6;7)。对于没有经历过的事件，受访者将其留空。

由于有二十多列，我将所有列连接在一起形成一列，形成一个字符列。我想提取该字符串中最小的数字。我无法强制将列转换为数字数据类型，因为受访者会多次经历某些事件，并被 R 解释为字符串(例如“5; 6; 7”)

我的数据如下所示:

df <- data.frame(ID = c("001", "002", "003", "004"),
             concatenated = c("NA_NA_NA_NA_5; 6_NA_4_NA_NA_NA",
                              "3_3_NA_NA_NA_3; 4; 5; 6_NA_NA_NA_NA",
                              "NA_5_4_2_NA_NA_NA_NA_6; 7; 8; 9; 10_NA",
                              "NA_NA_11_12_11_NA_4; 5; 6_NA_NA_9"))

df$concatenated <- as.character(df$concatenated)

我想要得到的最终结果如下:

ID                           concatenated smallest_number
1 001         NA_NA_NA_NA_5; 6_NA_4_NA_NA_NA               4
2 002    3_3_NA_NA_NA_3; 4; 5; 6_NA_NA_NA_NA               3
3 003 NA_5_4_2_NA_NA_NA_NA_6; 7; 8; 9; 10_NA               2
4 004      NA_NA_11_12_11_NA_4; 5; 6_NA_NA_9               4

谢谢!非常感谢!

Answer 1

使用 tidyverse 和 splitstackshape 你可以:

df %>%
 mutate(temp = gsub(";", "_", concatenated),
        temp = gsub(" ", "", temp)) %>%
 cSplit("temp", sep = "_") %>%
 gather(var, val, -c(concatenated, ID)) %>%
 group_by(ID) %>%
 mutate(res = min(val, na.rm = TRUE)) %>%
 spread(var, val) %>%
 select(ID, concatenated, res)

  ID    concatenated                             res
  <fct> <chr>                                  <dbl>
1 001   NA_NA_NA_NA_5; 6_NA_4_NA_NA_NA            4.
2 002   3_3_NA_NA_NA_3; 4; 5; 6_NA_NA_NA_NA       3.
3 003   NA_5_4_2_NA_NA_NA_NA_6; 7; 8; 9; 10_NA    2.
4 004   NA_NA_11_12_11_NA_4; 5; 6_NA_NA_9         4.

首先，它将 ; 替换为 _ 并根据 _ 拆分“串联”列。其次，它将数据从宽格式转换为长格式，并按“ID”列进行分组。最后，它评估最小值并将数据返回为宽格式。

或者仅使用tidyverse:

df %>% 
 mutate(temp = gsub(";", "_", concatenated),
        temp = gsub(" ", "", temp),
        temp = strsplit(temp, "_")) %>%
 unnest(temp) %>%
 group_by(ID) %>%
 mutate_if(is.character, as.numeric) %>%
 mutate(res = min(temp, na.rm = TRUE),
        rowid = row_number()) %>%
 spread(rowid, temp) %>%
 select(ID, concatenated , res)

  ID    concatenated                             res
  <fct> <fct>                                  <dbl>
1 001   NA_NA_NA_NA_5; 6_NA_4_NA_NA_NA            4.
2 002   3_3_NA_NA_NA_3; 4; 5; 6_NA_NA_NA_NA       3.
3 003   NA_5_4_2_NA_NA_NA_NA_6; 7; 8; 9; 10_NA    2.
4 004   NA_NA_11_12_11_NA_4; 5; 6_NA_NA_9         4.