Flow Data

Zach Nelson

Flow Data

totals and means

LADWP data

OVGA

time series

LADWP water-year monthly volumetric flow data transfer. Code automates extracting volumetric flow data from ZRXP ASCII data exchange format, transforming and merging updates with the active database, and exporting a csv file formatted for the OVGA data management system for groundwater production and surface water gage totals.

Author

Zach Nelson

Published

January 30, 2023

Modified

January 30, 2023

Code

source(here('code','R','functions.R'))

Data

read data

file_path_flow <-here('data','hydro','2022-23 Water Year Transfer Packet for ICWD','VolumeMonthAF_2022-23.dat')

# annual update
sum_stations <- read_csv(here('data','hydro','2022-23 Water Year Transfer Packet for ICWD','summary_stations22-23.csv'))

# attribute data from wells database  
staidlist <- read_csv(here('data','hydro','staid_current.csv'))

# meta data on production wells
production.meta <- read_csv(here('data','hydro','Production.csv')) %>% 
  select(mon_pt_name, method) %>% unique()
  
#historical data
monthlies <- read_csv(here('data','hydro','totals_means_thru_sep_2022.csv'))# 

# meta on flow pts
flow.meta <- read_csv(here('data','hydro','Flows.csv')) %>%  select(mon_pt_name, LADWP_ID) %>% unique() %>% mutate(staid = as.character(LADWP_ID)) %>% select(-LADWP_ID)

parse .dat ascii

dat_content <- readLines(file_path_flow)

# Initialize variables to store data
data_list <- list()

# Function to check if a line contains timestamp and value
is_data_line <- function(line) {
  grepl("\\d+ \\d+\\.\\d+", line)
}

# Loop through each line in the file
for (line in dat_content) {
  # Check if the line starts with '#TSPATH'
  if (startsWith(line, "#TSPATH")) {
    # Extract 'staid' from the '#TSPATH' line
    staid <- sub('.*/([^/]+)/VOLUME/.*', '\\1', line)
  }
  
  # Check if the line does not start with '#' and contains timestamp and value
  if (!startsWith(line, "#") && is_data_line(line) && !is.na(staid)) {
    # If the line does not start with '#' and contains timestamp and value,
    # add data to the list directly
    data_list <- c(data_list, list(data.frame(staid, dateread = line)))
  }
}

# Combine the data frames in the list into a single data frame
ann.update <- dplyr::bind_rows(data_list)

separate date read column

#separate the data column and assign numeric class to value
ann.update2 <- ann.update %>% separate(dateread, c("date", "read"),sep = " ") 
ann.update2$read <- as.numeric(ann.update2$read)

date columns

ann.update2$datetime <- ymd_hms(ann.update2$date)


ann.update3<-ann.update2 %>% mutate(year = year(datetime),
                            month = month(datetime),
                            month_abbr = month(datetime, label = TRUE, abbr = TRUE),
                            day = mday(datetime),
                            date.y.m.d = make_date(year, month,day))

# select, rename, ljoin, select columns
ann.update4<-ann.update3 %>% select(-date)%>% rename(date = date.y.m.d)%>% left_join(staidlist, by = 'staid') %>% select(staid,description,read,date,year,month,month_abbr) 

# mutate case when year breaks/types
ann.update5 <- ann.update4 %>% mutate(wy = case_when(month >= 10 ~ year + 1,
                                          month <= 9 ~ year),
                           roy = case_when(month <= 3 ~ year - 1,
                                           month >= 4 ~ year),
                           irrag = case_when(month %in% c(4,5,6,7,8,9) ~ year))

summary stations

# wide to long format using gather()
sstations <- sum_stations %>% 
  gather(staid,read,-date) %>% 
  left_join(staidlist, by = 'staid') %>% select(-desc_id)%>% 
  mutate(month = month(date),
         year = year(date),
         month_abbr = month(date, label = TRUE, abbr = TRUE)
         )

split into ovga and ic updates

# append rows of summary stations to the annual update
update.merged.ic <- bind_rows(sstations,ann.update4)
# without - don't append b/c ovga doesn't have that
update.merged <- ann.update4# for ovga, don't need summaries

ICWD Flow export

