GenerateData_v2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from tqdm import tqdm\n",
    "\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "def getCDFDataFixed(cdf, col_name):\n",
    "    cdf_pivot_cases = (\n",
    "        pd\n",
    "        .pivot_table(\n",
    "            cdf,\n",
    "            index=['date'],\n",
    "            columns=['fips'],\n",
    "            values=[col_name]\n",
    "        )\n",
    "        .reset_index()\n",
    "        .fillna(0))\n",
    "    cdf_pivot_cases.columns = (\n",
    "        np\n",
    "        .insert(\n",
    "            np\n",
    "            .sort(cdf['fips'].unique())\n",
    "            .astype('str'), 0, 'date', axis=0)\n",
    "    )\n",
    "\n",
    "    for col in cdf_pivot_cases.columns[1:]:\n",
    "        cdf_pivot_cases[col] = cdf_pivot_cases[col].diff().fillna(0)\n",
    "        cdf_pivot_cases[col] = cdf_pivot_cases[col].apply(\n",
    "            lambda x: x if x >= 0 else 0\n",
    "        )\n",
    "\n",
    "    for col in cdf_pivot_cases.columns[1:]:\n",
    "        cdf_pivot_cases[col] = (\n",
    "            cdf_pivot_cases[col]\n",
    "            .rolling(14, min_periods=1)\n",
    "            .sum()\n",
    "        )\n",
    "\n",
    "    cdf_mlt_cases = (\n",
    "        pd.melt(\n",
    "            cdf_pivot_cases,\n",
    "            id_vars=['date'],\n",
    "            value_vars=cdf_pivot_cases.columns[1:],\n",
    "            var_name='fips',\n",
    "            value_name=col_name)\n",
    "    )\n",
    "    return cdf_mlt_cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "def GenerateData(mdf, cdf, sdf, state_fips):\n",
    "    mdf['date'] = pd.to_datetime(mdf['date'])\n",
    "    mdf.rename(columns={'dest': 'fips'}, inplace=True)\n",
    "    cdf['date'] = pd.to_datetime(cdf['date'])\n",
    "    mdf = mdf[mdf['fips'] == state_fips]\n",
    "    cdf = pd.merge(cdf, sdf[['fips','Pop']])\n",
    "    cdf_mlt_cases = getCDFDataFixed(cdf, 'cases')\n",
    "    cdf_mlt_deaths = getCDFDataFixed(cdf, 'deaths')\n",
    "    cdf_fxd = pd.merge(cdf_mlt_cases, cdf_mlt_deaths)\n",
    "    cdf_new = sdf[['fips', 'Pop']]\n",
    "    cdf_new['fips'] = cdf_new['fips'].astype(str)\n",
    "    cdf_fxd['fips'] = cdf_fxd['fips'].astype(str)\n",
    "    cdf_fxd = pd.merge(cdf_fxd, cdf_new, left_on='fips', right_on='fips')\n",
    "\n",
    "    cdf_fxd['cases_pc'] = cdf_fxd['cases']*100000/cdf_fxd['Pop']\n",
    "    cdf_fxd['deaths_pc'] = cdf_fxd['deaths']*100000/cdf_fxd['Pop']\n",
    "    mdf.drop(columns=['fips'], inplace=True)\n",
    "    mdf.rename(columns={'src': 'fips'}, inplace=True)\n",
    "\n",
    "    mdf['fips'] = mdf['fips'].astype('str')\n",
    "    df = pd.merge(cdf_fxd, mdf, how='inner')\n",
    "    df['risk_c'] = df['cases_pc']*df['freq']\n",
    "    df['risk_d'] = df['deaths_pc']*df['freq']\n",
    "    ldf = df[df['fips'] == str(state_fips)]\n",
    "    edf = df[df['fips'] != str(state_fips)]\n",
    "\n",
    "    edf = edf.groupby(by=['date']).agg({\n",
    "        'risk_c': np.sum,\n",
    "        'risk_d': np.sum\n",
    "    }).reset_index()\n",
    "\n",
    "    edf.rename(columns={\n",
    "        'risk_c': 'Ext_risk_c',\n",
    "        'risk_d': 'Ext_risk_d'\n",
    "    }, inplace=True)\n",
    "    edf = edf[['date', 'Ext_risk_c', 'Ext_risk_d']]\n",
    "    ldf.rename(columns={\n",
    "        'risk_c': 'Int_risk_c',\n",
    "        'risk_d': 'Int_risk_d'\n",
    "    }, inplace=True)\n",
    "    ldf = ldf[['date', 'cases_pc', 'deaths_pc', 'Int_risk_c', 'Int_risk_d']]\n",
    "    cmbdf = pd.merge(edf, ldf)\n",
    "    cmbdf.to_csv('StateFiles/CombinedDF_'+str(state_fips)+'.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████████████████████████████████████████████████████| 50/50 [00:10<00:00,  4.62it/s]\n"
     ]
    }
   ],
   "source": [
    "mdf = pd.read_csv('Data/SafeGraphData.csv')\n",
    "cdf = pd.read_csv('Data/caseInformation_States.csv')\n",
    "sdf = pd.read_csv('Data/stateInfo.csv')\n",
    "\n",
    "state_fips_list = sdf['fips'].unique()\n",
    "for state_fips in tqdm(state_fips_list):\n",
    "    GenerateData(mdf, cdf, sdf, state_fips)"
   ]
  }
 ],
 "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.10.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}