faostat-export-dataset

name

faostat-export-dataset

description

Use when the user wants a clean, documented tabular export of FAOSTAT data — "export FAOSTAT", "download the data", "give me the CSV / xlsx of [indicator]", "I want the raw numbers", "tabular data pull", "export to spreadsheet". The deliverable is a bundle — one multi-sheet .xlsx (README / Data_tidy / Data_wide / Methodology / Sources) + one tidy-long .csv mirror + a data-dictionary .md. No prose, no charts, no narrative — just the data with full provenance. Do NOT use for reports, briefs, papers, infographics, or visualisations — those skills embed their own xlsx appendices. Use this skill when the data itself IS the deliverable.

FAOSTAT Data Export

Ship clean, documented FAOSTAT data. The user wants the numbers, not the story. Deliver a three-file bundle: one multi-sheet .xlsx, one tidy-long .csv, and a data-dictionary .md. Every row traces back to the FAOSTAT API call that produced it.

Prerequisites

FAOSTAT MCP tools: faostat_get_data, faostat_search_codes, faostat_list_domains, faostat_get_metadata. If any are missing, stop and tell the user the skill requires the FAOSTAT MCP server.

Python packages: openpyxl, pandas. Install with --break-system-packages in the sandbox.

Invariants

Cross-skill invariants (all six — violations are skill bugs):

1. FILTER vs DISPLAY codes. faostat_get_data takes FILTER codes (e.g., 2510 Production). faostat_get_rankings takes DISPLAY codes (e.g., 5510). Never invert.

2. Year syntax. Comma-separated lists only ('2010,2011,...,2023'). Colon ranges return empty in practice.

3. Element filter required on every faostat_get_data call. Unfiltered pulls are massive, especially in emissions domains.

4. TCL for national trade aggregates, TM only for partner breakdowns. Never sum TM rows to reconstruct national totals.

5. China composite default (Apr 2026 user preference). Default: composite China (area 351). China, mainland (41) is an opt-in; full disaggregation (41 + 96 + 128 + 214) is also opt-in. Record the choice in the README sheet with the FAOSTAT-default-41 caveat.

6. faostat_get_rankings HTTP-500 fallback. On failure, reconstruct by pulling faostat_get_data across all reporting countries and sorting client-side. Note the fallback in Methodology.

7. Element and item code resolution. Never use a hardcoded numeric element or item code as the primary value in a faostat_get_data call. Always resolve at runtime: faostat_search_codes(domain_code='<dom>', dimension_id='element', query='<metric name>') for elements; faostat_search_codes(domain_code='<dom>', dimension_id='item', query='<item name>') for items. Numeric codes shown in reference tables and code examples are verified hints — use them to validate the search result, not as the authoritative source. Domain letter-codes (QCL, TCL, GT, EM, FBS, FS…) are stable and may be used directly.

Export-specific invariants:

8. Every value traces to an API call. The Methodology sheet logs one row per faostat_get_data call with domain / area / element / item / year list / timestamp / rows-returned. No value appears in the export that did not come from a documented call.
9. FAO-native units kept as-is. No auto-conversion. Values stay in FAOSTAT's native units (kt CO₂eq, tonnes, USD 1000, etc.). Unit is a column in tidy-long and a header row in wide. If the user explicitly asks for normalised units, add normalised columns alongside the native ones — never in place of.
10. No FAO branding. No FAO logo, "Food and Agriculture Organization of the United Nations" masthead, ISSN, "FAO Statistics Division" stamp, or "Required citation: FAO. …" line. CC-BY-4.0 attribution to FAOSTAT (source, licence, access date) IS kept — that's a property of the source data.
11. Both shapes in the xlsx. Data_tidy (long) and Data_wide (years-as-columns pivot) are both always present. The CSV mirror is tidy-long only. A wide CSV is produced only if the user asks.

Workflow

Step 1 — Gather parameters

Required from the user (prompt via AskUserQuestion in Cowork, inline otherwise):

• Domain — e.g., GT, QCL, TCL, ET, PP, FBS, FS.
• Element(s) — FILTER code(s). If the user supplies a name rather than a code, resolve via faostat_search_codes (invariant 7) before use.
• Year range — list of years (will be passed comma-separated).
• Area scope — World, specific regions, specific country list, or "all reporting countries". Default if unspecified: all reporting countries plus regional aggregates (5000/5100/5200/5300/5400/5500).

Optional:

• Item(s) — FILTER code(s). If omitted, all items in domain for the chosen element.
• Flag filter — default keep all; optional strip to official-value-only.
• China handling — default composite_351. Options: mainland_41 or disaggregated (41,96,128,214).
• Topic title — propose one from domain + element + item + year range if the user does not supply.

Step 2 — Resolve codes

Always resolve element and item codes at runtime before the data pull (invariant 7). Call faostat_search_codes(domain_code=…, dimension_id='element'|'item'|'area', query=…) for every numeric code needed — even when the user supplies a code directly, verify it matches. Print the resolved codes back in a short confirmation block before the heavy pull so mismatches surface early.

Step 3 — Pull data

