CalFishTrack

Central Valley Enhanced

Acoustic Tagging Project

Sacramento River Green Sturgeon

2018-2019 Season (PROVISIONAL DATA)

---

1. Project Status

---

Study is complete, all tags are no longer active. All times in Pacific Standard Time.

Telemetry Study Template for this study can be found here

setwd(paste(file.path(Sys.getenv("USERPROFILE"),"Desktop",fsep="\\"), "\\Real-time data massaging\\products", sep = ""))

tagcodes <- read.csv("qry_HexCodes.txt", stringsAsFactors = F)
tagcodes$RelDT <- as.POSIXct(tagcodes$RelDT, format = "%m/%d/%Y %I:%M:%S %p", tz = "Etc/GMT+8")
latest <- read.csv("latest_download.csv", stringsAsFactors = F)


study_tagcodes <- tagcodes[tagcodes$StudyID == "Juv_Green_Sturgeon_2018",]
release_stats <- aggregate(list(Number_fish_released = study_tagcodes$TagID_Hex),
                           by= list(Release_time = study_tagcodes$RelDT),
                           FUN = function(x) {length(unique(x))}
                           )
release_stats <- merge(release_stats,
                       aggregate(list(Release_location = study_tagcodes$Rel_loc),
                           by= list(Release_time = study_tagcodes$RelDT),
                           FUN = function(x) {head(x,1)}),
                       by = c("Release_time"))
release_stats <- merge(release_stats,
                       aggregate(list(Release_rkm = study_tagcodes$Rel_rkm),
                           by= list(Release_time = study_tagcodes$RelDT),
                           FUN = function(x) {head(x,1)}),
                       by = c("Release_time"))
release_stats <- merge(release_stats,
                       aggregate(list(Total_length = study_tagcodes$Length),
                           by= list(Release_time = study_tagcodes$RelDT),
                           FUN = mean),
                       by = c("Release_time"))
release_stats <- merge(release_stats,
                       aggregate(list(Weight = study_tagcodes$Weight),
                           by= list(Release_time = study_tagcodes$RelDT),
                           FUN = mean),
                       by = c("Release_time"))

release_stats[,c("Total_length", "Weight")] <- round(release_stats[,c("Total_length", "Weight")],1)

kable(release_stats, "html") %>%
        kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"), full_width = F, position = "left")

Release_time	Number_fish_released	Release_location	Release_rkm	Total_length	Weight
2018-10-04 22:54:00	1	Bank Robber	452.5	207	44.4
2018-10-05 10:30:00	1	RBDD Release	461.0	180	28.3
2018-10-08 12:34:00	1	RBDD Release	461.0	177	28.6
2018-10-10 20:55:00	1	Hunters Resort	450.3	254	69.2
2018-10-16 22:00:00	1	Hunters Resort	450.3	234	64.2
2018-10-17 23:48:00	1	Upper Woodson	421.4	266	93.8
2018-10-31 23:59:00	1	Altube Island	460.0	266	87.7
2018-11-14 22:21:00	2	Woodsen	421.4	281	104.5

---

2. Real-time Fish Detections

---

Data current as of 2025-04-22 09:00:00. All times in Pacific Standard Time.

setwd(paste(file.path(Sys.getenv("USERPROFILE"),"Desktop",fsep="\\"), "\\Real-time data massaging\\products", sep = ""))

library(cder)

detects_study <- read.csv("C:/Users/field/Desktop/Real-time data massaging/products/Study_detection_files/detects_Juv_Green_Sturgeon_2018.csv", stringsAsFactors = F)

