Green Book Section I.C.1.a - Line point data processing - 2025

Green Book Section I.C.1.a - Line point data processing - 2025

Inyo County, California

Green Book - Box I.C.1.a.ii

ICWD Annual Report

2025

vegetation condition

Open-source, reproducible ETL pipeline for processing annual line point transect (LPT) vegetation monitoring data. Raw field data collected on paper data sheets are digitized into Google Sheets, organized by USGS quadrangle with individual parcels in separate worksheets. This automated workflow extracts data from multiple sources (ICWD Google Sheets, LADWP CSV files), applies data quality controls and species code standardization, transforms between long and wide formats, and loads processed datasets into a standardized database schema. The pipeline supports collaborative data management between ICWD and LADWP and ensures reproducibility through version-controlled code and cached intermediate data. Outputs serve as the backend for parameterized reports, interactive dashboards, and decision support tools, enabling transparent vegetation condition assessments.

Modified

December 5, 2025

1 Overview

This document documents the annual ETL workflow used to convert raw line‑point transect sheets into standardized, analysis‑ready datasets for reporting and QA/QC. Each section below describes the intent of the processing step, with code folded by default to keep the narrative readable.

2 Inputs

We start by loading reference data (species codes/traits) used to harmonize species codes and enrich the transect records with taxonomy and functional attributes.

3 Core Transform Functions

These helper functions handle: (1) pivoting from wide transect layout to long format, (2) standardizing types, and (3) joining species attributes and plot IDs. Keeping them here ensures the transformation steps are transparent and reproducible.

4 Data Sources

The workflow can pull directly from the shared Google Sheets or use a cached intermediate file for faster, reproducible runs. Set READ_FROM_SHEETS <- TRUE to refresh from Google Sheets.

5 Extract (Sheets or Cache)

This step reads all parcel sheets, caches them to output/raw_sheets_data_2025.rds, and then proceeds with the same downstream processing regardless of source.

[1] "Loading data from cached intermediary file..."
[1] "Loaded cached data from output/raw_sheets_data_2025.rds"

