Water Levels and Depth to Water

Inyo County, California

This report documents the annual water-year migration of depth-to-water observations, including ingestion of ZRXP data, QA/QC screening, integration with the active database, and preparation of OVGA-formatted deliverables.

Abstract

This report documents the annual processing and quality control of depth-to-water (DTW) observations from monitoring wells in the Owens Valley. The workflow ingests ZRXP-formatted data from LADWP, performs QA/QC screening, integrates observations with the active database, and generates OVGA-compliant deliverables for regional groundwater management.

Two key terms are used throughout: station identification (staid) for the unique well or sensor ID, and reference point elevation (RP elevation) for the fixed elevation from which depth is measured. Data are delivered as ZRXP text files, parsed and averaged to daily values per station, then filtered to retain only records with valid reference point elevations that correspond to OVGA-approved monitoring locations. Outputs include an annual update file, a consolidated master table, and upload-ready CSV files with accompanying plots and summary statistics for database import validation.

For a new water year, update the year variable in three locations: input paths and master, ICWD output file names, and OVGA upload. The full R workflow and report source are available in index.qmd.

Annual update instructions: See ANNUAL_UPDATE_GUIDE.md for comprehensive step-by-step instructions on updating this workflow for the next water year.

Data sources and preprocessing

Here we load the raw data and combine it into one table. Each file is a text format (ZRXP): every monitoring point has a header line with its station identification (staid), followed by lines of date-and-value pairs. In R we (1) read each file, (2) pull the staid from the #TSPATH header, and (3) join the three files by staid and date so we have depth from reference point, depth below ground, and water surface elevation in one place.

Many stations record more than once per day. We later average those to one value per station per day so the dataset is smaller and all stations are on the same time step—that’s “daily harmonization.”

Input files (WY2025):

• DepthRP_2024-25.dat — depth to water from reference point elevation (RP elevation)
• DepthGE_2024-25.dat — depth to water below ground elevation
• OwensValley_DepthWSE_2024-25.dat — water surface elevation

# new data updates -------

file_pathWSE <-here('data','hydro','2024-25 Water Year Transfer Packet for ICWD','OwensValley_DepthWSE_2024-25.dat')

file_pathRP <-here('data','hydro','2024-25 Water Year Transfer Packet for ICWD','DepthRP_2024-25.dat')

file_pathGE <-here('data','hydro','2024-25 Water Year Transfer Packet for ICWD','DepthGE_2024-25.dat')

wYear <- '2025'

parse_depth_file <- function(file_path, value_name) {
  dat_content <- readLines(file_path)
  data_list <- list()

  is_data_line <- function(line) {
    grepl("\\d+ \\d+\\.\\d+", line)
  }

  for (line in dat_content) {
    if (startsWith(line, "#TSPATH")) {
      staid <- sub('.*/([TVFRSW]\\w+).*', '\\1', line)
    }

    if (!startsWith(line, "#") && is_data_line(line) && !is.na(staid)) {
      data_list <- c(data_list, list(data.frame(staid, dateread = line)))
    }
  }

  data_df <- bind_rows(data_list) %>% select(staid, dateread) %>% na.omit()
  data_df <- data_df %>% separate(dateread, c("date", value_name), sep = " ")
  data_df[[value_name]] <- as.numeric(data_df[[value_name]])
  data_df
}

depthRP_path <- here('data','hydro','2025','depthRP.RDS')
depthGE_path <- here('data','hydro','2025','depthGE.RDS')
depthWSE_path <- here('data','hydro','2025','depthWSE.RDS')

depthRP <- if (file.exists(depthRP_path)) {
  readRDS(depthRP_path)
} else {
  depthRP <- parse_depth_file(file_pathRP, "dtw.rp")
  saveRDS(depthRP, file = depthRP_path)
  depthRP
}

depthGE <- if (file.exists(depthGE_path)) {
  readRDS(depthGE_path)
} else {
  depthGE <- parse_depth_file(file_pathGE, "dtw.bgs")
  saveRDS(depthGE, file = depthGE_path)
  depthGE
}

depthWSE <- if (file.exists(depthWSE_path)) {
  readRDS(depthWSE_path)
} else {
  depthWSE <- parse_depth_file(file_pathWSE, "wse")
  saveRDS(depthWSE, file = depthWSE_path)
  depthWSE
}

# ground surface elevation at staids
gse_staids <- read_csv(here('data','hydro', 'gse_staids.csv'))

