How to insert missing dates into a data frame?

Asked

Viewed 349 times

3

I have a time series with 30 years of data that presents filling failures. As noted in the example below, a few days were not recorded (1961-08-19,1961-08-20,1961-08-21...). How do I identify and insert the missing dates in the "date" column, assigning "NA" to the other columns on those dates when no monitoring was performed?

date        id  prec    tmax    tmin    tmed    urmax
1961-08-18  1   NA      23.53   14.90   27.90   36.33
1961-08-22  1   0.00    24.80   14.90   29.70   31.67
1961-08-24  1   1.00    24.37   15.30   28.80   37.33
1961-08-25  1   0.00    23.93   16.03   29.20   43.67
1961-08-26  1   0.00    25.97   15.27   31.60   30.33
1961-08-27  1   0.00    25.83   14.97   31.20   29.33
1961-08-29  1   0.00    24.60   15.87   30.20   39.33
1961-08-30  1   0.00    25.60   15.83   29.80   34.00
r

2 answers

5


One possible way is to create a data.frame with all possible dates:

library(lubridate)
library(dplyr)

all_dates <- data_frame(
  date = seq(from = ymd("1968-01-01"), to = ymd("2018-01-01"), by = "1 day")
)

And then give left_join with your date.frame:

df$date <- ymd(df$date)
df <- all_dates %>%
  left_join(df, by = "date")

1

one option is to use the library padr

dados <- structure(list(
       date = structure(c(-3058, -3054, -3052, -3051, -3050, -3049,
                          -3047, -3046), class = "Date"),
       id = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), 
       prec = c(NA, 0, 1, 0, 0, 0, 0, 0),
       tmax = c(23.53, 24.8, 24.37, 23.93, 25.97, 25.83, 24.6, 25.6),
       tmin = c(14.9, 14.9, 15.3, 16.03, 15.27, 14.97, 15.87, 15.83),
       tmed = c(27.9, 29.7, 28.8, 29.2, 31.6, 31.2, 30.2, 29.8),
       urmax = c(36.33, 31.67, 37.33, 43.67, 30.33, 29.33, 39.33, 34)),
  class = "data.frame", row.names = c(NA, -8L))

library(padr)
pad(dados)

pad applied on the interval: day
         date id prec  tmax  tmin tmed urmax
1  1961-08-18  1   NA 23.53 14.90 27.9 36.33
2  1961-08-19 NA   NA    NA    NA   NA    NA
3  1961-08-20 NA   NA    NA    NA   NA    NA
4  1961-08-21 NA   NA    NA    NA   NA    NA
5  1961-08-22  1    0 24.80 14.90 29.7 31.67
6  1961-08-23 NA   NA    NA    NA   NA    NA
7  1961-08-24  1    1 24.37 15.30 28.8 37.33
8  1961-08-25  1    0 23.93 16.03 29.2 43.67
9  1961-08-26  1    0 25.97 15.27 31.6 30.33
10 1961-08-27  1    0 25.83 14.97 31.2 29.33
11 1961-08-28 NA   NA    NA    NA   NA    NA
12 1961-08-29  1    0 24.60 15.87 30.2 39.33
13 1961-08-30  1    0 25.60 15.83 29.8 34.00

Browser other questions tagged r

You are not signed in. Login or sign up in order to post.