Excel workbook workflow

Purpose

This vignette explains how to organise an Excel workbook for run_reconstruction_excel(). It is aimed at users who prepare demographic data outside R but want the same constrained Weibull reconstruction routes used by the R functions.

The Excel interface does not change the underlying model. The central condition is still

\[ R_0 = \sum_x l_x m_x = 1. \]

The function analyses fertility exactly as supplied. It never normalises fertility silently.

Minimal input table

Each data sheet must contain one fertility column. An age column is optional and is kept as a label in the output. The internal calculations always use consecutive class indices 0, 1, 2, ..., n - 1.

A minimal sheet with observed survivorship can look like this:

select_sheet <- data.frame(
  `Age (years)` = 0:5,
  `mx (Fertility Rate)` = c(0, 0, 0.30, 0.75, 0.60, 0.20),
  `lx (Survivorship)` = c(1.0000000, 0.8480619, 0.7023945, 0.5759026, 0.4689639, 0.3798816),
  check.names = FALSE
)

knitr::kable(select_sheet, digits = 4)

A sheet with one fixed beta value and no observed survivorship can look like this:

fixed_sheet <- data.frame(
  Age = 0:5,
  mx = c(0, 0, 0.30, 0.75, 0.60, 0.20),
  Beta = c(1.20, rep(NA, 5)),
  check.names = FALSE
)

knitr::kable(fixed_sheet, digits = 4)

A sheet with fertility only is treated as a beta-scan scenario route:

scan_sheet <- data.frame(
  Age = 0:5,
  mx = c(0, 0, 0.30, 0.75, 0.60, 0.20),
  check.names = FALSE
)

knitr::kable(scan_sheet, digits = 4)

Recognised column names

Column names are matched case-insensitively and can contain units or descriptive text. These headings are recognised automatically:

When a workbook uses unusual headings, supply them explicitly:

run_reconstruction_excel(
  input_file = "demography.xlsx",
  output_file = "results.xlsx",
  sheets = "Ovis_dalli",
  age_column = "Age class",
  fertility_column = "Female fertility",
  survivorship_column = "Observed survivorship",
  beta_column = "Beta"
)

Automatic route selection

The default mode = "auto" chooses a route separately for each sheet.

This design avoids a common ambiguity: a scan without observed survivorship is not a statistical selection of beta. It is a scenario generator.

Running the workbook

The simplest interactive call opens the operating-system file chooser:

run_reconstruction_excel()

A reproducible script should pass file paths explicitly:

result <- run_reconstruction_excel(
  input_file = "demography.xlsx",
  output_file = "demography_StablePopulation.xlsx"
)

The function returns an invisible object, so assign it when metadata or internal result objects are needed:

result <- run_reconstruction_excel("demography.xlsx")

result$output_file
result$metadata

Standard and full outputs

The default output_detail = "standard" produces a compact workbook:

Overview
Result_<sheet 1>
Result_<sheet 2>
...

The Overview sheet records the route used for each input sheet, selected parameters when applicable, RMSE for the selection route, R0 checks, scan status, and notes.

For method development or auditing, request the full output:

run_reconstruction_excel(
  input_file = "demography.xlsx",
  output_file = "demography_full_StablePopulation.xlsx",
  output_detail = "full"
)

The full workbook additionally contains candidate tables, scan profiles, and metadata.

Terminal-window scenarios

When observed survivorship is absent, a terminal window can be supplied to the scan route:

run_reconstruction_excel(
  input_file = "demography.xlsx",
  output_file = "terminal_scenarios.xlsx",
  mode = "scan",
  beta_values = seq(0.05, 3.00, by = 0.05),
  terminal_window = c(0.0001, 0.05)
)

The terminal condition is:

lower <= final lx <= upper

It filters candidate scenarios. It does not estimate beta, and it does not identify a unique central profile.

Fertility normalisation before Excel analysis

The Excel workflow analyses fertility exactly as it appears in the sheet. If the scientific decision is to normalise fertility relative to a reference survivorship schedule, do it explicitly in R and then save the normalised values into the workbook.

normalised <- normalize_fertility(
  fertility_rates = mx,
  lx_reference = lx_observed
)

mx_normalised <- normalised$fertility_rates_normalized

Then use mx_normalised as the fertility column in the Excel sheet. This keeps raw and normalised analyses visibly separate.

Common data problems

The function is deliberately strict about data tables.

This strictness is useful for reproducibility. It is better for the workflow to fail early than to silently analyse the wrong column or remove biologically meaningful classes.

Legacy workflow

run_analysis() is retained for compatibility with the historical StablePopulation 1.0.3 source-tree workflow. It expects the old workbook layout inside inst/extdata/Input_Data.xlsx, fertility in column B, and one fixed beta value in cell C2. New analyses should normally use run_reconstruction_excel().