Fertilizer data prep

Scripts organized by data source. This repo contains source code only — no data. No input, intermediate, or output data files are committed (see .gitignore), including licensed Bloomberg extracts.

• fao/ — FAO fertilizer use/application/price tracker ("David's track"). scripts/ pulls FAOSTAT bulk data, prices, and pushes updates to the tracker DB/dashboard.
• wb_pink_sheets/ — World Bank Commodity Markets ("pink sheets") price data prep for the fertilizer/energy/grains dashboard.
• ifa_blog/ — IFA phosphate consumption data prep for the Delphine blog post/report.
• shared/ — scripts/paths.R (path helpers used by every script) plus downstream visualization scripts consuming FAO-derived output.

Paths

Open Tool_Fertilizer data prep_final.Rproj and run scripts from there. Every path is resolved with here::here(), anchored on the .Rproj file, so scripts behave identically whether run from RStudio, from Rscript, or knitted as an .Rmd. There are no setwd() calls and no absolute paths — don't reintroduce either.

Each script starts with:

source(here::here("shared/scripts/paths.R"))

which provides:

Helper	Returns
fsp_raw(source, ...)	path under data/<source>/raw/, creating the folder
fsp_processed(source, ...)	path under data/<source>/processed/, creating the folder
fsp_require(path)	the path, or a clear error naming the missing file
fsp_connection()	location of the DB credentials file
fsp_write_copies(x, filename)	writes copies into the configured dashboard folders

Folders are created on demand, so data/ appears on first run.

data/
  fao/{raw,processed}/
  wb_pink_sheets/{raw,processed}/
  ifa_blog/{raw,processed}/

Setup

Credentials

Nothing secret is stored in this repo. Two things must be supplied locally:

1. Tracker database. TRACKER_FAO_processing.R and TRACKER_Prices.R expect a connection.RData containing a connection list with dbname, host, port, user, password. Default location is the project root; override with FSP_DB_CONNECTION. It is gitignored and must never be committed.
2. Google Sheets account. TRACKER_update.R authenticates via gs4_auth(Sys.getenv("FSP_GSHEET_ACCOUNT")).

Environment variables (~/.Renviron)

FSP_GSHEET_ACCOUNT=your.account@example.org
FSP_DB_CONNECTION=C:/path/to/connection.RData
FSP_DASHBOARD_DIRS=C:/path/to/FertilizerDashboard_2025_08;C:/path/to/FertilizerDashboard13Jan22

FSP_DASHBOARD_DIRS is a ;-separated list of live Shiny app folders. Scripts always write to data/<source>/processed/; if this variable is set, they also copy the relevant .rds files into each folder listed. Leave it unset and the pipelines still run — they just skip the copies. Restart R after editing .Renviron.

Local data setup

Data is not in the repo. Place these files under data/ before running (all gitignored):

Destination	Contents	Source
data/fao/raw/AddOnTables_Fertilizer.Rdata	country.label concordance, fertiPstat FAO phosphate stats	FAO / World Bank, hand-curated
data/fao/raw/ticker_description.RData	50 price tickers mapped to FSP database IDs	Bloomberg (licensed, do not redistribute)
data/fao/raw/ExchangeRates/*.xlsx	Daily FX series (CAD, CNY, EUR)	Bloomberg terminal export (licensed). Currently unused — TRACKER_Prices.R pulls FX via tidyquant::tq_get()
data/fao/raw/FAO_ExportDataNPK_9-24-2024.csv	FAO NPK export extract	FAOSTAT
data/fao/raw/vulnerabilityTrade.Rdata, ERTimeProfile.Rdata	Trade vulnerability / export restriction profiles	Missing — see Known issues
data/wb_pink_sheets/raw/CMO-Historical-Data-Monthly.xlsx	Pink sheets monthly prices and indices	World Bank CMO
data/ifa_blog/raw/IFAConsumptionP_2024_12_12/	IFA consumption workbooks (By region, African countries)	IFA

The FAOSTAT bulk zips used by TRACKER_FAO_processing.R download themselves into data/fao/raw/ on first run.

Pipeline order

TRACKER_FAO_processing.R produces data/fao/processed/FAOSTATProcessed.Rdata, application.rds, and FertilizerUse.rds, which TRACKER_Prices.R, TRACKER_update.R, and shared/scripts/fertilizerProduction_2025_03_07.R all read. Run it first, or drop an existing copy of those files into data/fao/processed/.

Known issues

TRACKER_update.R cannot run yet. It loads vulnerabilityTrade.Rdata and ERTimeProfile.Rdata, which are not present anywhere — the old sourcesR/ folder holds only FAOSTATProcessed.Rdata. Both need to be located or regenerated. The script now fails immediately with a message naming the missing file.