Mobility Reports.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "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": 2,
   "metadata": {},
   "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": [
    "conn = psycopg2.connect(host=\"us-ppostgres02.uservices.umn.edu\",\n",
    "                        port=\"5432\",database=\"safegraph\", \n",
    "                        user=\"lixx4266\", password=\"rvs87bum1ya4jetey\")\n",
    "cursor = conn.cursor()\n",
    "cursor.execute(\"select relname from pg_class where relkind='r' and relname !~ '^(pg_|sql_)';\")\n",
    "print (cursor.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"select * from weekly_patterns_v2a where region = 'MN'\"\n",
    "sql2 = \"select * from core_places where region = 'MN'\"\n",
    "weekly_v2 = sqlio.read_sql_query(sql, conn)\n",
    "core_pls = sqlio.read_sql_query(sql2, conn)\n",
    "print (list(weekly_v2.columns))\n",
    "print (list(core_pls.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "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_v2,core_pls,left_on='safegraph_place_id',right_on='safegraph_place_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = merged_data['date_range_start'].drop_duplicates()\n",
    "# df = merged_data['date_range_end'].drop_duplicates()\n",
    "print (df.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "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",
    "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",
    "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 (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())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "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']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_median_buckets_grocery = get_overall_dwell_time(merged_data,weeks,'445110')\n",
    "grocery_bucket = pd.DataFrame.from_dict(get_median_buckets_grocery)\n",
    "grocery_bucket.insert(0, \"Date\", time, True)\n",
    "grocery_bucket.to_excel(\"grocery_bucket_dwell.xlsx\")\n",
    "\n",
    "get_median_buckets_restaurant = get_overall_dwell_time(merged_data,weeks,'722511')\n",
    "restaurant_bucket = pd.DataFrame.from_dict(get_median_buckets_restaurant)\n",
    "restaurant_bucket.insert(0, \"Date\", time, True)\n",
    "restaurant_bucket.to_excel(\"restaurant_bucket_dwell.xlsx\")\n",
    "\n",
    "get_median_buckets_hardware = get_overall_dwell_time(merged_data,weeks,'444130')\n",
    "hardware_bucket = pd.DataFrame.from_dict(get_median_buckets_hardware)\n",
    "hardware_bucket.insert(0, \"Date\", time, True)\n",
    "hardware_bucket.to_excel(\"hardware_bucket_dwell.xlsx\")\n",
    "\n",
    "get_median_buckets_hardware = get_overall_dwell_time(merged_data,weeks,'722410')\n",
    "hardware_bucket = pd.DataFrame.from_dict(get_median_buckets_hardware)\n",
    "hardware_bucket.insert(0, \"Date\", time, True)\n",
    "hardware_bucket.to_excel(\"bars_bucket_dwell.xlsx\")\n",
    "\n",
    "get_median_buckets_grocery = get_overall_dwell_time(merged_data,weeks,'445310')\n",
    "grocery_bucket = pd.DataFrame.from_dict(get_median_buckets_grocery)\n",
    "grocery_bucket.insert(0, \"Date\", time, True)\n",
    "grocery_bucket.to_excel(\"beer_wine_bucket_dwell.xlsx\")\n",
    "\n",
    "get_median_buckets_restaurant = get_overall_dwell_time(merged_data,weeks,'453991')\n",
    "restaurant_bucket = pd.DataFrame.from_dict(get_median_buckets_restaurant)\n",
    "restaurant_bucket.insert(0, \"Date\", time, True)\n",
    "restaurant_bucket.to_excel(\"tobacco_bucket_dwell.xlsx\")\n",
    "\n",
    "get_median_buckets_hardware = get_overall_dwell_time(merged_data,weeks,'813110')\n",
    "hardware_bucket = pd.DataFrame.from_dict(get_median_buckets_hardware)\n",
    "hardware_bucket.insert(0, \"Date\", time, True)\n",
    "hardware_bucket.to_excel(\"religious_bucket_dwell.xlsx\")\n",
    "\n",
    "get_median_buckets_hardware = get_overall_dwell_time(merged_data,weeks,'493110')\n",
    "hardware_bucket = pd.DataFrame.from_dict(get_median_buckets_hardware)\n",
    "hardware_bucket.insert(0, \"Date\", time, True)\n",
    "hardware_bucket.to_excel(\"general_warehouse_bucket_dwell.xlsx\")\n",
    "\n",
    "get_median_buckets_hardware = get_overall_dwell_time(merged_data,weeks,'492110')\n",
    "hardware_bucket = pd.DataFrame.from_dict(get_median_buckets_hardware)\n",
    "hardware_bucket.insert(0, \"Date\", time, True)\n",
    "hardware_bucket.to_excel(\"couriers_bucket_dwell.xlsx\")\n",
    "\n",
    "get_median_buckets_hardware = get_overall_dwell_time(merged_data,weeks,'423990')\n",
    "hardware_bucket = pd.DataFrame.from_dict(get_median_buckets_hardware)\n",
    "hardware_bucket.insert(0, \"Date\", time, True)\n",
    "hardware_bucket.to_excel(\"miscellaneous_durable_bucket_dwell.xlsx\")\n",
    "\n",
    "get_median_buckets_restaurant = get_overall_dwell_time(merged_data,weeks,'722513')\n",
    "restaurant_bucket = pd.DataFrame.from_dict(get_median_buckets_restaurant)\n",
    "restaurant_bucket.insert(0, \"Date\", time, True)\n",
    "restaurant_bucket.to_excel(\"limited_restaurant_bucket_dwell.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_length(bucket_list):\n",
    "    list_1 = []\n",
    "    list_2 = []\n",
    "    list_3 = []\n",
    "    list_4 = []\n",
    "    list_5 = []\n",
    "    for dwell_time in bucket_list:\n",
    "        list_1.append(dwell_time.get('<5'))\n",
    "        list_3.append(dwell_time.get('21-60'))\n",
    "        list_5.append(dwell_time.get('>240'))\n",
    "        if len(dwell_time) == 7:\n",
    "            list_2.append(dwell_time.get('5-10') + dwell_time.get('11-20'))\n",
    "            list_4.append(dwell_time.get('61-120') + dwell_time.get('121-240'))\n",
    "        elif len(dwell_time) == 5:\n",
    "            list_2.append(dwell_time.get('5-20'))  \n",
    "            list_4.append(dwell_time.get('61-240'))\n",
    "    \n",
    "    return (sum(list_1),sum(list_2),sum(list_3),sum(list_4),sum(list_5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_names(merged_data,code_list,category):\n",
    "    name_list = []\n",
    "    for code in code_list:\n",
    "        name_list.append(merged_data.loc[merged_data[category] == code]['location_name'].iloc[0])\n",
    "    return name_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_visits(merged_data,category):\n",
    "    x = top_sorted_by_buckets(merged_data,category,'bucketed_dwell_times')\n",
    "    bucket_sum_table = x.first()[category]\n",
    "    bucket_sum_table = pd.DataFrame(bucket_sum_table)\n",
    "    print (bucket_sum_table)\n",
    "    safegraph_list = x.first()[category].tolist()\n",
    "\n",
    "    list_1 = []\n",
    "    list_2 = []\n",
    "    list_3 = []\n",
    "    list_4 = []\n",
    "    list_5 = []\n",
    "\n",
    "    for code in safegraph_list:\n",
    "        bucket_list = x.get_group(code)['bucketed_dwell_times'].tolist()\n",
    "#         print (bucket_list)\n",
    "        sum_list_1,sum_list_2,sum_list_3,sum_list_4,sum_list_5 = check_length(bucket_list)\n",
    "        list_1.append(sum_list_1)\n",
    "        list_2.append(sum_list_2)\n",
    "        list_3.append(sum_list_3)\n",
    "        list_4.append(sum_list_4)\n",
    "        list_5.append(sum_list_5)\n",
    "\n",
    "    bucket_sum_table['<5'] = list_1\n",
    "    bucket_sum_table['5-20'] = list_2\n",
    "    bucket_sum_table['21-60'] = list_3\n",
    "    bucket_sum_table['61-240'] = list_4\n",
    "    bucket_sum_table['>240'] = list_5\n",
    "    \n",
    "    print (bucket_sum_table)\n",
    "    \n",
    "    bucket_sum_table['Total_Long_Visits'] = bucket_sum_table['21-60'] + bucket_sum_table['61-240'] + bucket_sum_table['>240']\n",
    "    bucket_sum_table['Total_Overall_Visits'] = bucket_sum_table['<5'] + bucket_sum_table['5-20'] + bucket_sum_table['21-60'] + bucket_sum_table['61-240'] + bucket_sum_table['>240']\n",
    "    \n",
    "    long_sorted = bucket_sum_table.sort_values(by='Total_Long_Visits',ascending=False).reset_index(drop=True)[0:10]\n",
    "    overall_sorted = bucket_sum_table.sort_values(by='Total_Overall_Visits',ascending=False).reset_index(drop=True)[0:10]\n",
    "    \n",
    "    long_visit_frame = long_sorted[[category,'Total_Long_Visits']]\n",
    "    overall_sorted_frame = overall_sorted[[category,'Total_Overall_Visits']]\n",
    "    \n",
    "    return long_visit_frame, overall_sorted_frame    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def write_excel(dataframe_dict,time,filename):\n",
    "    visits = pd.DataFrame.from_dict(dataframe_dict)\n",
    "    print (visits)\n",
    "    visits.insert(0, \"Date\", time, True)\n",
    "    visits.to_excel(filename+\".xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "long_visits_name_frame,overall_visits_name_frame = get_visits(merged_data,'safegraph_place_id')\n",
    "long_visits_name_frame_1,overall_visits_name_frame_1 = get_visits(merged_data,'sub_category')\n",
    "\n",
    "naics_category_long = long_visits_name_frame_1['sub_category'].tolist()\n",
    "naics_category_overall = overall_visits_name_frame_1['sub_category'].tolist()\n",
    "\n",
    "visits_long = long_visits_name_frame_1['Total_Long_Visits'].tolist()\n",
    "visits_long_float = [float(i) for i in visits_long]\n",
    "visits_overall = overall_visits_name_frame_1['Total_Overall_Visits'].tolist()\n",
    "visits_overall_float = [float(i) for i in visits_overall]\n",
    "\n",
    "df_bar_long2 = pd.DataFrame({'>20': visits_long_float, 'Category' : naics_category_long})\n",
    "df_bar_long2.plot.barh(x = 'Category', y = '>20')\n",
    "df_bar_overall2 = pd.DataFrame({'Total Visits': visits_overall_float, 'Category' : naics_category_overall})\n",
    "df_bar_overall2.plot.barh(x = 'Category', y = 'Total Visits')\n",
    "\n",
    "print (df_bar_long2)\n",
    "print (df_bar_overall2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "poi_category_long = long_visits_name_frame['safegraph_place_id'].tolist()\n",
    "poi_long_visits_name_list = get_names(merged_data,poi_category_long,'safegraph_place_id')\n",
    "poi_category_overall = overall_visits_name_frame['safegraph_place_id'].tolist()\n",
    "poi_overall_visits_name_list = get_names(merged_data,poi_category_overall,'safegraph_place_id')\n",
    "\n",
    "visits_long_poi = long_visits_name_frame['Total_Long_Visits'].tolist()\n",
    "visits_long_poi_float = [float(i) for i in visits_long]\n",
    "visits_poi_overall = overall_visits_name_frame['Total_Overall_Visits'].tolist()\n",
    "visits_poi_overall_float = [float(i) for i in visits_overall]\n",
    "\n",
    "df_bar_long1 = pd.DataFrame({'>20': visits_long_poi_float, 'Category' : poi_long_visits_name_list})\n",
    "df_bar_long1.plot.barh(x = 'Category', y = '>20')\n",
    "df_bar_overall1 = pd.DataFrame({'Total Visits': visits_overall_float, 'Category' : poi_overall_visits_name_list})\n",
    "df_bar_overall1.plot.barh(x = 'Category', y = 'Total Visits')\n",
    "\n",
    "print (df_bar_long1)\n",
    "print (df_bar_overall1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "naics_category_long = long_visits_name_frame_1['sub_category'].tolist()\n",
    "naics_category_overall = overall_visits_name_frame_1['sub_category'].tolist()\n",
    "\n",
    "weekly_long_dict = get_weekly_long_data(merged_data,'sub_category',naics_category_long)\n",
    "print (weekly_long_dict)\n",
    "weekly_overall_dict = get_weekly_overall_data(merged_data,'sub_category',naics_category_overall)\n",
    "print (weekly_overall_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "poi_category_long = long_visits_name_frame['safegraph_place_id'].tolist()\n",
    "poi_category_overall = overall_visits_name_frame['safegraph_place_id'].tolist()\n",
    "\n",
    "weekly_long_dict_poi = get_weekly_long_data(merged_data,'safegraph_place_id',poi_category_long)\n",
    "print (weekly_long_dict_poi)\n",
    "weekly_overall_dict_poi = get_weekly_overall_data(merged_data,'safegraph_place_id',poi_category_overall)\n",
    "print (weekly_overall_dict_poi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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": 4
}