Home

This is the wiki page for mapping out the Data Quality scripts

Goals

• Compare two Excel files to determine if data was added, changed, or deleted
• Determine if an Excel file follows its predefined format
• Check if numbers are in line with older records

Dependencies

These should already be included with Anaconda. In case they aren't run these through terminal

• Pandas - conda install pandas or python -m pip install pandas
• Xlsxwriter - conda install xlsxwriter or python -m pip install xlsxwriter

What Things Do and How to Run

Be sure to change the directory to where the script is located using cd

cd Documents/GitHub/Data-Quality-Checker/

Excel Diff

• Credit to Matthew Kudija for the Source Code
• Highlights differences between two Excel files
• Exports file with highlighted differences

Running Excel Diff through Terminal: python diff.py

python diff.py

• A Excel file will export in the output folder under "[old] vs [new].xlsx"

Format Checker

• A Python script that accepts an Excel file
• Creates formats based on sample Excel files
• Checks given Excel file for:
  • New or Missing Field Names
  • Unexpected or missing Field Entries
  • Unexpected Units of measurement or New items
  • Number of Withheld rows
• Can replace specific commodities

Setup only needs to be run once per file type (e.g. Monthly Production, Federal Production CY), edit the json file when adding new things

Running FormatCheck through Terminal: python formatcheck.py

python formatcheck.py

• Setup is done to create a general layout of a file type. This will export a .json file which can be edited.
• Note: Export may be missing formatting
• Will export to output/[new]filename.xlsx

Number Checker

• Highlights numbers if they surpass a certain threshold based on grouping
• Grouping determines default thresholds
• Editable json files for thresholds

Running NumberChecker through Terminal:

python numberchecker.py

• Setup will ask the user for input on which columns to use for grouping.
• Type in the column names separated by commas and a single space (e.g. Commodity, Revenue Type)
• Creates a .json file with thresholds based on standard deviation, is editable.
• Will export to output/NumChecked-filename.xlsx