library(zoo)
##
## Attaching package: 'zoo'
## The following objects are masked from 'package:base':
##
## as.Date, as.Date.numeric
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$datetime = paste0(dat$date_hour, ":", dat$second)
dat$datetime = as.POSIXct(dat$datetime, format = "%d/%m/%Y %H:%M:%OS")
# 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:00.1 18817372 12.42 NA NA
## 2 <NA> 00:00.2 18817373 12.42 -0.173 -0.075
## 3 <NA> 00:00.3 18817374 12.42 -0.071 0.032
## 4 <NA> 00:00.4 18817375 12.42 -0.102 -0.031
## 5 <NA> 00:00.5 18817376 12.42 -0.092 -0.100
## 6 <NA> 00:00.6 18817377 12.42 -0.152 -0.063
## 7 <NA> 00:00.7 18817378 12.42 -0.066 -0.083
## 8 <NA> 00:00.8 18817379 12.42 -0.112 -0.002
## 9 <NA> 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.0 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.2 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.5 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.7 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.0 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.2 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.5 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.7 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