CODEWITHSUNDEEP
rdataframedplyrzoo
roming
roming

Reputation: 1205

R remove groups with only NAs

I have a dataframe similar to the one generated by the following structure:

library(dplyr)

df1 <- expand.grid(region   = c("USA", "EUR", "World"),
                  time     = c(2000, 2005, 2010, 2015, 2020),
                  scenario = c("policy1", "policy2"),
                  variable = c("foo", "bar"))

df2 <- expand.grid(region   = c("USA", "EUR", "World"),
                  time     = seq(2000, 2020, 1),
                  scenario = c("policy1", "policy2"),
                  variable = c("foo", "bar"))

df2 <- filter(df2, !(time %in% c(2000, 2005, 2010, 2015, 2020)))

df1$value <- rnorm(dim(df1)[1], 1.5, 1)
df1[df1 < 0] <- NA
df2$value <- NA

df1[df1$region == "World" & df1$variable == "foo", "value"] <- NA

df <- rbind(df1, df2)

rm(df1, df2)

df <- arrange(df, region, scenario, variable, time)

df contains two "types" of NA. For one combination of region and variable (World/foo), there is no data at all. For all other combinations, we have NAs for all years except 2000, 2005, 2010, 2015, 2020.

I need a filter that removes the combinations of regions and variable that do only contain NAs, but keeps those combinations that only contain a few NAs. Background is that I want to apply a linear interpolation to compute the missing values for the latter by combining dplyr and functionality from the zoo-package (for the interpolation) using something like this:

df <- group_by(df, region, scenario, variable, time) %>%
      mutate(value = zoo::na.approx(value)) %>% ungroup()

The group containing only NAs leads to na.approx returning an error since it cannot function only with NAs.

Upvotes: 6

Views: 3144

Answers (1)

talat
talat

Reputation: 70266

To keep only combinations of region and variable that have at least 1 non-NA entry in value you can use:

df %>% group_by(region, variable) %>% filter(any(!is.na(value)))

Or equivalently:

df %>% group_by(region, variable) %>% filter(!all(is.na(value)))

And with data.table you could use:

library(data.table)
setDT(df)[, if(any(!is.na(value))) .SD, by = .(region, variable)]

An approach in base R could be:

df_split <- split(df, interaction(df$region, df$scenario, df$variable))
do.call(rbind.data.frame, df_split[sapply(df_split, function(x) any(!is.na(x$value)))])

Upvotes: 10

Related Questions