IC date columns

# for ic master db
update.merged.ic <- update.merged.ic %>% mutate(wy = case_when(month >= 10 ~ year + 1,
                                          month <= 9 ~ year),
                           roy = case_when(month <= 3 ~ year - 1,
                                           month >= 4 ~ year),
                           irrag = case_when(month %in% c(4,5,6,7,8,9) ~ year))

update IC DB

totals_means <- bind_rows(update.merged.ic, monthlies)
# tm_all_merged

# update.merged3 %>% get_dupes()
# 0

write updated IC DB

# totals_means %>% write_csv('totals_means_thru_sep_2021.csv')
totals_means %>% write_csv(here('data','hydro','totals_means_thru_sep_2023.csv'))
totals_means %>% write_csv(here('output','totals_means_thru_sep_2023.csv'))

OVGA Flow export

filter no value -777

tm <- ann.update5 %>% filter(read != -777)
# tm

split flow vs production

# 6758+2244

# flow
flowdf <- tm %>% filter(!str_detect(staid, "[[:alpha:]]"))%>%
  mutate(staid = ifelse(str_detect(staid, "^0"), str_replace(staid, "^0+", ""), staid))

flow_ovga <- flowdf %>% left_join(flow.meta, by = 'staid') 
# %>% filter(!is.na(mon_pt_name))

# use below
#production
proddf <- tm %>% filter(str_detect(staid, "[[:alpha:]]"))

ovga flow columns

sw.flow <- flow_ovga  %>% 
  mutate(WellName = mon_pt_name,
         FlowDate = date,
         FlowRateAcFtPM = read,
         FlowRateCFS = "",
         FlowRateGPM = "") %>% 
  select(WellName,FlowDate,FlowRateAcFtPM, FlowRateCFS, FlowRateGPM)
  # filter(FlowRateAcFtPM < 600000) %>%

# columns up to date 1/30/24 zn
sw.flow %>% datatable()

find/remove duplicates

dupes <- sw.flow %>% get_dupes() 

swflow2 <- sw.flow %>% anti_join(dupes)

write ovga flow

swflow2 %>% write_csv(here("output","ovga_swflow_import_wy23.csv"))

OVGA Production export

ovga production columns

prod_ovga <- proddf %>% left_join(production.meta, by = c('staid' = 'mon_pt_name'))  

tm.well.flow <- prod_ovga %>% 
  mutate(WellName = staid,
         StartDate = date,
         EndDate = ceiling_date(date,'month'),
         AgUsageAF = '',
         MIUsageAF = read,
         TotalUsageAF = read,
         Method = method,
         Notes = '') %>% 
  select(WellName,StartDate,EndDate, AgUsageAF, MIUsageAF, TotalUsageAF, Method, Notes)

#up to date 1-30-24

Code

tm.well.flow%>% datatable()

find/replace duplicates

dupeswell <- tm.well.flow %>% get_dupes() 
# 0
# tmwellflow <- tm.well.flow %>% anti_join(dupeswell)

write ovga production

tm.well.flow %>% write_csv(here("output","ovga_production_import_wy22-23.csv"))

removed the following staids after some iteration as they are not on the basin list: F103 V082 F380 F381 F382 F104 W426 F022 F391

QAQC Check updates on plots

Totals and Means - summary stations requested

OVR Owens Valley Runoff

Code

tm2 <- totals_means
monthly_uses_syncplot(staid_p = "OVR" ,data = tm2, group_name = 'ov')

Owens Valley Runoff

OVPW Owens Valley Pumped Water

Code

monthly_uses_syncplot(staid_p = "OVPW" ,data = tm2, group_name = 'ov')

Owens Valley Pumped Water

FTC L.A.A. Total Flow to the City

Code

monthly_uses_syncplot(staid_p = "FTC" ,data = tm2, group_name = 'ov')

LAA Total Flow to the City

LOLU Lower Owens River Project – Lakes and Ponds Use

Code

monthly_uses_syncplot(staid_p = "LOLU" ,data = tm2, group_name = 'ov')

Lower Owens River Project – Lakes and Ponds Use

LOOU Lower Owens River Project – Operations

Code

