Outbreaks AGILE 2022.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b83e3e6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#loading required libraries\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import csv    \n",
    "import numpy as np\n",
    "import ast"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1cbac264",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/arunsharma/opt/anaconda3/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3165: DtypeWarning: Columns (27,28) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,\n"
     ]
    }
   ],
   "source": [
    "#Download Files \"Weekly_Patterns.csv\" and \"core_places.csv\" by copy and pasting Google Drive link:\n",
    "#https://drive.google.com/drive/folders/1gE-yFdIgpPjkAIj_EtumlrRvpQHs0514?usp=sharing\n",
    "#Change the path variable as per the local machine address:\n",
    "path = \"/Users/arunsharma/Desktop/\"\n",
    "weekly_patterns = pd.read_csv(path+\"Weekly_Patterns.csv\")\n",
    "weekly_patterns = weekly_patterns.drop(['Unnamed: 0'], axis = 1)\n",
    "core_pls = pd.read_csv(path+\"core_places.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "be05a3cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Merge Core_Places and Weekly_Patterns Data\n",
    "core_pls = core_pls[['safegraph_place_id','top_category','sub_category', 'naics_code']]\n",
    "merged_data = pd.merge(weekly_patterns,core_pls,left_on='safegraph_place_id',right_on='safegraph_place_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "20d329ff",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "64068\n",
      "63678\n",
      "61574\n",
      "60403\n",
      "59647\n",
      "59512\n",
      "58879\n",
      "60796\n",
      "61202\n",
      "61360\n",
      "61789\n",
      "62772\n",
      "62846\n",
      "63474\n",
      "63680\n",
      "63680\n",
      "63799\n",
      "64078\n",
      "63698\n",
      "62917\n",
      "63466\n",
      "63272\n",
      "64702\n",
      "61247\n",
      "61203\n",
      "61316\n",
      "60968\n",
      "61413\n",
      "60734\n",
      "60677\n",
      "60969\n",
      "30\n"
     ]
    }
   ],
   "source": [
    "#Grouped Merged Data by Week from 2020-03-02T00:00:00-06:00 to 2021-06-21T00:00:00-04:00\n",
    "weeks = []\n",
    "\n",
    "week1 = merged_data.loc[(merged_data['date_range_start'] == \"2020-03-02T00:00:00-06:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-03-02T00:00:00-05:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-03-02T00:00:00-07:00\")]\n",
    "weeks.append(week1)\n",
    "\n",
    "week2 = merged_data.loc[(merged_data['date_range_start'] == \"2020-03-09T00:00:00-05:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-03-09T00:00:00-06:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-03-09T00:00:00-04:00\")]\n",
    "weeks.append(week2)\n",
    "\n",
    "week3 = merged_data.loc[(merged_data['date_range_start'] == \"2020-03-16T00:00:00-05:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-03-16T00:00:00-06:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-03-16T00:00:00-04:00\")]\n",
    "weeks.append(week3)\n",
    "\n",
    "week4 = merged_data.loc[(merged_data['date_range_start'] == \"2020-03-23T00:00:00-06:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-03-23T00:00:00-05:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-03-23T00:00:00-04:00\")]\n",
    "weeks.append(week4)\n",
    "\n",
    "week5 = merged_data.loc[(merged_data['date_range_start'] == \"2020-03-30T00:00:00-05:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-03-30T00:00:00-06:00\")]\n",
    "weeks.append(week5)\n",
    "\n",
    "week6 = merged_data.loc[(merged_data['date_range_start'] == \"2020-04-06T00:00:00-05:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-04-06T00:00:00-06:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-04-06T00:00:00-04:00\")]\n",
    "weeks.append(week6)\n",
    "\n",
    "week7 = merged_data.loc[(merged_data['date_range_start'] == \"2020-04-13T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-04-13T00:00:00-05:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-04-13T00:00:00-06:00\")]\n",
    "weeks.append(week7)\n",
    "\n",
    "week8 = merged_data.loc[(merged_data['date_range_start'] == \"2020-04-20T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-04-20T00:00:00-05:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-04-20T00:00:00-06:00\")]\n",
    "weeks.append(week8)\n",
    "\n",
    "week9 = merged_data.loc[(merged_data['date_range_start'] == \"2020-04-27T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-04-27T00:00:00-05:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-04-27T00:00:00-06:00\")]\n",
    "weeks.append(week9)\n",
    "\n",
    "week10 = merged_data.loc[(merged_data['date_range_start'] == \"2020-05-04T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-05-04T00:00:00-05:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-05-04T00:00:00-06:00\")]\n",
    "weeks.append(week10)\n",
    "\n",
    "week11 = merged_data.loc[(merged_data['date_range_start'] == \"2020-05-11T00:00:00-05:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-05-11T00:00:00-06:00\")]\n",
    "weeks.append(week11)\n",
    "\n",
    "week12 = merged_data.loc[(merged_data['date_range_start'] == \"2020-05-18T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-05-18T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-05-18T00:00:00-06:00\")]\n",
    "weeks.append(week12)\n",
    "\n",
    "week13 = merged_data.loc[(merged_data['date_range_start'] == \"2020-05-25T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-05-25T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-05-25T00:00:00-06:00\")]\n",
    "weeks.append(week13)\n",
    "\n",
    "week14 = merged_data.loc[(merged_data['date_range_start'] == \"2020-06-01T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-06-01T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-06-01T00:00:00-06:00\")]\n",
    "weeks.append(week14)\n",
    "\n",
    "week15 = merged_data.loc[(merged_data['date_range_start'] == \"2020-06-08T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-06-08T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-06-08T00:00:00-06:00\")]\n",
    "weeks.append(week15)\n",
    "\n",
    "week16 = merged_data.loc[(merged_data['date_range_start'] == \"2020-06-15T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-06-15T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-06-15T00:00:00-06:00\")]\n",
    "weeks.append(week16)\n",
    "\n",
    "week17 = merged_data.loc[(merged_data['date_range_start'] == \"2020-06-22T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-06-22T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-06-22T00:00:00-06:00\")]\n",
    "weeks.append(week17)\n",
    "\n",
    "week18 = merged_data.loc[(merged_data['date_range_start'] == \"2020-06-29T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-06-29T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-06-29T00:00:00-06:00\")]\n",
    "weeks.append(week18)\n",
    "\n",
    "week19 = merged_data.loc[(merged_data['date_range_start'] == \"2020-07-06T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-07-06T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-07-06T00:00:00-06:00\")]\n",
    "weeks.append(week19)\n",
    "\n",
    "week20 = merged_data.loc[(merged_data['date_range_start'] == \"2020-07-13T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-07-13T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-07-13T00:00:00-06:00\")]\n",
    "weeks.append(week20)\n",
    "\n",
    "week21 = merged_data.loc[(merged_data['date_range_start'] == \"2020-07-20T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-07-20T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-07-20T00:00:00-06:00\")]\n",
    "weeks.append(week21)\n",
    "\n",
    "week22 = merged_data.loc[(merged_data['date_range_start'] == \"2020-07-27T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-07-27T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-07-27T00:00:00-06:00\")]\n",
    "weeks.append(week22)\n",
    "\n",
    "week23 = merged_data.loc[(merged_data['date_range_start'] == \"2020-08-03T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-08-03T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-08-03T00:00:00-06:00\")]\n",
    "weeks.append(week23)\n",
    "\n",
    "week24 = merged_data.loc[(merged_data['date_range_start'] == \"2020-08-10T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-08-10T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-08-10T00:00:00-06:00\")]\n",
    "weeks.append(week24)\n",
    "\n",
    "week25 = merged_data.loc[(merged_data['date_range_start'] == \"2020-08-17T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-08-17T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-08-17T00:00:00-06:00\")]\n",
    "weeks.append(week25)\n",
    "\n",
    "week26 = merged_data.loc[(merged_data['date_range_start'] == \"2020-08-24T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-08-24T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-08-24T00:00:00-06:00\")]\n",
    "weeks.append(week26)\n",
    "\n",
    "week27 = merged_data.loc[(merged_data['date_range_start'] == \"2020-08-31T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-08-31T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-08-31T00:00:00-06:00\")]\n",
    "weeks.append(week27)\n",
    "\n",
    "week28 = merged_data.loc[(merged_data['date_range_start'] == \"2020-09-07T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-09-07T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-09-07T00:00:00-06:00\")]\n",
    "weeks.append(week28)\n",
    "\n",
    "week29 = merged_data.loc[(merged_data['date_range_start'] == \"2020-09-14T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-09-14T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-09-14T00:00:00-06:00\")]\n",
    "weeks.append(week29)\n",
    "\n",
    "week30 = merged_data.loc[(merged_data['date_range_start'] == \"2020-09-21T00:00:00-04:00\") \n",
    "                    | (merged_data['date_range_start'] == \"2020-09-21T00:00:00-05:00\")\n",
    "                    | (merged_data['date_range_start'] == \"2020-09-21T00:00:00-06:00\")]\n",
    "weeks.append(week30)\n",
    "\n",
    "time = ['03/02-03/09','03/09-3/16','03/16-03/23','03/23-03/30','03/30-04/06','04/06-04/13','04/13-04/20','04/20-04/27',\n",
    "        '04/27-05/04','05/04-05/11','05/11-05/18','05/18-05/25','05/25-06/01','06/01-06/08','06/08-06/15','06/15-06/22',\n",
    "        '06/22-06/29','06/29-07/06','07/06-07/13','07/13-07/20','07/20-07/27','07/27-08/03','08/03-08/10','08/10-08/17',\n",
    "        '08/17-08/24','08/24-08/31','08/31-09/07','09/07-09/14','09/14-09/21','09/21-09/28']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97ca05c2",
   "metadata": {},
   "source": [
    "### Code for generating Figure 11\n",
    "#### NOTE: Figure 11 shows combined output of NEW_OUTBREAK_June_multibar_data_bucket_dwell_20ONLY.xlsx and NEW_NO_OUTBREAK_June_multibar_data_bucket_dwell_20ONLY.xlsx."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a33bdcde",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_median_buckets_dwell_for_multiplaces_20more(merged_data,weeks,bars): #bars is a list og sg-ids (for bar in bars:)\n",
    "    list_1 = [0]*30 #44 zeros there is ONE ZERO FOR EACH WEEK\n",
    "    i=0\n",
    "    count = 0\n",
    "    global_dic = {}\n",
    "    for week in weeks:\n",
    "        count = count + 1  \n",
    "        for bar in bars:\n",
    "            naics_table = week.loc[merged_data['safegraph_place_id'] == bar]\n",
    "            dwell_list = naics_table[['bucketed_dwell_times']]['bucketed_dwell_times'].tolist()\n",
    "#             print (dwell_list)\n",
    "            #list_1[i] = list_1[i]+sumall([val1.get('21-60') for val1 in list_dic])+sumall([val1.get('61-240') for val1 in list_dic])+sumall([val1.get('>240') for val1 in list_dic])\n",
    "            if count > 39:   \n",
    "                list_1[i] = list_1[i]+sum([ast.literal_eval(dwell_time).get('21-60')+ast.literal_eval(dwell_time).get('61-120')+ast.literal_eval(dwell_time).get('121-240')+ast.literal_eval(dwell_time).get('>240') for dwell_time in dwell_list])\n",
    "            else :\n",
    "                list_1[i] = list_1[i]+sum([ast.literal_eval(dwell_time).get('21-60')+ast.literal_eval(dwell_time).get('61-240')+ast.literal_eval(dwell_time).get('>240') for dwell_time in dwell_list])\n",
    "        i=i+1 #after the \"bar in bars\" for loop is done\n",
    "        \n",
    "    global_dic['>20'] = list_1\n",
    "    return(global_dic)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "90d697a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_median_buckets_dwell_for_multiplaces(merged_data,weeks,bars):\n",
    "    list_1 = [0]*30\n",
    "    list_2 = [0]*30\n",
    "    list_3 = [0]*30\n",
    "    i=0\n",
    "    count=0\n",
    "    global_dic = {}\n",
    "    \n",
    "    for week in weeks:\n",
    "        count = count + 1\n",
    "        for bar in bars:\n",
    "            naics_table = week.loc[merged_data['safegraph_place_id'] == bar]\n",
    "            dwell_list = naics_table[['bucketed_dwell_times']]['bucketed_dwell_times'].tolist()\n",
    "            list_1[i] = list_1[i]+sum([ast.literal_eval(dwell_time).get('21-60') for dwell_time in dwell_list])\n",
    "            list_3[i] = list_3[i]+sum([ast.literal_eval(dwell_time).get('>240') for dwell_time in dwell_list])\n",
    "            if count > 39:   \n",
    "                list_2[i] = list_2[i]+sum([ast.literal_eval(dwell_time).get('61-120') for dwell_time in dwell_list])+sum([ast.literal_eval(dwell_time).get('121-240') for dwell_time in dwell_list])\n",
    "            else :\n",
    "                list_2[i] = list_2[i]+sum([ast.literal_eval(dwell_time).get('61-240') for dwell_time in dwell_list])\n",
    "        i=i+1\n",
    "        \n",
    "    global_dic['21-60'] = list_1\n",
    "    global_dic['61-240'] = list_2\n",
    "    global_dic['>240'] = list_3\n",
    "    return(global_dic)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "d0f56b23",
   "metadata": {},
   "outputs": [],
   "source": [
    "#  15 BARS WITH COVID OUTBREAKS JUNE/JULY GRAPH >20 min visits ONLY\n",
    "\n",
    "bar15list = ['sg:35e565e1ba984e2481fbfd9cd7316fb7','sg:c72fbd5475c34129bdef77e6213f2625',\n",
    "             'sg:7fcd1071749a47db93c0e7e88f60cac1', 'sg:aa69113232db402ebb22eaf52e466fab', \n",
    "             'sg:1725e44c8c7e4c1a8de357ac72b86fc7', 'sg:42e1e922072640b885d2c2b85d41c7cd',\n",
    "             'sg:d013b30b726d4cdb97fa8581223d665e', 'sg:bfc1d1c72019403d998fd6d974b1533c', \n",
    "             'sg:be8d7ae61a5849c18b4ec624637ef56e', 'sg:48a424dd5d4d4bd79aec898ec3200615', \n",
    "             'sg:ada4e650c6974b1f83488996f7e80c16', 'sg:728b7626816d4cd3ac9fa00ff49c4e89', \n",
    "             'sg:d6b233e59da3456c84ae59ac720d31e4', 'sg:0e60b4d991e1456e873f7a9f24d8b1ca', \n",
    "             'sg:c113fccc441b4978b5210d54b74936bd']\n",
    "get_median_buckets_bar = get_median_buckets_dwell_for_multiplaces_20more(merged_data,weeks,bar15list)\n",
    "bar_bucket = pd.DataFrame.from_dict(get_median_buckets_bar)\n",
    "bar_bucket\n",
    "bar_bucket.insert(0, \"Date\", time, True)\n",
    "bar_bucket.to_excel(\"NEW_OUTBREAK_June_multibar_data_bucket_dwell_20ONLY.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6f44fa5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#  15 BARS **WITHOUT** OUTBREAKS JUNE/JULY GRAPH >20 min visits ONLY\n",
    "\n",
    "bar15list = ['sg:10e9963b97a64776bc203651c859cf1d','sg:e813a8d5c0a845dfa5c0a969f12171d2',\n",
    "             'sg:32bff382102f4723a333f4e39df8d2a4', 'sg:978a86e23a794016bb7f51c08472ecb3', \n",
    "             'sg:52cfdc1bbbcb43609c24eb64fafcfcbc', 'sg:5687326369e14482aa28fed4e9f9aed6',\n",
    "             'sg:6f19b19df8df451ab2a29d0501acec1a', 'sg:1392236dc36e4116a59d5050821fc9d7', \n",
    "             'sg:3a44efa98fbf492bb3ecf3b9867caaee', 'sg:03ea7dc8a5424213aeec28fa1ce3249a', \n",
    "             'sg:a10c919816ec49a183bc36a653094b36', 'sg:2556dddef52846ea8ca49dfb5df6f1c9', \n",
    "             'sg:6d8a519ea5834359b156ae144cc0d785', 'sg:9e876a578f3d494585e8ce6f1b23eb91', \n",
    "             'sg:7e69b8e12f3f49d98bac7bce7180e16c']\n",
    "get_median_buckets_bar = get_median_buckets_dwell_for_multiplaces_20more(merged_data,weeks,bar15list)\n",
    "bar_bucket = pd.DataFrame.from_dict(get_median_buckets_bar)\n",
    "bar_bucket\n",
    "bar_bucket.insert(0, \"Date\", time, True)\n",
    "bar_bucket.to_excel(\"NEW_NO_OUTBREAK_June_multibar_data_bucket_dwell_20ONLY.xlsx\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}