This R script processes monthly Consumer Price Index (CPI) data for Pacific Island Countries and Territories (PICTs) and generates a standardised CPI dataset suitable for dissemination through an SDMX-based statistical data platform.
The script:
- Reads monthly CPI data from an Excel workbook.
- Reshapes CPI commodity data from wide to long format.
- Converts CPI observations to numeric values.
- Generates monthly, quarterly, and annual CPI datasets.
- Calculates monthly, quarterly, and annual inflation rates.
- Applies data completeness rules when calculating quarterly and annual averages.
- Adds official names of national statistical offices and statistical agencies.
- Standardises the output according to the required SDMX data structure.
- Writes the final combined dataset to a timestamped CSV file.
The script processes CPI data for the following Pacific Island Countries and Territories:
| GEO_PICT | Statistical Office / Data Provider |
|---|---|
| AS | Data and Statistics American Samoa Department of Commerce |
| CK | Cook Islands Statistics Office |
| FJ | Fiji Bureau of Statistics |
| FM | FSM Statistics |
| GU | The Bureau of Statistics and Plans - Guam |
| KI | Kiribati National Statistics Office |
| MH | Marshall Islands Economic Policy, Planning and Statistics Office (EPPSO) |
| MP | CNMI Department of Commerce |
| NC | Institut de la Statistique et des Etudes Economiques |
| NR | Nauru Bureau of Statistics |
| NU | Niue Statistics Office |
| PF | Institut de la statistique de la Polynésie française |
| PG | PNG National Statistics Office |
| PN | Pitcairn Statistics Office |
| PW | Palau Statistics Office |
| SB | Solomon Islands National Statistics Office |
| TK | Tokelau Statistics Office |
| TO | Tonga Statistics Department |
| TV | Tuvalu Statistics Office |
| VU | Vanuatu Bureau of Statistics Office |
| WF | Wallis and Futuna Statistics Office |
| WS | Samoa Bureau of Statistics |
The script reads CPI data from an Excel workbook using the readxl package.
The input workbook is expected to contain a worksheet named:
cpi_data
The expected data structure contains SDMX-related dimensions and attributes, including:
DATAFLOWFREQGEO_PICTINDICATORTIME_PERIODUNIT_MEASUREUNIT_MULTOBS_STATUSBASE_PEROBS_COMMENT
Commodity columns are stored in wide format and are converted to long format, with:
COMMODITYrepresenting the CPI commodity or classification.OBS_VALUErepresenting the CPI index observation.
The input file is currently referenced using a local OneDrive path and the Windows username obtained from the system environment.
The script uses the following R packages:
library(dplyr)
library(tidyr)
library(lubridate)
library(readr)
library(readxl)| Package | Purpose |
|---|---|
dplyr |
Data manipulation, grouping, summarisation and transformation |
tidyr |
Reshaping CPI data from wide to long format |
lubridate |
Extracting year and quarter information from dates |
readr |
Data import/export functionality |
readxl |
Reading CPI data from Excel workbooks |
The source CPI data is initially stored in wide format, where individual CPI commodities are represented as columns.
The script converts these commodity columns into long format using pivot_longer().
Each observation is represented by a combination of:
GEO_PICT
COMMODITY
TIME_PERIOD
OBS_VALUE
The monthly CPI index is assigned:
FREQ = M
INDICATOR = IDX
The script also creates a date variable from the TIME_PERIOD field to support subsequent annual and quarterly calculations.
Monthly inflation is calculated using the percentage change in the CPI index compared with the previous month.
The formula is:
Monthly Inflation (%) =
((CPI_t / CPI_(t-1)) - 1) × 100
where:
CPI_t= CPI index for the current month.CPI_(t-1)= CPI index for the previous month.
Monthly inflation is calculated separately for each:
GEO_PICT
COMMODITY
The first available observation for each country and commodity is excluded because there is no previous month available for calculating the percentage change.
The generated monthly inflation dataset uses:
INDICATOR = INF
UNIT_MEASURE = PERCENT
OBS_STATUS = E
Inflation rates are rounded to one decimal place.
Quarterly CPI indexes are calculated from monthly CPI indexes.
The calculation is performed separately for each:
DATAFLOW
GEO_PICT
COMMODITY
BASE_PER
UNIT_MEASURE
UNIT_MULT
A quarterly average is only calculated when all three months in the quarter are available.
Therefore:
Quarterly CPI = Mean of 3 monthly CPI observations
For example:
2024-Q1 = Mean(January 2024, February 2024, March 2024)
If fewer than three monthly observations are available, the quarterly average is not generated.
The resulting quarterly dataset uses:
FREQ = Q
INDICATOR = IDX
Quarterly CPI values are rounded to one decimal place.
Quarterly inflation is calculated from the quarterly average CPI indexes.
The formula is:
Quarterly Inflation (%) =
((CPI_Qt / CPI_Q(t-1)) - 1) × 100
where:
CPI_Qt= current quarterly average CPI.CPI_Q(t-1)= previous quarterly average CPI.
The calculation is performed separately for each country and commodity.
The resulting dataset uses:
FREQ = Q
INDICATOR = INF
UNIT_MEASURE = PERCENT
OBS_STATUS = E
Annual CPI indexes are calculated from the quarterly average CPI indexes.
The calculation is based on the four quarterly averages within each calendar year.
An annual CPI average is generated only when all four quarters are available.
Therefore:
Annual CPI = Mean(Q1, Q2, Q3, Q4)
For example:
2024 Annual CPI =
Mean(2024-Q1, 2024-Q2, 2024-Q3, 2024-Q4)
Because each quarterly average is itself calculated only when three monthly observations are available, the annual CPI calculation effectively requires:
4 complete quarters
12 complete months
This ensures that annual CPI averages are based on a complete calendar year.
The resulting annual dataset uses:
FREQ = A
INDICATOR = IDX
Annual inflation is calculated from annual average CPI indexes.
The formula is:
Annual Inflation (%) =
((Annual CPI_t / Annual CPI_(t-1)) - 1) × 100
This represents the percentage change in the annual average CPI compared with the previous year's annual average CPI.
The resulting dataset uses:
FREQ = A
INDICATOR = INF
UNIT_MEASURE = PERCENT
OBS_STATUS = E
The script generates six types of observations:
| Frequency | Indicator | Description |
|---|---|---|
Monthly (M) |
IDX |
Monthly CPI index |
Monthly (M) |
INF |
Month-on-month inflation rate |
Quarterly (Q) |
IDX |
Quarterly average CPI index |
Quarterly (Q) |
INF |
Quarter-on-quarter inflation rate |
Annual (A) |
IDX |
Annual average CPI index |
Annual (A) |
INF |
Year-on-year inflation rate based on annual average CPI |
The following completeness rules are applied:
Monthly observations are retained when a valid numeric OBS_VALUE is available.
Inflation is calculated only when a previous monthly CPI observation exists.
A quarterly average is calculated only when:
Number of available months = 3
An annual average is calculated only when:
Number of available quarters = 4
Since each quarter requires three months, this means that annual averages are based on a complete set of 12 monthly observations.
Annual inflation requires two consecutive annual CPI averages.
All generated datasets are combined into a single dataframe using bind_rows().
The final output contains the following standardised fields:
| Variable | Description |
|---|---|
DATAFLOW |
SDMX dataflow identifier |
FREQ |
Observation frequency: monthly, quarterly or annual |
GEO_PICT |
PICT geographic code |
INDICATOR |
CPI index (IDX) or inflation (INF) |
COMMODITY |
CPI commodity or classification |
TIME_PERIOD |
Observation period |
OBS_VALUE |
CPI index or inflation rate |
UNIT_MEASURE |
Unit of measurement |
UNIT_MULT |
Unit multiplier |
OBS_STATUS |
Observation status |
BASE_PER |
CPI base period |
OBS_COMMENT |
Observation source and methodology comment |
The script adds descriptive comments to generated observations.
Examples include:
Monthly consumer price indexes sourced from [Statistical Office]
Average Monthly inflation calculated from average monthly indexes sourced from [Statistical Office]
Quarterly average indexes calculated from average monthly indexes sourced from [Statistical Office]
Quarterly average inflation rate calculated from calculated average quarterly indexes sourced from [Statistical Office]
Annual average indexes calculated from calculated average quarterly indexes sourced from [Statistical Office]
Annual average inflation rate calculated from calculated annual average indexes sourced from [Statistical Office]
The relevant statistical office is appended to the observation comment based on the GEO_PICT code.
The final dataset is exported as a CSV file named:
DF_CPI-data.CSV
The file is saved in a timestamped directory using the following naming convention:
YYYYMMDD_HHMMSS_CPI_data_Update
For example:
20260723_083015_CPI_data_Update
This approach creates a unique output directory for each processing run and provides a clear record of when the CPI dataset was generated.
The resulting directory structure is:
DF_CPI/
└── YYYYMMDD_HHMMSS_CPI_data_Update/
└── DF_CPI-data.CSV
The overall processing workflow can be summarised as:
Excel CPI Data
│
▼
Read Monthly CPI Data
│
▼
Convert Wide Data to Long Format
│
▼
Convert CPI Values to Numeric
│
▼
Generate Monthly CPI Index
│
├───────────────┐
▼ ▼
Monthly Inflation Quarterly CPI
│
▼
Quarterly Inflation
│
▼
Annual Average CPI
│
▼
Annual Inflation
│
▼
Combine All Data
│
▼
Add Statistical Office
│
▼
Standardise SDMX Fields
│
▼
Export CSV Dataset
The script applies several basic data quality controls:
- CPI values are converted to numeric format.
- Missing CPI observations are excluded from the final output.
- Quarterly averages require three monthly observations.
- Annual averages require four quarterly observations.
- Inflation calculations require a preceding observation.
- CPI and inflation values are rounded to one decimal place.
- Statistical office names are mapped using the
GEO_PICTcode. - Output variables are arranged according to the expected SDMX data structure.
Quarterly CPI averages are calculated from monthly CPI indexes, while annual CPI averages are calculated from the quarterly CPI averages.
Therefore, the aggregation hierarchy is:
Monthly CPI
↓
Quarterly Average CPI
↓
Annual Average CPI
Annual CPI is therefore calculated only when four complete quarterly averages are available. This ensures that the annual average is based on a complete calendar year of CPI observations.
Inflation rates are then calculated from the corresponding frequency-specific CPI averages:
Monthly CPI → Monthly Inflation
Quarterly CPI → Quarterly Inflation
Annual CPI → Annual Inflation
The script currently uses a local Windows OneDrive directory and retrieves the Windows username using:
username <- Sys.getenv("USERNAME")The input and output paths therefore depend on the local user's OneDrive folder structure.
For improved portability and reproducibility, it is recommended that future versions of the script use configurable file paths, command-line arguments, environment variables, or project-relative paths rather than hard-coded local directories.
This script is intended for processing and standardising CPI data for Pacific Island Countries and Territories within an SDMX statistical data dissemination workflow.
The script should be updated when:
- New PICTs are added.
- Statistical office names change.
- CPI commodity classifications change.
- The SDMX data structure changes.
- Input Excel file structures change.
- CPI aggregation or inflation methodology is revised.