complete series in database

[Luisarreguin]

Hello everyone,

I have a database in long format and 4 columns (date, hour, site, pm25).

The "date" column ranges from 2020-01-01 to 2020-12-31, and for each day, the "hour" column ranges from 1 to 24 (which are the 24 hours of each day and are repeated for each day), and the "site" column corresponds to three different sites (groups: "PGO ", "PMNO", and "TEC"), each with a different "pm25" value for each hour and day of 2020 (some values in the "pm25" column may be repeated and others may be NA).

The problem is that not all sites (groups) have the complete series from 2020-01-01 to 2020-12-31.

I need 26352 rows of data to match to the 366 days (February has 29 days in 2020) * the 24 hours of the day * the three different sites, yet I only have 24887 rows.

My issue is, how do I complete the series while ensuring that the values added equal NA?

database link

thanks,
Luis E

[FJCC]

You can make a data frame with all the days, hours, and sites with expand.grid() and then left_join() the experimental data. Any day that doesn't have a PM25 value will receive an NA.

Days <- seq.Date(as.Date("2020-01-01"), as.Date("2020-12-31"),by = 1)
Hours <- 1:24
Sites <- c("PGO", "PMNO","TEC")
DF <- expand.grid(Days, Hours, Sites)
colnames(DF) <- c("Date", "Hour", "Site")
head(DF)
#>         Date Hour Site
#> 1 2020-01-01    1  PGO
#> 2 2020-01-02    1  PGO
#> 3 2020-01-03    1  PGO
#> 4 2020-01-04    1  PGO
#> 5 2020-01-05    1  PGO
#> 6 2020-01-06    1  PGO

#Invent some experimental data
ExpData <- data.frame(Date = as.Date(c("2020-01-01","2020-01-02",
                                       "2020-01-03","2020-01-05")),
                      Hour = c(1,1,1,1), Site = "PGO", PM25 =1:4)

library(dplyr)
FullData <- left_join(DF, ExpData, by = c("Date", "Hour", "Site"))
head(FullData)
#>         Date Hour Site PM25
#> 1 2020-01-01    1  PGO    1
#> 2 2020-01-02    1  PGO    2
#> 3 2020-01-03    1  PGO    3
#> 4 2020-01-04    1  PGO   NA
#> 5 2020-01-05    1  PGO    4
#> 6 2020-01-06    1  PGO   NA

^{Created on 2023-07-21 with reprex v2.0.2}

[Luisarreguin]

That works! Thank you for your help.