Fill NA with a series of characters in R dplyr

April 12, 2022

I have a large data frame that looks like this. Each player is assigned to a group.

library(tidyverse)

df <- tibble(player=c(1,2,3,4,5),groups=c("group1","group2","group2",NA,NA))
df
#> # A tibble: 5 × 2
#>   player groups
#>    <dbl> <chr> 
#> 1      1 group1
#> 2      2 group2
#> 3      3 group2
#> 4      4 <NA>  
#> 5      5 <NA>

^{Created on 2022-04-12 by the reprex package (v2.0.1)}
Some players are not assigned into groups and I want to fill them serially -i.e. like this-

#> # A tibble: 5 × 2
#>   player groups
#>    <dbl> <chr> 
#> 1      1 group1
#> 2      2 group2
#> 3      3 group2
#> 4      4 group3
#> 5      5 group4

>Solution :

dplyr

library(dplyr)
df %>%
  mutate(
    maxgrp = max(as.integer(gsub("[^0-9]", "", groups)), na.rm = TRUE),
    groups = if_else(is.na(groups), paste0("group", maxgrp + cumsum(is.na(groups))), groups)
  ) %>%
  select(-maxgrp)
# # A tibble: 5 x 2
#   player groups
#    <dbl> <chr> 
# 1      1 group1
# 2      2 group2
# 3      3 group2
# 4      4 group3
# 5      5 group4

data.table

library(data.table)
DT <- as.data.table(df)
DT[, groups := fifelse(
  is.na(groups),
  paste0("group", cumsum(is.na(groups)) + max(as.integer(gsub("[^0-9]", "", groups)), na.rm = TRUE)),
  groups) ]