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

Inyo County, California

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.

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. Transect IDs are kept as character — regular transects are "1", "2", etc. and suffixed transects retain their original label: "2_15", "5_20", "3_92". The suffix indicates the year the transect was first added (2015, 2020, 1992).

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: BGP019 from BGP"
[1] "Rows after filtering: 36"
[1] "Processing sheet: BGP031 from BGP"
[1] "Rows after filtering: 28"
[1] "Processing sheet: BGP047 from BGP"
[1] "Rows after filtering: 20"
[1] "Processing sheet: BGP086 from BGP"
[1] "Rows after filtering: 21"
[1] "Processing sheet: BGP088 from BGP"
[1] "Rows after filtering: 21"
[1] "Processing sheet: BGP094 from BGP"
[1] "Rows after filtering: 30"
[1] "Processing sheet: BGP154 from BGP"
[1] "Rows after filtering: 17"
[1] "Processing sheet: BGP157 from BGP"
[1] "Rows after filtering: 30"
[1] "Processing sheet: BGP162 from BGP"
[1] "Rows after filtering: 30"
[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_v2.csv (suffixed transects as 400/500/600-series IDs)"

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: 15034"

[1] "ICWD rows: 10987"

[1] "LADWP rows: 4047"

[1] "Unique parcels: 161"

[1] "Unique parcels ICWD: 144"

[1] "Unique parcels LADWP: 161"

[1] "Combined data (v2) saved to output/ICWD_LADWP_merged_lpt_2025_v2.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: 466257 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 (v2) saved to output/lpt_MASTER_2025_v2.csv"

12 Transect Inventory

Summary of all transect IDs per parcel. Suffixed transects (e.g. 2_15, 3_92) are kept as-is — the suffix indicates the year the transect was first added.

[1] "Transect inventory: 897 rows"

[1] "Suffixed transects: 49 across 18 parcels"

13 ICWD Wide Export

Pivot ICWD data back to a wide transect format. Suffixed transects (e.g. T2_15) are distinct columns, never collapsed into the base transect.

[1] "Wide format (v2, suffixed transects separated) saved to output/icwd_data2025_wide_v2.csv"

[1] "Production ICWD wide input saved to data/lpt_ICWD_2025.csv"

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: 1807"

[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 (v2) saved to output/ladwp_data2025_wide_v2.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. Suffixed transects appear as 400/500/600-series columns (e.g. T402 for T2_15).

[1] "Combined ICWD+LADWP wide format (v2) saved to output/ICWD_LADWP_merged_2025_wide_v2.csv"

[1] "Combined wide format dimensions: 2454 rows, 48 columns"

18 Optional: Interactive Exploration

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