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.
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
apply converts its first argument to a matrix.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)
'NULL'? If you mean the string'NULL', then you'll need to first convert your numeric columns tocharacterclass, then it's as simple asdf[is.na(df)] <- "NULL". If you want the actualNULL, then that doesn't really work.NULLhas 0-length, so it can't fill a spot in a data frame.c(1, 2, NULL)is the same asc(1, 2)-NULLisn't there.