code/part_3_data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7845c882-c763-4d4b-b830-676682b48cfe",
   "metadata": {},
   "source": [
    "# ![](https://ga-dash.s3.amazonaws.com/production/assets/logo-9f88ae6c9c3871690e33280fcf557f33.png) Group Project by Dolphin Sharma and Charles Crocicchia\n",
    "\n",
    "## Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "edf9cf6e-c595-4e98-a258-461061530c25",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "35a3ae2e-961d-48d0-95d9-845c265ac342",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Paths for the raw and clean data directories\n",
    "corn_raw_path = r'../data/raw_data/corn_data/'\n",
    "soybean_raw_path = r'../data/raw_data/soybean_data/'\n",
    "economic_raw_path = r'../data/raw_data/economic_data/'\n",
    "\n",
    "corn_clean_path = r'../data/clean_data/corn_data/'\n",
    "soybean_clean_path = r'../data/clean_data/soybean_data/'\n",
    "economic_clean_path = r'../data/clean_data/economic_data/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "36de92aa-739f-4df1-b7db-e1a578aaeec3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the function to standardize state names\n",
    "def standardize_state_names(df):\n",
    "    # Standardize state names by stripping extra spaces and capitalizing properly\n",
    "    if \"State\" in df.columns:\n",
    "        df[\"State\"] = df[\"State\"].str.strip().str.title()\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "32bee180-9f43-4e84-a973-8401759a9712",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to standardize economic data\n",
    "def standardize_economic_data(file_path, output_path, is_poverty_file=False):\n",
    "    \"\"\"\n",
    "    Function to standardize economic data by renaming the column 'GeoName' to 'State'.\n",
    "    Additionally, it handles transformations for the poverty_levels file.\n",
    "    \"\"\"\n",
    "    df = pd.read_csv(file_path)\n",
    "    \n",
    "    if is_poverty_file:\n",
    "        df = df.drop(columns=[\"Total population\", \"Percent in poverty\"])\n",
    "        df = df.pivot(index=\"State\", columns=\"Year\", values=\"Number in poverty\").reset_index()\n",
    "    else:\n",
    "        if \"GeoName\" in df.columns:\n",
    "            df = df.rename(columns={\"GeoName\": \"State\"})\n",
    "    \n",
    "    df.to_csv(output_path, index=False)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "57eaaa00-f0b7-47ec-ba32-da5d2cbf6341",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to process and save standardized files\n",
    "def process_and_standardize_files(data_path, output_path, is_economic_data=False):\n",
    "    \"\"\"\n",
    "    Standardize state names for each CSV file and save to the cleaned data directory.\n",
    "    If it is economic data, standardize using the economic data function.\n",
    "    \"\"\"\n",
    "    files_to_standardize = [f for f in os.listdir(data_path) if not f.startswith('.')]  # Skip hidden files/folders\n",
    "    \n",
    "    for file in files_to_standardize:\n",
    "        file_path = os.path.join(data_path, file)\n",
    "        \n",
    "        # Load the CSV file\n",
    "        df = pd.read_csv(file_path)\n",
    "        \n",
    "        # Standardize the state names\n",
    "        df_standardized = standardize_state_names(df)\n",
    "        \n",
    "        # Special handling for economic data\n",
    "        if is_economic_data:\n",
    "            is_poverty_file = file == \"poverty_levels.csv\"\n",
    "            df_standardized = standardize_economic_data(file_path, output_path=os.path.join(output_path, file), is_poverty_file=is_poverty_file)\n",
    "        else:\n",
    "            # Save the standardized file\n",
    "            df_standardized.to_csv(os.path.join(output_path, file), index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "cc43aaa5-078f-4f64-bde3-ad4685fc9bbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Ensure the output directories exist\n",
    "os.makedirs(corn_clean_path, exist_ok=True)\n",
    "os.makedirs(soybean_clean_path, exist_ok=True)\n",
    "os.makedirs(economic_clean_path, exist_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "cf58cfd9-5c31-45fe-a280-e78b53bb5502",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data processing and standardization complete!\n"
     ]
    }
   ],
   "source": [
    "# Process and standardize the data files for corn, soybean, and economic data\n",
    "process_and_standardize_files(corn_raw_path, corn_clean_path)\n",
    "process_and_standardize_files(soybean_raw_path, soybean_clean_path)\n",
    "process_and_standardize_files(economic_raw_path, economic_clean_path, is_economic_data=True)\n",
    "\n",
    "print(\"Data processing and standardization complete!\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}