if (nrow(detects_study) == 0){
  "No detections yet"
} else {
  detects_study$DateTime_PST <- as.POSIXct(detects_study$DateTime_PST, format = "%Y-%m-%d %H:%M:%S", "Etc/GMT+8")

  detects_study <- merge(detects_study, study_tagcodes[,c("TagID_Hex", "RelDT", "StudyID", "tag_life")], by.x = "TagCode", by.y = "TagID_Hex")


  detects_study <- detects_study[detects_study$general_location == "TowerBridge",]
  detects_study <- merge(detects_study,aggregate(list(first_detect = detects_study$DateTime_PST), by = list(TagCode= detects_study$TagCode), FUN = min))
  
  starttime <- as.Date(min(detects_study$first_detect)-60*60*24, "Etc/GMT+8")
  ## Endtime should be either now, or end of predicted tag life, whichever comes first
  endtime <- min(as.Date(c(Sys.time())), max(as.Date(detects_study$RelDT)+(detects_study$tag_life*1.5)))
  wlk_flow <- cdec_query("COL", "20", "H", starttime, endtime+1)
  wlk_flow$datetime <- as.Date(wlk_flow$DateTime)
  wlk_flow_day <- aggregate(list(parameter_value = wlk_flow$Value), by = list(Day = wlk_flow$datetime), FUN = mean, na.rm = T)
  
  detects_study$Day <- as.Date(detects_study$first_detect, "Etc/GMT+8")
  daterange <- data.frame(Day = seq.Date(from = starttime, to = endtime, by = "day"))
  
  
  tagcount <- aggregate(list(unique_tags = detects_study$TagCode), by = list(Day = detects_study$Day), FUN = function(x){length(unique(x))})
  
  daterange1 <- merge(daterange, tagcount, all.x=T)
  daterange2 <- merge(daterange1, wlk_flow_day, by = "Day", all.x = T)
  
  par(mar=c(6, 5, 2, 5) + 0.1)
  barp <- barplot(height= daterange2$unique_tags, plot = FALSE)
  barplot(height= daterange2$unique_tags, col="gray",  names.arg= daterange2$Day, xlab = "", ylab = "Number of fish arrivals per day", ylim = c(0,max(daterange2$unique_tags, na.rm = T)*1.2), las = 2, xlim=c(0,max(barp)+1), cex.lab = 1.5, yaxt = "n", xaxt = "n")
  ybreaks <- if(max(daterange2$unique_tags, na.rm = T) < 4) {max(daterange2$unique_tags, na.rm = T)} else {5}
  xbreaks <- if(length(barp) > 10) {seq(1, length(barp), 5)} else {1:length(barp)}
  axis(1, at = barp[xbreaks], labels = daterange2[xbreaks,"Day"], las = 2)
  axis(2, at = pretty(0:max(daterange2$unique_tags, na.rm = T), ybreaks))
  
  par(new=T)
  
  plot(x = barp, daterange2$parameter_value, yaxt = "n", xaxt = "n", ylab = "", xlab = "", col = "blue", type = "l", lwd=2, xlim=c(0,max(barp)+1))#, ylab = "Returning adults", xlab= "Outmigration year", yaxt="n", col="red", pch=20)
  axis(side = 4)#, labels = c(2000:2016), at = c(2000:2016))
  mtext("Flow (cfs) at Colusa Bridge", side=4, line=3, cex=1.5, col="blue")

}

2.1 Detections at Tower Bridge (downtown Sacramento) versus Sacramento River flows at Colusa Bridge

---

3. Detection Statistics

---

setwd(paste(file.path(Sys.getenv("USERPROFILE"),"Desktop",fsep="\\"), "\\Real-time data massaging\\products", sep = ""))

library(data.table)

test <- fread("C:/Users/field/Desktop/Real-time data massaging/products/Study_detection_files/detects_Juv_Green_Sturgeon_2018.csv", stringsAsFactors = FALSE, colClasses = c(DateTime_PST = "character", RelDT = "character"))
gen_locs <- read.csv("realtime_locs.csv", stringsAsFactors = F)

tagcodes <- read.csv("qry_HexCodes.txt", stringsAsFactors = F)
tagcodes$RelDT <- as.POSIXct(tagcodes$RelDT, format = "%m/%d/%Y %I:%M:%S %p", tz = "Etc/GMT+8")

study_tagcodes <- tagcodes[tagcodes$StudyID == "Juv_Green_Sturgeon_2018",]
study_count <- nrow(study_tagcodes)

if (nrow(test) == 0){
  "No detections yet"
} else {
  test$DateTime_PST <- as.POSIXct(test$DateTime_PST, format = "%Y-%m-%d %H:%M:%S")
  tag_stats <- aggregate(list(First_arrival = test$DateTime_PST), 
                         by= list(general_location = test$general_location), FUN = min)
  tag_stats <- merge(tag_stats, 
                     aggregate(list(Fish_count = test$TagCode), 
                         by= list(general_location = test$general_location), 
                         FUN = function(x) {length(unique(x))}), 
                     by = c("general_location"))
  tag_stats$Percent_arrived <- round(tag_stats$Fish_count/study_count * 100,2)
  
  tag_stats <- merge(tag_stats, unique(gen_locs[,c("general_location", "rkm")]))
  
  tag_stats <- tag_stats[order(tag_stats$rkm, decreasing = T),]
  
  kable(tag_stats, row.names = F, "html") %>%
        kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"), full_width = F, position = "left")
}

general_location	First_arrival	Fish_count	Percent_arrived	rkm
TowerBridge	2018-12-02 21:39:28	7	77.78	172.000
I80-50_Br	2018-12-02 14:17:33	6	66.67	170.748
Sac_BlwGeorgiana	2018-12-06 23:08:34	5	55.56	119.058
Sac_BlwGeorgiana2	2018-12-06 23:26:14	5	55.56	118.398