Efficient dynamic addition of rows in dataframe and dynamic calculation in R

Question

I have the following dataframe (ts1):

                D1 Value N
1 20/11/2014 16:00 0.00 
2 20/11/2014 17:00 0.01  1
3 20/11/2014 19:00 0.05  2
4 20/11/2014 22:00 0.20  3
5 20/11/2014 23:00 0.03  4

I would like to insert rows as the number of of (N-1) the new ts1 and rows will be:

                D1 Value N
1 20/11/2014 16:00 0.00  1
2 20/11/2014 17:00 0.01  1
3 20/11/2014 18:00 0.03  1 <---
4 20/11/2014 19:00 0.05  1
5 20/11/2014 20:00 0.10  1 <---
6 20/11/2014 21:00 0.15  1 <---
7 20/11/2014 22:00 0.20  1
8 20/11/2014 23:00 0.03  1

As can be seen lines 3, 5 and 6 were added because of the gap in time (N > 1) the number in ts1$Value is filled in by dividing the gap of ts1$Value and dividing them by the number of new rows. I would like to add the values as efficient as possible with minimum number of going over the dataframe.

See Note 1 in my answer to your previous question here: http://stackoverflow.com/questions/34705674/calculating-differences-of-dates-in-hours-between-rows-of-a-dataframe/34706210#34706210 — G. Grothendieck, Jan 10 '16 at 14:28
Thanks a lot @G. Grothendieck, I do really use N as an intermediate calculation. My target is to fill in the missing values in ts1$Value. My question is still how can I do it in the most efficient way either by time series or by using N? — Avi, Jan 10 '16 at 15:56

Avi · Accepted Answer · 2016-01-12T06:59:58.580

Here is the complete solution: The usage of the last command of linear interpolation solves the issue

> Lines <- "D1,Value
+ 1,20/11/2014 16:00,0.00
+ 2,20/11/2014 17:00,0.01  
+ 3,20/11/2014 19:00,0.05  
+ 4,20/11/2014 22:00,0.20  
+ 5,20/11/2014 23:00,0.03"
> ts1 <- read.csv(text = Lines, as.is = TRUE)
> library(zoo)
> z <- read.zoo(ts1, tz = "", format = "%d/%m/%Y %H:%M")
> 
> z0 <- zoo(, seq(start(z), end(z), "hours"))
> zz <- merge(z, z0)
> interpolated <- na.approx(zz)
> interpolated
2014-11-20 16:00:00 2014-11-20 17:00:00 2014-11-20 18:00:00 2014-11-20 19:00:00 2014-11-20 20:00:00 2014-11-20 21:00:00 
               0.00                0.01                0.03                0.05                0.10                0.15 
2014-11-20 22:00:00 2014-11-20 23:00:00 
               0.20                0.03

You can replace the last 4 lines with `na.approx(z, xout = seq(start(z), end(z), "hours"))` — G. Grothendieck, Jan 10 '16 at 23:20

Efficient dynamic addition of rows in dataframe and dynamic calculation in R

1 Answers1

Linked