Downscale the entire DataFrame to high temporal resolution using linear interpolation in R-CodePudding

I have a DataFrame with many columns. The data is at 3 hour temporal resolution. I want to create a new DataFrame at 30 minutes interval by linear interpolation from the existing DataFrame. I know one approach based on approxfun but I don't how to apply this for the entire DataFrame. Is there any more efficient approach for this problem? Below is the sample DataFrame and the approach I tried:

df <- structure(list(DateTime = structure(c(1475285400, 1475296200, 1475307000, 1475317800, 1475328600, 1475339400, 1475350200, 1475361000, 1475371800, 1475382600, 1475393400, 1475404200, 1475415000, 1475425800, 1475436600, 1475447400, 1475458200, 1475469000, 1475479800, 1475490600 ), class = c("POSIXct", "POSIXt"), tzone = "UTC"), X1 = c(294.3, 292.5, 291, 289.7, 289.6, 294.5, 297.5, 298.4, 296.3, 292.9, 290.9, 289.8, 290, 296.3, 297.9, 299.2, 297.1, 293.7, 291.7, 290), X2 = c(0.16, 0.16, 0.16, 0.16, 0.14, 0.14, 0.14, 0.14, 0.14, 0.14, 0.14, 0.14, 0.14, 0.14, 0.14, 0.14, 0.14, 0.14, 0.15, 0.15)), row.names = c(NA, 20L), class = "data.frame")

DateTimes <- seq(from=as.POSIXct("2016-10-01 00:00:00",tz="UTC"),to=as.POSIXct("2016-10-04 23:30:00",tz="UTC"),by="30 min")

ApproxFun <- approxfun(x = as.POSIXct(df$DateTime), y = df$X1)

X1_linear_filled <- ApproxFun(DateTimes)

But the problem with the above approach is that I have to repeat the procedure for all the columns.

CodePudding user response：

What about lapply over the columns of interest?

DateTimes <- seq(min(df[,1]), max(df[,1]), by="30 min")
f <- function(y,d) approxfun(x=d, y=y)(DateTimes)

data.frame(DateTimes, do.call(cbind,lapply(df[,2:3],f,d=df[,1])))

or with summarize(across())

df %>%  
  summarize(across(X1:X2, ~f(.x,DateTime))) %>% 
  mutate(DateTime=DateTimes) %>% 
  relocate(DateTime)

Output:

              DateTimes       X1        X2