Call faostat_get_data with:

• domain_code
• area = comma-separated list (applying the China rule: 351 composite / 41 mainland / 41,96,128,214 disaggregated)
• element = comma-separated FILTER codes
• item = comma-separated FILTER codes (or omit for "all items in domain")
• year = comma-separated year list (invariant 2)

Log one Methodology row per API call, as the call is made — do not retrofit. If multiple element codes need different item sets, issue separate calls and concatenate. Never conflate element-item pairings across calls.

Step 4 — Clean the dataframe

In order:

1. Coerce Value to numeric. Drop rows where Value is null.
2. Rename columns to the canonical schema: area_code, area, item_code, item, element_code, element, year, unit, value, flag, note (omit item_code/item if the domain has no items, e.g. ET temperature anomalies).
3. Keep both code and label columns — downstream analysts need them.
4. Sort by area, item, element, year.

Step 5 — Build the tidy-long CSV

Schema: area_code, area, item_code, item, element_code, element, year, unit, value, flag, note.

Header comment lines at the top of the CSV (prefixed with #):

# Source: FAOSTAT (https://www.fao.org/faostat/), accessed YYYY-MM-DD. Licence: CC-BY-4.0.
# Domain: <CODE> (<label>). Elements: <codes>. Items: <codes or "all">. Years: <list>.
# China handling: <composite_351 / mainland_41 / disaggregated>.
# Rows: <N>. Generated by faostat-export-dataset skill.

Save as <topic_slug>_<year_start>-<year_end>_tidy.csv.

Step 6 — Build the wide pivot

Rows: (area_code, area, item_code, item, element_code, element, unit). Columns: years. Values: value.

Keep area_code and item_code — do not drop identifiers. If the pivot contains multiple elements with different units, keep one row per element; unit shown in its own column.

Step 7 — Assemble the xlsx

Sheets in this order:

• README — plain-prose block:
  • Title (topic + year range)
  • Source line (FAOSTAT, licence, access date)
  • Column dictionary (one line per column — mirrors the .md file)
  • China-handling choice with the FAOSTAT-default-41 caveat
  • Flag codes key (from faostat_get_metadata)
  • File manifest (tidy.csv / xlsx / data-dictionary.md)
• Data_tidy — identical schema to the tidy CSV (no # comment header inside xlsx).
• Data_wide — pivoted with years as columns.
• Methodology — one row per API call: call_id, timestamp_utc, domain, area_param, element_param, item_param, year_param, rows_returned, notes. Year param stored as the literal comma-separated string that was passed.
• Sources — one row per domain used: domain_code, domain_label, citation, url, licence, access_date. Pulled from faostat_get_metadata.

Step 8 — Build data-dictionary.md

Plain markdown file:

• Dataset description — topic, year range, scope, row count.
• Column schema table — column name | type | description | example | nullable.
• Unit reference table — unit string | full name | note (no conversion applied; purely descriptive).
• Flag codes — flag | meaning.
• Known caveats — China handling, any FAOSTAT revisions relevant to the pulled years, any items that changed codes over the series.
• Citation block — FAOSTAT + licence + access date + full URL of each domain used.

Step 9 — Save and present

Save all three files to the outputs folder with a shared slug:

• <slug>.xlsx
• <slug>_tidy.csv
• <slug>_data-dictionary.md

Confirm to the user with links to all three files and a one-line summary: "topic, year range, N rows, K sheets in xlsx, China handling = choice." Nothing more — this is a data-delivery skill, not a narrative skill.

Slug convention

{domain_lower}_{primary_element_label}_{year_start}-{year_end}

Examples:

• gt_emissions-total_2001-2023
• qcl_production_cattle-poultry-pig_2010-2024
• tcl_export-value_cereals_2015-2024
• pp_producer-price_coffee-cocoa_2020-2024

Lowercase, hyphen-separated labels, underscore between segments.

Error handling

• Empty faostat_get_data result. Re-check comma-separated year syntax (invariant 2) and the element/item resolution. If the problem persists, widen the item filter to "all items in domain" to confirm the domain + area + year combo returns anything at all. Log the failure in Methodology with rows_returned = 0, notes = "…".
• Rankings HTTP 500. Use the get_data fallback (invariant 6). Record in Methodology.
• Mixed units in one element. Shouldn't happen for a single element code; if it does, split into separate Data_wide blocks per unit.
• User asks for a narrative or a chart. Decline politely and route to faostat-analytical-brief / faostat-infographic / faostat-story / faostat-scientific-paper / faostat-viz. This skill is data-only.
• User asks to normalise units. Explain the invariant (keep FAO-native, auditable). If they insist, add normalised columns alongside — never replace the native ones.

Defaults summary

knob	default
bundle	xlsx + tidy csv + data-dictionary md
shapes	tidy-long + wide pivot (both)
China	composite 351
units	FAO-native (no conversion)
flag filter	keep all flags
year range	user must supply — no default
item filter	if omitted, all items in domain
area scope	if omitted, all reporting countries + regional aggregates

All defaults can be overridden by the user; every override is documented in the README sheet and the data-dictionary.md.