monthly_uses_syncplot(staid_p = "LOOU" ,data = tm2, group_name = 'ov')

Lower Owens River Project – Operations

LORPDU Lower Owens River – Delta Uses

Code

monthly_uses_syncplot(staid_p = "LORPDU" ,data = tm2, group_name = 'ov')

Lower Owens River – Delta Uses

LORPRU Lower Owens River – River Uses

Code

monthly_uses_syncplot(staid_p = "LORPRU" ,data = tm2, group_name = 'ov')

Lower Owens River – River Uses

LORPTU Lower Owens River Project Total Uses

Code

monthly_uses_syncplot(staid_p = "LORPTU" ,data = tm2, group_name = 'ov')

Lower Owens River Project Total Uses

LOWU Lower Owens River Project – Waterfowl Uses

Code

monthly_uses_syncplot(staid_p = "LOWU" ,data = tm2, group_name = 'ov')

Lower Owens River Project – Waterfowl Uses

MBR Mono Basin Runoff

Code

monthly_uses_syncplot(staid_p = "MBR" ,data = tm2, group_name = 'ov')

Mono Basin Runoff

MTWP Mono Tunnel at West Portal

Code

monthly_uses_syncplot(staid_p = "MTWP" ,data = tm2, group_name = 'ov')

Mono Tunnel at West Portal

OLTU Owens Lake Total Uses

Code

monthly_uses_syncplot(staid_p = "OLTU" ,data = tm2, group_name = 'ov')

Owens Lake Total Uses

OVFG Owens Valley Flowing Groundwater

Code

monthly_uses_syncplot(staid_p = "OVFG" ,data = tm2, group_name = 'ov')

Owens Valley Flowing Groundwater

OVGR Owens Valley Groundwater Recharge

Code

monthly_uses_syncplot(staid_p = "OVGR" ,data = tm2, group_name = 'ov')

Owens Valley Groundwater Recharge

OVIR Owens Valley Irrigation

Code

monthly_uses_syncplot(staid_p = "OVIR" ,data = tm2, group_name = 'ov')

Owens Valley Irrigation

SHTO South Haiwee Total Outflow (1+2+3+BP)

Code

monthly_uses_syncplot(staid_p = "SHTO" ,data = tm2, group_name = 'ov')

South Haiwee Total Outflow (1+2+3+BP)

Stream Flow

Little Pine Creek

Code

monthly_flow_syncplot_staid(staid_p = '2051' ,data = totals_means,group_name = 'a')

Little Pine Creek @ McMurry Rd 2051

Pumping

runoff year pumping totals

Code

tozoo <- function(x) zoo(x$total_pumping, x$roy2)
dyMultiColumn <- function(dygraph) {
  dyPlotter(dygraph = dygraph,
            name = "MultiColumn",
            path = system.file("plotters/multicolumn.js",
                               package = "dygraphs"))
}

BP1

Code

linked_wells <- c("W210","W378","W379","W389")

DF <- tm2 %>% filter(staid %in% linked_wells) %>% mutate(case_when(read < 0 ~ 0)) %>% group_by(roy,staid)%>% summarise(total_pumping = sum(read)) %>% select(roy,staid,total_pumping) %>% mutate(roy2 = make_date(roy,4,1))

DF[is.na(DF)] <- 0

# %>% replace(is.na(.), 0)

Data <- do.call(merge, lapply(split(DF, DF$staid), tozoo))

Data %>% dygraph(group = 'a') %>% 
  dyStackedBarGroup(c("W210","W378","W379","W389"))%>%
  dyAxis("y", label = "Total Annual Pumping (AF)", valueRange = c(0, 8000))

BP1 linked wells

BP2

Code

linked_wells <- c("W220","W229","W374","W375")

DF <- totals_means %>% filter(staid %in% linked_wells) %>% mutate(case_when(read == -777 ~ 0)) %>% group_by(roy,staid)%>% summarise(total_pumping = sum(read)) %>% select(roy,staid,total_pumping) %>% mutate(roy2 = make_date(roy,4,1)) %>% replace(is.na(.), 0)

Data <- do.call(merge, lapply(split(DF, DF$staid), tozoo))