[1] "Processing sheet: FSP004 from FSP"
[1] "Rows after filtering: 34"
[1] "Processing sheet: FSP006 from FSP"
[1] "Rows after filtering: 20"
[1] "Processing sheet: FSP015 from FSP"
[1] "Rows after filtering: 42"
[1] "Processing sheet: FSP020 from FSP"
[1] "Rows after filtering: 33"
[1] "Processing sheet: FSL044 from FSL"
[1] "Rows after filtering: 19"
[1] "Processing sheet: FSL048 from FSL"
[1] "Rows after filtering: 30"
[1] "Processing sheet: FSL051 from FSL"
[1] "Rows after filtering: 37"
[1] "Processing sheet: FSL053 from FSL"
[1] "Rows after filtering: 21"
[1] "Processing sheet: FSL054 from FSL"
[1] "Rows after filtering: 51"
[1] "Processing sheet: FSL064 from FSL"
[1] "Rows after filtering: 31"
[1] "Processing sheet: FSL065 from FSL"
[1] "Rows after filtering: 27"
[1] "Processing sheet: FSL116 from FSL"
[1] "Rows after filtering: 33"
[1] "Processing sheet: FSL118 from FSL"
[1] "Rows after filtering: 28"
[1] "Processing sheet: FSL120 from FSL"
[1] "Rows after filtering: 41"
[1] "Processing sheet: FSL123 from FSL"
[1] "Rows after filtering: 19"
[1] "Processing sheet: FSL124 from FSL"
[1] "Rows after filtering: 23"
[1] "Processing sheet: FSL125 from FSL"
[1] "Rows after filtering: 15"
[1] "Processing sheet: FSL126 from FSL"
[1] "Rows after filtering: 16"
[1] "Processing sheet: FSL128 from FSL"
[1] "Rows after filtering: 32"
[1] "Processing sheet: FSL129 from FSL"
[1] "Rows after filtering: 28"
[1] "Processing sheet: FSL130 from FSL"
[1] "Rows after filtering: 27"
[1] "Processing sheet: FSL138 from FSL"
[1] "Rows after filtering: 30"
[1] "Processing sheet: FSL158 from FSL"
[1] "Rows after filtering: 30"
[1] "Processing sheet: FSL161 from FSL"
[1] "Rows after filtering: 21"
[1] "Processing sheet: FSL166 from FSL"
[1] "Rows after filtering: 22"
[1] "Processing sheet: FSL172 from FSL"
[1] "Rows after filtering: 25"
[1] "Processing sheet: FSL187 from FSL"
[1] "Rows after filtering: 15"
[1] "Processing sheet: BLK002 from BLK"
[1] "Rows after filtering: 27"
[1] "Processing sheet: BLK006 from BLK"
[1] "Rows after filtering: 28"
[1] "Processing sheet: BLK008 from BLK"
[1] "Rows after filtering: 31"
[1] "Processing sheet: BLK009 from BLK"
[1] "Rows after filtering: 25"
[1] "Processing sheet: BLK011 from BLK"
[1] "Rows after filtering: 24"
[1] "Processing sheet: BLK016 from BLK"
[1] "Rows after filtering: 36"
[1] "Processing sheet: BLK021 from BLK"
[1] "Rows after filtering: 20"
[1] "Processing sheet: BLK024 from BLK"
[1] "Rows after filtering: 19"
[1] "Processing sheet: BLK033 from BLK"
[1] "Rows after filtering: 21"
[1] "Processing sheet: BLK039 from BLK"
[1] "Rows after filtering: 30"
[1] "Processing sheet: BLK044 from BLK"
[1] "Rows after filtering: 13"
[1] "Processing sheet: BLK059 from BLK"
[1] "Rows after filtering: 18"
[1] "Processing sheet: BLK069 from BLK"
[1] "Rows after filtering: 27"
[1] "Processing sheet: BLK074 from BLK"
[1] "Rows after filtering: 23"
[1] "Processing sheet: BLK075 from BLK"
[1] "Rows after filtering: 37"
[1] "Processing sheet: BLK077 from BLK"
[1] "Rows after filtering: 21"
[1] "Processing sheet: BLK093 from BLK"
[1] "Rows after filtering: 29"
[1] "Processing sheet: BLK094 from BLK"
[1] "Rows after filtering: 52"
[1] "Processing sheet: BLK095 from BLK"
[1] "Rows after filtering: 15"
[1] "Processing sheet: BLK096 from BLK"
[1] "Rows after filtering: 18"
[1] "Processing sheet: BLK099 from BLK"
[1] "Rows after filtering: 24"
[1] "Processing sheet: BLK115 from BLK"
[1] "Rows after filtering: 34"
[1] "Processing sheet: BLK142 from BLK"
[1] "Rows after filtering: 22"
[1] "Processing sheet: BLK143 from BLK"
[1] "Rows after filtering: 20"
[1] "Processing sheet: IND011 from IND"
[1] "Rows after filtering: 26"
[1] "Processing sheet: IND019 from IND"
[1] "Rows after filtering: 26"
[1] "Processing sheet: IND021 from IND"
[1] "Rows after filtering: 28"
[1] "Processing sheet: IND024 from IND"
[1] "Rows after filtering: 44"
[1] "Processing sheet: IND026 from IND"
[1] "Rows after filtering: 22"
[1] "Processing sheet: IND029 from IND"
[1] "Rows after filtering: 23"
[1] "Processing sheet: IND035 from IND"
[1] "Rows after filtering: 30"
[1] "Processing sheet: IND064 from IND"
[1] "Rows after filtering: 21"
[1] "Processing sheet: IND067 from IND"
[1] "Rows after filtering: 23"
[1] "Processing sheet: IND086 from IND"
[1] "Rows after filtering: 22"
[1] "Processing sheet: IND087 from IND"
[1] "Rows after filtering: 24"
[1] "Processing sheet: IND096 from IND"
[1] "Rows after filtering: 17"
[1] "Processing sheet: IND106 from IND"
[1] "Rows after filtering: 17"
[1] "Processing sheet: IND111 from IND"
[1] "Rows after filtering: 29"
[1] "Processing sheet: IND119 from IND"
[1] "Rows after filtering: 19"
[1] "Processing sheet: IND122 from IND"
[1] "Rows after filtering: 17"
[1] "Processing sheet: IND124 from IND"
[1] "Rows after filtering: 16"
[1] "Processing sheet: IND132 from IND"
[1] "Rows after filtering: 20"
[1] "Processing sheet: IND133 from IND"
[1] "Rows after filtering: 15"
[1] "Processing sheet: IND139 from IND"
[1] "Rows after filtering: 26"
[1] "Processing sheet: IND151 from IND"
[1] "Rows after filtering: 31"
[1] "Processing sheet: IND163 from IND"
[1] "Rows after filtering: 30"
[1] "Processing sheet: IND205 from IND"
[1] "Rows after filtering: 29"
[1] "Processing sheet: IND231 from IND"
[1] "Rows after filtering: 16"
[1] "Processing sheet: MAN006 from MAN"
[1] "Rows after filtering: 20"
[1] "Processing sheet: MAN007 from MAN"
[1] "Rows after filtering: 28"
[1] "Processing sheet: MAN014 from MAN"
[1] "Rows after filtering: 21"
[1] "Processing sheet: MAN034 from MAN"
[1] "Rows after filtering: 18"
[1] "Processing sheet: MAN037 from MAN"
[1] "Rows after filtering: 29"
[1] "Processing sheet: MAN038 from MAN"
[1] "Rows after filtering: 36"
[1] "Processing sheet: MAN042 from MAN"
[1] "Rows after filtering: 26"
[1] "Processing sheet: MAN060 from MAN"
[1] "Rows after filtering: 29"
[1] "Processing sheet: LAW030 from LAW"
[1] "Rows after filtering: 39"
[1] "Processing sheet: LAW035 from LAW"
[1] "Rows after filtering: 36"
[1] "Processing sheet: LAW043 from LAW"
[1] "Rows after filtering: 36"
[1] "Processing sheet: LAW052 from LAW"
[1] "Rows after filtering: 26"
[1] "Processing sheet: LAW062 from LAW"
[1] "Rows after filtering: 29"
[1] "Processing sheet: LAW063 from LAW"
[1] "Rows after filtering: 25"
[1] "Processing sheet: LAW065 from LAW"
[1] "Rows after filtering: 20"
[1] "Processing sheet: LAW070 from LAW"
[1] "Rows after filtering: 30"
[1] "Processing sheet: LAW072 from LAW"
[1] "Rows after filtering: 35"
[1] "Processing sheet: LAW078 from LAW"
[1] "Rows after filtering: 36"
[1] "Processing sheet: LAW082 from LAW"
[1] "Rows after filtering: 22"
[1] "Processing sheet: LAW085 from LAW"
[1] "Rows after filtering: 17"
[1] "Processing sheet: LAW105 from LAW"
[1] "Rows after filtering: 21"
[1] "Processing sheet: LAW107 from LAW"
[1] "Rows after filtering: 24"
[1] "Processing sheet: LAW108 from LAW"
[1] "Rows after filtering: 45"
[1] "Processing sheet: LAW112 from LAW"
[1] "Rows after filtering: 13"
[1] "Processing sheet: LAW120 from LAW"
[1] "Rows after filtering: 37"
[1] "Processing sheet: LAW122 from LAW"
[1] "Rows after filtering: 27"
[1] "Processing sheet: LAW137 from LAW"
[1] "Rows after filtering: 38"
[1] "Processing sheet: ABD012 from ABD"
[1] "Rows after filtering: 15"
[1] "Processing sheet: PLC007 from PLC"
[1] "Rows after filtering: 28"
[1] "Processing sheet: PLC024 from PLC"
[1] "Rows after filtering: 26"
[1] "Processing sheet: PLC028 from PLC"
[1] "Rows after filtering: 46"
[1] "Processing sheet: PLC056 from PLC"
[1] "Rows after filtering: 25"
[1] "Processing sheet: PLC059 from PLC"
[1] "Rows after filtering: 30"
[1] "Processing sheet: PLC070 from PLC"
[1] "Rows after filtering: 42"
[1] "Processing sheet: PLC072 from PLC"
[1] "Rows after filtering: 16"
[1] "Processing sheet: PLC088 from PLC"
[1] "Rows after filtering: 30"
[1] "Processing sheet: PLC092 from PLC"
[1] "Rows after filtering: 20"
[1] "Processing sheet: PLC097 from PLC"
[1] "Rows after filtering: 34"
[1] "Processing sheet: PLC106 from PLC"
[1] "Rows after filtering: 19"
[1] "Processing sheet: PLC107 from PLC"
[1] "Rows after filtering: 22"
[1] "Processing sheet: PLC121 from PLC"
[1] "Rows after filtering: 43"
[1] "Processing sheet: PLC136 from PLC"
[1] "Rows after filtering: 28"
[1] "Processing sheet: PLC137 from PLC"
[1] "Rows after filtering: 25"
[1] "Processing sheet: PLC144 from PLC"
[1] "Rows after filtering: 34"
[1] "Processing sheet: PLC220 from PLC"
[1] "Rows after filtering: 31"
[1] "Processing sheet: PLC223 from PLC"
[1] "Rows after filtering: 32"
[1] "Processing sheet: LNP018 from LNP"
[1] "Rows after filtering: 26"
[1] "Processing sheet: LNP019 from LNP"
[1] "Rows after filtering: 39"
[1] "Processing sheet: LNP045 from LNP"
[1] "Rows after filtering: 25"
[1] "Processing sheet: LNP050 from LNP"
[1] "Rows after filtering: 39"
[1] "Processing sheet: LNP095 from LNP"
[1] "Rows after filtering: 41"
[1] "Processing sheet: BIS055 from BIS"
[1] "Rows after filtering: 27"
[1] "Processing sheet: BIS060 from BIS"
[1] "Rows after filtering: 33"
[1] "Processing sheet: BIS082 from BIS"
[1] "Rows after filtering: 26"
[1] "Processing sheet: BIS085 from BIS"
[1] "Rows after filtering: 28"
[1] "Processing sheet: TIN006 from TIN"
[1] "Rows after filtering: 19"
[1] "Processing sheet: TIN028 from TIN"
[1] "Rows after filtering: 20"
[1] "Processing sheet: TIN030 from TIN"
[1] "Rows after filtering: 46"
[1] "Processing sheet: TIN050 from TIN"
[1] "Rows after filtering: 23"
[1] "Processing sheet: TIN053 from TIN"
[1] "Rows after filtering: 21"
[1] "Processing sheet: TIN064 from TIN"
[1] "Rows after filtering: 19"
[1] "Processing sheet: TIN068 from TIN"
[1] "Rows after filtering: 19"
[1] "Processing sheet: UNW029 from UNW"
[1] "Rows after filtering: 19"
[1] "Processing sheet: UNW031 from UNW"
[1] "Rows after filtering: 48"
[1] "Processing sheet: UNW039 from UNW"
[1] "Rows after filtering: 42"

