/pears_monthly_data_cleaning

Primary LanguagePythonMIT LicenseMIT

PEARS Monthly Data Cleaning

The PEARS Monthly Data Cleaning script flags records based on guidance provided to PEARS users by the Illinois SNAP-Ed implementating agency. Users are notified via email how to update their flagged records.

PEARS guidance for Illinois Nutrition Education Programs (INEP) is located in the /il_pears_guidance directory. Illinois SNAP-Ed programming is delivered through three methods:

  1. Direct Education - which is tracked using the Program Activities module in PEARS.
  2. Indirect Education - which is tracked using the Indirect Activities module in PEARS.
  3. Policy, System, and Environment - which utilizes the Coalitions, Partnerships, PSE Site Activities, and Program Activities PEARS modules for reporting.

Installation

The recommended way to install the PEARS Monthly Data Cleaning script is through git, which can be downloaded here. Once downloaded, run the following command:

git clone https://github.com/jstadni2/pears_monthly_data_cleaning

Alternatively, this repository can be downloaded as a zip file via this link: https://github.com/jstadni2/pears_monthly_data_cleaning/zipball/master/

This repository is designed to run out of the box on a Windows PC using Docker and the /example_inputs and /example_outputs directories. To run the script in its current configuration, follow this link to install Docker Desktop for Windows.

With Docker Desktop installed, this script can be run simply by double clicking the run_script.bat file in your local directory.

The run_script.bat file can also be run in Command Prompt by entering the following command with the appropriate path:

C:\path\to\pears_monthly_data_cleaning\run_script.bat

Setup instructions for SNAP-Ed implementing agencies

The following steps are required to execute the PEARS Monthly Data Cleaning script using your organization's PEARS data:

  1. Contact PEARS support to set up an AWS S3 bucket to store automated PEARS exports.
  2. Download the automated PEARS exports. Illinois Extension's method for downloading exports from the S3 is detailed in the PEARS Nightly Export Reformatting script.
  3. Set the appropriate input and output paths in pears_monthly_data_cleaning.py and run_script.bat.
    • The Input Files and Output Files sections provide an overview of required and output data files.
    • Copying input files to the build context would enable continued use of Docker and run_script.bat with minimal modifications.
    • pears_monthly_data_cleaning.py may require additional alterations depending on the staff list format.
  4. Set the usename and password variables in pears_monthly_data_cleaning.py using valid Office 365 credentials.

Additional setup considerations

  • The formatting of PEARS export workbooks changes periodically. The example PEARS exports included in the /example_inputs directory are based on workbooks downloaded on 08/12/22. Modifications to pears_monthly_data_cleaning.py may be necessary to run with subsequent PEARS exports.
  • Illinois Extension utilized Task Scheduler to run this script from a Windows PC on a monthly basis.
  • Plans to deploy the PEARS Monthly Data Cleaning script on AWS were never implemented and are currently beyond the scope of this repository.
  • Other SNAP-Ed implementing agencies intending to utilize the PEARS Monthly Data Cleaning script should consider the following adjustments as they pertain to their organization:
    • If your organization actively maintains its SNAP-Ed staff list internally in PEARS, the User_Export.xlsx workbook could be used in lieu of external staff lists.
    • The conditions for update notifications were defined using Illinois SNAP-Ed PEARS guidance. Adjust as needed for your organization's specification.
    • The Illinois Baby Names csv is used to flag Coalition Members with individual's names in the member_name field. It may be possible to download a similar list for your state from the Social Security Administration website.
    • The send_mail() function in pears_monthly_data_cleaning.py is defined using Office 365 as the host. Change the host to the appropriate email service provider if necessary.
    • Update notification email templates include a cloud storage link to Illinois SNAP-Ed PEARS guidance or "cheat sheets." Lines 787 and 985 should be updated to applicable links for your organization.
    • The Illinois Extension Unit Counties workbook maps counties entered in each module's unit field to extension units. The unit value is used in lines 894-914 to append Regional Specialist contact info to update notification emails. This functionality is subject to your organization's structure and guidance for the unit field, and may be omitted from your implementation if necessary.

Input Files

The following input files are required to run the PEARS Monthly Data Cleaning script:

Example input files are provided in the /example_inputs directory. PEARS module exports included as example files are generated using the Faker Python package and do not represent actual program evaluation data.

Output Files

The following output files are produced by the PEARS Monthly Data Cleaning script:

Example output files are provided in the /example_outputs directory.