Data %>% dygraph(group = 'a') %>% 
  dyStackedBarGroup(c("W220","W229","W374","W375"))%>%
  dyAxis("y", label = "Total Annual Pumping (AF)", valueRange = c(0, 6000))

BP2 linked wells

BP3

Code

linked_wells <- c("W222","W223","W231","W232")
DF <- tm2 %>% filter(staid %in% linked_wells) %>% mutate(case_when(read == -777 ~ 0)) %>% group_by(roy,staid)%>% summarise(total_pumping = sum(read)) %>% select(roy,staid,total_pumping) %>% mutate(roy2 = make_date(roy,4,1))%>% replace(is.na(.), 0)

Data <- do.call(merge, lapply(split(DF, DF$staid), tozoo))

Data %>% dygraph(group = 'a') %>% 
  dyStackedBarGroup(c("W222","W223","W231","W232"))%>%
  dyAxis("y", label = "Total Annual Pumping (AF)", valueRange = c(0, 12000))

BP3 linked wells

BP4

Code

linked_wells <- c("W331")


DF <- tm2 %>% filter(staid %in% linked_wells) %>% mutate(case_when(read == -777 ~ 0)) %>% group_by(roy,staid)%>% summarise(total_pumping = sum(read)) %>% select(roy,staid,total_pumping) %>% mutate(roy2 = make_date(roy,4,1))%>% replace(is.na(.), 0)

Data <- do.call(merge, lapply(split(DF, DF$staid), tozoo))
# Data <- tozoo(DF)
# Data
Data %>% dygraph(group = 'a') %>% 
  dyBarChart()%>%
  dyAxis("y", label = "Total Annual Pumping (AF)", valueRange = c(0, 6000))

Figure 1: BP4 linked to well W331

Exempt Wells - Fish Hatchery and 218, 219

Code

linked_wells <- c("W218","W219","W330","W332")

DF <- tm2 %>% filter(staid %in% linked_wells) %>% mutate(case_when(read == -777 ~ 0)) %>% group_by(roy,staid)%>% summarise(total_pumping = sum(read)) %>% select(roy,staid,total_pumping) %>% mutate(roy2 = make_date(roy,4,1))%>% replace(is.na(.), 0)

Data <- do.call(merge, lapply(split(DF, DF$staid), tozoo))
# Data <- tozoo(DF)
# Data
Data %>% dygraph(group = 'a')%>% 
  dyStackedBarGroup(c("W218","W219","W330","W332"))%>%
  dyBarChart()%>%
  dyAxis("y", label = "Total Annual Pumping (AF)", valueRange = c(0, 32000))

Figure 2: hatchery wells 330/332 and exempt wells 218/219

Big Pine Pumping Totals

Code

linked_wells <- c("W210","W378","W379","W389","W220","W229","W374","W375","W222","W223","W231","W232","W331","W218","W219","W330","W332")

DF <- tm2 %>% filter(staid %in% linked_wells) %>% mutate(case_when(read == -777 ~ 0)) %>% group_by(roy,staid)%>% summarise(total_pumping = sum(read)) %>% select(roy,staid,total_pumping) %>% mutate(roy2 = make_date(roy,4,1))%>% replace(is.na(.), 0)

Data <- do.call(merge, lapply(split(DF, DF$staid), tozoo))
# Data <- tozoo(DF)
# Data
Data %>% dygraph(group = 'a')%>% 
  dyStackedBarGroup(c("W210","W378","W379","W389","W220","W229","W374","W375","W222","W223","W231","W232","W331","W218","W219","W330","W332"))%>%
  dyBarChart()%>%
  dyAxis("y", label = "Total Annual Pumping (AF)", valueRange = c(0, 32000))

Big Pine Wellfield Pumping

Citation

BibTeX citation:

@report{nelson2023,
  author = {Nelson, Zach},
  publisher = {Inyo County Water Department},
  title = {Flow {Data}},
  date = {2023-01-30},
  url = {https://inyo-gov.github.io/hydro-data/},
  langid = {en}
}

For attribution, please cite this work as:

Nelson, Zach. 2023. “Flow Data.” Data Report. Inyo County Water Department. https://inyo-gov.github.io/hydro-data/.