[1] "Final data preview:"
# A tibble: 6 × 19
  Parcel Code   Transect Cover  Year Entity plotid      Species CommonName Order
  <chr>  <chr>  <chr>    <dbl> <dbl> <chr>  <chr>       <chr>   <chr>      <chr>
1 ABD012 ERNA10 10          19  2025 ICWD   ABD012_10_… Ericam… rabbitbru… Aste…
2 ABD012 ARTRT  10           2  2025 ICWD   ABD012_10_… Artemi… sagebrush… Aste…
3 ABD012 ERBR7  10           2  2025 ICWD   ABD012_10_… Eriogo… buckwheat… Poly…
4 ABD012 ATCA2  10           1  2025 ICWD   ABD012_10_… Atripl… saltbush,… Cary…
5 ABD012 PSPO   10           1  2025 ICWD   ABD012_10_… Psorot… dalea, Do… Faba…
6 ABD012 ERNA10 12          17  2025 ICWD   ABD012_12_… Ericam… rabbitbru… Aste…
# ℹ 9 more variables: Family <chr>, Genus <chr>, Lifecycle <chr>,
#   Lifeform <chr>, Veg_Type <chr>, source <chr>, source.abr <chr>,
#   Phreatophyte <chr>, plotid.full <chr>

6 ICWD Processing Output

