Siemens API Collect Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# API Data Collection\n",
    "\n",
    "This notebook includes the work-in-progress code to query from the Siemens API and push to the influxdb node. Eventually this code will be worked into a self-contained scripte or library that will run on a cloud-based or UWC network-based instance and be maintained as the middleware in the 3for2 Data collection process"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from pandas.io.json import json_normalize\n",
    "import re\n",
    "import math\n",
    "\n",
    "import json\n",
    "import timeit\n",
    "\n",
    "import datetime\n",
    "import time\n",
    "from urllib2 import Request, urlopen, URLError\n",
    "import csv\n",
    "import sys\n",
    "import pytz\n",
    "import pycurl\n",
    "import certifi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "requests.packages.urllib3.disable_warnings()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "meta = pd.read_csv(\"mapping-point-list\", index_col=\"Unnamed: 0\")\n",
    "apilink = #Desigo link goes here\n",
    "tz = pytz.timezone('Asia/Singapore')\n",
    "timeoutsecs = 60"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def authenticate(timeoutsecs):\n",
    "    payload = #'grant_type=password&username=login_goes_here&password=password_goes_here'\n",
    "    token_response = requests.post(tokenurl, data=payload, verify=False, timeout = timeoutsecs)\n",
    "    authtoken = token_response.text.split('\"')[3]\n",
    "    authtoken_input = str('Bearer '+authtoken)\n",
    "    head = {'Authorization': authtoken_input, 'Content-Type': 'application/json'}\n",
    "    return head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "head = authenticate(timeoutsecs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Authorization': 'Bearer AQAAANCMnd8BFdERjHoAwE_Cl-sBAAAAURa1O4yIX0OB6h1pqfRWAgAAAAACAAAAAAAQZgAAAAEAACAAAABFmXsMwL-U2tJPDF4cHGqH1thkFjuXUw5JQbzGSFghsgAAAAAOgAAAAAIAACAAAABeAawADRbbz0uFlR4e6gCUTJ3UKO5yFZ7xhIQTnQM2MCABAACTVVsQHkDmmS449-sF6Xkk-N7hWOWiI3mUnZ7_GFCRgJd-tZXoD2xrl8gumg-ssjbQMw78o34jA_1GJEBOrjLpN-6VUxwsQ0zF6Xg4eYIvjNUzGZ5cEoNlxTPBdLL519h6nib3viFW8Qjhud7x8R-I3JDvrbOAcD3vDHfOGyiHQYtoxd8HSn9oDUfZwDb_KQmbDeFxLnNPstv9q7nMyfURv9sDKhqPvqsuqyB9hHOGT33P3m5R9RowRQmQd3pjaXjnxCwyq-JzU4V3q23S1fv0Gzp_kI89hFg6J2LJqhb3bCszcvRjItYyHM7DQPgP-Lc2_KX8Ozak5uzdH3FZ3GkosT6X7y21APCC9tZ68p_o4wmTJ87w_yT5rN8NC_1JiWNAAAAA1UpgTaGaXKs0Xp-dzuKincmuxHz3AkBSgtKQW7HC51bNLzTjXpiDARwfm4Vhbzbx1J3xHMngJ8wgBj6KY4YOZg',\n",
       " 'Content-Type': 'application/json'}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "head"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set the parameters for the Influxdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Set the parameters for the Influxdb\n",
    "host = #influxDB url here\n",
    "port = #port number here\n",
    "login = #influx login here\n",
    "pw = #influxpw here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#json.dumps(meta.index.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#json.dumps(list(meta[\"ObjectId\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#meta[\"CollectorObjectOrPropertyId\"].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#meta.index#.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#meta[(pd.notnull(meta.index))].index#.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_seimensdata(meta, apilink, timeoutsecs):\n",
    "\n",
    "    #Authenticate and get a token\n",
    "    head = authenticate(timeoutsecs)\n",
    "    payload = json.dumps(meta[(pd.notnull(meta.index))].index.tolist())\n",
    "    #payload = json.dumps(meta[\"CollectorObjectOrPropertyId\"].tolist())\n",
    "    \n",
    "    #Grab the instantaneous data\n",
    "    #trenddata = requests.post(apilink+'values', data=payload, headers=head, verify=('uwc_bms.cer'), timeout = timeoutsecs)\n",
    "    trenddata = requests.post(apilink+'values', data=payload, headers=head, verify=False, timeout = timeoutsecs)\n",
    "\n",
    "    #Convert and flatten the json data\n",
    "    raw_data = json.loads(trenddata.content)\n",
    "    raw_data_flat = json_normalize(raw_data)\n",
    "    \n",
    "    raw_data_flat_named = raw_data_flat.join(meta[\"Name\"], on=\"OriginalObjectOrPropertyId\" )\n",
    "    raw_data_flat_named = raw_data_flat_named.convert_objects(convert_numeric=True)\n",
    "    \n",
    "    raw_data_flat_named_pivoted = pd.pivot_table(raw_data_flat_named, values=\"Value.Value\", columns=\"Name\", index=\"Value.Timestamp\")\n",
    "    raw_data_flat_named_pivoted.index = pd.to_datetime(raw_data_flat_named_pivoted.index)\n",
    "    raw_data_flat_named_pivoted = raw_data_flat_named_pivoted.tz_localize('utc').tz_convert('Asia/Singapore')\n",
    "    \n",
    "    raw_data_flat_named_pivoted_resampled = raw_data_flat_named_pivoted.resample('min')\n",
    "    raw_data_flat_named_pivoted_resampled.index.name = 'timestamp'\n",
    "        \n",
    "    return raw_data_flat_named_pivoted_resampled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def appendstring(tagname, pointname, meta):\n",
    "    if meta[meta.Name == pointname][tagname].isnull()[0]:\n",
    "        return \"\"\n",
    "    else:\n",
    "        return \",\"+str(tagname)+\"=\"+str(meta[meta.Name == pointname][tagname][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getstring(added_line, meta):\n",
    "    #Get the nanosecond timestamp\n",
    "    timestamp = added_line.index.tz_convert('utc').to_pydatetime()#.timestamp()\n",
    "    nan_ts = int((timestamp[0]-datetime.datetime(1970,1,1, tzinfo=pytz.utc)).total_seconds()*1000000000)\n",
    "\n",
    "    #Create input string for influxdb\n",
    "    inputstring = \"\"\n",
    "    pointnamelist = list(added_line.columns)\n",
    "    for pointname in pointnamelist:\n",
    "        if math.isnan(added_line[pointname][0]):\n",
    "            #print \"Empty point:\"+pointname\n",
    "            continue\n",
    "        else:\n",
    "            try:\n",
    "                friendlypointname = re.sub(r'\\W+','_',pointname)\n",
    "                linestring = \"bmspoint,name=\"+str(friendlypointname)\\\n",
    "                +\",Area=\"+str(meta[meta.Name == pointname].Area[0])\\\n",
    "                +\",Type=\"+str(meta[meta.Name == pointname].Type[0])\\\n",
    "                +\",PointGroup=\"+str(meta[meta.Name == pointname].PointGroup[0])\\\n",
    "                +appendstring(\"Equip\", pointname, meta)\\\n",
    "                +appendstring(\"SubEquip1\", pointname, meta)\\\n",
    "                +appendstring(\"SubEquip2\", pointname, meta)\\\n",
    "                +\",PointType=\"+str(meta[meta.Name == pointname].PointType[0])\\\n",
    "                +\" value=\"+str(float(added_line[pointname][0]))\\\n",
    "                +\" \"+str(nan_ts)+\"\\n\"\n",
    "                inputstring += linestring\n",
    "            except:\n",
    "                e = sys.exc_info()[1]\n",
    "                print 'Error when creating an input line:', e\n",
    "                continue\n",
    "                \n",
    "    return inputstring"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def sendtoinflux(host, port, login, pw, inputstring):\n",
    "    apiURL = 'https://'+host+':'+port+'/write?u='+login+'&p='+pw+'&db=siemenstest'\n",
    "    c = pycurl.Curl()\n",
    "    c.setopt(c.URL, apiURL)\n",
    "    poststring = inputstring \n",
    "    c.setopt(c.POSTFIELDS, poststring)\n",
    "    c.setopt(pycurl.CAINFO, certifi.where())\n",
    "    #c.setopt(c.VERBOSE, True)\n",
    "    c.perform()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "added_line = get_seimensdata(meta, apilink, timeoutsecs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#added_line"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "timestamp\n",
       "2016-05-31 17:17:00+08:00    0.4\n",
       "Freq: T, Name: B10'Area3for2'Mtr'CGrpAHU'FCU1'Pwr, dtype: float64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "added_line[\"B10'Area3for2'Mtr'CGrpAHU'FCU1'Pwr\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def addvirtualmeters(added_line):\n",
    "    try:\n",
    "        added_line[\"ChillerPlant_KWperTon_Calculated\"] = added_line[\"B3'Flr7'C'ReC20'Efcy'Pwr\"] / added_line[\"B3'Flr7'C'ReC20'H'FullLd19\"] \n",
    "    except:\n",
    "        added_line[\"ChillerPlant_KWperTon_Calculated\"] = 0\n",
    "    try:\n",
    "        added_line[\"TotPrimAHUPwr\"] =  added_line[\"B10'Area3for2'Mtr'CGrpAHU'DOAS1'Pwr\"] + added_line[\"B10'Area3for2'Mtr'CGrpAHU'DOAS2'Pwr\"]  + added_line[\"B10'Area3for2'Mtr'CGrpAHU'DOAS3'Pwr\"] + added_line[\"B10'Area3for2'Mtr'CGrpAHU'DOAS4'Pwr\"] + added_line[\"B10'Area3for2'Mtr'CGrpAHU'FCU1'Pwr\"] + added_line[\"B10'Area3for2'Mtr'CGrpAHU'FCU2'Pwr\"] + added_line[\"B10'Area3for2'Mtr'CGrpAHU'FCU3'Pwr\"] + added_line[\"B10'Area3for2'Mtr'CGrpAHU'FCU4'Pwr\"]\n",
    "        added_line[\"PCBPumpElecPwrEst\"] = 0\n",
    "        added_line[\"TotElecAHUPwr\"] =  (added_line[\"B10'Area3for2'E'MtrEl9'PwrActv\"] + added_line[\"B10'Area3for2'E'MtrEl10'PwrActv\"] + added_line[\"B10'Area3for2'E'MtrEl11'PwrActv\"]  + added_line[\"B10'Area3for2'E'MtrEl12'PwrActv\"] + added_line[\"B10'Area3for2'E'MtrEl14'PwrActv\"] + added_line[\"B10'Area3for2'E'MtrEl15'PwrActv\"] + added_line[\"B10'Area3for2'E'MtrEl16'PwrActv\"] + added_line[\"B10'Area3for2'E'MtrEl17'PwrActv\"])*0.001\n",
    "        added_line[\"ChillerPlant_COP\"] = 3.5169 / added_line[\"ChillerPlant_KWperTon_Calculated\"]\n",
    "        added_line[\"TotElecChillerEst\"] = (added_line[\"TotPrimAHUPwr\"] + added_line[\"B10'Area3for2'Mtr'CGrpPCB'TotPrim'Fl\"]) / added_line[\"ChillerPlant_COP\"]\n",
    "        added_line[\"TotElecLighting\"] = np.abs(added_line[\"B10'Area3for2'E'MtrEl18'PwrActv\"])\n",
    "        added_line[\"TotElecPlugs\"] =  (np.abs(added_line[\"B10'Area3for2'E'MtrEl1'PwrActv\"]) + np.abs(added_line[\"B10'Area3for2'E'MtrEl2'PwrActv\"]) + np.abs(added_line[\"B10'Area3for2'E'MtrEl3'PwrActv\"]) + np.abs(added_line[\"B10'Area3for2'E'MtrEl4'PwrActv\"]) + np.abs(added_line[\"B10'Area3for2'E'MtrEl5'PwrActv\"]) + np.abs(added_line[\"B10'Area3for2'E'MtrEl6'PwrActv\"]) + np.abs(added_line[\"B10'Area3for2'E'MtrEl7'PwrActv\"]) + np.abs(added_line[\"B10'Area3for2'E'MtrEl8'PwrActv\"]))*0.001\n",
    "        added_line[\"TotalElectricalUse\"] = added_line[\"TotElecChillerEst\"] + added_line[\"TotElecAHUPwr\"] + added_line[\"TotElecLighting\"] + added_line[\"TotElecPlugs\"] + added_line[\"PCBPumpElecPwrEst\"]\n",
    "    except:\n",
    "        print \"virtual meter creation error\"\n",
    "        return added_line\n",
    "    return added_line"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "x =addvirtualmeters(added_line)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "timestamp\n",
       "2016-05-31 17:17:00+08:00    0.6528\n",
       "Freq: T, Name: TotElecPlugs, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x[\"TotElecPlugs\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sending data to influx\n",
      "Finished\n",
      "Retrieved 1638 pts in 24.0246468213 sec from query of 1775 pts at 2016-05-31 17:17:56.897000+08:00\n",
      "Error when calling Siemens API:"
     ]
    }
   ],
   "source": [
    "callAPI = True\n",
    "callfreq = 60\n",
    "timedelta = 0\n",
    "#filename = datetime.datetime.now(tz).strftime('%Y_%m_%d_%H_%M_%S')\n",
    "#filename_log = filename+\"_log.csv\"\n",
    "#firstline = get_seimensdata(meta, apilink)\n",
    "#firstline.to_csv(filename+\".csv\")#, date_format=\"%Y-%m-%d %H:%M:%S\"\n",
    "\n",
    "\n",
    "#with open(filename_log, \"a\") as logfile:\n",
    "while callAPI:\n",
    "    #print \"starting extraction\"\n",
    "    if timedelta > 0:\n",
    "        time.sleep(callfreq-timedelta)\n",
    "\n",
    "    start_time = timeit.default_timer()\n",
    "    #rawdata = pd.read_csv(filename+\".csv\", index_col='timestamp', parse_dates=True)\n",
    "    #Get the data from the Siemens API\n",
    "    try:\n",
    "        added_line = get_seimensdata(meta, apilink, timeoutsecs)\n",
    "        added_line = addvirtualmeters(added_line)\n",
    "        #rawdata = rawdata.append(added_line)\n",
    "        #rawdata.to_csv(filename+\".csv\")#, date_format=\"%Y-%m-%d %H:%M:%S\"\n",
    "    except:\n",
    "        e = sys.exc_info()[1]\n",
    "        logstring = 'Error when calling Siemens API:'+str(e)\n",
    "        #logfile.write(logstring + '\\n')\n",
    "        timedelta = timeit.default_timer() - start_time\n",
    "        print logstring\n",
    "        continue\n",
    "\n",
    "    #Send to Influxdb\n",
    "    try:\n",
    "        print \"Sending data to influx\"\n",
    "        inputstring = getstring(added_line, meta)\n",
    "        sendtoinflux(host, port, login, pw, inputstring)\n",
    "        print \"Finished\"\n",
    "    except:\n",
    "        e = sys.exc_info()[1]\n",
    "        logstring = 'Error when sending data to Influxdb:'+str(e)\n",
    "        timedelta = timeit.default_timer() - start_time\n",
    "        #logfile.write(logstring + '\\n')\n",
    "        print logstring\n",
    "        continue   \n",
    "\n",
    "    numberofcols = len(added_line.T.dropna())    \n",
    "    timedelta = timeit.default_timer() - start_time\n",
    "    logstring = \"Retrieved \"+str(numberofcols)+\" pts in \"+str(timedelta)+\" sec from query of \"+str(len(meta))+\" pts at \"+str(datetime.datetime.now(tz))\n",
    "    #logfile.write(logstring + '\\n')\n",
    "    print logstring"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}