R - Replace all NA's in a dataframe with NULL-CodePudding

How can I replace all 'NA' with 'NULL' in an R dataframe without specifying column names? I found replace_na function from tidyr which has an example as:

# Replace NAs in a data frame
df <- tibble(x = c(1, 2, NA), y = c("a", NA, "b"))
df %>% replace_na(list(x = 0, y = "unknown"))

but my table has more than 10 columns and it could change. Can't specify column names like in the example above.

CodePudding user response：

Base R way to do this:

apply(df, 2, function(x) { x[ is.na(x) ] <- 'NULL'; x})

Note that you can't really insert NULL as it has length 0, you can insert: '' or NA

CodePudding user response：

Here are two approaches. One assuming you want 0 in numeric columns and "unknown" in character, and one assuming you want 'NULL' independent of column type.

Approach 1:

library(tidyr); library(dplyr, warn.conflicts = FALSE)
df <- tibble(x = c(1, 2, NA), y = c("a", NA, "b"))
df %>% 
    mutate(
        across(where(is.numeric), replace_na, 0),
        across(where(is.character), replace_na, "unknown")
    )
#> # A tibble: 3 × 2
#>       x y      
#>   <dbl> <chr>  
#> 1     1 a      
#> 2     2 unknown
#> 3     0 b

^{Created on 2022-11-10 by the reprex package (v2.0.1)}

Approach 2:

library(tidyr); library(dplyr, warn.conflicts = FALSE)
df <- tibble(x = c(1, 2, NA), y = c("a", NA, "b"))
df %>% 
    mutate(
        across(everything(), ~ if(anyNA(.)) replace_na(as.character(.), 'NULL'))
    )
#> # A tibble: 3 × 2
#>   x     y    
#>   <chr> <chr>
#> 1 1     a    
#> 2 2     NULL 
#> 3 NULL  b

^{Created on 2022-11-10 by the reprex package (v2.0.1)}