0

I have this data.frame (12x2)called df_1 which represents monthly values :

      month    df_test
 [1,]    1 -1.4408567
 [2,]    2 -1.0007642
 [3,]    3  2.1454113
 [4,]    4  1.6935537
 [5,]    5  0.1149219
 [6,]    6 -1.3205144
 [7,]    7  1.0277486
 [8,]    8  1.0323482
 [9,]    9 -0.1442319
[10,]   10 -0.2091197
[11,]   11 -0.6803158
[12,]   12  0.5965196

and this data.frame(8760x2) called df_2 where each rows represent a value associated to an interval of one hour of a day. This data.frame contains hourly values for one year:

                   time           df_time
1           2015-01-01 00:00:00 -0.4035650
2           2015-01-01 01:00:00  0.1800579
3           2015-01-01 02:00:00 -0.3770589
4           2015-01-01 03:00:00  0.2573456
5           2015-01-01 04:00:00  1.2000178
6           2015-01-01 05:00:00 -0.4276127
...........................................
                  time                df_time
8755           2015-12-31 18:00:00  1.3540119
8756           2015-12-31 19:00:00  0.4852843
8757           2015-12-31 20:00:00 -0.9194670
8758           2015-12-31 21:00:00 -1.0751814
8759           2015-12-31 22:00:00  1.0097749
8760           2015-12-31 23:00:00 -0.1032468

I want to obtain df_1 for each hour of each day. The problem is that all months do not have the same amount of days.

Finally we should obtain a data.frame called df_3 (8760x2) that has interpolated values between the values of df_1.

Thanks for help!

Sebastien_H
  • 69
  • 7

1 Answers1

0

Here's done with zoo. I'm assuming that the monthly value is associated with a specific datetime stamp (middle of the month, midnight) - you have to do that. If you want a different datetime stamp, just change the value.

library(zoo)
library(dplyr)
library(tidyr)

df_3 <- df_1 %>%
   mutate(time = paste(2015, month, "15 00:00:00", sep = "-"),
          time = as.POSIXct(strptime(time, "%Y-%m-%d %H:%M:%S"))) %>%
   full_join(df_2) %>%
   arrange(time) %>%
   mutate(df_test = na.approx(df_test, rule = 2))
user2602640
  • 640
  • 6
  • 21
  • I have this error : 'cannot coerce type 'closure' to vector of type 'character'' – Sebastien_H Jan 11 '19 at 11:34
  • Can you post results of `str(df_1)` and `str(df_2)`? Also - can you try to run the pipe in bits? For example, only run up to (and including) the `mutate()` call and see if the error comes up, then only run up to the join, etc. – user2602640 Jan 11 '19 at 11:41
  • > str(df_1) num [1:12, 1:2] 1 2 3 4 5 6 7 8 9 10 ... - attr(*, "dimnames")=List of 2 ..$ : NULL ..$ : chr [1:2] "test" "df_test" > str(df_2) 'data.frame': 8760 obs. of 2 variables: $ seq(start, end, by = "hours"): POSIXct, format: "2015-01-01 00:00:00" "2015-01-01 01:00:00" "2015-01-01 02:00:00" "2015-01-01 03:00:00" ... $ df_time : num 0.207 -0.922 -0.279 0.186 -0.245 ... Error appears in paste – Sebastien_H Jan 11 '19 at 11:54
  • Looks like I was missing a bracket in `mutate`. It runs the `mutate` and `paste` now with no issue on a fake dataset I created. Does it change anything for you? if not - can you add `dput(df_2[1:20])` to provide a sample of df_2? – user2602640 Jan 11 '19 at 12:30
  • Can you show me your example, because it still doesn't work for me... Error in mutate_impl(.data, dots) : Evaluation error: cannot coerce type 'closure' to vector of type 'character'. – Sebastien_H Jan 11 '19 at 12:48
  • I used this definition of df_1. Of course, without df_2, can only run the `mutate` part. It looks like maybe your df_1 isn't a data.frame? `df_1 <- data.frame(month = 1:12, df_test = rnorm(12, 0, 1))` – user2602640 Jan 11 '19 at 12:52
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/186544/discussion-between-sebastien-h-and-user2602640). – Sebastien_H Jan 11 '19 at 13:16