Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Adding missing hours to dataframe in R

Tags:

r

dplyr

tidyverse

I have a data frame where some of the hours in Time GMT are missing.
Normally, the hours should be shown in a sequence from 00:00 to 23:00, but sometimes an hour is missed.

Where an hour is missing in the sequence, I would like to insert a new row.
The new row will be a copy of the previous row, but with the following columns changed as follows:

  • Time GMT: will contain the next hour of the previous row. i.e, if previous == 5:00, new == 6:00

  • Sample Measurement: will contain the average between the previous value and the next value in Sample Measurement column.

  • MDL: will contain the average between the previous value and the next value in column MDL

What have I tried

library(dplyr)
library(tidyr)

class(hrOzone$Time.GMT)   # output type "character"

hrOzone %>%
  group_by(Date.GMT) %>%
  complete(Time.GMT = full_seq(01:24, 1), fill = list(count = 0))

head(hrOzone$Time.GMT, n = 100L)

DataSource url: https://drive.google.com/file/d/1o1voBktR3i8ROt1Hp59OW5t3_uukIA3j/view?usp=sharing

Date GMT Time GMT Sample Measurement MDL

01/03/2016 21:00:00 0.036 0.005
01/03/2016 22:00:00 0.035 0.007
01/03/2016 23:00:00 0.029 0.008
02/03/2016 00:00:00 0.03
02/03/2016 01:00:00 0.01
02/03/2016 02:00:00
02/03/2016 03:00:00
02/03/2016 04:00:00
02/03/2016 05:00:00
02/03/2016 07:00:00
02/03/2016 08:00:00
02/03/2016 09:00:00
02/03/2016 10:00:00
02/03/2016 11:00:00
02/03/2016 12:00:00
02/03/2016 13:00:00
02/03/2016 14:00:00
02/03/2016 16:00:00
02/03/2016 17:00:00
02/03/2016 18:00:00
02/03/2016 19:00:00
02/03/2016 20:00:00
02/03/2016 21:00:00
02/03/2016 22:00:00
02/03/2016 23:00:00
02/03/2016 00:00:00
03/03/2016 01:00:00
03/03/2016 02:00:00
03/03/2016 03:00:00
03/03/2016 04:00:00
03/03/2016 06:00:00
03/03/2016 08:00:00

Update as per Waldi request