The extract step produces the ICWD long‑format dataset (final_data). This includes species‑code fixes, parcel IDs from sheet names, and per‑transect cover summaries.

[1] "No duplicates found in the dataset."

7 Transect Coverage Summary

Summarize how many transects were read per parcel for the current year to confirm sampling completeness.

8 Export ICWD Long Format

Save the ICWD long‑format data for downstream joins and QA/QC.

[1] "Data saved to output/icwd_data2025.csv"

9 LADWP Processing

Load LADWP’s long‑format cover file, harmonize codes and transect IDs, and join species attributes to match the ICWD schema.

[1] "Processing LADWP 2025 data..."

[1] "LADWP raw data rows: 4053"

[1] "LADWP unique parcels: 161"

[1] "LADWP processed data preview:"

# A tibble: 5 × 19
  Parcel Code   Transect Cover  Year Entity plotid      Species CommonName Order
  <chr>  <chr>  <chr>    <dbl> <dbl> <chr>  <chr>       <chr>   <chr>      <chr>
1 ABD012 ARTR2  1           15  2025 LADWP  ABD012_1_jm Artemi… sagebrush… Aste…
2 ABD012 ATCA2  1            4  2025 LADWP  ABD012_1_jm Atripl… saltbush,… Cary…
3 ABD012 ERNA10 1            2  2025 LADWP  ABD012_1_jm Ericam… rabbitbru… Aste…
4 ABD012 PSARM  1            1  2025 LADWP  ABD012_1_jm Psorot… indigo bu… Faba…
5 ABD012 ARTR2  3           10  2025 LADWP  ABD012_3_jm Artemi… sagebrush… Aste…
# ℹ 9 more variables: Family <chr>, Genus <chr>, Lifecycle <chr>,
#   Lifeform <chr>, Veg_Type <chr>, source <chr>, source.abr <chr>,
#   Phreatophyte <chr>, plotid.full <chr>