# staids in ovga database------
# used to cross reference what will be accepted into an import
# need to filter out staids that aren't in the ovga list, otherwise import will fail
# Latest monitoring points (if updated): https://owens.gladata.com/api/export.ashx?v=mp (may require authentication; save as Owens_Monitoring_Points.csv)
ovga_points <- read_csv(here("data","hydro","Owens_Monitoring_Points.csv"))

# If JSON export exists, load as alternate list for "now on new list" summary and optional use
ovga_points_from_json <- NULL
if (file.exists(here("data","hydro","owens_mp_export.json"))) {
  mp_json <- jsonlite::fromJSON(here("data","hydro","owens_mp_export.json"))
  need <- c("mon_pt_id", "site_id", "mon_pt_name", "mon_pt_lat", "mon_pt_long", "coord_sys", "county")
  if (all(need %in% names(mp_json))) {
    ovga_points_from_json <- mp_json %>% select(all_of(need))
    if (!"hide_from_public" %in% names(mp_json)) ovga_points_from_json$hide_from_public <- 0L
    else ovga_points_from_json$hide_from_public <- as.integer(mp_json$hide_from_public)
  }
}

# reference point elevation - some staids don't have RP elevations. Identify these here for the current water year transfer
rpelev <- read_csv(here('data','hydro','rp_elev.csv'))
last_ovga_mw <- read_csv(here('output','ovga_uploads_mw_with_rpe_102023_092024_zn.csv'))

# historical water levels------
hist <- read_csv(here('output','Monitoring_Wells_Master_2024.csv'))
hist$date <- ymd(hist$date)

Raw input coverage

The table below provides a quick inventory of each input file: record counts, unique monitoring points, and the raw date range. Use this to verify that the received transfer packet aligns with expectations before moving on to joins, aggregation, and exports.

File	Records	Unique_staids	Date_start	Date_end
DepthRP_2024-25.dat	82831	743	2024-10-01	2025-09-30
DepthGE_2024-25.dat	74670	655	2024-10-01	2025-09-30
OwensValley_DepthWSE_2024-25.dat	86968	383	2024-10-01	2025-09-30

Each file repeats headers for each monitoring well. Extract the well ID from #TSPATH and join to observation rows (lines that do not start with #). The example shows the header block followed by timestamped values.

Data combination and daily harmonization

# rename the columns
# clean up the date with formal date specification
testwell.up <- try %>% select(-date, -datetime) %>% rename(date = date.y.m.d) %>% mutate(source = "DWP")  
# head(testwell.up)

## Append updates to master
testwells.combined <- bind_rows(hist, testwell.up) 

# head(testwells.combined)
# 1,145,360 records going back to 1971

# testwells.combined %>% n_distinct(staid)
# testwells.combined %>% distinct(staid) %>% nrow()

Metric	Value
Annual update rows	89,289
Annual update staids	750
Active database rows	1,324,540
Active database staids	1,381

ICWD database refresh

This section appends the annual update to the internal (ICWD) master database, writes the yearly slice and refreshed master to output/2025wy/ICWD, and provides a preview of the update table for review. A full-resolution (pre-daily aggregation) extract is also written to preserve all raw reads for archive and troubleshooting.

At this stage the annual update (testwellwy2025.csv) represents the daily harmonized water-year slice, while the master database output (Monitoring_Wells_Master_2025.csv) provides the full historical record with the new year appended. These outputs are the authoritative internal datasets used for QA/QC, reporting, and follow-on exports.

ICWD database exports

2023 water year

• Completed 1-24-24 annual update (testwellwy2023.csv)
• Completed 1-24-24 full database (Monitoring_Wells_Master_2023.csv)

2024 water year

• Completed 12-10-24 annual update (testwellwy2024.csv)
• Completed 12-10-24 full database (Monitoring_Wells_Master_2024.csv)

2025 water year

• Completed 1-26-26 annual update (testwellwy2025.csv)
• Completed 1-26-26 raw annual update (testwellwy2025_raw.csv)
• Completed 1-26-26 full database (Monitoring_Wells_Master_2025.csv)

testwellwy2025.csv is the WY2025 daily slice (one row per station per day). testwellwy2025_raw.csv keeps every read before daily averaging, for archive or troubleshooting.

out_icwd <- here("output", "2025wy", "ICWD")
dir.create(out_icwd, recursive = TRUE, showWarnings = FALSE)

# single year
testwell.up %>% write_csv(file.path(out_icwd, "testwellwy2025.csv"))

# full-resolution annual update (pre-daily aggregation)
raw_update <- try %>%
  select(staid, datetime, dtw.rp, dtw.bgs, wse)
raw_update %>% write_csv(file.path(out_icwd, "testwellwy2025_raw.csv"))

