• http://dx.doi.org/10.18235/0013861

Naranjo, Sandra, et al. (2025). Sample Code for: Creating Public Payroll Indicators:  A Methodological Guide. IDB Open Data. https://doi.org/10.60966/6x55ayx4

1. Spanish

What is this? Is it a data file (e.g., a CSV)?

This is a methodological toolkit, not a static dataset. It is a comprehensive R project containing a series of scripts. These scripts are designed to ingest raw public payroll data, clean it, standardize it, and generate a final set of key performance indicators (KPIs) for analysis.

Who is the target audience for this toolkit?

** This project is designed for two primary audiences: * Data Analysts: R-proficient analysts in government, academia, or civil society who can execute the scripts to process raw payroll data and explore the methodology. * Policymakers: Officials who need to understand the types of indicators that can be generated (like wage bill trends, turnover, and pay gaps) to inform public sector management and reform.

What do I need to run this project?

** You will need: 1. R and RStudio. 2. The R libraries listed in the 00_global.R script (e.g., tidyverse, data.table, fixest). 3. Raw payroll data files. The scripts are configured to look for raw .csv files (e.g., country_planta.csv, country_contrata.csv, country_honorarios.csv) as their starting input.

What payroll key performance indicators (KPIs) can I calculate using this toolkit?

** The scripts are structured to generate a wide range of indicators. Key categories include: * Wage Bill & Pay: Wage bill growth, average pay, pay compression (e.g., top 10% vs. bottom 10%), and pay composition (e.g., base pay vs. bonuses). * Employment: Headcount growth and total employment figures, calculated using Full-Time Equivalent (FTE) logic. * Turnover & Mobility: Employee turnover (hires and leavers) for the public sector as a whole and within specific organizations. * Promotions: Career progression, including rates of promotion and promotions into executive/director-level positions. * Pay Equity: Detailed gender pay gap analysis.

How does this toolkit help me calculate the gender pay gap?

** The toolkit provides a robust, multi-step approach: 1. Gender Assignment: It first cleans and assigns a gender to each employee record based on first names using a dedicated dictionary (genero_comprimido). 2. Raw (Unadjusted) Gap: The main indicator script (11_crear_indicadores.R) calculates the raw pay gap. It does this by dividing the average male salary by the average female salary for a given group (e.g., by organization or rank). 3. Adjusted (Modeled) Gap: For a more advanced analysis, a separate script (12_limpiar_indicadores.R) uses regression modeling (feols). It calculates the "adjusted" gap by measuring the effect of gender on pay after controlling for other key factors, such as rank (grado), region, and contract type (contract_type_dummy).

How is the project structured?

** The project follows a clear, sequential pipeline: * Scripts 00-02: Set up the global environment, convert raw .csv data, and combine files. * Scripts 03-07: Perform deep cleaning and standardization on all columns, including organizations, gender, job positions, and unique employee IDs. * Scripts 08-09: Engineer key indicator variables, specifically for promotions and turnover. * Scripts 10-12: Finalize the dataset, calculate all indicators using a summary function, and model specific indicators like the adjusted gender pay gap.

How do the scripts define "turnover"?

** Turnover is calculated in 09_limpiar_rotacion.R. A "hire" (id_appear) is identified as the first time a unique employee ID appears in the data, or if there is a gap of more than one year in their record. A "leaver" (id_disappear) is identified as the last month an employee ID appears, provided it is not the end of the dataset or a gap of more than 1 year follows. The scripts calculate this both for the entire public sector and for individual organizations.

What is the generally accepted definition of the gender pay gap, and how is it measured?

This dataset does not provide a textual definition of the gender pay gap. However, it gives two distinct R scripts for its measurement: 1. Unadjusted Gap: The 11_crear_indicadores.R script calculates a raw, unadjusted gap by comparing the average pay of men and women (e.g., value[genero == 'hombre']/value[genero == 'mujer'] - 1). 2. Adjusted Gap: The 12_limpiar_indicadores.R script calculates an adjusted gap by running a regression model (feols) to measure the effect of gender on pay after controlling for other factors like employee rank (grado), region, and contract type.

Can you clarify the distinction between the 'unadjusted' and 'adjusted' gender pay gap figures?

** Yes. This toolkit provides the methodology to calculate both: * An 'unadjusted' (or raw) gap is calculated in the 11_crear_indicadores.R script. This is a direct comparison of the average pay for men and women (e.g., fmean(pago_bruto[genero == 'hombre'])/fmean(pago_bruto[genero == 'mujer'])) without accounting for any other factors. * An 'adjusted' gap is calculated in the 12_limpiar_indicadores.R script. This method uses a regression model (feols) to isolate the effect of gender on pay while statistically controlling for other variables that influence salary, such as employee rank (grado), region, and contract type (contract_type_dummy).

What are the core performance metrics for payroll departments?

** This toolkit is designed to generate a final, clean database (country_full_final) from which several core performance metrics can be derived. The 11_crear_indicadores.R script shows the methodology for calculating KPIs in categories such as: * Wage Bill & Cost: Total payroll expenditure, payroll as a share of GDP, and payroll as a share of government expenditure. * Employment: Total number of employees (headcount) and Full-Time Equivalents (FTEs). * Pay Equity & Compression: Gender pay gaps (both adjusted and unadjusted) and pay compression ratios (e.g., average pay of the top 10% vs. the bottom 10%). * Turnover & Mobility: Annual hire rates and leaver rates, calculated for the entire public sector and at the individual organization level. * Career Progression: Employee promotion rates, including promotions into director-level roles. * Payroll Operations: Number of multiple payments per employee per month (calculated in 10_limpiar_final.R).