Create 311 data CSV files that can be accessed through a Jupyter notebook

[akhaleghi]

Overview

We want to download 311 data and split by year, then month, so each is under 100MB and we can host tan append-only data warehouse on GitHub.

Action Items

• [x] Get cleaning rules from the 311-data repo and add a link to the rules to Resources below.
• [x] Get city data
• [x] Split by year, then by month
• [x] Outline what you did to clean the data in a comment below
• [x] Create Jupyter notebook to access the data and add notes explaining the cleaning rules
• [x] Create a website (ideally ghpages) that can display the jupyter notebook so that people don't have to know how to download and install one.

Resources/Instructions

Cleaning Rules: https://github.com/hackforla/data-science/blob/main/311-data/CSV_files/Docs/CleaningRules.txt City Data:: https://data.lacity.org/browse?q=311%20data%20%2C%202024&sortBy=relevance (Please update the filter for the year 2024 based on the requirements.) Website (ghpages): https://hackforla.github.io/311-data-jupyter-notebooks/lab (navigate to folder : 311_Data_CleaningScript)

[ExperimentsInHonesty]

https://www.google.com/search?q=jupyter+notebook+ghpages&oq=jupyter+notebook+ghpages&aqs=chrome..69i57j0i22i30j0i390i650l3j69i60.9524j0j15&sourceid=chrome&ie=UTF-8#ip=1

[ExperimentsInHonesty]

I made this repo for @chelseybeck to see if its feasable to use Jupyter Notebook with ghpages https://github.com/hackforla/jupyter-ghpages-test

I am going to create another repo for the 311 data to go into

[ExperimentsInHonesty]

https://discourse.jupyter.org/t/run-jupyter-notebooks-on-github-with-reporting-to-a-static-website/14982

[mru-hub]

Outline of Data Cleaning Steps Data Cleaning was essential to prepare the 311 service request data for analysis. The following steps were undertaken:

1. Removing Duplicates

• Action: Used data.drop_duplicates(inplace=True) to eliminate duplicate rows.

• Reason: Duplicates can lead to biased results in analysis and modeling by over-representing certain data points.

  2. Identifying Missing Values

  • Action: Checked for missing values using data.isnull().sum().
  • Reason: Knowing which columns have missing data is essential for deciding how to handle them, whether through imputation or deletion.

  3. Converting Date Columns

  • Action: Converted CreatedDate, UpdatedDate, ServiceDate, and ClosedDate to datetime format using pd.to_datetime().
  • Reason: Proper date formats are critical for any time-based analysis, such as trend analysis or date-based filtering.

  4. Analyzing Categorical Variables

  • Action: Analyzed the frequency of values in CD & CDMember, and NC & NCName.
  • Reason: This helps identify redundant columns or combined values, which can simplify the dataset and improve analysis accuracy.

  5. Dropping Unnecessary Columns

  • Action: Removed columns like SRNumber, MobileOS, and others using data.drop(columns=unnecessary_columns, inplace=True).
  • Reason: These columns were irrelevant or redundant/Unique identifier, and dropping them simplifies the dataset, improving processing efficiency.

  6. Standardizing Categorical Data

  • Action: Converted categorical text columns to lowercase using data[cat_columns] = data[cat_columns].apply(lambda x: x.str.lower()).
  • Reason: Standardization reduces inconsistencies and errors in data analysis, especially in text-based operations.

  7. Handling Missing Data

  • Action:
    • Dropped rows missing key geographical data.
    • Filled missing ServiceDate and ClosedDate based on Status and UpdatedDate.
  • Reason: Ensures critical data is complete and logical, particularly for location-based and time-based analysis.

  8. Cleaning Zipcode column

  • Action: Removed invalid entries from the ZipCode column.
  • Reason: Ensures only valid postal codes are used, which is essential for accurate location analysis.

9. Saving Cleaned Data

• Action: Saved the cleaned data into monthly CSV files, grouped by CreatedDate.
• Reason: Organizing the data by month makes it easier to perform time-series analysis and manage large datasets.

[salice]

@bonniewolfe: @mru-hub is asking for clarification on this issue. Do we have a github page already for Hack for LA? Should she create a new page or add her work here https://github.com/hackforla/311-data-jupyter-notebooks? Also she mentioned "We have one for our organization which is created by Bonnie. Also the project page in above URL has '311-data', so i think we have one project page for our repository too. If this is true I have to use the same URL for current ghpage purpose."

[ExperimentsInHonesty]

I answered this in the data science meeting on 2024-09-16. Basically, the repository is the work for this issue, but it needs updated data files.

[mru-hub]

Started working on ghpages. Website: https://hackforla.github.io/311-data-jupyter-notebooks/lab (navigate to folder: 311_Data_CleaningScript). I've made some initial updates to the script and will continue working on integrating it for the ghpages.