CODEWITHSUNDEEP
r
sachin katarki
sachin katarki

Reputation: 89

adding mising rows in R

I have a table in R as follows

 month  day total
1   1    3   1414
2   1    5   1070
3   1    6    211
4   1    7   2214
5   1    8   1766
6   1   13   2486
7   1   14     43
8   1   15   2349
9   1   16   4616
10  1   17   2432
11  1   18   1482
12  1   19    694
13  1   20    968
14  1   23    381
15  1   24    390
16  1   26   4063
17  1   27   3323
18  1   28    988
19  1   29   9671
20  1   30  11968

I have to add the missing values of days such as 1,2,4 with zero such that the result should be like

 month  day total
-   1    1   0
-   1    2   0
    1    3   1414
    1    4    0
2   1    5   1070
3   1    6    211
4   1    7   2214
5   1    8   1766
    1    9    0
    1   10    0
    1   11    0
    1   12    0
6   1   13   2486
7   1   14     43
8   1   15   2349
9   1   16   4616
10  1   17   2432
11  1   18   1482
12  1   19    694
13  1   20    968
    1   21     0
    1   22     0
14  1   23    381
15  1   24    390
    1   25     0
16  1   26   4063
17  1   27   3323
18  1   28    988
19  1   29   9671
20  1   30  11968

Upvotes: 2

Views: 58

Answers (2)

akrun
akrun

Reputation: 886938

In R, we could create a new dataset with 'day' column from 1:30 and 'month' as '1', left_join with the original dataset and replace the NA values after the merge with '0'

 df2 <- data.frame(month=1, day=1:30)
 library(dplyr)
 left_join(df2, df1) %>% 
              mutate(total=replace(total, which(is.na(total)),0))

Or use merge from base R to get 'dM' and assign the NA values in the 'total' to '0'

  dM <- merge(df1, df2, all.y=TRUE)
  dM$total[is.na(dM$total)] <- 0

Upvotes: 0

Dominic Comtois
Dominic Comtois

Reputation: 10401

Using only base R, you could do it this way:

for(d in 1:31) {
  if(!d %in% my.df$day) 
    my.df[nrow(my.df) + 1,] <- c(1,d,0)
}

# Reorder rows
my.df <- my.df[with(my.df, order(month, day)),]
rownames(my.df) <- NULL

# Check the results
head(my.df)

#  month day total
# 1     1   1     0
# 2     1   2     0
# 3     1   3  1414
# 4     1   4     0
# 5     1   5  1070
# 6     1   6   211

Upvotes: 1

Related Questions