ameriflux-bif-timeseries-sorter

name

ameriflux-bif-timeseries-sorter

description

Sort AmeriFlux BASE-BADM BIF variables from Excel or CSV files into observation, management-event, static metadata, and plotting-ready target time-series tables. Use when Codex is asked to sort AmeriFlux BIF variables into time series; organize BADM/BIF variables by date; pivot AMF *_BIF_*.xlsx files by GROUP_ID; output total LAI, leaf mass per area, above-ground biomass, canopy height, fruit yield, or total yield time series; write MATLAB readers for BIF time-series plotting; or prepare dated AmeriFlux site observations and management records for EcoSIM workflows.

AmeriFlux BIF Time-Series Sorter

Overview

Use this skill to convert AmeriFlux BADM BIF long tables into time-series tables. BIF files commonly store one variable per row with columns such as SITE_ID, GROUP_ID, VARIABLE_GROUP, VARIABLE, and DATAVALUE. A single dated observation or management event is reconstructed by grouping rows with the same SITE_ID, VARIABLE_GROUP, and GROUP_ID.

When To Use

Use this skill for requests such as:

• sort variables into time series
• sort AmeriFlux BIF variables into time series
• organize BADM variables by date
• convert AMF_<SITE>_BIF_*.xlsx into dated observation and management tables
• prepare BIF site observations or management events for EcoSIM comparison/input workflows

Do not treat BIF files as continuous half-hourly BASE flux files. BIF/BADM records are metadata, biological observations, soil observations, and management/event records, often irregular in time.

Workflow

1. Inspect the input workbook or CSV.

   • Prefer the AMF-BIF sheet for AmeriFlux BIF Excel files.
   • Confirm required columns: SITE_ID, GROUP_ID, VARIABLE_GROUP, VARIABLE, DATAVALUE.
   • Record the site ID and the source file path.

2. Reconstruct records.

   • Group rows by SITE_ID, VARIABLE_GROUP, and GROUP_ID.
   • Pivot VARIABLE names into columns and keep raw DATAVALUE strings.
   • Preserve comments, units, depth/profile fields, species/organ/phenology qualifiers, and date uncertainty fields.

3. Identify date-bearing records.

   • Treat variables ending in _DATE as observation dates.
   • Treat variables ending in _DATE_START and _DATE_END as event intervals.
   • Preserve _DATE_UNC as a qualifier, not as the timestamp.
   • Normalize dates like YYYYMMDD to YYYY-MM-DD for sorting, while keeping raw values in the original variable columns.

4. Classify record groups.

   • Observation series: dated biological or soil observations, for example GRP_AG_BIOMASS_CROP, GRP_LAI, GRP_HEIGHTC, GRP_LMA, GRP_BIOMASS_CHEM, GRP_SOIL_CHEM, and GRP_SPP_O.
   • Management/event series: dated site operations, usually GRP_DM_*, such as planting, fertilization, tillage, pesticide, agriculture, or external-weather events.
   • Static metadata: records without a usable date, such as location, climate averages, soil texture, IGBP, DOI, team members, and references.

5. Write outputs under result/<SITE_ID>/bif_timeseries/ unless the user asks for another path.

   • all_records_wide.csv: one row per reconstructed BIF record, sorted by series class, variable group, timestamp, and group ID.
   • time_series_long.csv: one row per main data variable from date-bearing records, with timestamp, date_start, date_end, unit, raw_value, and qualifiers_json.
   • by_group/<VARIABLE_GROUP>.csv: one wide dated table per variable group.
   • target_timeseries/total_lai.csv: plotting-ready rows from GRP_LAI variable LAI_TOT.
   • target_timeseries/leaf_mass_per_area.csv: plotting-ready rows from GRP_LMA variable LMA.
   • target_timeseries/aboveground_biomass.csv: plotting-ready rows from GRP_AG_BIOMASS_CROP variable AG_BIOMASS_CROP, preserving organ, phenology, unit, and comments.
   • target_timeseries/canopy_height.csv: plotting-ready rows from GRP_HEIGHTC variable HEIGHTC, preserving statistic qualifiers.
   • target_timeseries/fruit_yield.csv: plotting-ready rows from GRP_AG_PROD_CROP variable AG_PROD_CROP where AG_PROD_CROP_ORGAN is Fruits.
   • target_timeseries/total_yield.csv: plotting-ready rows from GRP_AG_PROD_CROP variable AG_PROD_CROP where AG_PROD_CROP_ORGAN is Total.
   • static_metadata.csv: wide records without usable dates.
   • summary.json: counts, groups, date fields, and output paths.

6. Report the result.

   • List observation time series, management/event series, and static metadata groups.
   • Mention records that lacked dates and were not sorted as time series.
   • For EcoSIM use, flag which groups can inform comparison targets versus management inputs.

Bundled Script

Use scripts/sort_bif_timeseries.py for deterministic conversion:

python3 .agents/skills/ameriflux-bif-timeseries-sorter/scripts/sort_bif_timeseries.py \
  /path/to/AMF_US-Ne3_BIF_20250522.xlsx \
  --out-dir result/US-Ne3/bif_timeseries

The script accepts .xlsx, .xlsm, and CSV files. It uses openpyxl for Excel input and writes only CSV/JSON outputs.

Use scripts/read_bif_target_timeseries.m in MATLAB to read the plotting-ready target files:

data = read_bif_target_timeseries("result/US-Ne3/bif_timeseries", true);

The returned struct contains data.total_lai, data.leaf_mass_per_area, data.aboveground_biomass, data.canopy_height, data.fruit_yield, and data.total_yield tables. Each table includes Time as datetime and Value as numeric when the source values can be parsed.

EcoSIM Notes

• Use observation groups such as LAI, crop biomass, yield/crop production, height, biomass chemistry, and soil chemistry for model-data comparison after checking units and aggregation windows.
• Use GRP_DM_* groups as candidate management inputs, but check event semantics before converting to EcoSIM planting, harvest, fertilization, irrigation, tillage, or pesticide records.
• Do not silently convert units. Preserve raw units and report missing units where comparisons or EcoSIM inputs require conversion.