R - Gap fill a time series
<p>I am trying to fill in the gaps in one of my time series by merging a full day time series into my original time series. But for some reason I get duplicate entries and all the rest of my data is NA.
My data looks like this:</p>

<pre><code>&gt; head(data)
                 TIME Water_Temperature
1 2016-08-22 00:00:00            81.000
2 2016-08-22 00:01:00            80.625
3 2016-08-22 00:02:00            85.000
4 2016-08-22 00:03:00            80.437
5 2016-08-22 00:04:00            85.000
6 2016-08-22 00:05:00            80.375

&gt; tail(data)
                    TIME Water_Temperature
1398 2016-08-22 23:54:00              19.5
1399 2016-08-22 23:55:00              19.5
1400 2016-08-22 23:56:00              19.5
1401 2016-08-22 23:57:00              19.5
1402 2016-08-22 23:58:00              19.5
1403 2016-08-22 23:59:00              19.5
</code></pre>

<p>In between are some minutes missing (1403 rows instead of 1440). I tried to fill them in using:</p>

<pre><code>data.length &lt;- length(data$TIME)
time.min &lt;- data$TIME[1]
time.max &lt;- data$TIME[data.length]
all.dates &lt;- seq(time.min, time.max, by="min")
all.dates.frame &lt;- data.frame(list(TIME=all.dates))
merged.data &lt;- merge(all.dates.frame, data, all=T)
</code></pre>

<p>But that gives me a result of 1449 rows instead of 1440. The first eight minutes are duplicates in the time stamp column and all other values in <code>Water_Temperature</code> are <code>NA</code>. Looks like this:</p>

<pre><code>&gt; merged.data[1:25,]
                  TIME Water_Temperature
1  2016-08-22 00:00:00                NA
2  2016-08-22 00:00:00            81.000
3  2016-08-22 00:01:00                NA
4  2016-08-22 00:01:00            80.625
5  2016-08-22 00:02:00                NA
6  2016-08-22 00:02:00            85.000
7  2016-08-22 00:03:00                NA
8  2016-08-22 00:03:00            80.437
9  2016-08-22 00:04:00                NA
10 2016-08-22 00:04:00            85.000
11 2016-08-22 00:05:00                NA
12 2016-08-22 00:05:00            80.375
13 2016-08-22 00:06:00                NA
14 2016-08-22 00:06:00            80.812
15 2016-08-22 00:07:00                NA
16 2016-08-22 00:07:00            80.812
17 2016-08-22 00:08:00                NA
18 2016-08-22 00:08:00            80.937
19 2016-08-22 00:09:00                NA
20 2016-08-22 00:10:00                NA
21 2016-08-22 00:11:00                NA
22 2016-08-22 00:12:00                NA
23 2016-08-22 00:13:00                NA
24 2016-08-22 00:14:00                NA
25 2016-08-22 00:15:00                NA

&gt; tail(merged.data)
                    TIME Water_Temperature
1444 2016-08-22 23:54:00                NA
1445 2016-08-22 23:55:00                NA
1446 2016-08-22 23:56:00                NA
1447 2016-08-22 23:57:00                NA
1448 2016-08-22 23:58:00                NA
1449 2016-08-22 23:59:00                NA
</code></pre>

<p>Does anyone has an idea whats going wrong? </p>

<p><strong>EDIT:</strong></p>

<p>Using the xts and zoo package now to do the job by doing:</p>

<pre><code>library(xts)
library(zoo)

df1.zoo&lt;-zoo(data[,-1],data[,1])
df2 &lt;- as.data.frame(as.zoo(merge(as.xts(df1.zoo), as.xts(zoo(,seq(start(df1.zoo),end(df1.zoo),by="min"))))))
</code></pre>

<p>Very easy and effective!</p>