Research Request - NTD by RTPA report tweaks

[tiffanychu90]

Complete the below when receiving a research request, and continue to add to this issue as you receive additional details and produce deliverables. Be sure to also add the appropriate project-level label to this issue (eg gtfs-rt, DLA).

Research Question

Single sentence description: After the first batch of NTD data went out to RTPAs, we received feedback to make minor changes to how we send out the output.

Follow-up issue to #978

Detailed description:

1. Export as .xlsx instead of .csv with multiple tabs
   • Set up title page, and add the same title page to every RTPA. MTC's requests (but we can add more to info too) for title page here.
   • Work a data dictionary into title page. Mode_full, TOS_full is the unabbreviated version of Mode and TOS, so let's spell that out very clearly...already it has been missed. Don't get rid of Mode and TOS because those are original NTD columns.

From MTC:  Add a cover sheet as the first tab that explains what the document contains, and what can 
be found on subsequent sheets. (Be sure to name the sheets as they are named in the document.)

Best to spell out abbreviations on the first occurrence. (For example, TOS.)

Within each sheet, select all of the data, and “Format as Table.” This will enable better filtering and 
use for people who use assistive technology. It also helps blind users know where the data ends.
When Formatting as Table, be sure to properly set the Header and Column Rows.

Column headers should not have unnecessary abbreviations. For example, JN1 currently reads 
“change_1yr_1/2023”. This might be difficult to understand without context. Better to write 
“Change in one year since 2023” (or whatever it actually means; I have no idea). Similarly, KH1
 “mode_full” doesn’t make a lot of sense without context.

Add a Data Dictionary tab to explain what values for variables mean. For example, the variable 
Mode has values like CB and CC, which are not understandable to the public.

The index/cover sheet tab should indicate that the Data Dictionary sheet has more than one table
 on it.

File “Properties” should be added. Especially the Title.

• Multiple tabs for tables --> we will not be able to "format as table" programmatically. We are zipping up an entire folder of Excel spreadsheets, so unless we want to manually open it every time we overwrite and format as table, the RTPA should do it when they are opening up the dataset.
• We will add multiple tabs for additional aggregated tables...like aggregated to TOS or Mode or Agency.
• Rename columns (with spaces). Should this be renamed to YOY (year over year or month over month...whatever industry standard).
  • We programmatically generate our column names, and we want to maintain our programmatic column names through our export to parquet.
  • Apply a function to get readable column names to parse away underscores and do titlecase, spaces, parentheses, whatever need be. Use this output df in the export.
• File “Properties” should be added. Especially the Title. --> unless someone finds a programmatic way to attach the Excel properties, we're not going into every spreadsheet to do this.

2. Making changes to the script

3. Populate the YAML for portfolio script

4. Notebook that renders report

Notes

• Work in ntd folder in GH repo and GCS

[csuyat-dot]

Related PRs:

• april 2024 updates for feb 2024 rtpa ntd reports #1075
• feb 2024 NTD Monthly Ridership by RTPA #1090

Encountered errors during initial set up of monthly NTD ridership site scripts regarding:

• netlify set up
• changes in raw data column names from NTD
• missing folders/files for scripts
• notebook conflicts

Resolved issues by: setting up netlify in CLI, adjusted scripts to accept changes in column names, running individual scripts until notebooks were built and website deployed. Unfortunately was not able to resolve initial notebook conflicts so had to start a new branch and everything ran as expected (no errors)

[csuyat-dot]

in #1190, was able to:

• Change data export to .xlsx instead of .csv files
• Add a READ ME tab to each excel file to act as a "cover sheet/title page" that explain the purpose and description of the file, defines the NTD Modes, Type of Service, and defines all the columns names
• Added to the save_rtpa_outputs to rename, unsnakecase then title case columns names for excel files for easier readability.

Still need to work on adding multiple tabs for aggregated TOS, Mode and Agency. These calculations are done in the notebook

[csuyat-dot]

completed last feature request of adding multiple tabs in the excel output. see...

• Monthly NTD ridership, more tabs on data outputs #1197