# whole dataset
testwells.combined %>% write_csv(file.path(out_icwd, "Monitoring_Wells_Master_2025.csv"))

# 

OVGA database export files

Reference point elevation integration

Depth-to-water is measured from a fixed mark at each well—the reference point elevation (RP elevation). We need that value to interpret the depth numbers. In this section we attach the most recent reference point elevation to each station id(entification) (staid) and flag any stations where it’s missing.

Stations without a reference point elevation stay in our internal tables but are left out of the OVGA upload so we don’t import incomplete records. We still keep the data for later if RP values are added.

First we convert the RP dates into a standard format so we can join them to the annual update.

## RP can be continually changing so need to be recursive with the RP file for updates.

# date conversion
rpelev_date <- rpelev %>% 
  mutate(date = lubridate::mdy(date_c))

head(rpelev_date) %>%
  datatable(options = list(pageLength = 10, lengthChange = TRUE, scrollX = TRUE))

Next, we identify the most recent reference point elevation per staid. That way each monitoring point uses the latest available reference point during the update.

We then attach that most recent reference point elevation to each staid so every row has a reference point value for the join.

We join RP elevations to the annual update so DTW values can be expressed relative to the reference point. This join also supports QA/QC checks for missing RP data.

  count_unique_staids
1                 750

After the reference-point join we average to one value per station per day. OVGA accepts no more than one value per station per day, so this daily aggregation is required for the OVGA import. For staids with many readings per day we take the mean; that also keeps the table smaller and every staid on the same time step.

The tables and plots below summarize measurement frequency and daily aggregation behavior, which is used to validate that the daily rollup is behaving as expected.

# A tibble: 10,079 × 4
# Groups:   staid [750]
   staid date       daily_count daily_count_category
   <chr> <date>           <int> <fct>               
 1 V271  2024-10-01          96 (50,100]            
 2 V271  2024-10-02          96 (50,100]            
 3 V271  2024-10-03          96 (50,100]            
 4 V271  2024-10-04          96 (50,100]            
 5 V271  2024-10-05          96 (50,100]            
 6 V271  2024-10-06          96 (50,100]            
 7 V271  2024-10-07          96 (50,100]            
 8 V271  2024-10-08          96 (50,100]            
 9 V271  2024-10-09          96 (50,100]            
10 V271  2024-10-10          96 (50,100]            
# ℹ 10,069 more rows

# A tibble: 10 × 2
   daily_count_category count_unique_staids
   <fct>                              <int>
 1 [0,1]                                749
 2 (1,2]                                 21
 3 (2,3]                                  4
 4 (3,4]                                  5
 5 (4,5]                                  5
 6 (5,10]                                 6
 7 (10,15]                                3
 8 (15,20]                                6
 9 (20,50]                               10
10 (50,100]                               1

      .
1 [0,1]

WY2025 update: 33 out of 750 staids have multiple measurements per day (some hourly and a few up to 15‑minute intervals). 32 of those 33 staids also have single‑measurement days.

We harmonize these to daily values to reduce data size and make the annual update consistent across measurement frequencies. The next steps show how we aggregate to daily means and then summarize record frequency by water year to confirm that high-frequency sensors are behaving as expected.

The distributions below provide a QA/QC snapshot of record counts per staid. A small subset of sites should show high counts (automated sensors), while most sites should fall in the low-frequency bins (manual measurements).

Daily averaging collapses multiple measurements on the same day to a single value per staid and day. This daily series is the basis for annual summaries and export preparation.

# daily average dtw
dtwrpavg.rpelev <- dtwrp.rpelev %>% 
  group_by(staid, date, rp_elev) %>% 
  summarise(dtw.rp =  round(mean(dtw.rp),2))


# dtwrpavg.rpelevBeta <- dtwrp.rpelev %>% group_by(staid, date, rp_elev) %>% summarise(dtw.rp =  round(mean(dtw.rp),2),
#           dailyCount = n()
#           ) %>% arrange(desc(dailyCount))
dtwrpavg.rpelev

# A tibble: 10,079 × 4
# Groups:   staid, date [10,079]
   staid date       rp_elev dtw.rp
   <chr> <date>       <dbl>  <dbl>
 1 F033  2024-10-10   3834.   4.56
 2 F033  2024-11-07   3834.   4.56
 3 F033  2024-12-05   3834.   4.56
 4 F033  2025-01-08   3834.   4.56
 5 F033  2025-03-10   3834.   4.56
 6 F033  2025-04-14   3834.   4.56
 7 F033  2025-06-09   3834.   4.56
 8 F033  2025-07-11   3834.   4.56
 9 F033  2025-08-06   3834.   4.56
