Data is like:
quarter name week value
17Q3 abc 1 0.7
17Q3 abc 3 0.65
17Q3 def 1 0.13
17Q3 def 2 0.04
Can I insert rows with value=0 where there is missing values for week i.e the output should be like:
quarter name week value
17Q3 abc 1 0.7
17Q3 abc 3 0.65
17Q3 abc 2 0.0
17Q3 def 1 0.13
17Q3 def 2 0.04
17Q3 def 3 0.0
need to fill till week 13.(i.e check till 13)
How about by using expand within complete.
library(tidyverse)
complete(df, expand(df, quarter, name, week), fill = list(value=0))
# quarter name week value
# <fct> <fct> <int> <dbl>
# 1 17Q3 abc 1 0.700
# 2 17Q3 abc 2 0
# 3 17Q3 abc 3 0.650
# 4 17Q3 def 1 0.130
# 5 17Q3 def 2 0.0400
# 6 17Q3 def 3 0
Or, maybe easier to understand:
df %>% expand(quarter, name, week) %>% left_join(df) %>% replace_na(list(value=0))
Here is one option with tidyverse. We get the missing combination of rows with complete, arrange the rows based on the 'quarter', 'name' and 'id', then mutate the 'id' to 'row_number())andselect` the columns to have the same order as in the original dataset
library(tidyverse)
df1 %>%
complete(quarter, name, week = full_seq(week, 1), fill = list(value = 0)) %>%
arrange(quarter, name, id) %>%
mutate(id = row_number()) %>%
select(names(df1))
# A tibble: 6 x 5
# id quarter name week value
# <int> <chr> <chr> <dbl> <dbl>
#1 1 17Q3 abc 1.00 0.700
#2 2 17Q3 abc 3.00 0.650
#3 3 17Q3 abc 2.00 0
#4 4 17Q3 def 1.00 0.130
#5 5 17Q3 def 2.00 0.0400
#6 6 17Q3 def 3.00 0
library(dplyr);df1 %>% complete(quarter, name, week = full_seq(week, 1), fill = list(value = 0)) %>% arrange(quarter, name, id) %>% mutate(id = row_number()) %>% select(names(df1))completeis fromtidyrsorry