Add row to dataframe based on condition of previous row in R

user_6036205 0
Viewed 322

I have a dataframe that looks something like this:

class <- c(3,0,3,0,0)
value <- c(50,50,70,30,100)
days <- c(3,3,2,2,1)
mydata <- data.frame(class, value, days)

What I need is for each day to have both classes represented - so if there is no class 3 on a given day (in this example, day 1) I'd like to add a row where class = 3 and value = 0 and day = 1. My real data is more complicated, because there are varying numbers of rows for each day (and many more days than 3), and many other columns (but for which it would be fine to enter NA). This doesn't seem like too complicated a problem, but I'm having trouble wrapping my head around the code. Thanks so much!

dplyr r
2 Answers

Using tidyverse you can use complete:

library(tidyverse)

mydata %>%
  complete(days, class, fill = list(value = 0))

Output

# A tibble: 6 x 3
   days class value
  <dbl> <dbl> <dbl>
1     1     0   100
2     1     3     0
3     2     0    30
4     2     3    70
5     3     0    50
6     3     3    50

Data

mydata <- structure(list(class = c(3, 0, 3, 0, 0), value = c(50, 50, 70, 
30, 100), days = c(3, 3, 2, 2, 1)), class = "data.frame", row.names = c(NA, 
-5L))
user_34606700

With base R, we can do

out <- merge(expand.grid(lapply(mydata[c('class', 'days')],
         unique)), mydata, all.x = TRUE)
out$value[is.na(out$value)] <- 0
out
#  class days value
#1     0    1   100
#2     0    2    30
#3     0    3    50
#4     3    1     0
#5     3    2    70
#6     3    3    50

NOTE: No packages used

Or with data.table

library(data.table)
setDT(mydata)[CJ(class, days, unique = TRUE),
     on = .(class, days)][is.na(value), value := 0][]
#   class value days
#1:     0   100    1
#2:     0    30    2
#3:     0    50    3
#4:     3     0    1
#5:     3    70    2
#6:     3    50    3

Or using crossing/left_join from tidyverse

library(dplyr)
library(tidyr)
tidyr::crossing(class = unique(mydata$class),
       days = unique(mydata$days)) %>% 
  left_join(mydata) %>%
  mutate(value = replace_na(value, 0))
# A tibble: 6 x 3
#  class  days value
#  <dbl> <dbl> <dbl>
#1     0     1   100
#2     0     2    30
#3     0     3    50
#4     3     1     0
#5     3     2    70
#6     3     3    50

data

mydata <- structure(list(class = c(3, 0, 3, 0, 0), value = c(50, 50, 70, 
30, 100), days = c(3, 3, 2, 2, 1)), class = "data.frame", row.names = c(NA, 
-5L))
user_37322710
Related