Create Python script to clean 2015 through 2019 data

[ryanfchase]

Overview

We need to clean the 2016 through 2019 data from the 311 Data Service Request APIs from the city so that we can access them through our Search and Filters modal.

Action Items

We want to know how to clean the data from 2015 to 2019 in order to make it consistent with our 2021-2024 data. To achieve this, complete the following:

• Review relevant code from our scripts folder (see R1)
• For each year (2015 through 2020), do the following...
  1. Download the dataset for that year and compare it to our 2024 data
  2. In a comment, answer the following questions:
     • q1: Does the dataset for this year have the same columns as the 2024 dataset? (can use check_column_count.py from R1)
     • q2: Does the dataset for this year contain any "problematic rows", e.g. rows that contain cells that might confuse our DuckDb interpretter (see inspect_csv.py from R1)
  3. Based on answers from q1 and q2, determine (if any) what steps should be taken to clean the dataset for that particular year
• If any significant differences or problem rows were identified in the previous step, write a Python script to fix those issues, creating a cleaned dataset as a byproduct
  • commit those scripts under a new folder: 311-data/scripts/clean-2015-through-2020
  • attend a 311 Data weekly general meeting or 311 Data weekly engineering meeting to review your work with the other devs

Resources/Instructions

R1: Relevant files and functions used in our build process, as well as tools used to determine where and how our datasets needed to be cleaned:

• Review 311-data/scripts/migrateOldHfDataset.py
  • Note: this script is very similar to 311-data/scripts/updateHfDataset.py, except that it allows you to pass in a year as an argument.
  • Review dlData(year): you'll notice we are drawing data from a personal repo, see resources to know where that data comes from
  • Review hfClean(year): this method has two parts:
    1. it opens a local file (e.g. 2024.csv), creates a new output file (e.g. 2024-fixed.csv), and does a string replacement on the input before writing to the output file
    2. it opens a connection to DuckDb, creates a temporary table with the data from our fixed csv, then converts it to Parquet
  • Skip hfUpload(year), this won't be needed for the purposes of this ticket
  • Skip process_data(...), this is our main control flow for the script, which is determined by command line arguments
• review 311-data/scripts/csv_debug_tools
  • review check_column_count.py, read documentation at the top of the file
  • review inspect_csv.py, read documentation at the top of the file

R2: Data Sources

• the 311 Data project's official HuggingFace repo listing
  • example: 311-data/2024
• Our previous dev lead kept a copy of each year's data as CSV. See: Edwin Huggingface repo listing
• Generally, data can be obtained from the LA City's open data portal. See: listing of 311 datasets for each year.
  • example: MyLA311 Service Request Data 2016 data preview

[ryanfchase]

This ticket is ready to be picked up