notebooks/generate_city_subs_raw.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import re\n",
    "import praw\n",
    "from datetime import datetime\n",
    "import pickle\n",
    "import datetime as dt\n",
    "from psaw import PushshiftAPI\n",
    "api = PushshiftAPI()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "PRAW_CLIENT_ID = os.getenv('PRAW_CLIENT_ID')\n",
    "PRAW_CLIENT_SECRET = os.getenv('PRAW_CLIENT_SECRET')\n",
    "PRAW_USER_AGENT = os.getenv('PRAW_USER_AGENT')\n",
    "\n",
    "reddit = praw.Reddit(client_id=PRAW_CLIENT_ID,\n",
    "                     client_secret=PRAW_CLIENT_SECRET,\n",
    "                     user_agent=PRAW_USER_AGENT)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://www.census.gov/geographies/reference-files/2016/demo/popest/2016-fips.html\n",
    "# convert from .xlsx to .csv in Excel/Google Sheets\n",
    "# Used to filter by geo level\n",
    "df_geo = pd.read_csv('../data/sources/all-geocodes-v2016.csv', skiprows=4)\n",
    "\n",
    "# https://www.census.gov/data/tables/time-series/demo/popest/2010s-total-cities-and-towns.html\n",
    "# Annual Estimates of the Resident Population for Incorporated Places of 50,000 or More, \n",
    "# Ranked by July 1, 2018 Population: April 1, 2010 to July 1, 2018 \n",
    "# https://factfinder.census.gov/bkmk/table/1.0/en/PEP/2018/PEPANNRSIP.US12A\n",
    "# Population\n",
    "df_cities_pop = pd.read_csv('../data/sources/cities_pop.csv', skiprows=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_geo.columns = [col.replace(' ', '_') for col in df_geo.columns]\n",
    "df_cities_pop.columns = [col.replace(' ', '_') for col in df_cities_pop.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cities_pop = df_cities_pop[['Target_Geo_Id2', 'Geography.2', 'April_1,_2010_-_Census', 'Population_Estimate_(as_of_July_1)_-_2018']]\n",
    "\n",
    "#Target_Geo_Id2: 2679000 first 2 digits indicate state, last 5 digits indicate city\n",
    "df_cities_pop = df_cities_pop.astype({'Target_Geo_Id2': str})\n",
    "df_cities_pop[['state_fip', 'city_fip']] = df_cities_pop['Target_Geo_Id2'].str.extract(r'(\\d{1,2})(\\d{5})$', expand=True)\n",
    "df_cities_pop = df_cities_pop.astype({'state_fip': int, 'city_fip': int})\n",
    "\n",
    "df_cities = df_geo[(df_geo['Summary_Level'] == 162) & (df_geo['Area_Name_(including_legal/statistical_area_description)'].str.contains(' city'))]\n",
    "df_cities = df_cities.rename({'Area_Name_(including_legal/statistical_area_description)': 'city'}, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# strip city ending ie \"Santa Cruz city\"\n",
    "regex_pat = re.compile(r' city$', flags=re.IGNORECASE)\n",
    "df_cities['city_short'] = df_cities['city'].str.replace(regex_pat, '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.merge(df_cities, df_cities_pop, left_on=['State_Code_(FIPS)', 'Place_Code_(FIPS)'], right_on=['state_fip', 'city_fip'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://www.reddit.com/dev/api/#POST_api_search_subreddits\n",
    "\n",
    "sub_city_dict = {}\n",
    "for index, row in df.iterrows():\n",
    "    city_short = row['city_short'].strip()\n",
    "    state_city_id = row['Target_Geo_Id2'].strip()\n",
    "    city_state = row['Geography.2']\n",
    "\n",
    "    sub = reddit.post('api/search_subreddits', data={'query': city_short})\n",
    "    if len(sub['subreddits']) > 0:\n",
    "        if sub['subreddits'][0]['subscriber_count'] > 1000:\n",
    "            sub_city_dict[state_city_id] = [\n",
    "                                            city_short,\n",
    "                                            city_state,\n",
    "                                            sub['subreddits'][0]['name'].strip(),\n",
    "                                            sub['subreddits'][0]['subscriber_count']\n",
    "                                           ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_city_subs = pd.DataFrame.from_dict(sub_city_dict, orient='index').reset_index()\n",
    "df_city_subs = df_city_subs.rename({'index': 'state_city_id', 0: 'city_short', 1: 'city_state', 2: 'city_sub', 3: 'sub_cnt'}, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the retrieved sub-reddits may not be valid so manually check later"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle.dump(df_city_subs, open('../data/df_city_subs_raw.pkl', 'wb'))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "metis",
   "language": "python",
   "name": "metis"
  },
  "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}