代码之家 › 专栏 › 技术社区 › Paul

如何使用tidyr::unite函数删除NAs?

tidyr r

Paul · 技术社区 · 7 年前

将多个列与 tidyr::unite() ,丢失的数据保留在我的字符向量中,这是我不想要的。

通过。 %in% 和 grepl() .

下面是一个代表性的例子:

library(dplyr)
library(tidyr)

df <- data_frame(a = paste0("A.", rep(1, 3)), b = " ", c = c("C.1", "C.3", " "), d = "D.4", e = "E.5")

cols <- letters[2:4]
df[, cols] <- gsub(" ", NA_character_, as.matrix(df[, cols]))
tidyr::unite(df, new, cols, sep = ",")

# # A tibble: 3 x 3
#   a     new        e    
#   <chr> <chr>      <chr>
# 1 A.1   NA,C.1,D.4 E.5  
# 2 A.1   NA,C.3,D.4 E.5  
# 3 A.1   NA,NA,D.4  E.5

期望输出:

# # A tibble: 3 x 3
#   a     new        e    
#   <chr> <chr>      <chr>
# 1 A.1   C.1,D.4    E.5  
# 2 A.1   C.3,D.4    E.5  
# 3 A.1   D.4        E.5

5 回复 | 直到 7 年前

CT Hall 7 年前

library(dplyr)
library(tidyr)

df <- data_frame(a = paste0("A.", rep(1, 3)), 
                 b = " ", 
                 c = c("C.1", "C.3", " "), 
                 d = "D.4", e = "E.5")

cols <- letters[2:4]
df[, cols] <- gsub(" ", NA_character_, as.matrix(df[, cols]))
tidyr::unite(df, new, cols, sep = ",") %>% 
     dplyr::mutate(new = stringr::str_replace_all(new, 'NA,?', ''))  # New line

输出:

# A tibble: 3 x 3
  a     new     e    
  <chr> <chr>   <chr>
1 A.1   C.1,D.4 E.5  
2 A.1   C.3,D.4 E.5  
3 A.1   D.4     E.5

Ronak Shah 6 年前

在新的 tidyr ,您现在可以使用 na.rm 要删除的参数 NA 价值观。

library(tidyr)
library(dplyr)

df %>% unite(new, cols, sep = ",", na.rm = TRUE)

#   a     new     e    
#  <chr> <chr>   <chr>
#1 A.1   C.1,D.4 E.5  
#2 A.1   C.3,D.4 E.5  
#3 A.1   D.4     E.5

然而, 不适用 如果have列是factors,则不会删除。我们需要在使用前把它们改成字符 unite

df %>% 
  mutate_all(as.character) %>%
  unite(new, cols, sep = ",", na.rm = TRUE)

你也可以用baser apply 方法相同。

apply(df[cols], 1, function(x) toString(na.omit(x)))
#[1] "C.1, D.4" "C.3, D.4" "D.4"

df <- data_frame(
a = c("A.1", "A.1", "A.1"),
b = c(NA_character_, NA_character_, NA_character_),
c = c("C.1", "C.3", NA),
d = c("D.4", "D.4", "D.4"),
e = c("E.5", "E.5", "E.5")
)

cols <- letters[2:4]

alistaire 7 年前

您可以通过对行进行迭代来避免插入它们:

library(tidyverse)

df <- data_frame(
    a = c("A.1", "A.1", "A.1"),
    b = c(NA_character_, NA_character_, NA_character_),
    c = c("C.1", "C.3", NA),
    d = c("D.4", "D.4", "D.4"),
    e = c("E.5", "E.5", "E.5")
)

cols <- letters[2:4]

df %>% mutate(x = pmap_chr(.[cols], ~paste(na.omit(c(...)), collapse = ',')))
#> # A tibble: 3 x 6
#>   a     b     c     d     e     x      
#>   <chr> <chr> <chr> <chr> <chr> <chr>  
#> 1 A.1   <NA>  C.1   D.4   E.5   C.1,D.4
#> 2 A.1   <NA>  C.3   D.4   E.5   C.3,D.4
#> 3 A.1   <NA>  <NA>  D.4   E.5   D.4

或使用 tidyr 的基础 stringi 包裹,