10 F033  2025-09-04   3834.   4.56
# ℹ 10,069 more rows

# datatable(dtwrpavg.rpelev,
  # caption = 'daily averaging reduces data')

We also infer a measurement method flag based on measurement frequency so that OVGA export metadata stays consistent when explicit method values are not provided with the raw data.

# More than 4 reads per month (48 per year) indicates pressure transducer (TR);
# fewer indicates electric sounder (ES).

record.number <- dtwrp.rpelev %>% 
  filter(!is.na(rp_elev)) %>% 
  group_by(staid) %>% 
  summarise(count.with.rpe = n()) %>% 
  mutate(MMethod = case_when(count.with.rpe >= 48 ~ "TR",
                            count.with.rpe < 48~ "ES"))
# if there are four reads per day, once per month, approximates to 48. quick and dirty separation. a list of pressure transducer staids would be better if it could be maintained.

head(record.number %>% arrange(-count.with.rpe))

# A tibble: 6 × 3
  staid count.with.rpe MMethod
  <chr>          <int> <chr>  
1 V271            3190 TR     
2 V202            1347 TR     
3 T387            1346 TR     
4 V251            1346 TR     
5 T389            1239 TR     
6 T438              17 ES     

# records per staid
LA.staids <- dtwrpavg.rpelev %>% group_by(staid) %>% summarise(records = n())
# head(LA.staids)
# LA.staids %>% arrange(desc(records))
#785

The next QA/QC steps narrow the dataset to records eligible for OVGA import. We first identify staids that are present in the OVGA monitoring-point list, then quantify which of those lack RP elevations. Finally, we filter to records with valid RP elevations and OVGA-listed points before building the export template.

LA.staids.in.ovga <- LA.staids %>% semi_join(ovga_points, by = c("staid"="mon_pt_name"))

# LA.staids.in.ovga %>% nrow()
#714

# monitoring points missing rp elevations after join
# LA.staids.in.ovga
# find how many staids are missing rp elevation
na.rp.elev <- dtwrpavg.rpelev %>% 
  semi_join(LA.staids.in.ovga, by = 'staid') %>% 
  filter(is.na(rp_elev)) %>% 
  group_by(staid) %>% 
  summarise(count.na = n())

# anti_join removes records in x that match y
# Select only data with rp elevations (not na)
rpselect <- dtwrpavg.rpelev %>% anti_join(na.rp.elev, by = "staid")
# rpselect
# 19,793 1.24.2024

Filter to records with RP elevation and valid OVGA monitoring points.

# 6,048 points including monitoring wells, pumping wells, surface water gauging stations.
# semi_join returns records in x with a match in y
rpselect2 <- rpselect %>% semi_join(ovga_points, by = c("staid"="mon_pt_name"))
# head(rpselect2)
rpselect2 %>% arrange(staid,desc(date))

# A tibble: 5,584 × 4
# Groups:   staid, date [5,584]
   staid date       rp_elev dtw.rp
   <chr> <date>       <dbl>  <dbl>
 1 F033  2025-09-04   3834.   4.56
 2 F033  2025-08-06   3834.   4.56
 3 F033  2025-07-11   3834.   4.56
 4 F033  2025-06-09   3834.   4.56
 5 F033  2025-04-14   3834.   4.56
 6 F033  2025-03-10   3834.   4.56
 7 F033  2025-01-08   3834.   4.56
 8 F033  2024-12-05   3834.   4.56
 9 F033  2024-11-07   3834.   4.56
10 F033  2024-10-10   3834.   4.56
# ℹ 5,574 more rows

# datatable(rpselect2)

OVGA template and export

The upload file for OVGA is built from the daily table after we drop rows that don’t belong: we keep only stations on the OVGA list, only rows that have a valid reference point elevation, and only depth values that pass our checks (not missing, not -777, not ≥ 500 ft). What’s left is a clean table that matches the OVGA import template.

Column names and order follow that template so the system can ingest the file without errors. The file is saved under output/2025wy/OVGA and used for the actual import.

# methodinfer %>% distinct()
methodinfer <- record.number %>% select(-count.with.rpe)
# %>% distinct(staid)

