How to delete missing observations for a subset of columns: the R equivalent of dropna(subset) from python pandas

user_5318986 0
Viewed 245

Consider a dataframe in R where I want to drop row 6 because it has missing observations for the variables var1:var3. But the dataframe has valid observations for id and year. See code below.

In python, this can be done in two ways:

  1. use df.dropna(subset = ['var1', 'var2', 'var3'], inplace=True)
  2. use df.set_index(['id', 'year']).dropna()

How to do this in R with tidyverse?

library(tidyverse)
df <- tibble(id = c(seq(1,10)), year=c(seq(2001,2010)), 
             var1 = c(sample(1:100, 10, replace=TRUE)), 
             var2 = c(sample(1:100, 10, replace=TRUE)), 
             var3 = c(sample(1:100, 10, replace=TRUE)))
df[3,4] = NA
df[6,3:5] = NA
df[8,3:4] = NA
df[10,4:5] = NA
dplyr tidyverse r
4 Answers

We may use complete.cases

library(dplyr)
df %>%
    filter(if_any(var1:var3, complete.cases))

-output

# A tibble: 9 x 5
     id  year  var1  var2  var3
  <int> <int> <int> <int> <int>
1     1  2001    48    55    82
2     2  2002    22    83    67
3     3  2003    89    NA    19
4     4  2004    56     1    38
5     5  2005    17    58    35
6     7  2007     4    30    94
7     8  2008    NA    NA    36
8     9  2009    97   100    80
9    10  2010    37    NA    NA
user_37322710

We can use pmap for this case also:

library(dplyr)
library(purrr)

df %>%
  filter(!pmap_lgl(., ~ {x <- c(...)[-c(1, 2)];
  all(is.na(x))}))

# A tibble: 9 x 5
     id  year  var1  var2  var3
  <int> <int> <int> <int> <int>
1     1  2001    90    55    77
2     2  2002    77     5    18
3     3  2003    17    NA    70
4     4  2004    72    33    33
5     5  2005    10    55    77
6     7  2007    22    81    17
7     8  2008    NA    NA    46
8     9  2009    93    28   100
9    10  2010    50    NA    NA

Or we could also use complete.cases function in pmap as suggested by dear @akrun:

df %>% 
    filter(pmap_lgl(select(., 3:5), ~ any(complete.cases(c(...)))))
user_143145200

You can use if_any in filter -

library(dplyr)
df %>% filter(if_any(var1:var3, Negate(is.na)))

#     id  year  var1  var2  var3
#  <int> <int> <int> <int> <int>
#1     1  2001    14    99    43
#2     2  2002    25    72    76
#3     3  2003    90    NA    15
#4     4  2004    91     7    32
#5     5  2005    69    42     7
#6     7  2007    57    83    41
#7     8  2008    NA    NA    74
#8     9  2009     9    78    23
#9    10  2010    93    NA    NA

In base R, we can use rowSums to select rows which has atleast 1 non-NA value.

cols <- grep('var', names(df))
df[rowSums(!is.na(df[cols])) > 0, ]
user_39629140

If looking for complete cases, use the following (kernel of this is based on other answers):

library(tidyverse)
df <- tibble(id = c(seq(1,10)), year=c(seq(2001,2010)), 
             var1 = c(sample(1:100, 10, replace=TRUE)), 
             var2 = c(sample(1:100, 10, replace=TRUE)), 
             var3 = c(sample(1:100, 10, replace=TRUE)))
df[3,4] = NA
df[6,3:5] = NA
df[8,3:4] = NA
df[10,4:5] = NA

df %>% filter(!if_any(var1:var3, is.na))
#> # A tibble: 6 x 5
#>      id  year  var1  var2  var3
#>   <int> <int> <int> <int> <int>
#> 1     1  2001    13    28    26
#> 2     2  2002    61    77    58
#> 3     4  2004    95    38    58
#> 4     5  2005    38    34    91
#> 5     7  2007    85    46    14
#> 6     9  2009    45    60    40

Created on 2021-06-24 by the reprex package (v2.0.0)

user_7457370
Related