zhrandell
commented
1 year ago @m-h-williams, after looking at the .csv files converted from .bin files, I now see that the sampling frequency of the ping altimeter is WAY faster than 0.5s . . . there are MANY values (e.g., a dozen) per second. So, given that we need to combine these ping data with our primary ROV telemetry file, which is at the 1s scale, I wrote a function to calculate average ping and confidence values every 1s.
The function is a little clunky, but it works. Code assumes you have a .csv file named dat . . . I worked off of the first .csv Ping file you converted (which I renamed locally)
## load packages
library(tidyverse)
library(lubridate) ## lubridate should be in tidyverse, but adding it here just in case
library(hms)
## load data
dat <- read.csv("PingData.csv", header=TRUE)
## rename columns
names(dat)[2] <- "dist"
names(dat)[3] <- "conf"
## convert altimeter data to meters
dat$dist <- dat$dist/1000
## function to calculate average ping and confidence per 1s
Avg.Ping <- function(x){
x$time <- as_hms(ymd_hms(x$timestamp)) ## extract HH:MM:SS
x$short <- str_sub(x$time, start=1L, end=8L) ## delete the fractions of a second characters
sec <- aggregate(x$dist, by=list(time=x$short), FUN=mean) ## calculate average based on second
names(sec)[2] <- "avg_dist" ## rename column
conf <- aggregate(x$conf, by=list(time=x$short), FUN=mean) ## calculate average based on second
names(conf)[2] <- "avg_conf" ## rename column
out <- cbind(sec, conf$avg_conf) ## bind the relevant columns
names(out)[3] <- "avg_conf" ## rename colume
return(out) ## export output
}
## call function
new_dat <- Avg.Ping(dat)
The dataframe output looks like:
time avg_dist avg_conf
134 09:59:53 1.4327333 100.0000000
135 09:59:54 1.3632667 100.0000000
136 09:59:55 1.2802000 100.0000000
137 09:59:56 1.2484375 100.0000000
138 09:59:57 1.2814000 100.0000000
139 09:59:58 1.2641333 100.0000000
140 09:59:59 1.2270000 100.0000000
141 10:00:00 1.1866000 100.0000000
142 10:00:01 1.1262667 100.0000000
143 10:00:02 1.0632667 100.0000000
144 10:00:03 1.0111333 100.0000000
145 10:00:04 0.9783750 100.0000000
146 10:00:05 0.9722000 100.0000000
147 10:00:06 0.9107333 100.0000000
148 10:00:07 0.8393333 100.0000000
149 10:00:08 0.8012000 100.0000000. . . with a separate value for each second. We can then use the time column to bind this dataframe with the ROV telemetry file (assuming the ROV telemetry file has the same time column w/ the same values).
m-h-williams
Opening this issue to organize code, resources, etc. re: the extraction of PingViewer data from .bin files and conversion into .csv files. Once the conversion is complete, we'll need to extract the relevant column(s) from the .csv and merge with the QGroundControl .csv files. I anticipate we'll need to do this by aligning HH:MM time stamps (and note there is a new Tidyverse package specifically for working with date/time). I also anticipate it'll be easier to merge the Ping .csv data with the raw QGroundControl .csv file, and only after that's complete will we want to clean up the .csv file, as I've exampled with this R-script.
First things first though, we'll need to extract the PingViewer data. This BlueRobotics post includes a Python script that we can use to make this happen.
All QGroundControl telemetry files (.csv) from the ROV dives completed with the Port of Seattle 2022_8_15 have been uploaded here, and the corresponding PingViewer (.bin) files have been uploaded here. Note that several transects were completed back-to-back, thus at least two of the files will contain multiple transects. This will be a good test of our ability to partition separate transects using the Ping data, once that's extracted and integrated into the primary QGC .csv file.