upload <- rpselect2 %>% 
  left_join(methodinfer, by = "staid") %>% 
  # select(-latest_rp_date) %>% 
  select(WellName = staid, 
         DateMeasured = date, 
         DepthToWater = dtw.rp, 
         ReferencePointElevation = rp_elev
         ,
         MMethod
         ) %>% 
  mutate(ReportingDate = "",
         QAQCLevel = "High",
         MeasMethod = MMethod,#"ES",# from join above
         NoMeasFlag = "",
         QuestMeasFlag = "",
         DataSource = "LADWP",
         CollectedBy = "LADWP",
         UseInReporting = "yes",
         Notes = "") %>%
  select(-MMethod) %>%
  filter(DepthToWater < 500 & !is.na(DepthToWater) & DepthToWater != 'NA' & DepthToWater != -777 & ReferencePointElevation != 0) %>%
  select(WellName, DateMeasured, ReportingDate, DepthToWater, ReferencePointElevation, QAQCLevel, MeasMethod, NoMeasFlag, QuestMeasFlag, DataSource, CollectedBy, UseInReporting, Notes)

upload 

# A tibble: 5,515 × 13
# Groups:   WellName, DateMeasured [5,515]
   WellName DateMeasured ReportingDate DepthToWater ReferencePointElevation
   <chr>    <date>       <chr>                <dbl>                   <dbl>
 1 F033     2024-10-10   ""                    4.56                   3834.
 2 F033     2024-11-07   ""                    4.56                   3834.
 3 F033     2024-12-05   ""                    4.56                   3834.
 4 F033     2025-01-08   ""                    4.56                   3834.
 5 F033     2025-03-10   ""                    4.56                   3834.
 6 F033     2025-04-14   ""                    4.56                   3834.
 7 F033     2025-06-09   ""                    4.56                   3834.
 8 F033     2025-07-11   ""                    4.56                   3834.
 9 F033     2025-08-06   ""                    4.56                   3834.
10 F033     2025-09-04   ""                    4.56                   3834.
# ℹ 5,505 more rows
# ℹ 8 more variables: QAQCLevel <chr>, MeasMethod <chr>, NoMeasFlag <chr>,
#   QuestMeasFlag <chr>, DataSource <chr>, CollectedBy <chr>,
#   UseInReporting <chr>, Notes <chr>

2023 water year (OVGA)

Completed 1-24-24

csv

2024 water year (OVGA)

Completed 12-10-24

csv

2025 water year (OVGA)

Completed 1-26-26

csv

• 9329 well-days in update with rp elevations

• 673 staids in update with rp elevations

• 5584 well-days in update with rp elevations and in ovga list

• 598 staids in update with rp elevations and in ovga list

• 5515 well-days in the ovga upload

• 590 staids in the ovga upload

OVGA export and comparison

The comparison below highlights year-over-year changes in which monitoring points are included in the OVGA DTW upload.

Review this comparison after WY2025 processing to confirm which staids are missing and whether WSE-derived RP calculations are needed.

Rows: 12
Columns: 11
$ staid   <chr> "T755", "T755", "T755", "T755", "T755", "T755", "T755", "T755"…
$ dtw.rp  <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA
$ wse     <dbl> 4208.76, 4208.51, 4208.51, 4208.28, 4208.51, 4208.51, 4208.28,…
$ dtw.bgs <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA
$ year    <dbl> 2024, 2024, 2024, 2025, 2025, 2025, 2025, 2025, 2025, 2025, 20…
$ month   <dbl> 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9
$ day     <int> 22, 19, 9, 22, 12, 12, 16, 15, 16, 14, 18, 23
$ hour    <int> 14, 13, 10, 10, 9, 9, 9, 14, 9, 10, 15, 13
$ minute  <int> 2, 39, 40, 27, 27, 37, 46, 19, 51, 15, 17, 12
$ date    <date> 2024-10-22, 2024-11-19, 2024-12-09, 2025-01-22, 2025-02-12, 20…
$ source  <chr> "DWP", "DWP", "DWP", "DWP", "DWP", "DWP", "DWP", "DWP", "DWP",…

# OVGA Import Depth to Water: columns per schema (ReportingDate and UseInReporting optional; blank ReportingDate uses DateMeasured)
ovga_dtw_cols <- c("WellName", "DateMeasured", "ReportingDate", "DepthToWater", "ReferencePointElevation", "QAQCLevel", "MeasMethod", "NoMeasFlag", "QuestMeasFlag", "DataSource", "CollectedBy", "UseInReporting", "Notes")
write_ovga_dtw_csv <- function(x, path) {
  # Drop any staid/date so only OVGA-named columns (WellName, DateMeasured, ...) are written
  x <- x %>% select(-any_of(c("staid", "date"))) %>% select(all_of(ovga_dtw_cols))
  x[] <- lapply(x, function(v) ifelse(is.na(v), "", as.character(v)))
  write.table(x, path, sep = ",", row.names = FALSE, quote = FALSE)
}