[1] "LADWP processed rows: 4047"

10 Combine ICWD + LADWP

Bind the two entity datasets into a combined 2025 dataset and export a merged long‑format file.

[1] "Combined 2025 data summary:"

[1] "Total rows: 14542"

[1] "ICWD rows: 10495"

[1] "LADWP rows: 4047"

[1] "Unique parcels: 161"

[1] "Unique parcels ICWD: 137"

[1] "Unique parcels LADWP: 161"

[1] "Combined data saved to output/ICWD_LADWP_merged_lpt_2025.csv"

11 Master Dataset Update

Append the current year to the prior master file and write a new master CSV.

[1] "Loaded previous master dataset: 451223 rows"
[1] "Master 2025 dataset: 465765 rows"
[1] "Years in master: 1985, 1986, 1987, 1991, 1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024, 2025"
[1] "Master dataset saved to output/lpt_MASTER_2025.csv"

12 Transect Lookup (Base vs Actual)

Create a lookup that maps base transects (e.g., T2) to actual transect IDs (e.g., T2_15) for traceability.

[1] "Transect lookup table saved to output/transect_lookup_2025.csv"

[1] "Lookup table dimensions: 853 rows"

[1] "Sample of lookup table:"

# A tibble: 10 × 4
   Parcel Base_Transect Actual_Transect  Year
   <chr>  <chr>         <chr>           <dbl>
 1 ABD012 10            10               2025
 2 ABD012 12            12               2025
 3 ABD012 2             2                2025
 4 ABD012 4             4                2025
 5 ABD012 6             6                2025
 6 ABD012 8             8                2025
 7 BIS055 2             2                2025
 8 BIS055 4             4                2025
 9 BIS055 6             6                2025
10 BIS055 8             8                2025

13 ICWD Wide Export

Pivot ICWD data back to a wide transect format for compatibility with legacy workflows.

14 Transect Entity Metadata

Track which parcel+transect records came from ICWD vs LADWP and flag duplicates.

[1] "Transect-Entity Metadata Summary:"

[1] "Total unique Parcel-Transect combinations: 1763"

[1] "Parcels with data: 161"

