ACTS Data Aggregation Tool

Overview

The ACTS Data Aggregation Tool is a Python application that processes institutional data from ACTS Excel templates and generates aggregated reports for submission. This tool validates your data, performs complex aggregations, and outputs two files per academic year for submission: an Aggregate Data CSV file and an Institution Review report Excel file. The Institution Review report is meant to provide you with an overview to review and verify the data looks as you would expect.

Quick Start

Prerequisites

• Python: Version 3.12
• pip: Python package installer (usually included with Python)

Installation

1. Clone or download this repository

2. Navigate to the project directory:

   cd acts-aggregator

3. Create a virtual environment (recommended):

   python -m venv .venv

4. Activate the virtual environment:

   • macOS/Linux:

     source .venv/bin/activate

   • Windows:

     .venv\Scripts\activate

5. Install dependencies:

   pip install -r requirements.txt

Quick Test with Example Data

To test the application with the provided example data:

python run_acts.py --unitid 123456 --year 2019-20 --input "example_data/example_2019_20.xlsx" --output-folder "output" --non-interactive

This will process the example Excel file and generate results in the output directory.

Running the Application

1. Prepare your ACTS Excel template file
2. Run the application:

Interactive Mode (Recommended for First-Time Users)

python run_acts.py

The application will prompt you for:

• Your institution's IPEDS UNITID
• Academic year (e.g., 2019-20, 2022-23)
• Path to your ACTS Excel template file [example_data/example_2019_20.xlsx]
• Confirmation to continue after validation checks

Command-Line Mode (For Advanced Users)

python run_acts.py --unitid 123456 --year 20NN-NN --input "path/to/template.xlsx" --output-folder "output" --non-interactive

Input Requirements

ACTS Excel Template

Your Excel file must contain:

• Required sheets: All expected worksheets for your academic year
• Proper formatting: Column headers and data types as specified in ACTS guidelines
• Complete data: All required fields populated according to ACTS specifications

Example Data

An example Excel template is provided in the example_data/ directory:

• example_2019_20.xlsx: Sample ACTS template file for academic year 2019-20
• Use UNITID 666666 when testing with this example file

Command-Line Options

Option	Description	Example
--unitid	Your institution's IPEDS UNITID	--unitid 123456
--year	Academic year in YYYY-YY format	--year 2022-23
--input	Path to your ACTS Excel template	--input "data/template.xlsx"
--output-folder	Output folder	--output-folder "output"
--non-interactive	Run without prompts	--non-interactive

Processing Steps

The application performs the following steps automatically:

1. 📋 Schema Validation - Verifies your Excel file structure and format
2. 🔍 Business Rules Check - Validates data consistency and logic rules
3. ⚙️ Data Transformation - Prepares data for aggregation
4. 📊 Aggregation - Computes institutional-level metrics
5. 📈 Quantile Calculation - Generates statistical bounds
6. 🔄 Data Merging - Combines all aggregated results
7. ✨ Final Processing - Applies formatting and presentation rules

Output

The application generates:

• Primary Output: A CSV file with aggregated institutional metrics
• Institutional Review Report: An excel sheet that contains an overview of the calculations to ensure the counts look as you expect given the input data.
• Validation Reports: JSON files with validation results
  • business.json - Business rules validation summary
  • review_file.json - Schema validation details

Error Handling

Validation Errors

If your data fails validation, the application will:

• Display specific error messages
• Generate detailed reports in JSON format
• Allow you to review and decide whether to continue

Common Issues and Solutions

Issue	Solution
"Input file not found"	Verify the file path is correct and file exists
Schema validation failures	Check your Excel template matches ACTS requirements
Business rule violations	Review the business.json file for specific violations
Missing required sheets	Ensure all required worksheets are present

System Requirements

• Python: Version 3.12, though other versions may work
• Operating System: Windows, macOS, or Linux
• Memory: Minimum 4GB RAM (8GB+ recommended for large datasets)
• Storage: At least 1GB free space for processing
• Permissions: Read access to input files, write access to output directory

Dependencies

This application requires several Python packages as listed in requirements.txt:

• pandas: Data manipulation and analysis
• numpy: Numerical computing
• openpyxl: Excel file reading and writing
• click: Command-line interface creation
• jsonschema: Data validation
• boto3: AWS SDK (if cloud features are used)
• And other supporting libraries

All dependencies can be installed using:

pip install -r requirements.txt

Troubleshooting

Performance Issues

• Large datasets: Processing may take several minutes for large institutions
• Memory usage: Monitor system memory during processing
• Progress: Check console output for processing status

Data Issues

• Missing data: Review ACTS template requirements
• Format errors: Ensure proper Excel formatting
• Encoding problems: Save Excel files in standard format

Getting Help

1. Check the console output for specific error messages
2. Review generated JSON reports for detailed validation information
3. Verify your input data matches ACTS template specifications

Advanced Usage

Batch Processing Multiple Files

# Process multiple years
python run_acts.py --unitid 123456 --year 2021-22 --input "data/2021-22.xlsx" --output "output/2021-22.csv" --non-interactive
python run_acts.py --unitid 123456 --year 2022-23 --input "data/2022-23.xlsx" --output "output/2022-23.csv" --non-interactive

Scripting Example (Bash)

#!/bin/bash
UNITID="123456"

# Activate virtual environment
source .venv/bin/activate

for year in 2020-21 2021-22 2022-23; do
    echo "Processing $year..."
    python run_acts.py --unitid $UNITID --year $year --input "data/template_$year.xlsx" --output "output/results_$year.csv" --non-interactive
done

Scripting Example (Windows Batch)

@echo off
set UNITID=123456

:: Activate virtual environment
call .venv\Scripts\activate

for %%y in (2020-21 2021-22 2022-23) do (
    echo Processing %%y...
    python run_acts.py --unitid %UNITID% --year %%y --input "data\template_%%y.xlsx" --output "output\results_%%y.csv" --non-interactive
)

Version Information

This Python application uses a modular architecture for optimal maintainability and flexibility. Python is required to run this application.