49

In dplyr I can replace NA with 0 using the following code. The issue is this inserts a list into my data frame which screws up further analysis down the line. I don't even understand lists or atomic vectors or any of that at this point. I just want to pick certain columns, and replace all occurrences of NA with zero. And maintain the columns integer status.

library(dplyr)
df <- tibble(x = c(1, 2, NA), y = c("a", NA, "b"), z = list(1:5, NULL, 10:20))
df
df %>% replace_na(list(x = 0, y = "unknown"))

That works but transforms the column into a list. How do I do it without transforming the column into a list?

And here's how to do it in base R. But not sure how to work this into a mutate statement:

df$x[is.na(df$x)] <- 0
stackinator
  • 5,429
  • 8
  • 43
  • 84

5 Answers5

118

To replace all NAs in a dataframe use

df %>% replace(is.na(.), 0)

Oliver Oliver
  • 2,057
  • 4
  • 16
  • 14
  • worked great for me in a dplyr pipe. Thanks! – Jens Feb 03 '21 at 13:07
  • 1
    This is quite a nice solution. – jaeyeon Nov 01 '21 at 22:15
  • 2
    How can we do it with the new pipe ( |> )and placeholder ( _ )? – RxT Jun 06 '22 at 19:20
  • 1
    Hi @RxT I had the same question and spent some time trying to find the solution. I have posted it as an alternative answer for all future searching people :-) – 53RT Sep 06 '22 at 12:25
  • It looks nice and simple, but ````Error: ! Assigned data `value` must be compatible with existing data. ℹ Error occurred for column `ESVD2.0_Ecozone_Code`. ✖ Can't convert to .```` – MsGISRocker Jul 04 '23 at 15:02
  • It looks nice and simple, but ````Error: ! Assigned data `value` must be compatible with existing data. ℹ Error occurred for column `ESVD2.0_Ecozone_Code`. ✖ Can't convert to .```` – MsGISRocker Jul 04 '23 at 15:04
  • @MsGISRocker if your column is a character data type you'll prob need to use "0" instead of just 0. – Oliver Oliver Jul 05 '23 at 15:18
45
dt  <- mutate(dt, x = ifelse(is.na(x), 0, x))
fdetsch
  • 5,239
  • 3
  • 30
  • 58
NT_
  • 641
  • 1
  • 5
  • 13
26

What version of dplyr are you using? It might be an old one. The replace_na function now seems to be in tidyr. This works

library(tidyr)
df <- tibble::tibble(x = c(1, 2, NA), y = c("a", NA, "b"), z = list(1:5, NULL, 10:20))
df %>% replace_na(list(x = 0, y = "unknown")) %>% str()
# Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 3 obs. of  3 variables:
#  $ x: num  1 2 0
#  $ y: chr  "a" "unknown" "b"
#  $ z:List of 3
#   ..$ : int  1 2 3 4 5
#   ..$ : NULL
#   ..$ : int  10 11 12 13 14 15 16 17 18 19 ...

We can see the NA values have been replaced and the columns x and y are still atomic vectors. Tested with tidyr_0.7.2.

MrFlick
  • 195,160
  • 17
  • 277
  • 295
3

Native Pipe Solution |>

I came across this question successfully using Oliver Olivers solution with the magrittr pipe %>%

Since his answer the native R pipe |> was introduced which doesn't work this way since it doesn't let you access the piped object with the .

A solution based on replace with the native pipe looks like

df |> {\(.) {replace(.,is.na(.),0)}}()

To further elaborate the answer and the syntax used we are shortening the call by using an anonymous function which would look like this if we would explicitly define it.

my_replace <- function(x){
  return(replace(
    x = x,
    list = is.na(x),
    values = 0))
}
  
df |> 
  my_replace()

# readable answer without defining it first
df |> {function(x) {replace(
  x=x,
  list=is.na(x),
  values = 0)}}()
53RT
  • 649
  • 3
  • 20
0

For the case of .xlsx, I placed an answer here.

#install.packages("xlsx")
library(xlsx)
extracted_df <- read.xlsx("test.xlsx", sheetName='Sheet1', stringsAsFactors=FALSE)
# Replace all NAs in a data frame with "G" character
extracted_df[is.na(extracted_df)] <- "G"
ozturkib
  • 1,493
  • 16
  • 28