[1] "\n⚠️ WARNING: Found 2 transects present in multiple entities:"
# A tibble: 2 × 4
  Parcel Transect n_entities entities   
  <chr>  <chr>         <int> <chr>      
1 LAW112 2                 2 ICWD, LADWP
2 PLC136 2                 2 ICWD, LADWP

[1] "\nTransect-entity metadata saved to output/transect_entity_metadata_2025.csv"

[1] "Duplicate transects report saved to output/transect_duplicates_2025.csv"

15 Export LADWP Long Format

Save LADWP’s processed long‑format data for audit and downstream analysis.

[1] "LADWP data saved to output/ladwp_data2025.csv"

16 LADWP Wide Export

Create a wide‑format LADWP file consistent with the ICWD export.

[1] "LADWP wide format data saved to output/ladwp_data2025_wide.csv"

[1] "LADWP wide format dimensions: 1627 rows, 20 columns"

17 Combined Wide Export

Generate a combined ICWD+LADWP wide‑format file for the full 2025 dataset.

[1] "Combined ICWD+LADWP wide format data saved to output/ICWD_LADWP_merged_2025_wide.csv"

[1] "Combined wide format dimensions: 2419 rows, 26 columns"

[1] "Preview of combined wide format:"

# A tibble: 5 × 26
  Parcel SPECIES T1    T2    T3    T4    T5    T6    T7    T8    T9    T10  
  <chr>  <chr>   <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 ABD012 ACHY    ""    ""    ""    ""    "1"   ""    ""    ""    ""    ""   
2 ABD012 ARTR2   "15"  ""    "10"  ""    "7"   ""    "13"  ""    "17"  ""   
3 ABD012 ARTRT   ""    "6"   ""    "17"  ""    "8"   ""    "15"  ""    "2"  
4 ABD012 ATCA2   "4"   "5"   ""    "0"   ""    "3"   "8"   "0"   "5"   "1"  
5 ABD012 ERBR7   ""    "1"   ""    "0"   ""    "0"   ""    "0"   ""    "2"  
# ℹ 14 more variables: T11 <chr>, T12 <chr>, T13 <chr>, T14 <chr>, T15 <chr>,
#   T16 <chr>, T17 <chr>, T18 <chr>, T19 <chr>, T20 <chr>, T21 <chr>,
#   T22 <chr>, T23 <chr>, T24 <chr>

[1] "Wide format data saved to output/icwd_data2025_wide.csv"

[1] "Wide format dimensions: 1582 rows, 23 columns"

[1] "Preview of wide format:"

# A tibble: 5 × 23
  Parcel SPECIES    T1    T2    T3    T4    T5    T6    T7    T8    T9   T10
  <chr>  <chr>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 ABD012 ARTRT      NA     6    NA    17    NA     8    NA    15    NA     2
2 ABD012 ATCA2      NA     5    NA     0    NA     3    NA     0    NA     1
3 ABD012 ERBR7      NA     1    NA     0    NA     0    NA     0    NA     2
4 ABD012 ERNA10     NA     4    NA     6    NA     5    NA    11    NA    19
5 ABD012 PSPO       NA     1    NA     1    NA     0    NA     0    NA     1
# ℹ 11 more variables: T11 <dbl>, T12 <dbl>, T13 <dbl>, T14 <dbl>, T15 <dbl>,
#   T16 <dbl>, T18 <dbl>, T19 <dbl>, T20 <dbl>, T22 <dbl>, T24 <dbl>

18 Optional: Interactive Exploration

If available, GWalkR provides an interactive way to explore the ICWD long‑format data.

Citation

BibTeX citation:

@report{2025,
  author = {},
  publisher = {Inyo County Water Department},
  title = {Green {Book} {Section} {I.C.1.a} - {Line} Point Data
    Processing - 2025},
  date = {2025-12-05},
  url = {https://inyo-gov.github.io/vegetation-condition/},
  langid = {en}
}

For attribution, please cite this work as:

“Green Book Section I.C.1.a - Line Point Data Processing - 2025.” 2025. Annual Report. Inyo County Water Department. https://inyo-gov.github.io/vegetation-condition/.