out_ovga <- here("output", "2025wy", "OVGA")
dir.create(out_ovga, recursive = TRUE, showWarnings = FALSE)
write_ovga_dtw_csv(upload, file.path(out_ovga, "ovga_uploads_mw_with_rpe_102024_092025_zn.csv"))
writexl::write_xlsx(upload %>% select(-any_of(c("staid", "date"))) %>% select(all_of(ovga_dtw_cols)) %>% mutate(across(everything(), ~replace(., is.na(.), ""))), file.path(out_ovga, "ovga_uploads_mw_with_rpe_102024_092025_zn.xlsx"))

col_w <- list(
  list(targets = 0:4, width = "80px"),
  list(targets = 5, width = "100px"),
  list(targets = 6:10, width = "90px")
)
datatable(
  upload,
  caption = "2024-2025 water year upload formatted for OVGA data management system.",
  options = list(
    pageLength = 10,
    lengthChange = TRUE,
    scrollX = TRUE,
    autoWidth = FALSE,
    columnDefs = col_w
  ),
  class = "compact stripe"
)

Metric	Value
Rows in OVGA upload	5515
Unique monitoring points	590
Date range	2024-10-01 to 2025-09-30

OVGA DTW exclusions (download)

Table 1: Staids not in OVGA monitoring points list

These staids are excluded because they are not on the current OVGA monitoring points list. This list should be reviewed by the hydrologist and DB manager to decide which staids should be added to the OVGA monitoring points list on the backend.

Table 2: Staids on OVGA list needing data quality resolution

These staids are on the OVGA monitoring points list but are excluded due to data quality issues. The “Missing reference point elevation” category excludes staids that received WSE-estimated RP (those 358 records were imported).

Exclusions summary

The exclusions are now split into two separate tables and export files for hydrologist handoff:

Table 1 (Not in OVGA list): 75 staids that are not on the current OVGA monitoring points list. These should be reviewed by the hydrologist and DB manager to decide which should be added to the OVGA backend.

Table 2 (Data quality issues): 49 exclusion entries for staids that are on the OVGA list but excluded for data quality reasons (missing reference point elevation, invalid depth, or invalid RP elevation). The “Missing RP” category in Table 2 excludes staids that received WSE-estimated RP—those were imported via the WSE-estimate-only file (358 records).

Export files for hydrologist: - ovga_excl_not_in_list_wy2025.csv / .xlsx — Table 1 (not in OVGA list) - ovga_excl_data_quality_wy2025.csv / .xlsx — Table 2 (data quality issues) - ovga_dtw_exclusions_wy2025.csv / .xlsx — Combined file (both tables)

Exclusions by reason and table:

OVGA DTW exclusions by reason (WY2025). Table 2 Missing RP excludes WSE-estimated staids.

Exclusion reason	Unique staids	Total records	Note
Not in OVGA monitoring points list	75	3745	Table 1: For hydrologist/DB manager backend list review
Invalid depth (NA, -777, or >= 500 ft)	12	69	Table 2: Data quality issue
Missing reference point elevation	37	392	Table 2: Staids needing RP (WSE-estimated staids excluded)

Key outputs and datasets (output/2025wy/OVGA/):

OVGA DTW export files and record counts (WY2025).

Output	CSV	XLSX	Records / staids
Main OVGA DTW upload (reported RP only)	ovga_uploads_mw_with_rpe_102024_092025_zn.csv	ovga_uploads_mw_with_rpe_102024_092025_zn.xlsx	5,515
WSE-estimate-only upload (estimated RP)	ovga_uploads_wy2025_wse_estimate_only.csv	ovga_uploads_wy2025_wse_estimate_only.xlsx	358 (imported)
Table 1: Not in OVGA list	ovga_excl_not_in_list_wy2025.csv	ovga_excl_not_in_list_wy2025.xlsx	75 staids
Table 2: Data quality issues	ovga_excl_data_quality_wy2025.csv	ovga_excl_data_quality_wy2025.xlsx	49 exclusions
Combined exclusions (all reasons)	ovga_dtw_exclusions_wy2025.csv	ovga_dtw_exclusions_wy2025.xlsx	124 exclusion rows
RP elevation candidates (WSE+DepthRP)	rp_elev_candidates_wse_plus_dtw_wy2025.csv	rp_elev_candidates_wse_plus_dtw_wy2025.xlsx	40 staids
RP elevation with sources (reported vs estimated)	rp_elev_with_sources_wy2025.csv	rp_elev_with_sources_wy2025.xlsx	—

