Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Replace all NA values for variable with one row equal to 0

Tags:

r

na

dplyr

Slightly difficult to phrase, as far as I saw none of the similar questions answered my problem.

I have a data.frame such as:

df1 <- data.frame(id = rep(c("a", "b","c"), each = 4),
                  val = c(NA, NA, NA, NA, 1, 2, 2, 3,NA,2,NA,3))

df1

   id val
1   a  NA
2   a  NA
3   a  NA
4   a  NA
5   b   1
6   b   2
7   b   2
8   b   3
9   c  NA
10  c   2
11  c  NA
12  c   3

and I want to get rid of all the NA values (easy enough using e.g. filter() ) but make sure that if this removes all of one id value (in this case it removes every instance of "a") that one extra row is inserted of (e.g.) a = 0

so that:

  id val
1  a   0
2  b   1
3  b   2
4  b   2
5  b   3
6  c   2
7  c   3

obviously easy enough to do this in a roundabout way but I was wondering if there's a tidy/elegant way to do this. I thought tidyr::complete() might help but not entirely sure how to apply it to a case like this

I don't care about the order of the rows

Cheers!

edit: updated with clearer desired output. might make desired answers submitted before that a bit less clear

like image 389
Robert Hickman Avatar asked Jan 03 '19 12:01

Robert Hickman


People also ask

How do I replace Na with 0 in a single column in R?

You can replace NA values with zero(0) on numeric columns of R data frame by using is.na() , replace() , imputeTS::replace() , dplyr::coalesce() , dplyr::mutate_at() , dplyr::mutate_if() , and tidyr::replace_na() functions.

How do I replace all Na in a Dataframe with 0 in R?

To replace NA with 0 in an R data frame, use is.na() function and then select all those values with NA and assign them to 0.

How do I remove all NA values?

To remove all rows having NA, we can use na. omit function. For Example, if we have a data frame called df that contains some NA values then we can remove all rows that contains at least one NA by using the command na. omit(df).


1 Answers

Another idea using dplyr,

library(dplyr)

df1 %>% 
 group_by(id) %>% 
 mutate(val = ifelse(row_number() == 1 & all(is.na(val)), 0, val)) %>% 
 na.omit()

which gives,

# A tibble: 5 x 2
# Groups:   id [2]
  id      val
  <fct> <dbl>
1 a         0
2 b         1
3 b         2
4 b         2
5 b         3
like image 100
Sotos Avatar answered Oct 02 '22 15:10

Sotos