CODEWITHSUNDEEP
rdataframe
umakant
umakant

Reputation: 49

Insert rows where there are missing values

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)

Upvotes: 4

Views: 84

Answers (2)

Joe
Joe

Reputation: 8601

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))

Upvotes: 1

akrun
akrun

Reputation: 886938

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

Upvotes: 0

Related Questions