Table 2 contains 49 unique staids that are on the OVGA list but excluded for data quality reasons (missing RP, invalid depth, or invalid RP). Note that the “Missing RP” category in Table 2 excludes staids that received WSE-estimated RP elevation—those were successfully imported via the WSE-estimate-only file (358 records).

Summary: WSE + DepthRP estimated staids (already imported)

The staids excluded for missing reference point elevation in the original analysis that had sufficient WSE and DepthRP overlap were given estimated RP elevations (see Potential RP elevations derived from WSE + DepthRP in Appendix (with QA/QC)). These staids were imported via the WSE-estimate-only upload file (358 records accepted by OVGA). They no longer appear in Table 2’s “Missing RP” category because they were successfully resolved.

0 of 37 staids excluded for missing reference point elevation can get an estimated RP from WSE + DepthRP: ****.

Summary: exclusions “not on OVGA list” that are now on the updated monitoring points list

If you have refreshed the monitoring points list from the GLA API export, the following staids were excluded because they were not on the old list but are on the new list. After updating Owens_Monitoring_Points.csv and re-running the report, they would no longer be excluded for that reason.

6 of 75 staids excluded for not being on the OVGA list are on the updated monitoring points export: T977, T978D, T978S, V137, W426, W428. (If the JSON export has not been refreshed, this shows the last known set.)

Staids not on OVGA list (handoff for backend)

The following staids are excluded from the OVGA DTW upload because they are not on the current OVGA monitoring points list. This list is provided to the hydrologist and DB manager to decide on adding these staids to the OVGA monitoring points list on the backend. Once added, they can be included in future uploads.

• Staids not on OVGA list: FS3D, FS3S, S004, S005, S006, S007, T1000D, T1000S, T1001D, T1001S, T313B, T936, T937, T938, T939, T940, T977, T978D, T978S, T979, T980, T986D, T986I, T986S, T988D, T988I, T988S, T990D, T990I, T990S, T997D, T997S, T998D, T998S, TS801, TS802, V056D, V056S, V061, V076, V082, V087D, V087S, V137, V145D, V145I, V145S, V208I, V208S, V224D, V224S, V233D, V233S, V243I, V243S, V298I, V298S, V341, V365, V390D, V390I, V932, V933, V934, V935, VCPPDM, VPANCH, VPKIV, VPLACE, VPLANE, VS093, VS360, VWATSON, W426, W428 (75 staids; see exclusions table above for record counts and date ranges; CSV/XLSX download available).

WY2025 OVGA DTW conclusion

The WY2025 depth-to-water import to OVGA completed successfully (358 records imported). Exclusions are documented above: staids on the OVGA list but excluded for data-quality reasons (missing RP, invalid depth/RP), and staids not on the OVGA list. The not-on-list staids are highlighted above for handoff to the hydrologist and DB manager to decide on adding them to the OVGA monitoring points list on the backend.

Record attrition by step

The table below summarizes how many records remain after each processing step. Raw reads are joined to reference point elevation, averaged to daily, then filtered to OVGA list and valid depth and RP so the final upload is a subset of the original.

WY2025 DTW record attrition by processing step.

Step	Records	stringsAsFactors	Percent_of_raw
Raw reads (all timestamps)	89,289	FALSE	100%
DTW with RP joined (pre-daily)	89,289	FALSE	100%
Daily DTW (post-aggregation)	10,079	FALSE	11.3%
Daily DTW in OVGA list	6,334	FALSE	7.1%
Daily DTW with RP elevation	5,643	FALSE	6.3%
Final OVGA upload	5,515	FALSE	6.2%

Appendix (with QA/QC)

GLA Data Depth to Water Import Procedures

Last verified in prior annual update; re-check against current OVGA guidance when preparing a new upload.

GLA Data Web application. The uploaded Excel Workbook must contain one spreadsheet with 13 columns with the following names in this order:

Field Name	Data Type	Required
WellName	Text	Yes
DateMeasured	Date	Yes
ReportingDate	Date	No
DepthToWater	Numeric	Conditional
ReferencePointElevation	Numeric	Conditional
QAQCLevel	Text	Yes
MeasMethod	Text	Yes
NoMeasFlag	Text	Conditional
QuestMeasFlag	Text	No
DataSource	Text	Yes
CollectedBy	Text	No
UseInReporting	Text	No
Notes	Text	Conditional

WellName The WellName column is required and must contain the name of a monitoring point within the basin selected when the file was uploaded.

DateMeasured The DateMeasured column is required. The field must be a date and can not be in the future nor more than 100 years in the past.