1   2016-10-01 01:30:00 294.3000 0.1600000
2   2016-10-01 02:00:00 294.0000 0.1600000
3   2016-10-01 02:30:00 293.7000 0.1600000
4   2016-10-01 03:00:00 293.4000 0.1600000
5   2016-10-01 03:30:00 293.1000 0.1600000
6   2016-10-01 04:00:00 292.8000 0.1600000
7   2016-10-01 04:30:00 292.5000 0.1600000
8   2016-10-01 05:00:00 292.2500 0.1600000
9   2016-10-01 05:30:00 292.0000 0.1600000
10  2016-10-01 06:00:00 291.7500 0.1600000
11  2016-10-01 06:30:00 291.5000 0.1600000
12  2016-10-01 07:00:00 291.2500 0.1600000
13  2016-10-01 07:30:00 291.0000 0.1600000
14  2016-10-01 08:00:00 290.7833 0.1600000
15  2016-10-01 08:30:00 290.5667 0.1600000
16  2016-10-01 09:00:00 290.3500 0.1600000
17  2016-10-01 09:30:00 290.1333 0.1600000
18  2016-10-01 10:00:00 289.9167 0.1600000
19  2016-10-01 10:30:00 289.7000 0.1600000
20  2016-10-01 11:00:00 289.6833 0.1566667
21  2016-10-01 11:30:00 289.6667 0.1533333
22  2016-10-01 12:00:00 289.6500 0.1500000
23  2016-10-01 12:30:00 289.6333 0.1466667
24  2016-10-01 13:00:00 289.6167 0.1433333
25  2016-10-01 13:30:00 289.6000 0.1400000
26  2016-10-01 14:00:00 290.4167 0.1400000
27  2016-10-01 14:30:00 291.2333 0.1400000
28  2016-10-01 15:00:00 292.0500 0.1400000
29  2016-10-01 15:30:00 292.8667 0.1400000
30  2016-10-01 16:00:00 293.6833 0.1400000
31  2016-10-01 16:30:00 294.5000 0.1400000
32  2016-10-01 17:00:00 295.0000 0.1400000
33  2016-10-01 17:30:00 295.5000 0.1400000
34  2016-10-01 18:00:00 296.0000 0.1400000
35  2016-10-01 18:30:00 296.5000 0.1400000
36  2016-10-01 19:00:00 297.0000 0.1400000
37  2016-10-01 19:30:00 297.5000 0.1400000
38  2016-10-01 20:00:00 297.6500 0.1400000
39  2016-10-01 20:30:00 297.8000 0.1400000
40  2016-10-01 21:00:00 297.9500 0.1400000
41  2016-10-01 21:30:00 298.1000 0.1400000
42  2016-10-01 22:00:00 298.2500 0.1400000
43  2016-10-01 22:30:00 298.4000 0.1400000
44  2016-10-01 23:00:00 298.0500 0.1400000
45  2016-10-01 23:30:00 297.7000 0.1400000
46  2016-10-02 00:00:00 297.3500 0.1400000
47  2016-10-02 00:30:00 297.0000 0.1400000
48  2016-10-02 01:00:00 296.6500 0.1400000
49  2016-10-02 01:30:00 296.3000 0.1400000
50  2016-10-02 02:00:00 295.7333 0.1400000
51  2016-10-02 02:30:00 295.1667 0.1400000
52  2016-10-02 03:00:00 294.6000 0.1400000
53  2016-10-02 03:30:00 294.0333 0.1400000
54  2016-10-02 04:00:00 293.4667 0.1400000
55  2016-10-02 04:30:00 292.9000 0.1400000
56  2016-10-02 05:00:00 292.5667 0.1400000
57  2016-10-02 05:30:00 292.2333 0.1400000
58  2016-10-02 06:00:00 291.9000 0.1400000
59  2016-10-02 06:30:00 291.5667 0.1400000
60  2016-10-02 07:00:00 291.2333 0.1400000
61  2016-10-02 07:30:00 290.9000 0.1400000
62  2016-10-02 08:00:00 290.7167 0.1400000
63  2016-10-02 08:30:00 290.5333 0.1400000
64  2016-10-02 09:00:00 290.3500 0.1400000
65  2016-10-02 09:30:00 290.1667 0.1400000
66  2016-10-02 10:00:00 289.9833 0.1400000
67  2016-10-02 10:30:00 289.8000 0.1400000
68  2016-10-02 11:00:00 289.8333 0.1400000
69  2016-10-02 11:30:00 289.8667 0.1400000
70  2016-10-02 12:00:00 289.9000 0.1400000
71  2016-10-02 12:30:00 289.9333 0.1400000
72  2016-10-02 13:00:00 289.9667 0.1400000
73  2016-10-02 13:30:00 290.0000 0.1400000
74  2016-10-02 14:00:00 291.0500 0.1400000
75  2016-10-02 14:30:00 292.1000 0.1400000
76  2016-10-02 15:00:00 293.1500 0.1400000
77  2016-10-02 15:30:00 294.2000 0.1400000
78  2016-10-02 16:00:00 295.2500 0.1400000
79  2016-10-02 16:30:00 296.3000 0.1400000
80  2016-10-02 17:00:00 296.5667 0.1400000
81  2016-10-02 17:30:00 296.8333 0.1400000
82  2016-10-02 18:00:00 297.1000 0.1400000
83  2016-10-02 18:30:00 297.3667 0.1400000
84  2016-10-02 19:00:00 297.6333 0.1400000
85  2016-10-02 19:30:00 297.9000 0.1400000
86  2016-10-02 20:00:00 298.1167 0.1400000
87  2016-10-02 20:30:00 298.3333 0.1400000
88  2016-10-02 21:00:00 298.5500 0.1400000
89  2016-10-02 21:30:00 298.7667 0.1400000
90  2016-10-02 22:00:00 298.9833 0.1400000
91  2016-10-02 22:30:00 299.2000 0.1400000
92  2016-10-02 23:00:00 298.8500 0.1400000
93  2016-10-02 23:30:00 298.5000 0.1400000
94  2016-10-03 00:00:00 298.1500 0.1400000
95  2016-10-03 00:30:00 297.8000 0.1400000
96  2016-10-03 01:00:00 297.4500 0.1400000
97  2016-10-03 01:30:00 297.1000 0.1400000
98  2016-10-03 02:00:00 296.5333 0.1400000
99  2016-10-03 02:30:00 295.9667 0.1400000
100 2016-10-03 03:00:00 295.4000 0.1400000
101 2016-10-03 03:30:00 294.8333 0.1400000
102 2016-10-03 04:00:00 294.2667 0.1400000
103 2016-10-03 04:30:00 293.7000 0.1400000
104 2016-10-03 05:00:00 293.3667 0.1416667
105 2016-10-03 05:30:00 293.0333 0.1433333
106 2016-10-03 06:00:00 292.7000 0.1450000
107 2016-10-03 06:30:00 292.3667 0.1466667
108 2016-10-03 07:00:00 292.0333 0.1483333
109 2016-10-03 07:30:00 291.7000 0.1500000
110 2016-10-03 08:00:00 291.4167 0.1500000
111 2016-10-03 08:30:00 291.1333 0.1500000
112 2016-10-03 09:00:00 290.8500 0.1500000
113 2016-10-03 09:30:00 290.5667 0.1500000
114 2016-10-03 10:00:00 290.2833 0.1500000
115 2016-10-03 10:30:00 290.0000 0.1500000