Chennai_scratch_pad.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2019-08-13 03:13:05,265 - DEBUG - CognicityLoader constructed\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Int64Index([161, 174, 175, 182, 184, 192, 217, 244, 315, 352,\n",
       "            ...\n",
       "            595, 590, 593, 597, 566, 584, 582, 586, 598, 588],\n",
       "           dtype='int64', name='pkey', length=356)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from loaders.cognicity_loader import CognicityLoader\n",
    "import chennai_config\n",
    "\n",
    "config = chennai_config.config\n",
    "loader = CognicityLoader(config)\n",
    "df = loader.get_flood_depth()\n",
    "\n",
    "a = loader.config[\"flood_pkeys\"]\n",
    "b = loader.config[\"no_flood_pkeys\"]\n",
    "all_keys = a.union(b)\n",
    "diff = pd.Index(all_keys).difference(df.index)\n",
    "\n",
    "blah = pd.DataFrame(data=np.array([0]*diff.size), index=diff, columns=[\"flood_depth\"]).rename_axis(index=\"pkey\")\n",
    "blah = blah.append(df)\n",
    "blah.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "\n",
    "start_known_flood = \"'2017-11-01 00:00:35.630000-04:00'\" \n",
    "end_known_flood = \"'2017-11-07 00:00:35.630000-04:00'\"\n",
    "\n",
    "chennai_all_data = pd.read_sql_query('''\n",
    "    SELECT pkey, created_at, text, disaster_type, report_data, tags FROM riskmap.all_reports \n",
    "''', params={\"start_date\": start_known_flood, \"end_date\": end_known_flood}, con=defaultConfig[\"database_engine\"], index_col=\"pkey\")\n",
    "pd.options.display.max_rows = None\n",
    "pd.options.display.max_colwidth = 1000\n",
    "#from IPython.display import display\n",
    "# chennai_all_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "set(chennai_all_data.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "res = np.zeros((10, 1))\n",
    "res[0] = 1\n",
    "res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_data_bin_by_minute(start_date, end_date, interval=\"'900 minute'\"):\n",
    "    \"\"\" Gets data from sql database between start_date and end_date\n",
    "    Args: \n",
    "        start_date (str): the start date and time as a ISO8601 string\n",
    "        end_date (str):  the end date and time as an ISO8601 string\n",
    "        interval (str): a postgresql interval string\n",
    "\n",
    "    Returns: \n",
    "        Pandas dataframe, with the index being a date and the 'count' column \n",
    "        saying how many flood reports were received on that interval\n",
    "        Zero values are included for intervals that do not have any reports\n",
    "    \"\"\"\n",
    "    date_trunc_to = \"minute\"\n",
    "\n",
    "    num_reports_with_zeros = pd.read_sql_query('''\n",
    "        SELECT date, COALESCE(count, NULL, 0) as count FROM \n",
    "                (SELECT date_trunc(%(date_trunc_to)s, offs) as date FROM \n",
    "                        generate_series(\n",
    "                            %(start_date)s::timestamptz,\n",
    "                            %(end_date)s::timestamptz,\n",
    "                            %(interval)s::interval\n",
    "                            ) as offs ORDER BY date ASC) empty_hours\n",
    "        LEFT JOIN \n",
    "                (select date_trunc(%(date_trunc_to)s, created_at), count(pkey) \n",
    "                   from riskmap.all_reports \n",
    "                     WHERE text  NOT SIMILAR To '%%(T|t)(E|e)(S|s)(T|t)%%' \n",
    "                     GROUP BY date_trunc(%(date_trunc_to)s, created_at)\n",
    "                   ) no_test \n",
    "                   ON date = date_trunc\n",
    "    ''', params={\"start_date\":start_date, \"end_date\":end_date, \"interval\":interval, \"date_trunc_to\":date_trunc_to}, con=engine, index_col=\"date\", parse_dates={\"date\":{\"utc\":True}})\n",
    "    return num_reports_with_zeros"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = get_data_bin_by_minute(start_known_flood, end_known_flood)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mock = pd.DataFrame({\"text\": [\"sentence test\"]},index=[125]).rename_axis(\"pkey\")\n",
    "mock.iloc[0][\"text\"]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 4, 32,  3])"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import torch\n",
    "t = torch.tensor(4)\n",
    "l = []\n",
    "l.append(t)\n",
    "l.append(32)\n",
    "l.append(3)\n",
    "np.array(l)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[32, 3, 534]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "l.pop(0)\n",
    "l.append(534)\n",
    "l"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "502"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "l[-1] - l[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "_python",
   "language": "python",
   "name": "_python"
  },
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}