library(zoo)
##
## Attaching package: 'zoo'
## The following objects are masked from 'package:base':
##
## as.Date, as.Date.numeric
library(dplyr)
##
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
setwd("~/Downloads")
# Read data
dat = read.csv(
file = "vine1_29.8_0000.csv",
skip = 3,
col.names = paste0("V", 1:14),
stringsAsFactors = FALSE
)
# Original table
head(dat, 30)
## V1 V2 V3 V4 V5 V6 V7 V8
## 1 00:00.1 18817372 12.42 NAN NAN -0.103 0.008 -0.188
## 2 00:00.2 18817373 12.42 -0.173 -0.075 -0.121 -0.517 -0.507
## 3 00:00.3 18817374 12.42 -0.071 0.032 -0.160 -0.546 -0.472
## 4 00:00.4 18817375 12.42 -0.102 -0.031 -0.142 -0.459 -0.423
## 5 00:00.5 18817376 12.42 -0.092 -0.1 -0.167 -0.530 -0.439
## 6 00:00.6 18817377 12.42 -0.152 -0.063 -0.166 -0.635 -0.418
## 7 00:00.7 18817378 12.42 -0.066 -0.083 -0.140 -0.558 -0.520
## 8 00:00.8 18817379 12.42 -0.112 -0.002 -0.205 -0.566 -0.493
## 9 00:00.9 18817380 12.42 -0.152 -0.014 -0.167 -0.580 -0.454
## 10 29/08/2018 0:00 18817381 12.42 -0.092 -0.093 -0.141 -0.563 -0.497
## 11 00:01.1 18817382 12.42 -0.104 -0.052 -0.141 -0.517 -0.510
## 12 00:01.2 18817383 12.42 -0.062 -0.085 -0.157 -0.611 -0.493
## 13 00:01.3 18817384 12.42 -0.146 -0.068 -0.186 -0.530 -0.477
## 14 00:01.4 18817385 12.42 -0.164 -0.077 -0.090 -0.617 -0.459
## 15 00:01.5 18817386 12.42 -0.144 -0.056 -0.138 -0.572 -0.436
## 16 00:01.6 18817387 12.42 -0.112 -0.006 -0.209 -0.612 -0.460
## 17 00:01.7 18817388 12.42 -0.159 -0.042 -0.130 -0.551 -0.490
## 18 00:01.8 18817389 12.42 -0.078 -0.101 -0.185 -0.630 -0.549
## 19 00:01.9 18817390 12.42 -0.132 -0.13 -0.198 -0.601 -0.483
## 20 29/08/2018 0:00 18817391 12.42 -0.138 -0.059 -0.197 -0.522 -0.456
## 21 00:02.1 18817392 12.42 -0.113 -0.07 -0.175 -0.564 -0.538
## 22 00:02.2 18817393 12.42 -0.124 -0.13 -0.075 -0.545 -0.559
## 23 00:02.3 18817394 12.42 -0.125 -0.129 -0.191 -0.569 -0.520
## 24 00:02.4 18817395 12.42 -0.111 -0.128 -0.190 -0.597 -0.495
## 25 00:02.5 18817396 12.42 -0.084 -0.119 -0.186 -0.585 -0.592
## 26 00:02.6 18817397 12.42 -0.131 -0.132 -0.192 -0.590 -0.489
## 27 00:02.7 18817398 12.42 -0.107 -0.109 -0.230 -0.633 -0.537
## 28 00:02.8 18817399 12.42 -0.086 -0.07 -0.177 -0.588 -0.444
## 29 00:02.9 18817400 12.42 -0.057 -0.154 -0.203 -0.602 -0.562
## 30 29/08/2018 0:00 18817401 12.42 -0.16 -0.111 -0.176 -0.609 -0.572
## V9 V10 V11 V12 V13 V14
## 1 -0.554 -0.484 -0.517 -0.382 -0.546 52.37
## 2 -0.481 -0.449 -0.422 -0.386 -24.160 24.32
## 3 -0.496 -0.423 -0.459 -0.435 -24.250 24.34
## 4 -0.490 -0.473 -0.486 -0.419 -24.200 24.33
## 5 -0.554 -0.414 -0.428 -0.423 -24.230 24.30
## 6 -0.604 -0.475 -0.482 -0.472 -24.270 24.31
## 7 -0.478 -0.463 -0.470 -0.443 -24.270 24.31
## 8 -0.456 -0.402 -0.398 -0.410 -24.240 24.33
## 9 -0.509 -0.478 -0.445 -0.454 -24.200 24.34
## 10 -0.519 -0.343 -0.441 -0.386 -24.240 24.34
## 11 -0.463 -0.434 -0.415 -0.381 -24.380 24.38
## 12 -0.567 -0.426 -0.422 -0.409 -24.270 24.34
## 13 -0.522 -0.467 -0.408 -0.414 -24.120 24.36
## 14 -0.516 -0.457 -0.428 -0.412 -24.260 24.35
## 15 -0.527 -0.419 -0.461 -0.344 -24.310 24.36
## 16 -0.517 -0.442 -0.473 -0.387 -24.180 24.33
## 17 -0.506 -0.414 -0.474 -0.440 -24.260 24.38
## 18 -0.569 -0.538 -0.463 -0.428 -24.190 24.37
## 19 -0.539 -0.407 -0.454 -0.370 -24.140 24.34
## 20 -0.530 -0.383 -0.488 -0.431 -24.240 24.33
## 21 -0.511 -0.431 -0.454 -0.426 -24.190 24.30
## 22 -0.546 -0.328 -0.467 -0.365 -24.200 24.31
## 23 -0.559 -0.408 -0.423 -0.388 -24.130 24.35
## 24 -0.463 -0.423 -0.480 -0.377 -24.220 24.38
## 25 -0.559 -0.492 -0.436 -0.451 -24.230 24.30
## 26 -0.533 -0.469 -0.476 -0.330 -24.260 24.34
## 27 -0.516 -0.420 -0.445 -0.399 -24.160 24.32
## 28 -0.545 -0.420 -0.421 -0.394 -24.220 24.37
## 29 -0.508 -0.431 -0.373 -0.420 -24.270 24.29
## 30 -0.555 -0.340 -0.418 -0.378 -24.260 24.35
# Replace "NAN" with NA
dat[dat == "NAN"] = NA
# Convert all columns except 1st to numeric
dat[, -1] = lapply(dat[, -1], as.numeric)
# Convert to POSIXct
dat$date_hour_minute = gsub("\\..*", "", dat$V1)
dat$date_hour_minute[nchar(dat$date_hour_minute) == 5] = NA
dat$date_hour_minute = na.locf(dat$date_hour_minute, na.rm = FALSE)
dat$second = gsub(".*:", "", dat$V1)
whole_seconds = !grepl(".", dat$second, fixed = TRUE)
next_second = dat$second[which(whole_seconds) + 1]
dat$second[whole_seconds] = gsub(".1", ".0", next_second, fixed = TRUE)
dat$date_hour_minute = as.POSIXct(dat$date_hour_minute, format = "%d/%m/%Y %H:%M")
dat$date_hour_minute = format(dat$date_hour_minute, "%Y-%m-%d %H:%M")
dat$datetime = paste0(dat$date_hour, ":", dat$second)
# Remove unnecessary columns
dat$date_hour_minute = NULL
dat$second = NULL
dat$whole_seconds = NULL
# Reorder
dat = cbind(
dat[, "datetime", drop = FALSE],
dat[, !names(dat) %in% "datetime"]
)
# New table
options(digits.secs = 3)
head(dat, 30)
## datetime V1 V2 V3 V4 V5
## 1 NA:00.1 00:00.1 18817372 12.42 NA NA
## 2 NA:00.2 00:00.2 18817373 12.42 -0.173 -0.075
## 3 NA:00.3 00:00.3 18817374 12.42 -0.071 0.032
## 4 NA:00.4 00:00.4 18817375 12.42 -0.102 -0.031
## 5 NA:00.5 00:00.5 18817376 12.42 -0.092 -0.100
## 6 NA:00.6 00:00.6 18817377 12.42 -0.152 -0.063
## 7 NA:00.7 00:00.7 18817378 12.42 -0.066 -0.083
## 8 NA:00.8 00:00.8 18817379 12.42 -0.112 -0.002
## 9 NA:00.9 00:00.9 18817380 12.42 -0.152 -0.014
## 10 2018-08-29 00:00:01.0 29/08/2018 0:00 18817381 12.42 -0.092 -0.093
## 11 2018-08-29 00:00:01.1 00:01.1 18817382 12.42 -0.104 -0.052
## 12 2018-08-29 00:00:01.2 00:01.2 18817383 12.42 -0.062 -0.085
## 13 2018-08-29 00:00:01.3 00:01.3 18817384 12.42 -0.146 -0.068
## 14 2018-08-29 00:00:01.4 00:01.4 18817385 12.42 -0.164 -0.077
## 15 2018-08-29 00:00:01.5 00:01.5 18817386 12.42 -0.144 -0.056
## 16 2018-08-29 00:00:01.6 00:01.6 18817387 12.42 -0.112 -0.006
## 17 2018-08-29 00:00:01.7 00:01.7 18817388 12.42 -0.159 -0.042
## 18 2018-08-29 00:00:01.8 00:01.8 18817389 12.42 -0.078 -0.101
## 19 2018-08-29 00:00:01.9 00:01.9 18817390 12.42 -0.132 -0.130
## 20 2018-08-29 00:00:02.0 29/08/2018 0:00 18817391 12.42 -0.138 -0.059
## 21 2018-08-29 00:00:02.1 00:02.1 18817392 12.42 -0.113 -0.070
## 22 2018-08-29 00:00:02.2 00:02.2 18817393 12.42 -0.124 -0.130
## 23 2018-08-29 00:00:02.3 00:02.3 18817394 12.42 -0.125 -0.129
## 24 2018-08-29 00:00:02.4 00:02.4 18817395 12.42 -0.111 -0.128
## 25 2018-08-29 00:00:02.5 00:02.5 18817396 12.42 -0.084 -0.119
## 26 2018-08-29 00:00:02.6 00:02.6 18817397 12.42 -0.131 -0.132
## 27 2018-08-29 00:00:02.7 00:02.7 18817398 12.42 -0.107 -0.109
## 28 2018-08-29 00:00:02.8 00:02.8 18817399 12.42 -0.086 -0.070
## 29 2018-08-29 00:00:02.9 00:02.9 18817400 12.42 -0.057 -0.154
## 30 2018-08-29 00:00:03.0 29/08/2018 0:00 18817401 12.42 -0.160 -0.111
## V6 V7 V8 V9 V10 V11 V12 V13 V14
## 1 -0.103 0.008 -0.188 -0.554 -0.484 -0.517 -0.382 -0.546 52.37
## 2 -0.121 -0.517 -0.507 -0.481 -0.449 -0.422 -0.386 -24.160 24.32
## 3 -0.160 -0.546 -0.472 -0.496 -0.423 -0.459 -0.435 -24.250 24.34
## 4 -0.142 -0.459 -0.423 -0.490 -0.473 -0.486 -0.419 -24.200 24.33
## 5 -0.167 -0.530 -0.439 -0.554 -0.414 -0.428 -0.423 -24.230 24.30
## 6 -0.166 -0.635 -0.418 -0.604 -0.475 -0.482 -0.472 -24.270 24.31
## 7 -0.140 -0.558 -0.520 -0.478 -0.463 -0.470 -0.443 -24.270 24.31
## 8 -0.205 -0.566 -0.493 -0.456 -0.402 -0.398 -0.410 -24.240 24.33
## 9 -0.167 -0.580 -0.454 -0.509 -0.478 -0.445 -0.454 -24.200 24.34
## 10 -0.141 -0.563 -0.497 -0.519 -0.343 -0.441 -0.386 -24.240 24.34
## 11 -0.141 -0.517 -0.510 -0.463 -0.434 -0.415 -0.381 -24.380 24.38
## 12 -0.157 -0.611 -0.493 -0.567 -0.426 -0.422 -0.409 -24.270 24.34
## 13 -0.186 -0.530 -0.477 -0.522 -0.467 -0.408 -0.414 -24.120 24.36
## 14 -0.090 -0.617 -0.459 -0.516 -0.457 -0.428 -0.412 -24.260 24.35
## 15 -0.138 -0.572 -0.436 -0.527 -0.419 -0.461 -0.344 -24.310 24.36
## 16 -0.209 -0.612 -0.460 -0.517 -0.442 -0.473 -0.387 -24.180 24.33
## 17 -0.130 -0.551 -0.490 -0.506 -0.414 -0.474 -0.440 -24.260 24.38
## 18 -0.185 -0.630 -0.549 -0.569 -0.538 -0.463 -0.428 -24.190 24.37
## 19 -0.198 -0.601 -0.483 -0.539 -0.407 -0.454 -0.370 -24.140 24.34
## 20 -0.197 -0.522 -0.456 -0.530 -0.383 -0.488 -0.431 -24.240 24.33
## 21 -0.175 -0.564 -0.538 -0.511 -0.431 -0.454 -0.426 -24.190 24.30
## 22 -0.075 -0.545 -0.559 -0.546 -0.328 -0.467 -0.365 -24.200 24.31
## 23 -0.191 -0.569 -0.520 -0.559 -0.408 -0.423 -0.388 -24.130 24.35
## 24 -0.190 -0.597 -0.495 -0.463 -0.423 -0.480 -0.377 -24.220 24.38
## 25 -0.186 -0.585 -0.592 -0.559 -0.492 -0.436 -0.451 -24.230 24.30
## 26 -0.192 -0.590 -0.489 -0.533 -0.469 -0.476 -0.330 -24.260 24.34
## 27 -0.230 -0.633 -0.537 -0.516 -0.420 -0.445 -0.399 -24.160 24.32
## 28 -0.177 -0.588 -0.444 -0.545 -0.420 -0.421 -0.394 -24.220 24.37
## 29 -0.203 -0.602 -0.562 -0.508 -0.431 -0.373 -0.420 -24.270 24.29
## 30 -0.176 -0.609 -0.572 -0.555 -0.340 -0.418 -0.378 -24.260 24.35
# Fill missing values
datetime = as.POSIXct(dat$datetime, format = "%Y-%m-%d %H:%M:%OS")
datetime = seq(
from = min(datetime, na.rm = TRUE),
to = max(datetime, na.rm = TRUE),
by = "1 secs"
)
datetime = as.character(datetime)
tmp = expand.grid(datetime = datetime, secs = paste0(".", 0:9))
tmp$datetime = paste0(tmp$datetime, tmp$secs)
tmp$secs = NULL
tmp = tmp[order(tmp$datetime), , drop = FALSE]
dat = left_join(tmp, dat, "datetime")
# New table
head(dat, 30)
## datetime V1 V2 V3 V4 V5
## 1 2018-08-29 00:00:01.0 29/08/2018 0:00 18817381 12.42 -0.092 -0.093
## 2 2018-08-29 00:00:01.1 00:01.1 18817382 12.42 -0.104 -0.052
## 3 2018-08-29 00:00:01.2 00:01.2 18817383 12.42 -0.062 -0.085
## 4 2018-08-29 00:00:01.3 00:01.3 18817384 12.42 -0.146 -0.068
## 5 2018-08-29 00:00:01.4 00:01.4 18817385 12.42 -0.164 -0.077
## 6 2018-08-29 00:00:01.5 00:01.5 18817386 12.42 -0.144 -0.056
## 7 2018-08-29 00:00:01.6 00:01.6 18817387 12.42 -0.112 -0.006
## 8 2018-08-29 00:00:01.7 00:01.7 18817388 12.42 -0.159 -0.042
## 9 2018-08-29 00:00:01.8 00:01.8 18817389 12.42 -0.078 -0.101
## 10 2018-08-29 00:00:01.9 00:01.9 18817390 12.42 -0.132 -0.130
## 11 2018-08-29 00:00:02.0 29/08/2018 0:00 18817391 12.42 -0.138 -0.059
## 12 2018-08-29 00:00:02.1 00:02.1 18817392 12.42 -0.113 -0.070
## 13 2018-08-29 00:00:02.2 00:02.2 18817393 12.42 -0.124 -0.130
## 14 2018-08-29 00:00:02.3 00:02.3 18817394 12.42 -0.125 -0.129
## 15 2018-08-29 00:00:02.4 00:02.4 18817395 12.42 -0.111 -0.128
## 16 2018-08-29 00:00:02.5 00:02.5 18817396 12.42 -0.084 -0.119
## 17 2018-08-29 00:00:02.6 00:02.6 18817397 12.42 -0.131 -0.132
## 18 2018-08-29 00:00:02.7 00:02.7 18817398 12.42 -0.107 -0.109
## 19 2018-08-29 00:00:02.8 00:02.8 18817399 12.42 -0.086 -0.070
## 20 2018-08-29 00:00:02.9 00:02.9 18817400 12.42 -0.057 -0.154
## 21 2018-08-29 00:00:03.0 29/08/2018 0:00 18817401 12.42 -0.160 -0.111
## 22 2018-08-29 00:00:03.1 00:03.1 18817402 12.42 -0.167 -0.137
## 23 2018-08-29 00:00:03.2 00:03.2 18817403 12.42 -0.189 -0.095
## 24 2018-08-29 00:00:03.3 00:03.3 18817404 12.42 -0.106 -0.154
## 25 2018-08-29 00:00:03.4 00:03.4 18817405 12.42 -0.236 -0.125
## 26 2018-08-29 00:00:03.5 00:03.5 18817406 12.42 -0.159 -0.122
## 27 2018-08-29 00:00:03.6 00:03.6 18817407 12.42 -0.165 -0.108
## 28 2018-08-29 00:00:03.7 00:03.7 18817408 12.42 -0.117 -0.077
## 29 2018-08-29 00:00:03.8 00:03.8 18817409 12.42 -0.106 -0.061
## 30 2018-08-29 00:00:03.9 00:03.9 18817410 12.42 -0.164 -0.129
## V6 V7 V8 V9 V10 V11 V12 V13 V14
## 1 -0.141 -0.563 -0.497 -0.519 -0.343 -0.441 -0.386 -24.24 24.34
## 2 -0.141 -0.517 -0.510 -0.463 -0.434 -0.415 -0.381 -24.38 24.38
## 3 -0.157 -0.611 -0.493 -0.567 -0.426 -0.422 -0.409 -24.27 24.34
## 4 -0.186 -0.530 -0.477 -0.522 -0.467 -0.408 -0.414 -24.12 24.36
## 5 -0.090 -0.617 -0.459 -0.516 -0.457 -0.428 -0.412 -24.26 24.35
## 6 -0.138 -0.572 -0.436 -0.527 -0.419 -0.461 -0.344 -24.31 24.36
## 7 -0.209 -0.612 -0.460 -0.517 -0.442 -0.473 -0.387 -24.18 24.33
## 8 -0.130 -0.551 -0.490 -0.506 -0.414 -0.474 -0.440 -24.26 24.38
## 9 -0.185 -0.630 -0.549 -0.569 -0.538 -0.463 -0.428 -24.19 24.37
## 10 -0.198 -0.601 -0.483 -0.539 -0.407 -0.454 -0.370 -24.14 24.34
## 11 -0.197 -0.522 -0.456 -0.530 -0.383 -0.488 -0.431 -24.24 24.33
## 12 -0.175 -0.564 -0.538 -0.511 -0.431 -0.454 -0.426 -24.19 24.30
## 13 -0.075 -0.545 -0.559 -0.546 -0.328 -0.467 -0.365 -24.20 24.31
## 14 -0.191 -0.569 -0.520 -0.559 -0.408 -0.423 -0.388 -24.13 24.35
## 15 -0.190 -0.597 -0.495 -0.463 -0.423 -0.480 -0.377 -24.22 24.38
## 16 -0.186 -0.585 -0.592 -0.559 -0.492 -0.436 -0.451 -24.23 24.30
## 17 -0.192 -0.590 -0.489 -0.533 -0.469 -0.476 -0.330 -24.26 24.34
## 18 -0.230 -0.633 -0.537 -0.516 -0.420 -0.445 -0.399 -24.16 24.32
## 19 -0.177 -0.588 -0.444 -0.545 -0.420 -0.421 -0.394 -24.22 24.37
## 20 -0.203 -0.602 -0.562 -0.508 -0.431 -0.373 -0.420 -24.27 24.29
## 21 -0.176 -0.609 -0.572 -0.555 -0.340 -0.418 -0.378 -24.26 24.35
## 22 -0.210 -0.575 -0.494 -0.500 -0.438 -0.415 -0.412 -24.21 24.40
## 23 -0.102 -0.594 -0.507 -0.551 -0.357 -0.363 -0.395 -24.25 24.28
## 24 -0.159 -0.586 -0.530 -0.502 -0.364 -0.425 -0.403 -24.30 24.35
## 25 -0.084 -0.646 -0.555 -0.532 -0.420 -0.436 -0.398 -24.24 24.28
## 26 -0.131 -0.623 -0.452 -0.528 -0.360 -0.453 -0.356 -24.17 24.28
## 27 -0.144 -0.569 -0.501 -0.548 -0.407 -0.379 -0.354 -24.26 24.32
## 28 -0.149 -0.571 -0.561 -0.583 -0.336 -0.320 -0.360 -24.24 24.33
## 29 -0.181 -0.635 -0.513 -0.554 -0.342 -0.456 -0.298 -24.19 24.32
## 30 -0.199 -0.554 -0.531 -0.558 -0.423 -0.394 -0.339 -24.32 24.33