Rapid Python analysis to Google Sheets for reporters EXAMPLE.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 292,
   "id": "0a4fafa9",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i globals.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c538edb",
   "metadata": {},
   "source": [
    "<h2>Rapid Python analysis to Google Sheets for reporters</h2>\n",
    "<h3>Example using Texas Census data</h3>\n",
    "<p>In this example, we will be doing simple analysis on the fastest growing counties in Texas, according to U.S. Census data. The goal is to find the following:</p>\n",
    "\n",
    "<ul>\n",
    "    <li>Find how much the state of Texas has grown over the last decade</li>\n",
    "    <li>The county that has grown the most in percentage and in raw number</li>\n",
    "    <li>How many counties have lost population</li>\n",
    "    <li>The top 10 fastest growing counties in Texas (with a population of 10,000 residents)</li>\n",
    "\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 293,
   "id": "b11910ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Variables\n",
    "\n",
    "filename1 = 'raw/ACSDT5Y2016.B01003-Data.csv'\n",
    "\n",
    "filename2 = 'raw/ACSDT5Y2021.B01003-Data.csv'\n",
    "\n",
    "outputfile = 'output/top_10_county_Texas_change'\n",
    "\n",
    "outputfile_agg = 'output/aggregate_file'\n",
    "\n",
    "## Google Sheet Name\n",
    "\n",
    "google_sheet_name = \"Texas Census analysis (2016 5-year estimate) and (2021 5-year estimate)\"\n",
    "\n",
    "## Google Sheet Directory ID\n",
    "\n",
    "google_folderID = 'TKTKTKTKTK'\n",
    "\n",
    "\n",
    "## Google Sheet url for the template\n",
    "\n",
    "source_gsheet_url = 'https://docs.google.com/spreadsheets/d/TKTKTKTKTKTKTK/'\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3fad515",
   "metadata": {},
   "source": [
    "<h3>Analysis of data</h3>\n",
    "<h4>Merge the data</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 294,
   "id": "5a741a34",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_csv(filename1, skiprows=1, dtype={'Estimate!!Total': int})\n",
    "\n",
    "df2 = pd.read_csv(filename2, skiprows=1, dtype={'Estimate!!Total': int})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 295,
   "id": "a55716fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge = pd.merge(df1, df2, how=\"outer\", on=[\"Geography\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 296,
   "id": "9673c3d1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Geography</th>\n",
       "      <th>Geographic Area Name_x</th>\n",
       "      <th>Estimate!!Total_x</th>\n",
       "      <th>Annotation of Estimate!!Total_x</th>\n",
       "      <th>Margin of Error!!Total_x</th>\n",
       "      <th>Annotation of Margin of Error!!Total_x</th>\n",
       "      <th>Geographic Area Name_y</th>\n",
       "      <th>Estimate!!Total_y</th>\n",
       "      <th>Annotation of Estimate!!Total_y</th>\n",
       "      <th>Margin of Error!!Total_y</th>\n",
       "      <th>Annotation of Margin of Error!!Total_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0500000US48001</td>\n",
       "      <td>Anderson County, Texas</td>\n",
       "      <td>57772</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*****</td>\n",
       "      <td>*****</td>\n",
       "      <td>Anderson County, Texas</td>\n",
       "      <td>58133</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*****</td>\n",
       "      <td>*****</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0500000US48003</td>\n",
       "      <td>Andrews County, Texas</td>\n",
       "      <td>17215</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*****</td>\n",
       "      <td>*****</td>\n",
       "      <td>Andrews County, Texas</td>\n",
       "      <td>18184</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*****</td>\n",
       "      <td>*****</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0500000US48005</td>\n",
       "      <td>Angelina County, Texas</td>\n",
       "      <td>87657</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*****</td>\n",
       "      <td>*****</td>\n",
       "      <td>Angelina County, Texas</td>\n",
       "      <td>86584</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*****</td>\n",
       "      <td>*****</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0500000US48007</td>\n",
       "      <td>Aransas County, Texas</td>\n",
       "      <td>24729</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*****</td>\n",
       "      <td>*****</td>\n",
       "      <td>Aransas County, Texas</td>\n",
       "      <td>24149</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*****</td>\n",
       "      <td>*****</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0500000US48009</td>\n",
       "      <td>Archer County, Texas</td>\n",
       "      <td>8750</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*****</td>\n",
       "      <td>*****</td>\n",
       "      <td>Archer County, Texas</td>\n",
       "      <td>8616</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*****</td>\n",
       "      <td>*****</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Geography  Geographic Area Name_x  Estimate!!Total_x  \\\n",
       "0  0500000US48001  Anderson County, Texas              57772   \n",
       "1  0500000US48003   Andrews County, Texas              17215   \n",
       "2  0500000US48005  Angelina County, Texas              87657   \n",
       "3  0500000US48007   Aransas County, Texas              24729   \n",
       "4  0500000US48009    Archer County, Texas               8750   \n",
       "\n",
       "   Annotation of Estimate!!Total_x Margin of Error!!Total_x  \\\n",
       "0                              NaN                    *****   \n",
       "1                              NaN                    *****   \n",
       "2                              NaN                    *****   \n",
       "3                              NaN                    *****   \n",
       "4                              NaN                    *****   \n",
       "\n",
       "  Annotation of Margin of Error!!Total_x  Geographic Area Name_y  \\\n",
       "0                                  *****  Anderson County, Texas   \n",
       "1                                  *****   Andrews County, Texas   \n",
       "2                                  *****  Angelina County, Texas   \n",
       "3                                  *****   Aransas County, Texas   \n",
       "4                                  *****    Archer County, Texas   \n",
       "\n",
       "   Estimate!!Total_y  Annotation of Estimate!!Total_y  \\\n",
       "0              58133                              NaN   \n",
       "1              18184                              NaN   \n",
       "2              86584                              NaN   \n",
       "3              24149                              NaN   \n",
       "4               8616                              NaN   \n",
       "\n",
       "  Margin of Error!!Total_y Annotation of Margin of Error!!Total_y  \n",
       "0                    *****                                  *****  \n",
       "1                    *****                                  *****  \n",
       "2                    *****                                  *****  \n",
       "3                    *****                                  *****  \n",
       "4                    *****                                  *****  "
      ]
     },
     "execution_count": 296,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42acba2e",
   "metadata": {},
   "source": [
    "<h4>Find population changes, in raw number and percentages </h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 297,
   "id": "28876655",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge['pop_change'] = df_merge['Estimate!!Total_y'] - df_merge['Estimate!!Total_x']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 298,
   "id": "a6dbddc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge['pop_change_pct'] = ((df_merge['Estimate!!Total_y'] - df_merge['Estimate!!Total_x'])/df_merge['Estimate!!Total_x']) * 100\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24c99098",
   "metadata": {},
   "source": [
    "<h4>Take out unnecessary columns, clean up column names and county names</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 299,
   "id": "37e23111",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge_short = df_merge[['Geographic Area Name_x', 'Estimate!!Total_x', 'Estimate!!Total_y', 'pop_change', 'pop_change_pct']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 300,
   "id": "8f37944c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Geographic Area Name_x</th>\n",
       "      <th>Estimate!!Total_x</th>\n",
       "      <th>Estimate!!Total_y</th>\n",
       "      <th>pop_change</th>\n",
       "      <th>pop_change_pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anderson County, Texas</td>\n",
       "      <td>57772</td>\n",
       "      <td>58133</td>\n",
       "      <td>361</td>\n",
       "      <td>0.624870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andrews County, Texas</td>\n",
       "      <td>17215</td>\n",
       "      <td>18184</td>\n",
       "      <td>969</td>\n",
       "      <td>5.628812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Angelina County, Texas</td>\n",
       "      <td>87657</td>\n",
       "      <td>86584</td>\n",
       "      <td>-1073</td>\n",
       "      <td>-1.224089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Aransas County, Texas</td>\n",
       "      <td>24729</td>\n",
       "      <td>24149</td>\n",
       "      <td>-580</td>\n",
       "      <td>-2.345424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Archer County, Texas</td>\n",
       "      <td>8750</td>\n",
       "      <td>8616</td>\n",
       "      <td>-134</td>\n",
       "      <td>-1.531429</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Geographic Area Name_x  Estimate!!Total_x  Estimate!!Total_y  pop_change  \\\n",
       "0  Anderson County, Texas              57772              58133         361   \n",
       "1   Andrews County, Texas              17215              18184         969   \n",
       "2  Angelina County, Texas              87657              86584       -1073   \n",
       "3   Aransas County, Texas              24729              24149        -580   \n",
       "4    Archer County, Texas               8750               8616        -134   \n",
       "\n",
       "   pop_change_pct  \n",
       "0        0.624870  \n",
       "1        5.628812  \n",
       "2       -1.224089  \n",
       "3       -2.345424  \n",
       "4       -1.531429  "
      ]
     },
     "execution_count": 300,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge_short.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 301,
   "id": "dd5b0a7c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/carlaastudillo/.local/share/virtualenvs/nicar-2023-rapid-python-google-sheets-3FyDBX37/lib/python3.7/site-packages/pandas/core/frame.py:5047: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  errors=errors,\n",
      "/Users/carlaastudillo/.local/share/virtualenvs/nicar-2023-rapid-python-google-sheets-3FyDBX37/lib/python3.7/site-packages/ipykernel_launcher.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    }
   ],
   "source": [
    "df_merge_short = df_merge[['Geographic Area Name_x', 'Estimate!!Total_x', 'Estimate!!Total_y', 'pop_change', 'pop_change_pct']]\n",
    "df_merge_short.rename(columns={'Geographic Area Name_x': 'county', 'Estimate!!Total_x': 'pop_2016', 'Estimate!!Total_y': 'pop_2021'}, inplace=True)\n",
    "df_merge_short['county'] = df_merge_short['county'].str.replace(\" County, Texas\", \"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3836e0d5",
   "metadata": {},
   "source": [
    "<h4>Take out counties with less than 10,000 residents and find the top 10 fastest growing counties</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 302,
   "id": "c6a413ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "no_small_counties = df_merge_short[df_merge_short['pop_2021'] > 10000].sort_values(by=['pop_change_pct'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 303,
   "id": "56cab5b0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>county</th>\n",
       "      <th>pop_2016</th>\n",
       "      <th>pop_2021</th>\n",
       "      <th>pop_change</th>\n",
       "      <th>pop_change_pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>Hays</td>\n",
       "      <td>185686</td>\n",
       "      <td>234573</td>\n",
       "      <td>48887</td>\n",
       "      <td>26.327779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Comal</td>\n",
       "      <td>124234</td>\n",
       "      <td>156257</td>\n",
       "      <td>32023</td>\n",
       "      <td>25.776358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>Kaufman</td>\n",
       "      <td>111830</td>\n",
       "      <td>140145</td>\n",
       "      <td>28315</td>\n",
       "      <td>25.319682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Bastrop</td>\n",
       "      <td>78286</td>\n",
       "      <td>94887</td>\n",
       "      <td>16601</td>\n",
       "      <td>21.205580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>Williamson</td>\n",
       "      <td>490619</td>\n",
       "      <td>591759</td>\n",
       "      <td>101140</td>\n",
       "      <td>20.614774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>Dawson</td>\n",
       "      <td>13317</td>\n",
       "      <td>12371</td>\n",
       "      <td>-946</td>\n",
       "      <td>-7.103702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>Hockley</td>\n",
       "      <td>23377</td>\n",
       "      <td>21670</td>\n",
       "      <td>-1707</td>\n",
       "      <td>-7.302049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>Uvalde</td>\n",
       "      <td>27055</td>\n",
       "      <td>24918</td>\n",
       "      <td>-2137</td>\n",
       "      <td>-7.898725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175</th>\n",
       "      <td>Newton</td>\n",
       "      <td>14138</td>\n",
       "      <td>12532</td>\n",
       "      <td>-1606</td>\n",
       "      <td>-11.359457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>Duval</td>\n",
       "      <td>11510</td>\n",
       "      <td>10001</td>\n",
       "      <td>-1509</td>\n",
       "      <td>-13.110339</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>163 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         county  pop_2016  pop_2021  pop_change  pop_change_pct\n",
       "104        Hays    185686    234573       48887       26.327779\n",
       "45        Comal    124234    156257       32023       25.776358\n",
       "128     Kaufman    111830    140145       28315       25.319682\n",
       "10      Bastrop     78286     94887       16601       21.205580\n",
       "245  Williamson    490619    591759      101140       20.614774\n",
       "..          ...       ...       ...         ...             ...\n",
       "57       Dawson     13317     12371        -946       -7.103702\n",
       "109     Hockley     23377     21670       -1707       -7.302049\n",
       "231      Uvalde     27055     24918       -2137       -7.898725\n",
       "175      Newton     14138     12532       -1606      -11.359457\n",
       "65        Duval     11510     10001       -1509      -13.110339\n",
       "\n",
       "[163 rows x 5 columns]"
      ]
     },
     "execution_count": 303,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "no_small_counties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 304,
   "id": "66d0a56d",
   "metadata": {},
   "outputs": [],
   "source": [
    "no_small_counties.head(10).to_csv(outputfile + '.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7acdab97",
   "metadata": {},
   "source": [
    "<h4>Total Texas population changes</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 305,
   "id": "36a4aedd",
   "metadata": {},
   "outputs": [],
   "source": [
    "TX_population_2016 = df_merge_short['pop_2016'].sum()\n",
    "TX_population_2021 = df_merge_short['pop_2021'].sum()\n",
    "TX_population_change = TX_population_2021 - TX_population_2016\n",
    "TX_pop_pct_change = ((TX_population_2021 - TX_population_2016)/TX_population_2016)*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 306,
   "id": "ed05fb08",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1906146"
      ]
     },
     "execution_count": 306,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TX_population_change"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 307,
   "id": "a9f7f789",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7.07120952752098"
      ]
     },
     "execution_count": 307,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TX_pop_pct_change"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44f25b67",
   "metadata": {},
   "source": [
    "<h4>Texas counties with the bigget population changes</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 308,
   "id": "3c01a446",
   "metadata": {},
   "outputs": [],
   "source": [
    "county_most_pct_change = df_merge_short.sort_values(by=['pop_change_pct'], ascending=False).head(1)\n",
    "county_most_num_change = df_merge_short.sort_values(by=['pop_change'], ascending=False).head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "id": "767274e8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>county</th>\n",
       "      <th>pop_2016</th>\n",
       "      <th>pop_2021</th>\n",
       "      <th>pop_change</th>\n",
       "      <th>pop_change_pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Harris</td>\n",
       "      <td>4434257</td>\n",
       "      <td>4697957</td>\n",
       "      <td>263700</td>\n",
       "      <td>5.946881</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     county  pop_2016  pop_2021  pop_change  pop_change_pct\n",
       "100  Harris   4434257   4697957      263700        5.946881"
      ]
     },
     "execution_count": 309,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "county_most_num_change"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "id": "dfbe3771",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Harris'"
      ]
     },
     "execution_count": 310,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "county_most_num_change['county'].iat[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e17d58b7",
   "metadata": {},
   "source": [
    "<h4>Texas counties that lost population</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 311,
   "id": "07039c96",
   "metadata": {},
   "outputs": [],
   "source": [
    "lost_population = df_merge_short[df_merge_short['pop_change'] < 0].sort_values(by=['pop_change'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 312,
   "id": "6433a64d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>county</th>\n",
       "      <th>pop_2016</th>\n",
       "      <th>pop_2021</th>\n",
       "      <th>pop_change</th>\n",
       "      <th>pop_change_pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>Hamilton</td>\n",
       "      <td>8232</td>\n",
       "      <td>8211</td>\n",
       "      <td>-21</td>\n",
       "      <td>-0.255102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237</th>\n",
       "      <td>Ward</td>\n",
       "      <td>11396</td>\n",
       "      <td>11375</td>\n",
       "      <td>-21</td>\n",
       "      <td>-0.184275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Glasscock</td>\n",
       "      <td>1253</td>\n",
       "      <td>1221</td>\n",
       "      <td>-32</td>\n",
       "      <td>-2.553871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>Kent</td>\n",
       "      <td>667</td>\n",
       "      <td>632</td>\n",
       "      <td>-35</td>\n",
       "      <td>-5.247376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>King</td>\n",
       "      <td>274</td>\n",
       "      <td>229</td>\n",
       "      <td>-45</td>\n",
       "      <td>-16.423358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>Zavala</td>\n",
       "      <td>12107</td>\n",
       "      <td>9900</td>\n",
       "      <td>-2207</td>\n",
       "      <td>-18.229124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>Jasper</td>\n",
       "      <td>35640</td>\n",
       "      <td>33369</td>\n",
       "      <td>-2271</td>\n",
       "      <td>-6.372054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>Jim Wells</td>\n",
       "      <td>41486</td>\n",
       "      <td>39203</td>\n",
       "      <td>-2283</td>\n",
       "      <td>-5.503061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242</th>\n",
       "      <td>Wichita</td>\n",
       "      <td>132148</td>\n",
       "      <td>129419</td>\n",
       "      <td>-2729</td>\n",
       "      <td>-2.065109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>Potter</td>\n",
       "      <td>121883</td>\n",
       "      <td>119043</td>\n",
       "      <td>-2840</td>\n",
       "      <td>-2.330103</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>142 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        county  pop_2016  pop_2021  pop_change  pop_change_pct\n",
       "96    Hamilton      8232      8211         -21       -0.255102\n",
       "237       Ward     11396     11375         -21       -0.184275\n",
       "86   Glasscock      1253      1221         -32       -2.553871\n",
       "131       Kent       667       632         -35       -5.247376\n",
       "134       King       274       229         -45      -16.423358\n",
       "..         ...       ...       ...         ...             ...\n",
       "253     Zavala     12107      9900       -2207      -18.229124\n",
       "120     Jasper     35640     33369       -2271       -6.372054\n",
       "124  Jim Wells     41486     39203       -2283       -5.503061\n",
       "242    Wichita    132148    129419       -2729       -2.065109\n",
       "187     Potter    121883    119043       -2840       -2.330103\n",
       "\n",
       "[142 rows x 5 columns]"
      ]
     },
     "execution_count": 312,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lost_population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 313,
   "id": "dd547aaa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "142"
      ]
     },
     "execution_count": 313,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "number_lost_population = len(lost_population.index)\n",
    "number_lost_population"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "333b4747",
   "metadata": {},
   "source": [
    "<h3>Putting aggregate totals in a spreadsheet</h3>\n",
    "<p>This creates a spreadsheet of all of the topline stats so we have them in one place.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 314,
   "id": "e4a4de92",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is where we put the fields that we have to include\n",
    "agg_columns = ['texas_growth', 'texas_growth_pct_change', 'county_most_pct_change',\n",
    "               'county_most_num_change', 'number_lost_population']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 315,
   "id": "a2c093aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is the data\n",
    "agg_data = [comma_format(TX_population_change), pct_format(TX_pop_pct_change),county_most_pct_change['county'].iat[0], county_most_num_change['county'].iat[0], number_lost_population]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 316,
   "id": "e5110fc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is where I create the dataframe for the aggregate numbers\n",
    "data_to_add = {'Criteria': agg_columns, 'Totals': agg_data}\n",
    "\n",
    "df_agg = pd.DataFrame(data_to_add)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 317,
   "id": "9ba67d24",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Criteria</th>\n",
       "      <th>Totals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>texas_growth</td>\n",
       "      <td>1,906,146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>texas_growth_pct_change</td>\n",
       "      <td>7.07%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>county_most_pct_change</td>\n",
       "      <td>Hays</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>county_most_num_change</td>\n",
       "      <td>Harris</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>number_lost_population</td>\n",
       "      <td>142</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Criteria     Totals\n",
       "0             texas_growth  1,906,146\n",
       "1  texas_growth_pct_change      7.07%\n",
       "2   county_most_pct_change       Hays\n",
       "3   county_most_num_change     Harris\n",
       "4   number_lost_population        142"
      ]
     },
     "execution_count": 317,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_agg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 318,
   "id": "53b0e1a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_agg.to_csv(outputfile_agg + '.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c7f91d0",
   "metadata": {},
   "source": [
    "<h3>Upload to Google Sheets</h3>\n",
    "<p>This uploads several dataframes into our Google Sheet template that is available to our reporters.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 319,
   "id": "b3f40a04",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_frames_to_add = [{\n",
    "        'file_data': df_agg,\n",
    "        'sheet': 'aggregate_hidden',\n",
    "        'hidden': 'true',\n",
    "    },\n",
    "    {\n",
    "        'file_data': no_small_counties.head(10),\n",
    "        'sheet': 'top_10_counties_over_10000',\n",
    "        'hidden': 'false',\n",
    "    },\n",
    "    {\n",
    "        'file_data': df_merge_short.sort_values(by='pop_change_pct', ascending=False),\n",
    "        'sheet': 'raw_data',\n",
    "        'hidden': 'false',\n",
    "    }]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 320,
   "id": "c1322784",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_sheet = createNewSheetIfNotExist(google_sheet_name, google_folderID)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 321,
   "id": "44dbc0bc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'kind': 'drive#file',\n",
       " 'id': '16nJNpc6Ts9dSPq48SOsQNMiRzFwRinYne-HxTQi8cdU',\n",
       " 'name': 'Texas Census analysis (2016 5-year estimate) and (2021 5-year estimate)',\n",
       " 'mimeType': 'application/vnd.google-apps.spreadsheet'}"
      ]
     },
     "execution_count": 321,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 322,
   "id": "07d33948",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_workbook = client.open(google_sheet_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 323,
   "id": "5db5857b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# First, check to see if tabs already exist. Need to create a list of tabs in this particular worksheet\n",
    "worksheet_objs = data_workbook.worksheets()\n",
    "worksheets_list = []\n",
    "\n",
    "for worksheet in worksheet_objs:\n",
    "    worksheets_list.append(worksheet.title)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 324,
   "id": "f4cf5b5c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Sheet1']"
      ]
     },
     "execution_count": 324,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "worksheets_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 325,
   "id": "4e9323c6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Uploading the aggregate_hidden file to its Google spreadsheet\n",
      "Uploading the top_10_counties_over_10000 file to its Google spreadsheet\n",
      "Uploading the raw_data file to its Google spreadsheet\n"
     ]
    }
   ],
   "source": [
    "# This checks to see if the worksheet exists. If not, it will create a new one.\n",
    "for file in data_frames_to_add:\n",
    "    print('Uploading the ' + file['sheet'] + ' file to its Google spreadsheet')\n",
    "    #If the tab is already in the worksheet, update it. If not, add a new tab\n",
    "    data_sheet = data_workbook.worksheet(file['sheet']) if file['sheet'] in worksheets_list else data_workbook.add_worksheet(file['sheet'], rows=1000, cols=40, index=None)\n",
    "    #Clears any existing data in the existing datasheet\n",
    "    data_sheet.clear()\n",
    "    # Add the data to tab\n",
    "    set_with_dataframe(data_sheet, file['file_data'])\n",
    "    if file['hidden'] == 'true':\n",
    "        # Hide this tab if it says in needs to be hidden\n",
    "        body = {'requests': [{\n",
    "            'updateSheetProperties': {\n",
    "                'properties': {\n",
    "                    'sheetId': data_sheet.id,\n",
    "                    'hidden': True\n",
    "                },\n",
    "                'fields': 'hidden'\n",
    "            }\n",
    "        }]}\n",
    "        data_workbook.batch_update(body=body)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 326,
   "id": "cde62d09",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'kind': 'drive#file',\n",
       " 'id': '16nJNpc6Ts9dSPq48SOsQNMiRzFwRinYne-HxTQi8cdU',\n",
       " 'name': 'Texas Census analysis (2016 5-year estimate) and (2021 5-year estimate)',\n",
       " 'mimeType': 'application/vnd.google-apps.spreadsheet'}"
      ]
     },
     "execution_count": 326,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 327,
   "id": "b7401762",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This finds if this is a new Google Sheet that was just created and copies the template Google Sheet `aggregate` section to the new Google Sheet.\n",
    "# Then it renames that new tab aggregate, erases the existing 'Sheet1' and reorders 'aggregate' to be the first tab shown\n",
    "if 'Sheet1' in worksheets_list:\n",
    "    source_worksheet_name = 'aggregate'\n",
    "    dest_gsheet_url = new_sheet['id']\n",
    "\n",
    "    templateWS = client.open_by_url(source_gsheet_url).worksheet(source_worksheet_name)\n",
    "    target_ws_props = templateWS.copy_to(dest_gsheet_url)\n",
    "    target_ws = gspread.Worksheet(client.open_by_key(dest_gsheet_url), target_ws_props)\n",
    "    target_ws.update_title(\"aggregate\")\n",
    "\n",
    "    created_sheet = client.open_by_key(dest_gsheet_url)\n",
    "\n",
    "    sheet_to_be_deleted = created_sheet.worksheet('Sheet1')\n",
    "\n",
    "    created_sheet.del_worksheet(sheet_to_be_deleted)\n",
    "    created_sheet.reorder_worksheets([created_sheet.worksheet('aggregate'), created_sheet.worksheet('aggregate_hidden')\n",
    "                                        , created_sheet.worksheet('top_10_counties_over_10000'), created_sheet.worksheet('raw_data')])\n"
   ]
  }
 ],
 "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}