format_ethnicity_data_from_csv.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Clean & Process COVID-19 Ethnicity Files for Pie Chart Symbology Maps\n",
    "Center for Human Dynamics in the Mobile Age (HDMA) at San Diego State University\n",
    "\n",
    "Jessica Embury"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MODULES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "import arcpy\n",
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### USER SET VARIABLE FOR FILE DATE\n",
    "Use county's naming convention for file date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "date = '26DEC2020'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CREATE LIST OBJECT WITH ALL FILE NAMES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['COVID19_DailyTotalRate_AIAN_26DEC2020.csv', 'COVID19_DailyTotalRate_API_26DEC2020.csv', 'COVID19_DailyTotalRate_Black_26DEC2020.csv', 'COVID19_DailyTotalRate_Hispanic_26DEC2020.csv', 'COVID19_DailyTotalRate_White_26DEC2020.csv', 'COVID19_DailyTotalRate_MultipleRace_26DEC2020.csv']\n"
     ]
    }
   ],
   "source": [
    "#file directory\n",
    "path = '../covid_data/ethnicity/{}/'.format(date)\n",
    "\n",
    "#file names\n",
    "base = 'COVID19_DailyTotalRate_'\n",
    "\n",
    "white_file = base + 'White_' + date + '.csv'\n",
    "multi_file = base + 'MultipleRace_' + date + '.csv'\n",
    "hispanic_file = base + 'Hispanic_' + date + '.csv'\n",
    "black_file = base + 'Black_' + date + '.csv'\n",
    "api_file = base + 'API_' + date + '.csv'\n",
    "aian_file = base + 'AIAN_' + date + '.csv'\n",
    "\n",
    "files = [aian_file, api_file, black_file, hispanic_file, white_file, multi_file]\n",
    "print(files)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DATA FRAME WITH SUPPLEMENTAL SRA DATA, COORDINATES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "41\n"
     ]
    },
    {
     "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>sra_num</th>\n",
       "      <th>sra_name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Central San Diego</td>\n",
       "      <td>32.722644</td>\n",
       "      <td>-117.141073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Peninsula</td>\n",
       "      <td>32.742274</td>\n",
       "      <td>-117.216035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Coronado</td>\n",
       "      <td>32.657317</td>\n",
       "      <td>-117.143062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>National City</td>\n",
       "      <td>32.665847</td>\n",
       "      <td>-117.099737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Southeastern San Diego</td>\n",
       "      <td>32.685705</td>\n",
       "      <td>-117.038621</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sra_num                sra_name   latitude   longitude\n",
       "0        1       Central San Diego  32.722644 -117.141073\n",
       "1        2               Peninsula  32.742274 -117.216035\n",
       "2        3                Coronado  32.657317 -117.143062\n",
       "3        4           National City  32.665847 -117.099737\n",
       "4        5  Southeastern San Diego  32.685705 -117.038621"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#path\n",
    "sra_in = './data/sra_info.csv'\n",
    "\n",
    "#base sra data\n",
    "sra = pd.read_csv(sra_in)\n",
    "print(len(sra))\n",
    "sra.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ADD A COLUMN FOR EACH ETHNICITY TO THE SRA DATAFRAME"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "41\n"
     ]
    },
    {
     "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>SRA</th>\n",
       "      <th>Name</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Date</th>\n",
       "      <th>AIAN</th>\n",
       "      <th>Asian, Pacific Islander</th>\n",
       "      <th>Black</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>White</th>\n",
       "      <th>Multiple Race</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Central San Diego</td>\n",
       "      <td>32.722644</td>\n",
       "      <td>-117.141073</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>13.1</td>\n",
       "      <td>300.5</td>\n",
       "      <td>431.8</td>\n",
       "      <td>4010.6</td>\n",
       "      <td>2075.3</td>\n",
       "      <td>80.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Peninsula</td>\n",
       "      <td>32.742274</td>\n",
       "      <td>-117.216035</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>0.0</td>\n",
       "      <td>35.3</td>\n",
       "      <td>38.3</td>\n",
       "      <td>311.6</td>\n",
       "      <td>766.7</td>\n",
       "      <td>27.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Coronado</td>\n",
       "      <td>32.657317</td>\n",
       "      <td>-117.143062</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>241.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>National City</td>\n",
       "      <td>32.665847</td>\n",
       "      <td>-117.099737</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>5.8</td>\n",
       "      <td>369.5</td>\n",
       "      <td>77.7</td>\n",
       "      <td>2818.9</td>\n",
       "      <td>221.0</td>\n",
       "      <td>15.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Southeastern San Diego</td>\n",
       "      <td>32.685705</td>\n",
       "      <td>-117.038621</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>18.0</td>\n",
       "      <td>868.5</td>\n",
       "      <td>545.2</td>\n",
       "      <td>5917.3</td>\n",
       "      <td>671.5</td>\n",
       "      <td>81.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SRA                    Name   Latitude  ...  Hispanic   White  Multiple Race\n",
       "0    1       Central San Diego  32.722644  ...    4010.6  2075.3           80.1\n",
       "1    2               Peninsula  32.742274  ...     311.6   766.7           27.9\n",
       "2    3                Coronado  32.657317  ...      82.0   241.0            0.0\n",
       "3    4           National City  32.665847  ...    2818.9   221.0           15.6\n",
       "4    5  Southeastern San Diego  32.685705  ...    5917.3   671.5           81.2\n",
       "\n",
       "[5 rows x 11 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#add ethnicity data as columns to the sra df\n",
    "for f in range(len(files)):\n",
    "    \n",
    "    #not every week has had a file for each ethnicity, so check if file for ethnicity exists\n",
    "    if os.path.exists(path + files[f]):\n",
    "        \n",
    "        #ethnicity column name\n",
    "        ethnicity = files[f].split('_')[2]\n",
    "        if ethnicity == 'MultipleRace':\n",
    "            ethnicity = 'Multiple\\nRace'\n",
    "    \n",
    "        #temp df for specific ethnicity\n",
    "        df = pd.read_csv(path + files[f])\n",
    "\n",
    "        #add date to sra df\n",
    "        df = df.rename(columns = {'date':'Date'})\n",
    "        if f == 0:\n",
    "            sra['Date'] = df['Date'][0]\n",
    "    \n",
    "        #add ethnicity columns to sra df\n",
    "        #multiple column name options to catch naming variations in files from the county\n",
    "        df = df.rename(columns = {'Allocated {}\\nCase Count (Raw)'.format(ethnicity):'{}'.format(ethnicity.replace('\\n',' ')),\n",
    "                                  'Allocated {} Case Count (Raw)'.format(ethnicity.replace('\\n',' ')):'{}'.format(ethnicity.replace('\\n',' ')),\n",
    "                                  'Allocated\\n{} Case Count (Raw)'.format(ethnicity):'{}'.format(ethnicity.replace('\\n',' ')),\n",
    "                                  'Allocated\\n{} Case\\nCount\\n(Raw)'.format(ethnicity):'{}'.format(ethnicity.replace('\\n',' ')), \n",
    "                                  'Allocated\\n{}\\nCase\\nCount\\n(Raw)'.format(ethnicity):'{}'.format(ethnicity.replace('\\n',' ')), \n",
    "                                  'Allocated\\n{}\\nRace\\nCase\\nCount\\n(Raw)'.format(ethnicity):'{}'.format(ethnicity.replace('\\n',' ')),\n",
    "                                  'Allocated {} Race Case Count (Raw)'.format(ethnicity.replace('\\n',' ')):'{}'.format(ethnicity.replace('\\n',' ')),\n",
    "                                  'Geography':'sra_name'})\n",
    "        subset_df = df[['sra_name', ethnicity.replace('\\n',' ')]].fillna(0)\n",
    "    \n",
    "        sra = sra.merge(subset_df, how='left', on='sra_name')\n",
    "\n",
    "sra = sra.rename(columns = {'sra_num':'SRA', \n",
    "                            'sra_name':'Name', \n",
    "                            'latitude':'Latitude', \n",
    "                            'longitude':'Longitude', \n",
    "                            #'AIAN':'American Indian, AK Native', \n",
    "                            'API':'Asian, Pacific Islander'})\n",
    "print(len(sra))\n",
    "sra.head()    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>SRA</th>\n",
       "      <th>Name</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Date</th>\n",
       "      <th>AIAN</th>\n",
       "      <th>Asian, Pacific Islander</th>\n",
       "      <th>Black</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>White</th>\n",
       "      <th>Multiple Race</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>38</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>32.837524</td>\n",
       "      <td>-116.758443</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>7.1</td>\n",
       "      <td>7.4</td>\n",
       "      <td>5.6</td>\n",
       "      <td>111.6</td>\n",
       "      <td>292.6</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>63</td>\n",
       "      <td>Anza-Borrego Springs</td>\n",
       "      <td>33.114514</td>\n",
       "      <td>-116.289908</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>17.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>41</td>\n",
       "      <td>Carlsbad</td>\n",
       "      <td>33.133083</td>\n",
       "      <td>-117.284496</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>9.1</td>\n",
       "      <td>133.0</td>\n",
       "      <td>36.5</td>\n",
       "      <td>819.5</td>\n",
       "      <td>1297.6</td>\n",
       "      <td>30.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Central San Diego</td>\n",
       "      <td>32.722644</td>\n",
       "      <td>-117.141073</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>13.1</td>\n",
       "      <td>300.5</td>\n",
       "      <td>431.8</td>\n",
       "      <td>4010.6</td>\n",
       "      <td>2075.3</td>\n",
       "      <td>80.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>21</td>\n",
       "      <td>Chula Vista</td>\n",
       "      <td>32.622386</td>\n",
       "      <td>-117.077827</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>7.6</td>\n",
       "      <td>280.6</td>\n",
       "      <td>118.0</td>\n",
       "      <td>6626.6</td>\n",
       "      <td>539.8</td>\n",
       "      <td>47.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    SRA                  Name   Latitude  ...  Hispanic   White  Multiple Race\n",
       "25   38                Alpine  32.837524  ...     111.6   292.6            6.5\n",
       "40   63  Anza-Borrego Springs  33.114514  ...      33.0    17.7            0.0\n",
       "28   41              Carlsbad  33.133083  ...     819.5  1297.6           30.3\n",
       "0     1     Central San Diego  32.722644  ...    4010.6  2075.3           80.1\n",
       "15   21           Chula Vista  32.622386  ...    6626.6   539.8           47.6\n",
       "\n",
       "[5 rows x 11 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#alphabetize rows by sra name\n",
    "sra = sra.sort_values(by='Name', axis=0, ascending=True, inplace=False, kind='quicksort', na_position='last')\n",
    "sra.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### EXPORT CSV FILE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save sra df as csv\n",
    "date2 = sra['Date'][0]\n",
    "sra.to_csv('./data/covid_ethnicity_data_{}.csv'.format(date2.replace('/','')), index=False)\n",
    "sra.to_csv('C:/Users/jesse/Dropbox/Mapping-Vulearable-Pop-Tasks/Ethnicity-Maps/covid_ethnicity_data_{}.csv'.format(date2.replace('/','')), index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### APPEND NEW DATE TO ETHNICITY POINTS LAYER"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<h2>Output</h2>C:\\Users\\jesse\\iCloudDrive\\HDMA\\covid_ethnicity\\covid_ethnicity.gdb\\lyr_covid_ethnicity_data_12262020<h2>Messages</h2>Start Time: Sunday, January 3, 2021 2:53:00 PM<br/>Succeeded at Sunday, January 3, 2021 2:53:01 PM (Elapsed Time: 1.18 seconds)<br/><style>.rendered_html td, .rendered_html th {text-align: left;}.rendered_html tbody {border: 1px solid black;}</style>"
      ],
      "text/plain": [
       "<Result 'C:\\\\Users\\\\jesse\\\\iCloudDrive\\\\HDMA\\\\covid_ethnicity\\\\covid_ethnicity.gdb\\\\lyr_covid_ethnicity_data_12262020'>"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# csv path for gdb table\n",
    "csv_in = './data/covid_ethnicity_data_{}.csv'.format(date2.replace('/',''))\n",
    "\n",
    "# Get and set current project and geodatabase info\n",
    "arcpy.env.overwriteOutput = True\n",
    "aprx = arcpy.mp.ArcGISProject('CURRENT')\n",
    "defaultGeoDb = arcpy.env.workspace\n",
    "aprx.defaultGeodatabase = defaultGeoDb\n",
    "currentMap = aprx.activeMap\n",
    "\n",
    "# set names for table and layer\n",
    "tempStr = 'covid_ethnicity_data_{}'.format(date2.replace('/',''))\n",
    "newMapName= 'lyr_' + tempStr\n",
    "newTableName = 'tbl_' + tempStr\n",
    "\n",
    "# convert CSV data into table and add to default database - look in database to see it was added\n",
    "arcpy.TableToTable_conversion(csv_in,defaultGeoDb,newTableName)\n",
    "\n",
    "# convert table to XY point layer\n",
    "arcpy.management.XYTableToPoint(newTableName, newMapName, 'Longitude', 'Latitude')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<h2>Output</h2>C:\\Users\\jesse\\iCloudDrive\\HDMA\\covid_ethnicity\\covid_ethnicity.gdb\\lyr_main_ethnicity_pts<h2>Messages</h2>Start Time: Sunday, January 3, 2021 2:53:06 PM<br/>Succeeded at Sunday, January 3, 2021 2:53:06 PM (Elapsed Time: 0.26 seconds)<br/><style>.rendered_html td, .rendered_html th {text-align: left;}.rendered_html tbody {border: 1px solid black;}</style>"
      ],
      "text/plain": [
       "<Result 'C:\\\\Users\\\\jesse\\\\iCloudDrive\\\\HDMA\\\\covid_ethnicity\\\\covid_ethnicity.gdb\\\\lyr_main_ethnicity_pts'>"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# append new date's point data to main feature class\n",
    "arcpy.management.Append(newMapName, 'lyr_main_ethnicity_pts')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ArcGISPro",
   "language": "Python",
   "name": "python3"
  },
  "language_info": {
   "file_extension": ".py",
   "name": "python",
   "version": "3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}