df %>% mutate(x = pmap_chr(.[cols], ~stringi::stri_flatten(
    c(...), collapse = ",", 
    na_empty = TRUE, omit_empty = TRUE
)))
#> # A tibble: 3 x 6
#>   a     b     c     d     e     x      
#>   <chr> <chr> <chr> <chr> <chr> <chr>  
#> 1 A.1   <NA>  C.1   D.4   E.5   C.1,D.4
#> 2 A.1   <NA>  C.3   D.4   E.5   C.3,D.4
#> 3 A.1   <NA>  <NA>  D.4   E.5   D.4

问题是,对行进行迭代通常需要许多因此在规模上可能相当缓慢。不幸的是,似乎没有一个伟大的矢量化替代删除 NA

Paul 7 年前

谢谢大家,我总结了解决方案,并在我的数据上做了基准:

library(microbenchmark)
library(dplyr)
library(stringr)
library(tidyr)
library(biometrics) # has my helper function for column selection

cols <- biometrics::variables(c("diagnosis", "dagger", "ediag"), 20) 
system.time({
  df <- dat[, cols]
  df <- gsub(" ", NA_character_, as.matrix(df)) %>% tbl_df()
})

microbenchmark(
  ## search by base R `match()` function
  match_spaces = apply(dat, 1, function(x) any(c("A37.0","A37.1","A37.8","A37.9") %in% x[cols])), # original search (match)

  match_NAs = apply(df, 1, function(x) any(c("A37.0","A37.1","A37.8","A37.9") %in% x[cols])), # matching with " " replaced by NAs with gsub 

  ## search by base R 'grep()' function - the same regex is used in each case
  regex_str_replace_all = tidyr::unite(df, new, cols, sep = ",") %>% # grepl search with NAs removed with `stringr::str_replace_all()`
    mutate(new = str_replace_all(new, "NA,?", "")) %>%
    apply(1, function(x) grepl("A37.*", x, ignore.case = T)),

  regex_toString = tidyr::unite(df, new, cols, sep = ",") %>%  # grepl search with NAs removed with `apply()` & `toString()`
    mutate(new = apply(df[cols], 1, function(x) toString(na.omit(x)))) %>%
    apply(1, function(x) grepl("A37.*", x, ignore.case = T)),

  regex_row_iteration = df %>% # grepl search after iterating over rows (using syntax I'm not familiar with and need to learn!)
    mutate(new = pmap_chr(.[cols], ~paste(na.omit(c(...)), collapse = ','))) %>%
    select(new) %>%
    apply(1, function(x) grepl("A37.*", x, ignore.case = T)),

  regex_stringi = df %>% mutate(new = pmap_chr(.[cols], ~stringi::stri_flatten( # grepl after stringi
    c(...), collapse = ",", 
    na_empty = TRUE, omit_empty = TRUE
  ))) %>%
    select(new) %>%
    apply(1, function(x) grepl("A37.*", x, ignore.case = T)),

  times = 10L
)

# Unit: milliseconds
#                   expr        min        lq      mean    median        uq       max neval
#           match_spaces 14820.2076 15060.045 15558.092 15573.885 15901.015 16521.855    10
#              match_NAs   998.3184  1061.973  1191.691  1203.849  1301.511  1378.314    10
#  regex_str_replace_all  1464.4502  1487.473  1637.832  1596.522  1701.718  2114.055    10
#         regex_toString  4324.0914  4341.725  4631.998  4487.373  4977.603  5439.026    10
#    regex_row_iteration  5794.5994  6107.475  6458.339  6436.273  6720.185  7256.980    10
#          regex_stringi  4772.3859  5267.456  5466.510  5436.804  5806.272  6011.713    10

%in% 在用NAs替换空值(“”)之后,是赢家。如果我使用正则表达式,那么使用 stringr::string_replace_all() 是最快的。

Kreitz Gigs 7 年前

如果在使用unite函数时删除它们,可能会出现一些错误。事后我会把他们从专栏中删除。

df <- data_frame(a = paste0("A.", rep(1, 3)), b = " ", c = c("C.1", "C.3", " "), d = "D.4", e = "E.5")

cols <- letters[2:4]
df[, cols] <- gsub(" ", NA_character_, as.matrix(df[, cols]))
df <- tidyr::unite(df, new, cols, sep = ",")

df$new <- gsub("NA,","",df$new)