R: Find missing timestamps in csv
<p>as I failed to solve my problem with PHP/MySQL or Excel due to the data size, I'm trying to do my very first steps with R now and struggle a bit. The problem is this: I have a second-by-second CSV-file with half a year of data, that looks like this:</p>

<pre><code>metering,timestamp
123,2016-01-01 00:00:00
345,2016-01-01 00:00:01
243,2016-01-01 00:00:02
101,2016-01-01 00:00:04
134,2016-01-01 00:00:06
</code></pre>

<p>As you see, there are some seconds missing every once in a while (don't ask me, why the values are written before the timestamp, but that's how I received the data…). Now I try to calculate the amount of values (= seconds) that are missing.</p>

<p>So my idea was</p>

<ol>
<li>to create a vector that is correct (includes all sec-by-sec timestamps), </li>
<li>match the given CSV file with that new vector, and</li>
<li>sum up all the timestamps with no value.</li>
</ol>

<p>I managed to make step 1 happen with the following code:</p>

<pre><code>RegularTimeSeries &lt;- seq(as.POSIXct("2016-01-01 00:00:00", tz = "UTC"), as.POSIXct("2016-01-01 00:00:30", tz = "UTC"), by = "1 sec")  
write.csv(RegularTimeSeries, file = "RegularTimeSeries.csv")
</code></pre>

<p>To have an idea what I did I also exported the vector to a CSV that looks like this:</p>

<pre><code>"1",2016-01-01 00:00:00
"2",2016-01-01 00:00:01
"3",2016-01-01 00:00:02
"4",2016-01-01 00:00:03
"5",2016-01-01 00:00:04
"6",2016-01-01 00:00:05
"7",2016-01-01 00:00:06
</code></pre>

<p>Unfortunately I have no idea how to go on with step 2 and 3. I found some very similar examples (<a href="http://www.r-bloggers.com/fix-missing-dates-with-r/" rel="nofollow noreferrer">http://www.r-bloggers.com/fix-missing-dates-with-r/</a>, <a href="https://stackoverflow.com/questions/16787038/r-insert-rows-for-missing-dates-times">R: Insert rows for missing dates/times</a>), but as a total R noob I struggled to translate these examples to my given sec-by-sec data. </p>

<p>Some hints for the greenhorn would be very very helpful – thank you very much in advance :)</p>