> dput(head(hrOzone,20))
structure(list(State.Code = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), County.Code = c(3L, 
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 
3L, 3L, 3L), Site.Num = c(10L, 10L, 10L, 10L, 10L, 10L, 10L, 
10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L
), Parameter.Code = c(44201L, 44201L, 44201L, 44201L, 44201L, 
44201L, 44201L, 44201L, 44201L, 44201L, 44201L, 44201L, 44201L, 
44201L, 44201L, 44201L, 44201L, 44201L, 44201L, 44201L), POC = c(1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L), Latitude = c(30.497478, 30.497478, 30.497478, 30.497478, 
30.497478, 30.497478, 30.497478, 30.497478, 30.497478, 30.497478, 
30.497478, 30.497478, 30.497478, 30.497478, 30.497478, 30.497478, 
30.497478, 30.497478, 30.497478, 30.497478), Longitude = c(-87.880258, 
-87.880258, -87.880258, -87.880258, -87.880258, -87.880258, -87.880258, 
-87.880258, -87.880258, -87.880258, -87.880258, -87.880258, -87.880258, 
-87.880258, -87.880258, -87.880258, -87.880258, -87.880258, -87.880258, 
-87.880258), Datum = c("NAD83", "NAD83", "NAD83", "NAD83", "NAD83", 
"NAD83", "NAD83", "NAD83", "NAD83", "NAD83", "NAD83", "NAD83", 
"NAD83", "NAD83", "NAD83", "NAD83", "NAD83", "NAD83", "NAD83", 
"NAD83"), Parameter.Name = c("Ozone", "Ozone", "Ozone", "Ozone", 
"Ozone", "Ozone", "Ozone", "Ozone", "Ozone", "Ozone", "Ozone", 
"Ozone", "Ozone", "Ozone", "Ozone", "Ozone", "Ozone", "Ozone", 
"Ozone", "Ozone"), Date.Local = c("2016-03-01", "2016-03-01", 
"2016-03-01", "2016-03-01", "2016-03-01", "2016-03-01", "2016-03-01", 
"2016-03-01", "2016-03-01", "2016-03-02", "2016-03-02", "2016-03-02", 
"2016-03-02", "2016-03-02", "2016-03-02", "2016-03-02", "2016-03-02", 
"2016-03-02", "2016-03-02", "2016-03-02"), Time.Local = c("15:00", 
"16:00", "17:00", "18:00", "19:00", "20:00", "21:00", "22:00", 
"23:00", "01:00", "02:00", "03:00", "04:00", "05:00", "06:00", 
"07:00", "08:00", "10:00", "11:00", "12:00"), Date.GMT = c("2016-03-01", 
"2016-03-01", "2016-03-01", "2016-03-02", "2016-03-02", "2016-03-02", 
"2016-03-02", "2016-03-02", "2016-03-02", "2016-03-02", "2016-03-02", 
"2016-03-02", "2016-03-02", "2016-03-02", "2016-03-02", "2016-03-02", 
"2016-03-02", "2016-03-02", "2016-03-02", "2016-03-02"), Time.GMT = c("21:00", 
"22:00", "23:00", "00:00", "01:00", "02:00", "03:00", "04:00", 
"05:00", "07:00", "08:00", "09:00", "10:00", "11:00", "12:00", 
"13:00", "14:00", "16:00", "17:00", "18:00"), Sample.Measurement = c(0.041, 
0.041, 0.042, 0.041, 0.038, 0.038, 0.036, 0.035, 0.029, 0.026, 
0.03, 0.03, 0.028, 0.027, 0.025, 0.023, 0.025, 0.034, 0.036, 
0.038), Units.of.Measure = c("Parts per million", "Parts per million", 
"Parts per million", "Parts per million", "Parts per million", 
"Parts per million", "Parts per million", "Parts per million", 
"Parts per million", "Parts per million", "Parts per million", 
"Parts per million", "Parts per million", "Parts per million", 
"Parts per million", "Parts per million", "Parts per million", 
"Parts per million", "Parts per million", "Parts per million"
), MDL = c(0.005, 0.005, 0.005, 0.005, 0.005, 0.005, 0.005, 0.005, 
0.005, 0.005, 0.005, 0.005, 0.005, 0.005, 0.005, 0.005, 0.005, 
0.005, 0.005, 0.005), Uncertainty = c(NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), Qualifier = c("", 
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 
"", "", ""), Method.Type = c("FEM", "FEM", "FEM", "FEM", "FEM", 
"FEM", "FEM", "FEM", "FEM", "FEM", "FEM", "FEM", "FEM", "FEM", 
"FEM", "FEM", "FEM", "FEM", "FEM", "FEM"), Method.Code = c(47L, 
47L, 47L, 47L, 47L, 47L, 47L, 47L, 47L, 47L, 47L, 47L, 47L, 47L, 
47L, 47L, 47L, 47L, 47L, 47L), Method.Name = c("INSTRUMENTAL - ULTRA VIOLET", 
"INSTRUMENTAL - ULTRA VIOLET", "INSTRUMENTAL - ULTRA VIOLET", 
"INSTRUMENTAL - ULTRA VIOLET", "INSTRUMENTAL - ULTRA VIOLET", 
"INSTRUMENTAL - ULTRA VIOLET", "INSTRUMENTAL - ULTRA VIOLET", 
"INSTRUMENTAL - ULTRA VIOLET", "INSTRUMENTAL - ULTRA VIOLET", 
"INSTRUMENTAL - ULTRA VIOLET", "INSTRUMENTAL - ULTRA VIOLET", 
"INSTRUMENTAL - ULTRA VIOLET", "INSTRUMENTAL - ULTRA VIOLET", 
"INSTRUMENTAL - ULTRA VIOLET", "INSTRUMENTAL - ULTRA VIOLET", 
"INSTRUMENTAL - ULTRA VIOLET", "INSTRUMENTAL - ULTRA VIOLET", 
"INSTRUMENTAL - ULTRA VIOLET", "INSTRUMENTAL - ULTRA VIOLET", 
"INSTRUMENTAL - ULTRA VIOLET"), State.Name = c("Alabama", "Alabama", 
"Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama", 
"Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama", 
"Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama"
), County.Name = c("Baldwin", "Baldwin", "Baldwin", "Baldwin", 
"Baldwin", "Baldwin", "Baldwin", "Baldwin", "Baldwin", "Baldwin", 
"Baldwin", "Baldwin", "Baldwin", "Baldwin", "Baldwin", "Baldwin", 
"Baldwin", "Baldwin", "Baldwin", "Baldwin"), Date.of.Last.Change = c("2016-06-20", 
"2016-06-20", "2016-06-20", "2016-06-20", "2016-06-20", "2016-06-20", 
"2016-06-20", "2016-06-20", "2016-06-20", "2016-06-20", "2016-06-20", 
"2016-06-20", "2016-06-20", "2016-06-20", "2016-06-20", "2016-06-20", 
"2016-06-20", "2016-06-20", "2016-06-20", "2016-06-20")), row.names = c(NA, 
20L), class = "data.frame")
>
like image 901
bibscy Avatar asked Aug 14 '26 15:08

bibscy

1 Answers

Another variation on linear interpolation:

library(dplyr)
library(purrr)
library(lubridate)

data <- hrOzone %>% select(Date.GMT,Time.GMT,Sample.Measurement) %>%
                    mutate(date  = ymd(Date.GMT) + hm(Time.GMT)) 
             
data.extended <- approx(x = data$date,
                        y = data$Sample.Measurement,
                        xout = seq(from = min(data$date),
                                   to   = max(data$date),
                                   by   = 3600))

map2_dfr(data.extended$x, data.extended$y,
         ~(list(DateTime.Gmt=.x,
                Date.GMT=format(.x,"%Y-%m-%d"),
                Time.GMT=format(.x,"%H:%M"),
                Sample.Measurement=.y))))

# A tibble: 22 x 4
   DateTime.Gmt        Date.GMT   Time.GMT Sample.Measurement
   <dttm>              <chr>      <chr>                 <dbl>
 1 2016-03-01 21:00:00 2016-03-01 21:00                0.041 
 2 2016-03-01 22:00:00 2016-03-01 22:00                0.041 
 3 2016-03-01 23:00:00 2016-03-01 23:00                0.042 
 4 2016-03-02 00:00:00 2016-03-02 00:00                0.041 
 5 2016-03-02 01:00:00 2016-03-02 01:00                0.038 
 6 2016-03-02 02:00:00 2016-03-02 02:00                0.038 
 7 2016-03-02 03:00:00 2016-03-02 03:00                0.036 
 8 2016-03-02 04:00:00 2016-03-02 04:00                0.035 
 9 2016-03-02 05:00:00 2016-03-02 05:00                0.029 
10 2016-03-02 06:00:00 2016-03-02 06:00                0.0275
# ... with 12 more rows
like image 171
Waldi Avatar answered Aug 17 '26 06:08

Waldi
Sign in to Comment

Related questions

R - For each row in a data frame, how to check if at least one column is not NA? [duplicate]
Transforming R code into R style

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!