2-1-1 import error says that ReportingDate is not part of the column list ReportingDate The ReportingDate column must be blank or a date. If the field is a date, it must be within 14 days of DateMeasured. If left blank, the column is populated with the value in the DateMeasured column. This field allows users to assign a measurement to an adjacent month for reporting purposes. For example, a measurement collected on May 31st may be intended to be used as an April measurement.

DepthToWater This column must be blank or numeric. DepthToWater is the number of feet from the reference point. If blank, NoMeasFlag is required and ReferencePointElevation must also be blank. Positive values indicate the water level is below the top of the casing, while negative values indicate the water level is above the top of the casing (flowing artesian conditions).

ReferencePointElevation This column must be blank or numeric. ReferencePointElevation is the elevation in feet from where the depth to water measurement took place. If blank, NoMeasFlag is required and DepthToWater must also be blank.

QAQCLevel This field is required and must be one of the following values:

• High - Data are of high quality

• Medium - Data are inconsistent with previous values or sampling conditions were not ideal. Values will be displayed with a different color on plots.

• Low - Data are not considered suitable for display or analysis due to inconsistencies with previous values or poor sampling conditions. Preserves sample in database for record-keeping purposes but not displayed on figures, tables, or used in analyses.

• Undecided - QA/QC level has not been determined.

MeasMethod This field is required and must be one of the following values:

Code	Description
ES	Electric sounder measurement
ST	Steel tape measurement
AS	Acoustic or sonic sounder
PG	Airline measurement, pressure gage, or manometer
TR	Electronic pressure transducer
OTH	Other
UNK	Unknown

NoMeasFlag This field must be blank if DepthToWater and ReferencePointElevation contain values. Otherwise, this field is required and must be one of the following values:

Code	Description
0	Measurement discontinued
1	Pumping
2	Pump house locked
3	Tape hung up
4	Can’t get tape in casing
5	Unable to locate well
6	Well has been destroyed
7	Special/other
8	Casing leaking or wet
9	Temporary inaccessible
D	Dry well
F	Flowing artesian

QuestMeasFlag This field must be blank or be one of the following values:

Code	Description
0	Caved or deepened
1	Pumping
2	Nearby pump operating
3	Casing leaking or wet
4	Pumped recently
5	Air or pressure gauge measurement
6	Other
7	Recharge or surface water effects near well
8	Oil or foreign substance in casing
9	Acoustical sounder
E	Recently flowing
F	Flowing
G	Nearby flowing
H	Nearby recently flowing

DataSource This field is required and used to identify where the water level data came from (e.g., entity, database, file, etc.). Limit is 100 characters. default = “LADWP”

CollectedBy This field is optional and used to identify the person that physically collected the data. Limit is 50 characters. default = “LADWP”

UseInReporting This field is optional and used to filter measurements used in reports. If included, the value must be “yes”, “no”, “true”, “false”, “1” or “0”. If blank, a value of “yes” is assumed. default = “yes”

Notes This field must be populated if NoMeasFlag is 7 (special/other) or QuestMeasFlag is 6 (other), otherwise this field is optional. Limit is 255 characters. default = “blank”

OVGA DTW record density (north to south)

These maps show where records are concentrated north to south for the OVGA DTW upload and for excluded records. Point size scales with record counts. If the ggExtra package is available, a marginal density curve is added to highlight the north-south distribution.

OVGA DTW upload records with north-south density (size scales with record count).

OVGA DTW excluded records with north-south density (size scales with record count).

QA/QC and hydrographs

In this section we do two things: look at hydrographs (time-series plots of depth to water by well) and run automated checks. Hydrographs let you spot sudden shifts or odd patterns; the checks confirm record counts, missing reference point elevation, and that we only export station IDs that are on the OVGA list. Vertical lines on the plots mark water-year boundaries.

Laws wellfield

Hydrographs of indicator wells in the Laws wellfield.

Bishop wellfield

Hydrographs of indicator wells in the Bishop wellfield.

Big Pine wellfield

Hydrographs of indicator wells in the Big Pine wellfield. T565, and V017GC are in south Big Pine near W218/219.

Taboose-Aberdeen wellfield

Hydrographs of indicator wells in the Taboose-Aberdeen wellfield.

Thibaut-Sawmill wellfield

Hydrographs of indicator wells in Thibaut-Sawmill wellfield.

Independence-Oak wellfield

Hydrographs of indicator wells in Independence-Oak wellfield.

Symmes-Shepherd wellfield

Hydrographs of indicator wells in Symmes-Shepherd wellfield.

Bairs-George wellfield

Hydrographs of indicator wells in Bairs-George wellfield.