Outbreaks.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "31IigfVkmcIh"
      },
      "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 psycopg2\n",
        "import pandas.io.sql as sqlio"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "2ZcGvJoxmcIj"
      },
      "outputs": [],
      "source": [
        "#creating connection to database server\n",
        "conn = psycopg2.connect(host=\"us-ppostgres02.uservices.umn.edu\",\n",
        "                        port=\"5432\",database=\"safegraph\", \n",
        "                        user=\"lixx4266\", password=\"rvs87bum1ya4jetey\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "lFcU1aI8mcIk",
        "outputId": "5310385c-1fe7-4e0f-9ea9-bc77c9f87844"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "[('monthly_patterns',), ('weekly_patterns',), ('socialdistancing_v2',), ('core_places',), ('spatial_ref_sys',), ('weekly_patterns_v2a',), ('weekly_patterns_v2',)]\n"
          ]
        }
      ],
      "source": [
        "#creating handler to extract data\n",
        "cursor = conn.cursor()\n",
        "cursor.execute(\"select relname from pg_class where relkind='r' and relname !~ '^(pg_|sql_)';\")\n",
        "dataarray = cursor.fetchall()\n",
        "print(dataarray)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "CfeZIZxfmcIp"
      },
      "outputs": [],
      "source": [
        "#sql = \"select placekey, safegraph_place_id, location_name, street_address, city, region, date_range_start, date_range_end, raw_visit_counts, raw_visitor_counts, visits_by_day, visits_by_each_hour, poi_cbg, median_dwell, bucketed_dwell_times, related_same_day_brand, related_same_week_brand, parent_safegraph_place_id from weekly_patterns_v2a where region = 'MN'\"\n",
        "sql = \"select safegraph_place_id, location_name, street_address, city, date_range_start, date_range_end, bucketed_dwell_times from weekly_patterns_v2a where region = 'MN'\"\n",
        "weekly_v2 = sqlio.read_sql_query(sql, conn)\n",
        "weekly_v2"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "L041AKfOmcIs",
        "outputId": "046f16b4-4c78-455c-ec75-0973090c860f"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "['safegraph_place_id', 'parent_safegraph_place_id', 'location_name', 'safegraph_brand_ids', 'brands', 'top_category', 'sub_category', 'naics_code', 'latitude', 'longitude', 'street_address', 'city', 'region', 'postal_code', 'iso_country_code', 'phone_number', 'open_hours', 'category_tags', 'geom']\n"
          ]
        }
      ],
      "source": [
        "sql2 = \"select * from core_places where region = 'MN'\"\n",
        "#sql2 = \"select * from core_places where region = 'MN'LIMIT 5000\"\n",
        "core_pls = sqlio.read_sql_query(sql2, conn)\n",
        "core_pls\n",
        "print(list(core_pls.columns))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Yr3El173mcIs",
        "outputId": "aa993f0a-94cd-4bfc-a597-58cecb64ab0c"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "['placekey', 'safegraph_place_id', 'location_name', 'street_address', 'city', 'region', 'date_range_start', 'date_range_end', 'raw_visit_counts', 'median_dwell', 'bucketed_dwell_times', 'parent_safegraph_place_id', 'top_category', 'sub_category', 'naics_code', 'latitude', 'longitude', 'phone_number', 'open_hours', 'category_tags', 'geom']\n"
          ]
        }
      ],
      "source": [
        "core_pls = core_pls[['safegraph_place_id','top_category','sub_category', 'naics_code', 'latitude', 'longitude','phone_number', 'open_hours', 'category_tags', 'geom']]\n",
        "merged_data = pd.merge(weekly_v3,core_pls,left_on='safegraph_place_id',right_on='safegraph_place_id') #replace with \"placekey\" later\n",
        "print(list(merged_data.columns))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "I-08CQnRmcIt",
        "outputId": "53bdb0a2-a0ad-49ae-c22c-e773ce671ece"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "['2019-04-22T00:00:00-05:00', '2019-04-29T00:00:00-05:00', '2021-05-03T00:00:00-05:00', '2021-05-10T00:00:00-05:00', '2021-05-17T00:00:00-05:00', '2021-05-24T00:00:00-05:00', '2020-10-12T00:00:00-05:00', '2020-10-05T00:00:00-05:00', '2020-09-28T00:00:00-05:00', '2020-05-11T00:00:00-05:00', '2020-05-18T00:00:00-05:00', '2020-05-25T00:00:00-05:00', '2020-06-01T00:00:00-05:00', '2020-06-08T00:00:00-05:00', '2020-06-15T00:00:00-05:00', '2020-06-22T00:00:00-05:00', '2020-06-29T00:00:00-05:00', '2020-07-06T00:00:00-05:00', '2020-07-13T00:00:00-05:00', '2020-07-20T00:00:00-05:00', '2020-07-27T00:00:00-05:00', '2020-08-03T00:00:00-05:00', '2020-08-10T00:00:00-05:00', '2020-08-17T00:00:00-05:00', '2020-08-24T00:00:00-05:00', '2020-08-31T00:00:00-05:00', '2020-09-07T00:00:00-05:00', '2020-09-14T00:00:00-05:00', '2020-09-21T00:00:00-05:00', '2020-03-02T00:00:00-06:00', '2020-03-09T00:00:00-05:00', '2020-03-16T00:00:00-05:00', '2020-03-23T00:00:00-05:00', '2020-03-30T00:00:00-05:00', '2020-04-06T00:00:00-05:00', '2020-04-13T00:00:00-05:00', '2020-04-20T00:00:00-05:00', '2020-04-27T00:00:00-05:00', '2020-05-04T00:00:00-05:00', '2020-11-16T00:00:00-06:00', '2020-11-23T00:00:00-06:00', '2020-11-30T00:00:00-06:00', '2020-12-07T00:00:00-06:00', '2020-10-19T00:00:00-05:00', '2020-12-14T00:00:00-06:00', '2020-12-21T00:00:00-06:00', '2020-12-28T00:00:00-06:00', '2020-11-09T00:00:00-06:00', '2020-11-02T00:00:00-06:00', '2020-10-26T00:00:00-05:00', '2021-01-04T00:00:00-06:00', '2021-01-11T00:00:00-06:00', '2021-01-18T00:00:00-06:00', '2021-01-25T00:00:00-06:00', '2021-02-01T00:00:00-06:00', '2021-02-08T00:00:00-06:00', '2021-02-15T00:00:00-06:00', '2021-02-22T00:00:00-06:00', '2021-03-01T00:00:00-06:00', '2021-03-08T00:00:00-06:00', '2021-03-15T00:00:00-05:00', '2021-03-22T00:00:00-05:00', '2021-03-29T00:00:00-05:00', '2021-04-05T00:00:00-05:00', '2021-04-12T00:00:00-05:00', '2021-04-19T00:00:00-05:00', '2021-04-26T00:00:00-05:00', '2018-12-31T00:00:00-06:00', '2019-01-07T00:00:00-06:00', '2019-01-14T00:00:00-06:00', '2019-01-21T00:00:00-06:00', '2019-01-28T00:00:00-06:00', '2019-02-04T00:00:00-06:00', '2019-02-11T00:00:00-06:00', '2019-02-18T00:00:00-06:00', '2019-02-25T00:00:00-06:00', '2019-03-04T00:00:00-06:00', '2019-03-11T00:00:00-05:00', '2019-03-18T00:00:00-05:00', '2019-03-25T00:00:00-05:00', '2019-04-01T00:00:00-05:00', '2019-04-08T00:00:00-05:00', '2019-04-15T00:00:00-05:00', '2019-04-22T00:00:00-06:00', '2019-04-29T00:00:00-06:00', '2020-05-11T00:00:00-06:00', '2020-05-18T00:00:00-06:00', '2020-05-25T00:00:00-06:00', '2020-06-01T00:00:00-06:00', '2020-06-08T00:00:00-06:00', '2020-06-15T00:00:00-06:00', '2020-06-22T00:00:00-06:00', '2020-06-29T00:00:00-06:00', '2020-07-06T00:00:00-06:00', '2020-07-13T00:00:00-06:00', '2020-07-20T00:00:00-06:00', '2020-07-27T00:00:00-06:00', '2020-08-03T00:00:00-06:00', '2020-08-10T00:00:00-06:00', '2020-08-17T00:00:00-06:00', '2020-08-24T00:00:00-06:00', '2020-08-31T00:00:00-06:00', '2020-09-07T00:00:00-06:00', '2020-09-14T00:00:00-06:00', '2020-09-21T00:00:00-06:00', '2020-03-02T00:00:00-07:00', '2020-03-09T00:00:00-06:00', '2020-03-16T00:00:00-06:00', '2020-03-23T00:00:00-06:00', '2020-03-30T00:00:00-06:00', '2020-04-06T00:00:00-06:00', '2020-04-13T00:00:00-06:00', '2020-04-20T00:00:00-06:00', '2020-04-27T00:00:00-06:00', '2020-05-04T00:00:00-06:00', '2018-12-31T00:00:00-07:00', '2019-01-07T00:00:00-07:00', '2019-01-14T00:00:00-07:00', '2019-01-21T00:00:00-07:00', '2019-01-28T00:00:00-07:00', '2019-02-04T00:00:00-07:00', '2019-02-11T00:00:00-07:00', '2019-02-18T00:00:00-07:00', '2019-02-25T00:00:00-07:00', '2019-03-04T00:00:00-07:00', '2019-03-11T00:00:00-06:00', '2019-03-18T00:00:00-06:00', '2019-03-25T00:00:00-06:00', '2019-04-01T00:00:00-06:00', '2019-04-08T00:00:00-06:00', '2019-04-15T00:00:00-06:00', '2020-08-31T00:00:00-04:00', '2020-09-21T00:00:00-04:00', '2019-05-06T00:00:00-05:00', '2019-04-29T00:00:00-04:00', '2021-05-03T00:00:00-04:00', '2021-05-10T00:00:00-04:00', '2021-05-17T00:00:00-04:00', '2021-05-24T00:00:00-04:00', '2020-10-05T00:00:00-04:00', '2020-09-28T00:00:00-04:00', '2020-05-18T00:00:00-04:00', '2020-05-25T00:00:00-04:00', '2020-06-08T00:00:00-04:00', '2020-06-15T00:00:00-04:00', '2020-06-22T00:00:00-04:00', '2020-06-29T00:00:00-04:00', '2020-07-06T00:00:00-04:00', '2020-07-13T00:00:00-04:00', '2020-07-20T00:00:00-04:00', '2020-07-27T00:00:00-04:00', '2020-08-03T00:00:00-04:00', '2020-08-10T00:00:00-04:00', '2020-08-17T00:00:00-04:00', '2020-08-24T00:00:00-04:00', '2020-09-07T00:00:00-04:00', '2020-03-02T00:00:00-05:00', '2020-03-09T00:00:00-04:00', '2020-03-16T00:00:00-04:00', '2020-03-23T00:00:00-04:00', '2020-04-06T00:00:00-04:00', '2020-04-13T00:00:00-04:00', '2020-04-20T00:00:00-04:00', '2020-04-27T00:00:00-04:00', '2020-05-04T00:00:00-04:00', '2020-11-16T00:00:00-05:00', '2020-11-30T00:00:00-05:00', '2020-12-07T00:00:00-05:00', '2020-12-28T00:00:00-05:00', '2020-11-02T00:00:00-05:00', '2020-10-26T00:00:00-04:00', '2021-01-04T00:00:00-05:00', '2021-01-18T00:00:00-05:00', '2021-02-01T00:00:00-05:00', '2021-02-15T00:00:00-05:00', '2021-03-01T00:00:00-05:00', '2021-03-08T00:00:00-05:00', '2021-03-15T00:00:00-04:00', '2021-03-29T00:00:00-04:00', '2021-04-05T00:00:00-04:00', '2018-12-31T00:00:00-05:00', '2019-01-07T00:00:00-05:00', '2019-01-14T00:00:00-05:00', '2019-01-21T00:00:00-05:00', '2019-01-28T00:00:00-05:00', '2019-02-04T00:00:00-05:00', '2019-02-11T00:00:00-05:00', '2019-02-18T00:00:00-05:00', '2019-02-25T00:00:00-05:00', '2019-03-04T00:00:00-05:00', '2019-03-11T00:00:00-04:00', '2019-03-18T00:00:00-04:00', '2019-03-25T00:00:00-04:00', '2019-04-01T00:00:00-04:00', '2019-04-08T00:00:00-04:00', '2019-04-15T00:00:00-04:00', '2020-10-12T00:00:00-04:00', '2020-06-01T00:00:00-04:00', '2020-09-14T00:00:00-04:00', '2020-11-23T00:00:00-05:00', '2020-10-19T00:00:00-04:00', '2020-12-14T00:00:00-05:00', '2020-12-21T00:00:00-05:00', '2021-01-11T00:00:00-05:00', '2021-02-08T00:00:00-05:00', '2021-02-22T00:00:00-05:00', '2021-03-22T00:00:00-04:00', '2021-04-12T00:00:00-04:00', '2021-04-19T00:00:00-04:00', '2021-04-26T00:00:00-04:00']\n"
          ]
        }
      ],
      "source": [
        "df = merged_data['date_range_start'].drop_duplicates()\n",
        "print(df.tolist())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "CrC_eymSmcIu"
      },
      "outputs": [],
      "source": [
        "def load_weeks(weeks,merged_data):\n",
        "    weeks = []\n",
        "\n",
        "#     week_4 = merged_data.loc[(merged_data['date_range_start'] == \"2019-1-14T00:00:00-04:00\") #count >44, has to be changed\n",
        "#                         | (merged_data['date_range_start'] == \"2019-1-14T00:00:00-05:00\") \n",
        "#                         | (merged_data['date_range_start'] == \"2019-1-14T00:00:00-06:00\")]\n",
        "#     weeks.append(week_4)\n",
        "    \n",
        "#     week_3 = merged_data.loc[(merged_data['date_range_start'] == \"2020-02-03T00:00:00-05:00\") #count >43, has to be changed\n",
        "#                         | (merged_data['date_range_start'] == \"2020-02-03T00:00:00-05:00\") \n",
        "#                         | (merged_data['date_range_start'] == \"2020-02-03T00:00:00-06:00\")]\n",
        "#     weeks.append(week_3)\n",
        "    \n",
        "#     week_2 = merged_data.loc[(merged_data['date_range_start'] == \"2020-02-10T00:00:00-06:00\")\n",
        "#                         | (merged_data['date_range_start'] == \"2020-02-10T00:00:00-05:00\") \n",
        "#                         | (merged_data['date_range_start'] == \"2020-02-10T00:00:00-07:00\")]\n",
        "#     weeks.append(week_2)\n",
        "    \n",
        "#     week_1 = merged_data.loc[(merged_data['date_range_start'] == \"2020-02-17T00:00:00-06:00\") \n",
        "#                         | (merged_data['date_range_start'] == \"2020-02-17T00:00:00-05:00\") \n",
        "#                         | (merged_data['date_range_start'] == \"2020-02-17T00:00:00-07:00\")]\n",
        "#     weeks.append(week_1) \n",
        "    \n",
        "#     week0 = merged_data.loc[(merged_data['date_range_start'] == \"2020-02-24T00:00:00-06:00\") \n",
        "#                         | (merged_data['date_range_start'] == \"2020-02-24T00:00:00-05:00\") \n",
        "#                         | (merged_data['date_range_start'] == \"2020-02-24T00:00:00-07:00\")]\n",
        "#     weeks.append(week0)\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",
        "    week31 = merged_data.loc[(merged_data['date_range_start'] == \"2020-09-28T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2020-09-28T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2020-09-28T00:00:00-06:00\")]\n",
        "    weeks.append(week31)\n",
        "\n",
        "    week32 = merged_data.loc[(merged_data['date_range_start'] == \"2020-10-05T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2020-10-05T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2020-10-05T00:00:00-06:00\")]\n",
        "    weeks.append(week32)\n",
        "\n",
        "    week33 = merged_data.loc[(merged_data['date_range_start'] == \"2020-10-12T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2020-10-12T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2020-10-12T00:00:00-06:00\")]\n",
        "    weeks.append(week33)\n",
        "\n",
        "    week34 = merged_data.loc[(merged_data['date_range_start'] == \"2020-10-19T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2020-10-19T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2020-10-19T00:00:00-06:00\")]\n",
        "    weeks.append(week34)\n",
        "\n",
        "    week35 = merged_data.loc[(merged_data['date_range_start'] == \"2020-10-26T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2020-10-26T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2020-10-26T00:00:00-06:00\")]\n",
        "    weeks.append(week35)\n",
        "\n",
        "    week36 = merged_data.loc[(merged_data['date_range_start'] == \"2020-11-02T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2020-11-02T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2020-11-02T00:00:00-06:00\")]\n",
        "    weeks.append(week36)\n",
        "\n",
        "    week37 = merged_data.loc[(merged_data['date_range_start'] == \"2020-11-09T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2020-11-09T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2020-11-09T00:00:00-06:00\")]\n",
        "    weeks.append(week37)\n",
        "\n",
        "    week38 = merged_data.loc[(merged_data['date_range_start'] == \"2020-11-16T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2020-11-16T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2020-11-16T00:00:00-06:00\")]\n",
        "    weeks.append(week38)\n",
        "\n",
        "    week39 = merged_data.loc[(merged_data['date_range_start'] == \"2020-11-23T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2020-11-23T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2020-11-23T00:00:00-06:00\")]\n",
        "    weeks.append(week39)\n",
        "\n",
        "    week40 = merged_data.loc[(merged_data['date_range_start'] == \"2020-11-30T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2020-11-30T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2020-11-30T00:00:00-06:00\")]\n",
        "    weeks.append(week40)\n",
        "\n",
        "    week41 = merged_data.loc[(merged_data['date_range_start'] == \"2020-12-07T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2020-12-07T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2020-12-07T00:00:00-06:00\")]\n",
        "    weeks.append(week41)\n",
        "\n",
        "    week42 = merged_data.loc[(merged_data['date_range_start'] == \"2020-12-14T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2020-12-14T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2020-12-14T00:00:00-06:00\")]\n",
        "    weeks.append(week42)\n",
        "\n",
        "    week43 = merged_data.loc[(merged_data['date_range_start'] == \"2020-12-21T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2020-12-21T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2020-12-21T00:00:00-06:00\")]\n",
        "    weeks.append(week43)\n",
        "\n",
        "    week44 = merged_data.loc[(merged_data['date_range_start'] == \"2020-12-28T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2020-12-28T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2020-12-28T00:00:00-06:00\")]\n",
        "    weeks.append(week44)\n",
        "\n",
        "    week45 = merged_data.loc[(merged_data['date_range_start'] == \"2021-01-04T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2021-01-04T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2021-01-04T00:00:00-06:00\")]\n",
        "    weeks.append(week45)\n",
        "\n",
        "    week46 = merged_data.loc[(merged_data['date_range_start'] == \"2021-01-11T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2021-01-11T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2021-01-11T00:00:00-06:00\")]\n",
        "    weeks.append(week46)\n",
        "\n",
        "    week47 = merged_data.loc[(merged_data['date_range_start'] == \"2021-01-18T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2021-01-18T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2021-01-18T00:00:00-06:00\")]\n",
        "    weeks.append(week47)\n",
        "\n",
        "    week48 = merged_data.loc[(merged_data['date_range_start'] == \"2021-01-25T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2021-01-25T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2021-01-25T00:00:00-06:00\")]\n",
        "    weeks.append(week48)\n",
        "\n",
        "    week49 = merged_data.loc[(merged_data['date_range_start'] == \"2021-02-01T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2021-02-01T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2021-02-01T00:00:00-06:00\")]\n",
        "    weeks.append(week49)\n",
        "\n",
        "    week50 = merged_data.loc[(merged_data['date_range_start'] == \"2021-02-08T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2021-02-08T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2021-02-08T00:00:00-06:00\")]\n",
        "    weeks.append(week50)\n",
        "\n",
        "    week51 = merged_data.loc[(merged_data['date_range_start'] == \"2021-02-15T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2021-02-15T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2021-02-15T00:00:00-06:00\")]\n",
        "    weeks.append(week51)\n",
        "\n",
        "    week52 = merged_data.loc[(merged_data['date_range_start'] == \"2021-02-22T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2021-02-22T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2021-02-22T00:00:00-06:00\")]\n",
        "    weeks.append(week52)\n",
        "\n",
        "    week53 = merged_data.loc[(merged_data['date_range_start'] == \"2021-03-01T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2021-03-01T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2021-03-01T00:00:00-06:00\")]\n",
        "    weeks.append(week53)\n",
        "\n",
        "    week54 = merged_data.loc[(merged_data['date_range_start'] == \"2021-03-08T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2021-03-08T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2021-03-08T00:00:00-06:00\")]\n",
        "    weeks.append(week54)\n",
        "\n",
        "    week55 = merged_data.loc[(merged_data['date_range_start'] == \"2021-03-15T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2021-03-15T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2021-03-15T00:00:00-06:00\")]\n",
        "    weeks.append(week55)\n",
        "\n",
        "    week56 = merged_data.loc[(merged_data['date_range_start'] == \"2021-03-22T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2021-03-22T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2021-03-22T00:00:00-06:00\")]\n",
        "    weeks.append(week56)\n",
        "\n",
        "    week57 = merged_data.loc[(merged_data['date_range_start'] == \"2021-03-29T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2021-03-29T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2021-03-29T00:00:00-06:00\")]\n",
        "    weeks.append(week57)\n",
        "\n",
        "    week58 = merged_data.loc[(merged_data['date_range_start'] == \"2021-04-05T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2021-04-05T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2021-04-05T00:00:00-06:00\")]\n",
        "    weeks.append(week58)\n",
        "\n",
        "    week59 = merged_data.loc[(merged_data['date_range_start'] == \"2021-04-12T00:00:00-04:00\") \n",
        "                        | (merged_data['date_range_start'] == \"2021-04-12T00:00:00-05:00\")\n",
        "                        | (merged_data['date_range_start'] == \"2021-04-12T00:00:00-06:00\")]\n",
        "    weeks.append(week59)\n",
        "\n",
        "    week60 = merged_data.loc[(merged_data['date_range_start'] == \"2021-04-19T00:00:00-04:00\") \n",
        "                         | (merged_data['date_range_start'] == \"2021-04-19T00:00:00-05:00\")\n",
        "                         | (merged_data['date_range_start'] == \"2021-04-19T00:00:00-06:00\")]\n",
        "    weeks.append(week60)\n",
        "    \n",
        "    week61 = merged_data.loc[(merged_data['date_range_start'] == \"2021-04-26T00:00:00-04:00\") \n",
        "                         | (merged_data['date_range_start'] == \"2021-04-26T00:00:00-05:00\")\n",
        "                         | (merged_data['date_range_start'] == \"2021-04-26T00:00:00-06:00\")]\n",
        "    weeks.append(week61)\n",
        "    \n",
        "    week62 = merged_data.loc[(merged_data['date_range_start'] == \"2021-05-03T00:00:00-04:00\") \n",
        "                         | (merged_data['date_range_start'] == \"2021-05-03T00:00:00-05:00\")\n",
        "                         | (merged_data['date_range_start'] == \"2021-05-03T00:00:00-06:00\")]\n",
        "    weeks.append(week62)\n",
        "\n",
        "    week63 = merged_data.loc[(merged_data['date_range_start'] == \"2021-05-10T00:00:00-04:00\") \n",
        "                         | (merged_data['date_range_start'] == \"2021-05-10T00:00:00-05:00\")\n",
        "                         | (merged_data['date_range_start'] == \"2021-05-10T00:00:00-06:00\")]\n",
        "    weeks.append(week63)\n",
        "\n",
        "    week64 = merged_data.loc[(merged_data['date_range_start'] == \"2021-05-17T00:00:00-04:00\") \n",
        "                         | (merged_data['date_range_start'] == \"2021-05-17T00:00:00-05:00\")\n",
        "                         | (merged_data['date_range_start'] == \"2021-05-17T00:00:00-06:00\")]\n",
        "    weeks.append(week64)\n",
        "\n",
        "    week65 = merged_data.loc[(merged_data['date_range_start'] == \"2021-05-24T00:00:00-04:00\") \n",
        "                         | (merged_data['date_range_start'] == \"2021-05-24T00:00:00-05:00\")\n",
        "                         | (merged_data['date_range_start'] == \"2021-05-24T00:00:00-06:00\")]\n",
        "    weeks.append(week65)\n",
        "    \n",
        "#     week66 = merged_data.loc[(merged_data['date_range_start'] == \"2021-05-31T00:00:00-04:00\") \n",
        "#                          | (merged_data['date_range_start'] == \"2021-05-31T00:00:00-05:00\")\n",
        "#                          | (merged_data['date_range_start'] == \"2021-05-31T00:00:00-06:00\")]\n",
        "#     weeks.append(week66)\n",
        "    \n",
        "#     week67 = merged_data.loc[(merged_data['date_range_start'] == \"2021-06-07T00:00:00-04:00\") \n",
        "#                          | (merged_data['date_range_start'] == \"2021-06-07T00:00:00-05:00\")\n",
        "#                          | (merged_data['date_range_start'] == \"2021-06-07T00:00:00-06:00\")]\n",
        "#     weeks.append(week67)\n",
        "    \n",
        "    #NEED TO ADD NEW WEEKS HERE TO BRING NEW DATA\n",
        "\n",
        "#     print(week_4['date_range_start'].count())\n",
        "#     print(week_3['date_range_start'].count())\n",
        "#     print(week_2['date_range_start'].count())\n",
        "#     print(week_1['date_range_start'].count())\n",
        "#     print(week0['date_range_start'].count())\n",
        "    print(week1['date_range_start'].count())\n",
        "    print(week2['date_range_start'].count())\n",
        "    print(week3['date_range_start'].count())\n",
        "    print(week4['date_range_start'].count())\n",
        "    print(week5['date_range_start'].count())\n",
        "    print(week6['date_range_start'].count())\n",
        "    print(week7['date_range_start'].count())\n",
        "    print(week8['date_range_start'].count())\n",
        "    print(week9['date_range_start'].count())\n",
        "    print(week10['date_range_start'].count())\n",
        "    print(week11['date_range_start'].count())\n",
        "    print(week12['date_range_start'].count())\n",
        "    print(week13['date_range_start'].count())\n",
        "    print(week14['date_range_start'].count())\n",
        "    print(week15['date_range_start'].count())\n",
        "    print(week16['date_range_start'].count())\n",
        "    print(week17['date_range_start'].count())\n",
        "    print(week18['date_range_start'].count())\n",
        "    print(week19['date_range_start'].count())\n",
        "    print(week20['date_range_start'].count())\n",
        "    print(week21['date_range_start'].count())\n",
        "    print(week22['date_range_start'].count())\n",
        "    print(week23['date_range_start'].count())\n",
        "    print(week24['date_range_start'].count())\n",
        "    print(week25['date_range_start'].count())\n",
        "    print(week26['date_range_start'].count())\n",
        "    print(week27['date_range_start'].count())\n",
        "    print(week28['date_range_start'].count())\n",
        "    print(week29['date_range_start'].count())\n",
        "    print(week30['date_range_start'].count())\n",
        "    print(week31['date_range_start'].count())\n",
        "    print(week32['date_range_start'].count())\n",
        "    print(week33['date_range_start'].count())\n",
        "    print(week34['date_range_start'].count())\n",
        "    print(week35['date_range_start'].count())\n",
        "    print(week36['date_range_start'].count())\n",
        "    print(week37['date_range_start'].count())\n",
        "    print(week38['date_range_start'].count())\n",
        "    print(week39['date_range_start'].count())\n",
        "    print(week40['date_range_start'].count())\n",
        "    print(week41['date_range_start'].count())\n",
        "    print(week42['date_range_start'].count())\n",
        "    print(week43['date_range_start'].count())\n",
        "    print(week44['date_range_start'].count())\n",
        "    print(week45['date_range_start'].count())\n",
        "    print(week46['date_range_start'].count())\n",
        "    print(week47['date_range_start'].count())\n",
        "    print(week48['date_range_start'].count())\n",
        "    print(week49['date_range_start'].count())\n",
        "    print(week50['date_range_start'].count())\n",
        "    print(week51['date_range_start'].count())\n",
        "    print(week52['date_range_start'].count())\n",
        "    print(week53['date_range_start'].count())\n",
        "    print(week54['date_range_start'].count())\n",
        "    print(week55['date_range_start'].count())\n",
        "    print(week56['date_range_start'].count())\n",
        "    print(week57['date_range_start'].count())\n",
        "    print(week58['date_range_start'].count())\n",
        "    print(week59['date_range_start'].count())\n",
        "    print(week60['date_range_start'].count())\n",
        "    print(week61['date_range_start'].count())\n",
        "    print(week62['date_range_start'].count())\n",
        "    print(week63['date_range_start'].count())\n",
        "    print(week64['date_range_start'].count())\n",
        "    print(week65['date_range_start'].count())\n",
        "#     print(week66['date_range_start'].count())\n",
        "    print(\"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\")\n",
        "    return weeks"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bixrTv6FmcI1",
        "outputId": "210c91ca-d2e2-47a1-fa04-f66e1572114e"
      },
      "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",
            "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",
            "60288\n",
            "60226\n",
            "60078\n",
            "59550\n",
            "59656\n",
            "59000\n",
            "57931\n",
            "57815\n",
            "56467\n",
            "58015\n",
            "57926\n",
            "58059\n",
            "56612\n",
            "57049\n",
            "57227\n",
            "57106\n",
            "56681\n",
            "56730\n",
            "56735\n",
            "56410\n",
            "56960\n",
            "57282\n",
            "57489\n",
            "57523\n",
            "57581\n",
            "57561\n",
            "58924\n",
            "58433\n",
            "58451\n",
            "58236\n",
            "58985\n",
            "58969\n",
            "59342\n",
            "59524\n",
            "59291\n",
            "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\n"
          ]
        }
      ],
      "source": [
        "weeks = [] #Main Weeks for All Visits\n",
        "weeks = load_weeks(weeks,merged_data)\n",
        "#'02/03-02/10','02/10-02/17','02/17-02/24','02/24-03/02',\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','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','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','08/17-08/24','08/24-08/31','08/31-09/07','09/07-09/14','09/14-09/21','09/21-09/28','09/28-10/05','10/05-10/12','10/12-10/19','10/19-10/26','10/26-11/02','11/02-11/09','11/09-11/16','11/16-11/23','11/23-11/30','11/30-12/07','12/07-12/14','12/14-12/21','12/21-12/28','12/28-01/04','01/04-01/11','01/11-01/18','01/18-01/25','01/25-02/01','02/01-02/08', '02/08-02/15', '02/15-02/22', '02/22-03/01', '03/01-03/08', '03/08-03/15', '03/15-03/22', '03/22-03/29', '03/29-04/05', '04/05-04/12', '04/12-04/19', '04/19-04/26', '04/26-5/03', '05/03-5/10', '05/10-5/17', '05/17-5/24', '05/24-5/31']"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "h33qs3gmmcI3"
      },
      "outputs": [],
      "source": [
        "def get_median_buckets_dwell(merged_data,weeks,code):\n",
        "    \n",
        "    list_1 = []\n",
        "    list_2 = []\n",
        "    list_3 = []\n",
        "    list_4 = []\n",
        "    list_5 = []\n",
        "    \n",
        "    global_dic = {}\n",
        "    count = 0\n",
        "    for week in weeks:\n",
        "        count = count + 1        \n",
        "        naics_table = week.loc[merged_data['naics_code'] == code]\n",
        "        dwell_list = naics_table[['bucketed_dwell_times']]['bucketed_dwell_times'].tolist()\n",
        "        list_1.append(sum([dwell_time.get('<5') for dwell_time in dwell_list]))\n",
        "        list_3.append(sum([dwell_time.get('21-60') for dwell_time in dwell_list]))\n",
        "        list_5.append(sum([dwell_time.get('>240') for dwell_time in dwell_list]))\n",
        "        if count > 39:   \n",
        "            list_2.append(sum([dwell_time.get('5-10') for dwell_time in dwell_list])+sum([dwell_time.get('11-20') for dwell_time in dwell_list])) \n",
        "            list_4.append(sum([dwell_time.get('61-120') for dwell_time in dwell_list])+sum([dwell_time.get('121-240') for dwell_time in dwell_list]))\n",
        "        else :\n",
        "            list_2.append(sum([dwell_time.get('5-20') for dwell_time in dwell_list]))  \n",
        "            list_4.append(sum([dwell_time.get('61-240') for dwell_time in dwell_list]))\n",
        "        \n",
        "    global_dic['<5'] = list_1\n",
        "    global_dic['5-20'] = list_2\n",
        "    global_dic['21-60'] = list_3\n",
        "    global_dic['61-240'] = list_4\n",
        "    global_dic['>240'] = list_5\n",
        "    \n",
        "    print (sum(global_dic['<5']))\n",
        "    print (sum(global_dic['5-20']))\n",
        "    print (sum(global_dic['21-60']))\n",
        "    print (sum(global_dic['61-240']))\n",
        "    print (sum(global_dic['>240']))\n",
        "    \n",
        "    return global_dic"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "sMgJM79imcI-"
      },
      "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]*50 #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",
        "            #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([dwell_time.get('21-60')+dwell_time.get('61-120')+dwell_time.get('121-240')+dwell_time.get('>240') for dwell_time in dwell_list])\n",
        "            else :\n",
        "                list_1[i] = list_1[i]+sum([dwell_time.get('21-60')+dwell_time.get('61-240')+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": null,
      "metadata": {
        "id": "O3jukmR2mcI_"
      },
      "outputs": [],
      "source": [
        "def get_median_buckets_dwell_for_multiplaces(merged_data,weeks,bars):\n",
        "    list_1 = [0]*50\n",
        "    list_2 = [0]*50\n",
        "    list_3 = [0]*50\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([dwell_time.get('21-60') for dwell_time in dwell_list])\n",
        "            list_3[i] = list_3[i]+sum([dwell_time.get('>240') for dwell_time in dwell_list])\n",
        "            if count > 39:   \n",
        "                list_2[i] = list_2[i]+sum([dwell_time.get('61-120') for dwell_time in dwell_list])+sum([dwell_time.get('121-240') for dwell_time in dwell_list])\n",
        "            else :\n",
        "                list_2[i] = list_2[i]+sum([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": null,
      "metadata": {
        "id": "AobhzkmImcJC"
      },
      "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_output/winter2020_results/NEW_OUTBREAK_June_multibar_data_bucket_dwell_20ONLY.xlsx\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Ztq30zBnmcJC"
      },
      "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_output/winter2020_results/NEW_NO_OUTBREAK_June_multibar_data_bucket_dwell_20ONLY.xlsx\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "MPuMCmHMmcJD"
      },
      "outputs": [],
      "source": [
        "#  15 BARS WITH COVID OUTBREAKS JUNE/JULY GRAPH \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(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_output/winter2020_results/OUTBREAK_June_multibar_data_bucket_dwell.xlsx\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "WLxzDc6kmcJE"
      },
      "outputs": [],
      "source": [
        "#  15 BARS **WITHOUT** OUTBREAKS JUNE/JULY GRAPH \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(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_output/winter2020_results/NO_OUTBREAK_June_multibar_data_bucket_dwell.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.6.8"
    },
    "colab": {
      "name": "SafeGraphBars_AdiCode.ipynb",
      "provenance": [],
      "collapsed_sections": []
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}