03_ApplyLocationFilter.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "machine_shape": "hm"
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "weBxwDTlyv06"
      },
      "outputs": [],
      "source": [
        "import requests\n",
        "import csv\n",
        "from functools import reduce\n",
        "import pandas as pd\n",
        "import time\n",
        "import numpy as np"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "place_geoid = pd.read_csv(\"../us-place-over100pop.csv\")\n",
        "state_abbr = pd.read_csv(\"../stateabbr.csv\")"
      ],
      "metadata": {
        "id": "DNO0N65i0uJX"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "base = [place_geoid, state_abbr] # Merging state abbreviation\n",
        "placecode = reduce(lambda left, right: pd.merge(left, right, on=['state'], how='left'), base)\n",
        "\n",
        "# Creating city name\n",
        "placecode['cityname'] = placecode['NAME'].str.split(',').str[0]\n",
        "placecode[\"cityname\"] = placecode[\"cityname\"].str.rsplit(' ', n=1).str[0]\n",
        "\n",
        "# Remove characters after '/' or '-'\n",
        "placecode[\"cityname\"] = placecode[\"cityname\"].str.replace(r'[-/].*', '', regex=True)\n",
        "placecode[\"cityname\"] = placecode[\"cityname\"].str.lower()\n",
        "\n",
        "# Replace a specific city name\n",
        "placecode[\"cityname\"] = placecode[\"cityname\"].replace('san buenaventura (ventura)', 'ventura')\n",
        "placecode[\"cityname\"] = placecode[\"cityname\"].replace('urban honolulu', 'honolulu')\n",
        "\n",
        "# Remove where cell value includes \"CDP\"\n",
        "placecode = placecode[~placecode[\"cityname\"].str.contains('cdp')]\n",
        "\n",
        "# Subset the DataFrame to include only rows where the Address contains \"St.\"\n",
        "sub_placecode_st = placecode[placecode['cityname'].str.contains(\"st. \")]\n",
        "sub_placecode_st['cityname'] = sub_placecode_st['cityname'].str.replace(\"st.\", \"saint\")\n",
        "\n",
        " # Subset the DataFrame to include only rows where the cityname has an \"apostrophes\"\n",
        "sub_placecode_ap = placecode[placecode['cityname'].str.contains(\"['']\")]\n",
        "sub_placecode_ap['cityname'] = sub_placecode_ap['cityname'].str.replace(\"'\", \"\", regex=False)\n",
        "\n",
        "# Subset the DataFrame to include only rows where the cityname ends with \"city\"\n",
        "sub_placecode_city = placecode[placecode['cityname'].str.endswith(\" city\")]\n",
        "sub_placecode_city['cityname'] = sub_placecode_city['cityname'].str.replace(\" city\", \"\", regex=False)\n",
        "\n",
        "# Subset the DataFrame to include only rows where the cityname ends with \"town\"\n",
        "sub_placecode_town = placecode[placecode['cityname'].str.endswith(\" town\")]\n",
        "sub_placecode_town['cityname'] = sub_placecode_town['cityname'].str.replace(\" town\", \"\", regex=False)\n",
        "\n",
        "# Subset the DataFrame to include only rows where the cityname ends with \"village\"\n",
        "sub_placecode_village = placecode[placecode['cityname'].str.endswith(\" village$\")]\n",
        "sub_placecode_village['cityname'] = sub_placecode_village['cityname'].str.replace(\" village\", \"\", regex=False)\n"
      ],
      "metadata": {
        "id": "8FCWbDIH0zhu"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "concatenated_df = pd.concat([placecode, sub_placecode_st, sub_placecode_ap, sub_placecode_city, sub_placecode_town, sub_placecode_village])\n",
        "\n",
        "# Reset the index of the concatenated dataframe and drop the old index\n",
        "concatenated_df.reset_index(drop=True, inplace=True)\n",
        "\n",
        "# Make a copy of the concatenated dataframe and store it in the variable placecode\n",
        "placecode = concatenated_df.copy()\n",
        "\n",
        "# Remove characters after '/' or '-' in the 'cityname' column using regular expressions\n",
        "placecode[\"cityname\"] = placecode[\"cityname\"].str.replace(r'[(].*', '', regex=True)\n",
        "\n",
        "# Sort the dataframe by the values in the 'GEO_ID' column in ascending order\n",
        "placecode = placecode.sort_values(by='GEO_ID')\n",
        "\n",
        "# Reset the index of the sorted dataframe and drop the old index\n",
        "placecode.reset_index(drop=True, inplace=True)"
      ],
      "metadata": {
        "id": "ESB5MYtT03MY"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 01 Generating Location Filters (Dictionaries Matching Fips/GEOID)"
      ],
      "metadata": {
        "id": "_ndqoEVi1AvJ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import numpy as np\n",
        "import pandas as pd\n",
        "from collections import defaultdict\n",
        "\n",
        "def create_city_filter(code):\n",
        "    # Creating keys\n",
        "    code[\"key1\"] = code[\"cityname\"] + \" \" + code[\"statename\"]\n",
        "    code[\"key2\"] = code[\"cityname\"] + \" \" + code[\"state_abbr\"]\n",
        "    code[\"key3\"] = code[\"cityname\"] + \", \" + code[\"statename\"]\n",
        "    code[\"key4\"] = code[\"cityname\"] + \", \" + code[\"state_abbr\"]\n",
        "    code[\"key5\"] = code[\"cityname\"] + \"  \" + code[\"statename\"]\n",
        "    code[\"key6\"] = code[\"cityname\"] + \"  \" + code[\"state_abbr\"]\n",
        "    code[\"key7\"] = code[\"cityname\"] + \" , \" + code[\"statename\"]\n",
        "    code[\"key8\"] = code[\"cityname\"] + \" , \" + code[\"state_abbr\"]\n",
        "    code[\"key9\"] = code[\"cityname\"]\n",
        "    code[\"key10\"] = code[\"cityname\"]\n",
        "    code[\"key11\"] = code[\"cityname\"]\n",
        "\n",
        "    # Replace a \"cityname\" with NaN where population is less than 30000 only for key9 (dict09) - Find the exact match\n",
        "    code.loc[code['Population'] <= 30000, 'key9'] = np.nan\n",
        "    # Replace a \"cityname\" with NaN where population is less than 150,000 only for key10 (dict10) - Find substring\n",
        "    code.loc[code['Population'] <= 150000, 'key10'] = np.nan\n",
        "    # This is a column to detect the cities that share their names\n",
        "    code.loc[code['Population'] <= 15000, 'key11'] = np.nan\n",
        "\n",
        "    # Convert all keys to lowercase\n",
        "    cols = [\"key1\", \"key2\", \"key3\", \"key4\", \"key5\", \"key6\", \"key7\", \"key8\", \"key9\", \"key10\", \"key11\"]\n",
        "    for col in cols:\n",
        "        code[col] = code[col].str.lower()\n",
        "\n",
        "    '''\n",
        "    creating three lists of the cities to exclude from searching tweets only with the city names\n",
        "    '''\n",
        "\n",
        "    # List 01. For cities with population greater than 15K, creating a dictionary like 'Fitchburg': ['Fitchburg city, Massachusetts', 'Fitchburg city, Wisconsin']\n",
        "    dup_dict = defaultdict(list)\n",
        "    for index, row in code.iterrows():\n",
        "        dup_dict[row['key11']].append(row['NAME'])\n",
        "\n",
        "    # List 02. Cities in other countries\n",
        "    dup_list =  list(dup_dict.values())\n",
        "\n",
        "    other_exc = [\"Columbia city, Missouri\", \"Vancouver city, Washington\", \"Melbourne city, Florida\",\n",
        "                  \"Amsterdam village, Ohio\", \"Amsterdam city, Missouri\", \"Hollywood city, Florida\",\n",
        "                  \"Bristol city, Connecticut\", \"New Britain city, Connecticut\", \"Cicero town, Illinois\",\n",
        "                  \"Edinburg city, Texas\", \"Carmel city, Indiana\"]\n",
        "\n",
        "    # List 03. CDPs\n",
        "    cdp_lst = []\n",
        "    for index, row in code.iterrows():\n",
        "        # Check if the \"NAME\" column's substring contains \"CDP\"\n",
        "        if 'CDP' in row['NAME']:\n",
        "            # If it does, add the corresponding value from the \"cityname\" column to the list\n",
        "            cdp_lst.append(row['cityname'])\n",
        "\n",
        "    # Combine three lists and create the list of cities to exclude\n",
        "    exc_list = dup_list + other_exc + cdp_lst\n",
        "\n",
        "    # Replace values in key9 with NaN if NAME includes any of the items in the dup_list using numpy.where()\n",
        "    code['key9'] = np.where(code['NAME'].isin(exc_list), float('NaN'), code['key9']) # find the exact match just using a city's name\n",
        "    code['key10'] = np.where(code['NAME'].isin(exc_list), float('NaN'), code['key10']) # find the exact match just using a city's name\n",
        "\n",
        "    # Creating dictionaries (filters to apply)\n",
        "    dict01 = dict(zip(code.key1, code.GEO_ID))\n",
        "    dict02 = dict(zip(code.key2, code.GEO_ID))\n",
        "    dict03 = dict(zip(code.key3, code.GEO_ID))\n",
        "    dict04 = dict(zip(code.key4, code.GEO_ID))\n",
        "    dict05 = dict(zip(code.key5, code.GEO_ID))\n",
        "    dict06 = dict(zip(code.key6, code.GEO_ID))\n",
        "    dict07 = dict(zip(code.key7, code.GEO_ID))\n",
        "    dict08 = dict(zip(code.key8, code.GEO_ID))\n",
        "\n",
        "    # Creating the dictionary from GEO_ID and key9 (only with city names) but excluding rows where key9 is missing\n",
        "    dict09 = {row['key9']: row['GEO_ID'] for index, row in code.dropna(subset=['key9']).iterrows()}\n",
        "    dict10 = {row['key10']: row['GEO_ID'] for index, row in code.dropna(subset=['key10']).iterrows()}\n",
        "\n",
        "    return dict01, dict02, dict03, dict04, dict05, dict06, dict07, dict08, dict09, dict10, code\n",
        "\n",
        "# City Filters\n",
        "dict01, dict02, dict03, dict04, dict05, dict06, dict07, dict08, dict09, dict10, code = create_city_filter(placecode)"
      ],
      "metadata": {
        "id": "iVou5Z2NmsKB"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "code.to_csv(\"../location_filter.csv\", index=False)"
      ],
      "metadata": {
        "id": "gV9ngWWrLHj4"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def create_state_filter(state_abbr):\n",
        "    # Important: Descending order to fix the West Virginia issue\n",
        "    state_abbr = state_abbr.sort_values(by=['state'], ascending=False, ignore_index=True)\n",
        "\n",
        "    # Convert the 'Value' column to a string column\n",
        "    state_abbr['state'] = state_abbr['state'].apply(lambda x: f'{x:02}')\n",
        "\n",
        "    # Convert it to lowercase\n",
        "    state_abbr[\"statename\"] = state_abbr[\"statename\"].str.lower()\n",
        "\n",
        "    # Creating the dictionary from statename to state\n",
        "    state_names = dict(zip(state_abbr.statename, state_abbr.state))\n",
        "\n",
        "    return state_names\n",
        "\n",
        "# State Filters\n",
        "dc_othernames = {'washington dc': '11', 'washington, d.c.': '11', 'washington d.c.': '11'}\n",
        "state_names = create_state_filter(state_abbr)\n",
        "\n",
        "dc_othernames.update(state_names)\n",
        "dict_state = dc_othernames\n",
        "\n",
        "# The US filter\n",
        "dict_usa = {\"united states\": \"00\", \"usa\": \"00\", \"us\": \"00\", \"united states of america\": \"00\"}"
      ],
      "metadata": {
        "id": "yYAKX9jY3v4G"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 02. Location Fiter Functions (Both Substring & Exact Strings)"
      ],
      "metadata": {
        "id": "8C5NCvPH6Lyj"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Define a function named 'match_substring_geoid'\n",
        "def match_substring_geoid(df, filterdict):\n",
        "\n",
        "    # Create a boolean mask that checks if the 'GEO_ID' column in the DataFrame 'df' has null values.\n",
        "    mask = df['GEO_ID'].isnull()\n",
        "\n",
        "    # Iterate through the DataFrame rows where 'GEO_ID' is null.\n",
        "    for i, user_loc in df.loc[mask, 'user_loc'].items():\n",
        "        # Try to find a value in the 'filterdict' dictionary that is a substring of 'user_loc'.\n",
        "        # If found, assign the matching value to 'location_found'.\n",
        "        location_found = next((filterdict[location] for location in filterdict.keys() if isinstance(location, str) and location in user_loc), None)\n",
        "\n",
        "        # Check if a value (GEO_ID) was found in 'location_found'.\n",
        "        if location_found:\n",
        "\n",
        "            # Update the 'GEO_ID' column at the current row 'i' with the found value.\n",
        "            df.at[i, 'GEO_ID'] = location_found\n",
        "\n",
        "    # Calculate the count of missing values in the 'GEO_ID' column.\n",
        "    missing_count = df[\"GEO_ID\"].isna().sum()\n",
        "\n",
        "    # Return the modified DataFrame 'df' and the count of missing values.\n",
        "    return df, missing_count"
      ],
      "metadata": {
        "id": "TeQIS_ME3zoM"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Define a function named 'match_exact_string_geoidid'\n",
        "def match_exact_string_geoidid(df, filterdict):\n",
        "\n",
        "    # Create a boolean mask that checks if the 'GEO_ID' column in the DataFrame 'df' has null values.\n",
        "    mask = df['GEO_ID'].isnull()\n",
        "\n",
        "    # Iterate through the DataFrame rows where 'GEO_ID' is null.\n",
        "    for i, user_loc in df.loc[mask, 'user_loc'].items():\n",
        "\n",
        "        # Check if 'user_loc' exists as a key in the 'filterdict' dictionary.\n",
        "        if user_loc in filterdict:\n",
        "\n",
        "            # Update the 'GEO_ID' column at the current row 'i' with the value (state FIPS) from 'filterdict'.\n",
        "            df.at[i, 'GEO_ID'] = filterdict[user_loc]\n",
        "\n",
        "    # Calculate the count of missing values in the 'GEO_ID' column.\n",
        "    missing_count = df['GEO_ID'].isna().sum()\n",
        "\n",
        "    # Return the modified DataFrame 'df' and the count of missing values.\n",
        "    return df, missing_count"
      ],
      "metadata": {
        "id": "pLb5Ta9O6Oxh"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Define a function named 'match_substring_geoid'\n",
        "def match_substring_stateid(df, filterdict):\n",
        "\n",
        "    # Create a boolean mask that checks if the 'state' column in the DataFrame 'df' has null values.\n",
        "    mask = df['state'].isnull()\n",
        "\n",
        "    # Iterate through the DataFrame rows where 'state' is null.\n",
        "    for i, user_loc in df.loc[mask, 'user_loc'].items():\n",
        "        # Try to find a value in the 'filterdict' dictionary that is a substring of 'user_loc'.\n",
        "        # If found, assign the matching value to 'location_found'.\n",
        "        location_found = next((filterdict[location] for location in filterdict.keys() if isinstance(location, str) and location in user_loc), None)\n",
        "\n",
        "        # Check if a value (state) was found in 'location_found'.\n",
        "        if location_found:\n",
        "\n",
        "            # Update the 'state' column at the current row 'i' with the found value.\n",
        "            df.at[i, 'state'] = location_found\n",
        "\n",
        "    # Calculate the count of missing values in the 'state' column.\n",
        "    missing_count = df[\"state\"].isna().sum()\n",
        "\n",
        "    # Return the modified DataFrame 'df' and the count of missing values.\n",
        "    return df, missing_count"
      ],
      "metadata": {
        "id": "QFpSuna86QfK"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Define a function named 'match_exact_string_stateid'\n",
        "def match_exact_string_stateid(df, filterdict):\n",
        "\n",
        "    # Create a boolean mask that checks if the 'state' column in the DataFrame 'df' has null values.\n",
        "    mask = df['state'].isnull()\n",
        "\n",
        "    # Iterate through the DataFrame rows where 'state' is null.\n",
        "    for i, user_loc in df.loc[mask, 'user_loc'].items():\n",
        "\n",
        "        # Check if 'user_loc' exists as a key in the 'filterdict' dictionary.\n",
        "        if user_loc in filterdict:\n",
        "\n",
        "            # Update the 'state' column at the current row 'i' with the value (state FIPS) from 'filterdict'.\n",
        "            df.at[i, 'state'] = filterdict[user_loc]\n",
        "\n",
        "    # Calculate the count of missing values in the 'state' column.\n",
        "    missing_count = df[\"state\"].isna().sum()\n",
        "\n",
        "    # Return the modified DataFrame 'df' and the count of missing values.\n",
        "    return df, missing_count"
      ],
      "metadata": {
        "id": "8bVnE16s6R3N"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 03. Implementation: Import Data (Each Year)"
      ],
      "metadata": {
        "id": "EZU6wJFS6WnQ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "year = \"2013\"\n",
        "\n",
        "file_path = f\"../SolarSentiment_{year}_cleaned.csv\"\n",
        "df_tweet = pd.read_csv(file_path, lineterminator='\\n')\n",
        "\n",
        "df_tweet['GEO_ID'] = np.nan # city-level census GEO-ID\n",
        "df_tweet[\"state\"] = np.nan # state FIPS\n",
        "df_tweet = df_tweet.astype({'GEO_ID': 'string', \"state\": 'string'})\n",
        "df_tweet.user_loc = df_tweet.user_loc.astype(str)\n",
        "df_tweet.user_loc = df_tweet.user_loc.str.lower()\n",
        "\n",
        "missing_raw = len(df_tweet['GEO_ID'].isnull())\n",
        "print(f\"Number of Tweets without GEO_ID: {missing_raw}\")"
      ],
      "metadata": {
        "id": "y_pjYKGENbPb"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def remove_tweets_international_includes(df, places_to_remove):\n",
        "    # Create a boolean mask to identify rows where the \"user_loc\" column exactly matches any of the strings to be removed\n",
        "    mask = df[\"user_loc\"].isin(places_to_remove)\n",
        "\n",
        "    # Remove rows with the specified strings from the DataFrame\n",
        "    df = df[~mask]\n",
        "\n",
        "    return df\n",
        "\n",
        "def remove_tweets_countries_matched(df, countries_to_remove):\n",
        "    # Combine the countries into a single regular expression pattern\n",
        "    pattern = '|'.join(rf'\\b{country}\\b' for country in countries_to_remove)\n",
        "\n",
        "    # Use the pattern to filter the DataFrame\n",
        "    df = df[~df['user_loc'].str.contains(pattern, case=False)]\n",
        "\n",
        "    return df\n",
        "\n",
        "#remove_tweets_international_includes\n",
        "places_to_remove = [\"melbourne, australia\", \"cambridge, uk\", \"alberta, canada\", \"calgary, canada\", \"toronto, ontario\", \"midhurst, ontario\", \"buenos aires\",\n",
        "                     \"budapest, hungary\", \"kitchener, ontario\", \"dover, kent\", \"bristol, england\", \"midhurst, ontario\", \"bristol, uk\", \"bristol uk\", \"ontario, canada\"]\n",
        "df_tweet = remove_tweets_international_includes(df_tweet, places_to_remove)\n",
        "\n",
        "# remove_tweets_countries_matched\n",
        "countries_to_remove = [\" india\", \" australia\", \" new zealand\", \" united kingdom\", \" ireland\", \", alberta\", \"alberta, \",\n",
        "                       \", ontario\", \", columbia\", \" ghana\", \" france\", \" spain\", \" russia\", \" lebanon\", \", greece\",\n",
        "                       \", egypt\", \", guatemala\", \", england\", \", canada\", \", hungary\", \"yorkshire\", \"lancshire\"]\n",
        "df_tweet = remove_tweets_countries_matched(df_tweet, places_to_remove)"
      ],
      "metadata": {
        "id": "kTj_tGt87Z9_"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 04. Process Data Using the Location Filters"
      ],
      "metadata": {
        "id": "Dar_7AWk71nP"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 01. Applying `dict01` (e.g., denver colorado) - find the exact match"
      ],
      "metadata": {
        "id": "Fix0IcLq75zc"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Start timing\n",
        "start_time = time.time()\n",
        "df_tweet_up01, missing_count01 = match_exact_string_geoidid(df_tweet, dict01)\n",
        "newly_annot01 = missing_raw - missing_count01\n",
        "\n",
        "# Calculate the total execution time for the whole cell\n",
        "process_duration = (time.time() - start_time) / 60\n",
        "\n",
        "print(f\"Newly annotated GEO_ID using dict01 in {year}: {newly_annot01}\")\n",
        "print(f\"Total execution time: {process_duration:.2f} minutes\")"
      ],
      "metadata": {
        "id": "SYOaf6k671A7"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 02 Applying `dict02` (e.g., denver co) - find the exact match"
      ],
      "metadata": {
        "id": "JTNqBzeM781N"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Start timing\n",
        "start_time = time.time()\n",
        "df_tweet_up02, missing_count02 = match_exact_string_geoidid(df_tweet_up01, dict02)\n",
        "newly_annot02 = missing_count01 - missing_count02\n",
        "\n",
        "# Calculate the total execution time for the whole cell\n",
        "process_duration = (time.time() - start_time) / 60\n",
        "\n",
        "print(f\"Newly annotated GEO_ID using dict02 in {year}: {newly_annot02}\")\n",
        "print(f\"Total execution time: {process_duration:.2f} minutes\")"
      ],
      "metadata": {
        "id": "jcKpG-UM77Kc"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 03 Applying `dict 03` (e.g., denver, colorado) - find the exact match"
      ],
      "metadata": {
        "id": "DdMfW29T8DMw"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Start timing\n",
        "start_time = time.time()\n",
        "df_tweet_up03, missing_count03 = match_exact_string_geoidid(df_tweet_up02, dict03)\n",
        "newly_annot03 = missing_count02 - missing_count03\n",
        "\n",
        "# Calculate the total execution time for the whole cell\n",
        "process_duration = (time.time() - start_time) / 60\n",
        "\n",
        "print(f\"Newly annotated GEO_ID using dict03 in {year}: {newly_annot03}\")\n",
        "print(f\"Total execution time: {process_duration:.2f} minutes\")"
      ],
      "metadata": {
        "id": "qBWy2DBv7-ls"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 04 Applying `dict 04` (e.g., denver, co) - find the exact match"
      ],
      "metadata": {
        "id": "gUmGU3HH8Hja"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Start timing\n",
        "start_time = time.time()\n",
        "df_tweet_up04, missing_count04 = match_exact_string_geoidid(df_tweet_up03, dict04)\n",
        "newly_annot04 = missing_count03 - missing_count04\n",
        "\n",
        "# Calculate the total execution time for the whole cell\n",
        "process_duration = (time.time() - start_time) / 60\n",
        "\n",
        "print(f\"Newly annotated GEO_ID using dict04 in {year}: {newly_annot04}\")\n",
        "print(f\"Total execution time: {process_duration:.2f} minutes\")"
      ],
      "metadata": {
        "id": "MrVKkaUy8F0n"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 05. Applying `dict05` (e.g., denver  colorado) - find the exact match - (double spaced)"
      ],
      "metadata": {
        "id": "OHea66rZCT-Y"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Start timing\n",
        "start_time = time.time()\n",
        "df_tweet_up05, missing_count05 = match_exact_string_geoidid(df_tweet_up04, dict05)\n",
        "newly_annot05 = missing_count04 - missing_count05\n",
        "\n",
        "# Calculate the total execution time for the whole cell\n",
        "process_duration = (time.time() - start_time) / 60\n",
        "\n",
        "print(f\"Newly annotated GEO_ID using dict05 in {year}: {newly_annot05}\")\n",
        "print(f\"Total execution time: {process_duration:.2f} minutes\")"
      ],
      "metadata": {
        "id": "H_szXrJ0C6mh"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 06 Applying `dict06` (e.g., denver  co) - find the exact match - (double spaced)"
      ],
      "metadata": {
        "id": "kAPwo0w0CdlM"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Start timing\n",
        "start_time = time.time()\n",
        "df_tweet_up06, missing_count06 = match_exact_string_geoidid(df_tweet_up05, dict06)\n",
        "newly_annot06 = missing_count05 - missing_count06\n",
        "\n",
        "# Calculate the total execution time for the whole cell\n",
        "process_duration = (time.time() - start_time) / 60\n",
        "\n",
        "print(f\"Newly annotated GEO_ID using dict06 in {year}: {newly_annot06}\")\n",
        "print(f\"Total execution time: {process_duration:.2f} minutes\")"
      ],
      "metadata": {
        "id": "HLgNGA_kDFqj"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 07 Applying `dict 07` (e.g., denver , colorado) - find the exact match - (double spaced)"
      ],
      "metadata": {
        "id": "YasGo44CCdcX"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Start timing\n",
        "start_time = time.time()\n",
        "df_tweet_up07, missing_count07 = match_exact_string_geoidid(df_tweet_up06, dict07)\n",
        "newly_annot07 = missing_count06 - missing_count07\n",
        "\n",
        "# Calculate the total execution time for the whole cell\n",
        "process_duration = (time.time() - start_time) / 60\n",
        "\n",
        "print(f\"Newly annotated GEO_ID using dict07 in {year}: {newly_annot07}\")\n",
        "print(f\"Total execution time: {process_duration:.2f} minutes\")"
      ],
      "metadata": {
        "id": "sML4F3aDDQuH"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 08 Applying `dict 08` (e.g., denver , co) - find the exact match - (double spaced)"
      ],
      "metadata": {
        "id": "JrbMuIdBCz6G"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Start timing\n",
        "start_time = time.time()\n",
        "df_tweet_up08, missing_count08 = match_exact_string_geoidid(df_tweet_up07, dict08)\n",
        "newly_annot08 = missing_count07 - missing_count08\n",
        "\n",
        "# Calculate the total execution time for the whole cell\n",
        "process_duration = (time.time() - start_time) / 60\n",
        "\n",
        "print(f\"Newly annotated GEO_ID using dict08 in {year}: {newly_annot08}\")\n",
        "print(f\"Total execution time: {process_duration:.2f} minutes\")"
      ],
      "metadata": {
        "id": "M1YMp6OrDWMF"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 09 Applying `dict 09` (e.g., denver) - find the exact match"
      ],
      "metadata": {
        "id": "12JYXr7g8pGQ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Start timing\n",
        "start_time = time.time()\n",
        "df_tweet_up09, missing_count09 = match_exact_string_geoidid(df_tweet_up08, dict09)\n",
        "newly_annot09 = missing_count08 - missing_count09\n",
        "\n",
        "# Calculate the total execution time for the whole cell\n",
        "process_duration = (time.time() - start_time) / 60\n",
        "\n",
        "print(f\"Newly annotated GEO_ID using dict09 in {year}: {newly_annot09}\")\n",
        "print(f\"Total execution time: {process_duration:.2f} minutes\")"
      ],
      "metadata": {
        "id": "s3L7O9Jc8I6Q"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 10 Applying `dict 10` (e.g., denver) - find substrings"
      ],
      "metadata": {
        "id": "ITsbu0Q2yazC"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Start timing\n",
        "start_time = time.time()\n",
        "df_tweet_up10, missing_count10 = match_substring_geoid(df_tweet_up09, dict10)\n",
        "newly_annot10 = missing_count09 - missing_count10\n",
        "\n",
        "# Calculate the total execution time for the whole cell\n",
        "process_duration = (time.time() - start_time) / 60\n",
        "\n",
        "print(f\"Newly annotated GEO_ID using dict10 (names of the cities), matching substrings in {year}: {newly_annot10}\")\n",
        "print(f\"Total execution time: {process_duration:.2f} minutes\")"
      ],
      "metadata": {
        "id": "8gkWY8qQyc_Z"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 11 Applying `dict_state` (e.g., north carolina) - find the exact match"
      ],
      "metadata": {
        "id": "VRlTyEM29JX7"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Start timing\n",
        "start_time = time.time()\n",
        "df_tweet_up11, missing_count11 = match_exact_string_stateid(df_tweet_up10, dict_state)\n",
        "newly_annot11 = missing_raw - missing_count11\n",
        "\n",
        "# Calculate the total execution time for the whole cell\n",
        "process_duration = (time.time() - start_time) / 60\n",
        "\n",
        "print(f\"Newly annotated State FIPS using dict_state in {year}: {newly_annot11}\")\n",
        "print(f\"Total execution time: {process_duration:.2f} minutes\")"
      ],
      "metadata": {
        "id": "ffo1XMY08rzC"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 12 Applying `dict_state` (e.g., north carolina) - find substrings"
      ],
      "metadata": {
        "id": "4DExD5zE9VxI"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Start timing\n",
        "start_time = time.time()\n",
        "df_tweet_up12, missing_count12 = match_substring_stateid(df_tweet_up11, dict_state)\n",
        "newly_annot12 = missing_count11 - missing_count12\n",
        "\n",
        "# Calculate the total execution time for the whole cell\n",
        "process_duration = (time.time() - start_time) / 60\n",
        "\n",
        "print(f\"Newly annotated State FIPS using dict_state, matching substrings in {year}: {newly_annot12}\")\n",
        "print(f\"Total execution time: {process_duration:.2f} minutes\")"
      ],
      "metadata": {
        "id": "mh-NWaTH9LOH"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 13 Applying `dict_usa` (e.g., united states) - find the exact match"
      ],
      "metadata": {
        "id": "EDqHbwe39in2"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Start timing\n",
        "start_time = time.time()\n",
        "df_tweet_up13, missing_count13 = match_exact_string_stateid(df_tweet_up12, dict_usa)\n",
        "newly_annot13 = missing_count12 - missing_count13\n",
        "\n",
        "# Calculate the total execution time for the whole cell\n",
        "process_duration = (time.time() - start_time) / 60\n",
        "\n",
        "print(f\"Newly annotated State FIPS using dict_usa in {year}: {newly_annot13}\")\n",
        "print(f\"Total execution time: {process_duration:.2f} minutes\")"
      ],
      "metadata": {
        "id": "HlzpHoVT9XIp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 05. Save"
      ],
      "metadata": {
        "id": "J2Hv0jZzzNDP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import re\n",
        "\n",
        "def clean_subset_dataframe(df):\n",
        "    # Remove rows where either 'GEO_ID' or 'state' column has a value\n",
        "    df = df[df['GEO_ID'].notna() | df['state'].notna()]\n",
        "\n",
        "    # Extract State Fips column (two digits following \"US\" in GEO_ID)\n",
        "    df.loc[:, \"STATE_FIPS\"] = df[\"GEO_ID\"].str[9:11]\n",
        "\n",
        "    # Replace STATE_FIPS with state ID where it is NaN\n",
        "    df.loc[df[\"STATE_FIPS\"].isna(), \"STATE_FIPS\"] = df['state']\n",
        "\n",
        "    # Drop the 'state' column\n",
        "    df = df.drop(\"state\", axis=1)\n",
        "\n",
        "    # Replace all words starting with \"@\" in the 'text' column with \"[NAME]\"\n",
        "    df['text'] = df['text'].str.replace(r'@\\w+', '[NAME]', regex=True)\n",
        "\n",
        "    # Remove URLs from the 'text' column\n",
        "    df['text'] = df['text'].str.replace(r'\\s*http://\\S+(\\s+|$)', '', regex=True).str.strip()\n",
        "\n",
        "    # Replace all phone numbers in the 'text' column with \"[PHONE]\"\n",
        "    df['text'] = df['text'].str.replace(r'(\\d{3})[-.\\s]?(\\d{3})[-.\\s]?(\\d{4})', '[PHONE]', regex=True)\n",
        "\n",
        "    # Get tweet text length\n",
        "    df[\"text_length\"] = df.text.str.len()\n",
        "\n",
        "    # Remove meaningless tweets (text length > 5)\n",
        "    df = df[df[\"text_length\"] > 5]\n",
        "\n",
        "    # Drop the 'text_length' column\n",
        "    df = df.drop(\"text_length\", axis=1)\n",
        "\n",
        "    df_pred = df[[\"tweet_id\", \"text\"]]\n",
        "\n",
        "    df_left = df[['tweet_id', 'created_at', 'user_id', 'user_loc', 'like_count',\n",
        "       'retweet_count', 'referenced_type', 'referenced_id',\n",
        "       'referenced_tweet', 'GEO_ID', 'STATE_FIPS']]\n",
        "\n",
        "    return df, df_pred, df_left\n",
        "\n",
        "df, df_pred, df_left = clean_subset_dataframe(df_tweet_up13)"
      ],
      "metadata": {
        "id": "7zbN5uVK9kEn"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df_left.to_csv(\"../\" + year + \".csv\", index=False)\n",
        "\n",
        "df_pred.to_csv(\"../\" + year + \".csv\", index=False)"
      ],
      "metadata": {
        "id": "NOje1fyazIX6"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}