Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Replace all particular values in a data frame

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
like image 365
zxzak Avatar asked Oct 21 '13 19:10

zxzak


People also ask

How do you replace all values in a DataFrame based on a condition?

You can replace values of all or selected columns based on the condition of pandas DataFrame by using DataFrame. loc[ ] property. The loc[] is used to access a group of rows and columns by label(s) or a boolean array. It can access and can also manipulate the values of pandas DataFrame.

How do I replace a specific value in a DataFrame in R?

In this article, we will see how to replace specific values in a column of DataFrame in R Programming Language. Method 1: Using Replace() function. replace() function in R Language is used to replace the values in the specified string vector x with indices given in list by those given in values.


3 Answers

Like this:

> df[df==""]<-NA
> df
     A    B
1 <NA>   12
2  xyz <NA>
3  jkl  100
like image 188
mrip Avatar answered Oct 19 '22 17:10

mrip


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)
like image 38
sedot Avatar answered Oct 19 '22 17:10

sedot


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'))
like image 26
sbha Avatar answered Oct 19 '22 16:10

sbha