Advertisement
Advertisement

## Replace all particular values in a data frame

### Question

Having a data frame, how do I go about replacing all particular values along all rows and columns. Say for example I want to replace all empty records with `NA`'s (without typing the positions):

``````df <- data.frame(list(A=c("", "xyz", "jkl"), B=c(12, "", 100)))

A   B
1      12
2  xyz
3  jkl 100
``````

Expected result:

``````    A   B
1  NA   12
2  xyz  NA
3  jkl  100
``````
2017/12/05
1
88
12/5/2017 3:12:36 AM

### Accepted Answer

Like this:

``````> df[df==""]<-NA
> df
A    B
1 <NA>   12
2  xyz <NA>
3  jkl  100
``````
2013/10/21
137
10/21/2013 7:44:17 PM

Since PikkuKatja and glallen asked for a more general solution and I cannot comment yet, I'll write an answer. You can combine statements as in:

``````> df[df=="" | df==12] <- NA
> df
A    B
1  <NA> <NA>
2  xyz  <NA>
3  jkl  100
``````

For factors, zxzak's code already yields factors:

``````> df <- data.frame(list(A=c("","xyz","jkl"), B=c(12,"",100)))
> str(df)
'data.frame':   3 obs. of  2 variables:
\$ A: Factor w/ 3 levels "","jkl","xyz": 1 3 2
\$ B: Factor w/ 3 levels "","100","12": 3 1 2
``````

If in trouble, I'd suggest to temporarily drop the factors.

``````df[] <- lapply(df, as.character)
``````
2015/12/08

Here are a couple `dplyr` options:

``````library(dplyr)

# all columns:
df %>%
mutate_all(~na_if(., ''))

# specific column types:
df %>%
mutate_if(is.factor, ~na_if(., ''))

# specific columns:
df %>%
mutate_at(vars(A, B), ~na_if(., ''))

# or:
df %>%
mutate(A = replace(A, A == '', NA))

# replace can be used if you want something other than NA:
df %>%
mutate(A = as.character(A)) %>%
mutate(A = replace(A, A == '', 'used to be empty'))
``````
2019/03/22

We can use data.table to get it quickly. First create df without factors,

``````df <- data.frame(list(A=c("","xyz","jkl"), B=c(12,"",100)), stringsAsFactors=F)
``````

Now you can use

``````setDT(df)
for (jj in 1:ncol(df)) set(df, i = which(df[[jj]]==""), j = jj, v = NA)
``````

and you can convert it back to a data.frame

``````setDF(df)
``````

If you only want to use data.frame and keep factors it's more difficult, you need to work with

``````levels(df\$value)[levels(df\$value)==""] <- NA
``````

where value is the name of every column. You need to insert it in a loop.

2016/11/28

If you want to replace multiple values in a data frame, looping through all columns might help.

Say you want to replace `""` and `100`:

``````na_codes <- c(100, "")
for (i in seq_along(df)) {
df[[i]][df[[i]] %in% na_codes] <- NA
}
``````
2017/04/07

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]