You can expand your data frame by day using the accepted solution here:
library(data.table)
df2 <- setDT(df)[, list(Value = Value,
date = seq(from = Start, to = End, length.out = Days)),
by = 1:nrow(df)]
> df2
nrow Value date
1: 1 398 2011-03-24
2: 1 398 2011-03-25
3: 1 398 2011-03-26
4: 1 398 2011-03-27
5: 1 398 2011-03-28
---
750: 16 371 2013-04-11
751: 16 371 2013-04-12
752: 16 371 2013-04-13
753: 16 371 2013-04-14
754: 16 371 2013-04-16
Assuming you want to distribute each value in the original data frame evenly across all the days from start to end, you can then do this:
library(dplyr)
library(lubridate)
df2 %>%
# calculate average for each day
group_by(nrow) %>%
mutate(Value = Value / n()) %>%
ungroup() %>%
# summarize by month
mutate(Month = format(date, "%Y-%m")) %>%
group_by(Month) %>%
summarise(Value = sum(Value)) %>%
ungroup() %>%
# derive start / end dates for each month
mutate(Start = ymd(paste0(Month, "-1"))) %>%
mutate(End = Start %m+% months(1) - 1) %>%
mutate(Days = End - Start + 1) %>%
select(Start, End, Value, Days)
# A tibble: 26 x 4
Start End Value Days
<date> <date> <dbl> <time>
1 2011-03-01 2011-03-31 53.06667 31 days
2 2011-04-01 2011-04-30 192.36667 30 days
3 2011-05-01 2011-05-31 186.94167 31 days
4 2011-06-01 2011-06-30 142.41071 30 days
5 2011-07-01 2011-07-31 173.02198 31 days
6 2011-08-01 2011-08-31 224.42308 31 days
7 2011-09-01 2011-09-30 217.80048 30 days
8 2011-10-01 2011-10-31 225.93750 31 days
9 2011-11-01 2011-11-30 242.25347 30 days
10 2011-12-01 2011-12-31 285.55556 31 days
# ... with 16 more rows
Data:
df <- read.table(
header = T,
stringsAsFactors = F,
text = "Start End Value Days
24-03-2011 24-05-2011 398 60
25-05-2011 21-07-2011 275 56
22-07-2011 13-09-2011 389 52
14-09-2011 18-11-2011 482 64
19-11-2011 13-01-2012 514 54
14-01-2012 19-02-2012 330 35
20-02-2012 12-04-2012 519 51
13-04-2012 24-05-2012 361 40
25-05-2012 24-06-2012 202 29
25-05-2012 06-08-2012 691 72
07-08-2012 23-09-2012 376 46
24-09-2012 06-11-2012 300 42
07-11-2012 21-12-2012 272 43
22-12-2012 31-01-2013 276 39
01-02-2013 02-03-2013 188 28
03-03-2013 16-04-2013 371 43"
)
df$Start = as.Date(df$Start, "%d-%m-%Y")
df$End = as.Date(df$End, "%d-%m-%Y")
