101 lines
3.3 KiB
R
101 lines
3.3 KiB
R
library(DBI)
|
|
library(tidyr)
|
|
library(dplyr)
|
|
library(lubridate)
|
|
library(R.utils)
|
|
|
|
|
|
get_freq_df <- function(con, startDate, endDate) {
|
|
startStr <- strftime(startDate, "%Y-%m-%d %H:%M:%S", tz="UTC")
|
|
endStr <- strftime(endDate, "%Y-%m-%d %H:%M:%S", tz="UTC")
|
|
|
|
# get from database
|
|
res <-dbSendQuery(con, "select time, location, freq from mainsfrequency where valid=1 and time >= $1 and time < $2")
|
|
dbBind(res, list(startStr, endStr))
|
|
frequencies <- dbFetch(res)
|
|
dbClearResult(res)
|
|
|
|
# get values from all location at one time in a row
|
|
freq_wide <- frequencies %>%
|
|
pivot_wider(names_from = location,
|
|
values_from = freq,
|
|
values_fn = mean)
|
|
|
|
# remove measurement error (frequency gradient greater than THRESHOLD)
|
|
THRESHOLD <- 0.5
|
|
for (colIdx in 2:length(freq_wide)) {
|
|
last <- freq_wide[[1, colIdx]]
|
|
for (rowIdx in 1:length(freq_wide[[colIdx]])) {
|
|
current <- freq_wide[[rowIdx, colIdx]]
|
|
if (!is.na(current) && !is.na(last) && (abs(current - last) > THRESHOLD)) {
|
|
freq_wide[[rowIdx, colIdx]] = NA
|
|
}
|
|
last <- current
|
|
}
|
|
}
|
|
|
|
return (freq_wide)
|
|
}
|
|
|
|
con <- dbConnect(RPostgres::Postgres(),
|
|
dbname='mainscnt',
|
|
host='172.16.10.27',
|
|
user='wn')
|
|
|
|
|
|
START <- "2021-08-12 00:00:00"
|
|
INTERVAL <- 3600
|
|
|
|
freq_deviation_integrals <- data.frame()
|
|
|
|
for (offset in 0:23) {
|
|
startDate <- ymd_hms(START) + INTERVAL * offset
|
|
endDate <- startDate + INTERVAL
|
|
|
|
|
|
# get prepared and sanitized data from database
|
|
freq_wide <- get_freq_df(con, startDate, endDate)
|
|
#
|
|
location_names <- names(freq_wide)[-1]
|
|
|
|
for (colIdx in 1:length(location_names)) {
|
|
colName.mean <- paste("mean.w.o.", location_names[colIdx], sep="")
|
|
colName.diff <- paste(location_names[colIdx], ".to.mean", sep="")
|
|
freq_wide <- freq_wide %>%
|
|
rowwise() %>%
|
|
mutate(!!colName.mean := mean(c_across(location_names[- colIdx]), na.rm=TRUE)) %>%
|
|
mutate(!!colName.diff := abs(eval(as.name(colName.mean)) - eval(as.name(location_names[colIdx]))))
|
|
|
|
}
|
|
|
|
means <- freq_wide %>% select(ends_with(".to.mean"))
|
|
sum.means <- apply(means, 2, sum, na.rm=TRUE)
|
|
#printf("start: %s, end: %s\n", startDate, endDate)
|
|
#print(sum.means)
|
|
#printf("\n")
|
|
|
|
next.row.no <- nrow(freq_deviation_integrals) + 1
|
|
freq_deviation_integrals[next.row.no, c(1, 2)] <- c(strftime(startDate, "%Y-%m-%d %H:%M:%S", tz="UTC"), strftime(endDate, "%Y-%m-%d %H:%M:%S", tz="UTC"))
|
|
freq_deviation_integrals[next.row.no, c(3:(2 + length(sum.means)))] <- sum.means[order(names(sum.means))]
|
|
|
|
}
|
|
|
|
names(freq_deviation_integrals) <- c("startDate", "endDate", sort(location_names))
|
|
|
|
|
|
for (colIdx in 1:length(location_names)) {
|
|
freq_deviation_integrals[,ncol(freq_deviation_integrals)+1] <- c(0, diff(freq_deviation_integrals[,2+colIdx],1))
|
|
names(freq_deviation_integrals)[length(location_names)+2+colIdx] = paste("diff", sort(location_names)[colIdx], sep=".")
|
|
}
|
|
|
|
|
|
dbDisconnect(con)
|
|
|
|
x1 <- freq_deviation_integrals %>% select(c('endDate', starts_with('diff.'))) %>%
|
|
pivot_longer(cols = starts_with('diff.'), names_to = 'location', names_pattern = "diff.(.*)", values_to = 'coa2m')
|
|
|
|
p <- ggplot(x1, aes(x=endDate, y=coa2m, color=location)) +
|
|
geom_point() +
|
|
theme(axis.text.x = element_text(angle = 90))
|
|
print(p)
|