Count and merge NA rows in R-CodePudding

I have the following dataframe:

var1 <- c("a", "b", "c", "d", "e")
var2 <- c(5, 10, NA, NA, NA)

df <- data.frame (var1, var2)

df

# A tibble: 5 × 2
  var1   var2
  <chr> <dbl>
1 a         5
2 b         10
3 c        NA
4 d        NA
5 e        NA

I would like to count and merge the NA rows. Expected output:

# A tibble: 3 × 2
  var1   var2
  <chr> <dbl>
1 a         5
2 b         10
3 x         3

I have tried aggregate(data=df,var2~.,na.rm = FALSE, FUN = sum) but it only returns the results for a and b.

Thank you in advance

CodePudding user response：

With dplyr

    df %>% 
   mutate(var1 = ifelse(is.na(var2), "x", var1),
          var2 = ifelse(var1=="x", sum(is.na(var2) & var1 == "x"),
                       var2)
          ) %>% 
   unique()
  var1 var2
1    a    1
2    b    2
3    c    3
4    x    2

Data

df <- structure(list(var1 = c("a", "b", "c", "d", "e"), var2 = c(1, 
2, 3, NA, NA)), class = "data.frame", row.names = c(NA, -5L))

CodePudding user response：

Try this with Base R

s <- df[complete.cases(df) , ]

s[nrow(s) 1 ,] <- c("x" , sum(is.na(df$var2) == T))

s

output

  var1 var2
1    a    5
2    b   10
3    x    3

CodePudding user response：

Using aggregate:

df$var1 <- ifelse(is.na(df$var2), "x", df$var1)

aggregate(data = df, var2 ~ ., 
  FUN = \(x) if (!all(is.na(x))) sum(x) else length(x), na.action = NULL)

#>   var1 var2
#> 1    a    5
#> 2    b   10
#> 3    x    3