Json Dictionary.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# JSON dictionary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Date: August 2019 | Author: Hilary Goh | Team: Woodside, Intelligent Assets & Robotics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Only using data from one pump\n",
    "* cleanest signal\n",
    "* Ground truth available\n",
    "* ON & OFF state\n",
    "\n",
    "###### OPAM:1473 | FLOC: AU21.A6424AP7 | Thing ID: 00:80:00:00:04:01:21:C0 \n",
    "###### PHD tag: PGP.64FI491.DACA.PV | Group: Membrane Biological Reactor | GTm: m3/hr\n",
    "\n",
    "---\n",
    "\n",
    "Test a random forest, aim > 80% accuracy\n",
    "\n",
    "Can it select useful features?\n",
    "\n",
    "Will it just use temperature - easy feature\n",
    "\n",
    "###### Data source:\n",
    "* saved json from OnOff_c0.ipynb\n",
    "* var timestart=\"2019-07-07 00:00:00\"\n",
    "* var timeend=\"2019-07-27 23:55:00\"\n",
    "* no filtering - full raw dataset\n",
    "\n",
    "\n",
    "Test/Train/Validate\n",
    "* need to set aside a test set (unseen data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Import libraries and modules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import pandas as pd\n",
    "import pandas_profiling\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Examine dicts and lists\n",
    "* check timestamp conversion\n",
    "* check array (correct columns, correct index)\n",
    "* split pandas dataframe\n",
    "* check keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('c0.json','r') as fp: #open the file for viewing, not using pandas just yet\n",
    "     rawr = json.load(fp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['id', 'start', 'end', 'V1', 'V2', 'V3', 'T', 'gotData', 'groundtruth', 'sensorId', 'sensorType', 'QUUUUuery'])"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr.keys() #check for dictionaries and lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "00:80:00:00:04:01:21:c0\n",
      "2019-07-07 00:00:00\n",
      "2019-07-27 23:55:00\n",
      "False\n",
      "0\n",
      "T\n"
     ]
    }
   ],
   "source": [
    "print (rawr['id']) #should only be one sensor ID\n",
    "print (rawr['start'])\n",
    "print (rawr['end'])\n",
    "print (rawr['gotData'])\n",
    "print (rawr['sensorId'])\n",
    "print (rawr['sensorType'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['PHD', 'start', 'end', 'url', 'PHDhits', 'OnOff', 'ts', 'value'])"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['groundtruth'].keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PGP.64FI491.DACA.PV\n",
      "2019-07-06 16:00:00\n",
      "2019-07-27 15:55:00\n",
      "https://api-timeseries.woodside.io/api/v1/tag?query={%22start_time%22:%222019-07-06 16:00:00%22,%22end_time%22:%222019-07-27 15:55:00%22,%22tags%22:[{%22name%22:%22PGP.64FI491.DACA.PV%22}],%22format%22:%22json%22}\n"
     ]
    }
   ],
   "source": [
    "print (rawr['groundtruth']['PHD'])\n",
    "print (rawr['groundtruth']['start'])\n",
    "print (rawr['groundtruth']['end'])\n",
    "print (rawr['groundtruth']['url'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'name': 'PGP.64FI491.DACA.PV',\n",
       "  'value': 109.1254,\n",
       "  'time': '2019-07-06 16:01:00'},\n",
       " {'name': 'PGP.64FI491.DACA.PV',\n",
       "  'value': 108.9785,\n",
       "  'time': '2019-07-06 16:02:00'},\n",
       " {'name': 'PGP.64FI491.DACA.PV',\n",
       "  'value': 109.0653,\n",
       "  'time': '2019-07-06 16:03:00'},\n",
       " {'name': 'PGP.64FI491.DACA.PV',\n",
       "  'value': 108.9085,\n",
       "  'time': '2019-07-06 16:04:00'}]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['groundtruth']['PHDhits'][0:4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1, 1, 1, 1]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['groundtruth']['OnOff'][0:4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2019-7-7 00:01:00',\n",
       " '2019-7-7 00:02:00',\n",
       " '2019-7-7 00:03:00',\n",
       " '2019-7-7 00:04:00',\n",
       " '2019-7-7 00:05:00',\n",
       " '2019-7-7 00:06:00',\n",
       " '2019-7-7 00:07:00',\n",
       " '2019-7-7 00:08:00',\n",
       " '2019-7-7 00:09:00',\n",
       " '2019-7-7 00:10:00']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['groundtruth']['ts'][0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[109.1254,\n",
       " 108.9785,\n",
       " 109.0653,\n",
       " 108.9085,\n",
       " 108.9919,\n",
       " 108.815,\n",
       " 109.0453,\n",
       " 109.1154,\n",
       " 109.0953,\n",
       " 109.1187]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['groundtruth']['value'][0:10] #flowrate, m3/hr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['size', 'from', 'query', 'sort'])"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['QUUUUuery'].keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2500\n",
      "0\n",
      "[{'Timestamp': 'asc'}]\n"
     ]
    }
   ],
   "source": [
    "print (rawr['QUUUUuery']['size'])\n",
    "print (rawr['QUUUUuery']['from'])\n",
    "print (rawr['QUUUUuery']['sort'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['bool'])"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['QUUUUuery']['query'].keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['must', 'filter'])"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['QUUUUuery']['query']['bool'].keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'match_phrase': {'TagName': '00:80:00:00:04:01:21:c0'}},\n",
       " {'match_phrase': {'SensorType': 'T'}}]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['QUUUUuery']['query']['bool']['must']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['range'])"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['QUUUUuery']['query']['bool']['filter'].keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Timestamp': {'gte': '2019-07-07 00:00:00',\n",
       "  'lte': '2019-07-27 23:55:00',\n",
       "  'time_zone': '+08:00',\n",
       "  'format': 'yyyy-MM-dd HH:mm:ss'}}"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['QUUUUuery']['query']['bool']['filter']['range']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Temperature Dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['gotData', 'hits', 'data'])"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['T'].keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['T']['gotData']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'_index': 'iottimeseries_temperature',\n",
       "  '_type': '_doc',\n",
       "  '_id': 'NmYGyGsB3zul1noaseq0',\n",
       "  '_score': None,\n",
       "  '_source': {'MoteId': '00:80:00:00:04:01:21:c0',\n",
       "   'DeploymentId': -1,\n",
       "   'TagName': '00:80:00:00:04:01:21:c0--1-T1u',\n",
       "   'SensorId': 'T1u',\n",
       "   'Timestamp': 1562429007,\n",
       "   'TagValue': '23.5',\n",
       "   'Rssi': -75,\n",
       "   'Voltage': 3.08,\n",
       "   'MsgNo': 873,\n",
       "   'MsgNoPersist': 877,\n",
       "   'SensorType': 'T'},\n",
       "  'sort': [1562429007000]}]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['T']['hits'][0:1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['T1', 'T2', 'T3', 'OnOff'])"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['T']['data'].keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['ts', 'T'])"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['T']['data']['T1'].keys() #nb another T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2019-7-24 13:06:10', '2019-7-24 13:36:10']"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['T']['data']['T1']['ts'][-2:] #check timestamp format for temperature\n",
    "# readings every half hour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['23.5', '23.1', '23', '22.9', '22.8', '22.7', '22.6', '22.5', '22.3', '22.3']"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['T']['data']['T1']['T'][0:10] #check temperature values\n",
    "# readings every half hour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1, 1, 1, 1, 1]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['T']['data']['OnOff'][0:5] #Brad's on/off detector"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Vibration dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['gotData', 'hits', 'data'])"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['V1'].keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['freq', 'amp', 'ts', 'OnOff'])"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['V1']['data'].keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[320, 13, 5, 11, 213, 320, 5, 213, 11, 13]"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['V1']['data']['freq'][0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[213, 13, 11, 533, 8, 16, 13, 213, 8, 533]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['V2']['data']['freq'][0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[18, 5, 30, 107, 7, 18, 30, 5, 7, 107]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['V3']['data']['freq'][0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[0.06952,\n",
       " 0.07669,\n",
       " 0.07902,\n",
       " 0.0855,\n",
       " 0.10278,\n",
       " 0.05162,\n",
       " 0.05633,\n",
       " 0.07594,\n",
       " 0.0872,\n",
       " 0.08946]"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['V1']['data']['amp'][0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2019-7-7 00:03:56',\n",
       " '2019-7-7 00:03:56',\n",
       " '2019-7-7 00:03:56',\n",
       " '2019-7-7 00:03:56',\n",
       " '2019-7-7 00:03:56',\n",
       " '2019-7-7 00:33:55',\n",
       " '2019-7-7 00:33:55',\n",
       " '2019-7-7 00:33:55',\n",
       " '2019-7-7 00:33:55',\n",
       " '2019-7-7 00:33:55']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['V1']['data']['ts'][0:10] #check timestamp format for vib 1\n",
    "# readings every half hour, top 5 peaks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1, 1, 1, 1, 1, 1, 1, 1, 1, 1]"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['V1']['data']['OnOff'][0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2019-7-7 00:04:50',\n",
       " '2019-7-7 00:04:50',\n",
       " '2019-7-7 00:04:50',\n",
       " '2019-7-7 00:04:50',\n",
       " '2019-7-7 00:04:50',\n",
       " '2019-7-7 00:34:53',\n",
       " '2019-7-7 00:34:53',\n",
       " '2019-7-7 00:34:53',\n",
       " '2019-7-7 00:34:53',\n",
       " '2019-7-7 00:34:53']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawr['V3']['data']['ts'][0:10] #check timestamp format for vib 1\n",
    "# readings every half hour, top 5 peaks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}