pjm.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# The %... is an iPython thing, and is not part of the Python language.\n",
    "# In this case we're just telling the plotting library to draw things on\n",
    "# the notebook, instead of on a separate window.\n",
    "%matplotlib inline\n",
    "# See all the \"as ...\" contructs? They're just aliasing the package names.\n",
    "# That way we can call methods like plt.plot() instead of matplotlib.pyplot.plot().\n",
    "import numpy as np\n",
    "import scipy as sp\n",
    "import matplotlib as mpl\n",
    "import matplotlib.cm as cm\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import time\n",
    "pd.set_option('display.width', 500)\n",
    "pd.set_option('display.max_columns', 100)\n",
    "pd.set_option('display.notebook_repr_html', True)\n",
    "import seaborn as sns\n",
    "sns.set_style(\"whitegrid\")\n",
    "sns.set_context(\"poster\")\n",
    "import requests\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#PJM Has a REST API\n",
    "\n",
    "PJM has documentation for a REST API available at http://www.pjm.com/~/media/etools/data-miner/user-guide.ashx\n",
    "\n",
    "We'll use the API to get the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#These are all the nodes we're interested in so far.\n",
    "nodeList = [\n",
    "    5021673,\n",
    "    32417525,\n",
    "    32417527,\n",
    "    32417545,\n",
    "    32417547,\n",
    "    32417599,\n",
    "    32417601,\n",
    "    32417629,\n",
    "    32417631,\n",
    "    32417633,\n",
    "    32417635\n",
    "]\n",
    "#This is the base URL for the PJM REST API\n",
    "url = 'https://dataminer.pjm.com/dataminer/rest/public/api/markets/realtime/lmp/daily'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def splitDateTime(utchour):\n",
    "    #split datetime into date and time components\n",
    "    datetime_parts = utchour.split('T', 1)\n",
    "    parts = dict(date = datetime_parts[0], time = datetime_parts[1].rstrip('Z'))\n",
    "    return parts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from datetime import datetime, timedelta, date, time\n",
    "\n",
    "#Using code adapted from http://stackoverflow.com/questions/1060279/iterating-through-a-range-of-dates-in-python\n",
    "def daterange(start, end):\n",
    "    for n in range(int((end - start).days)):\n",
    "        yield start + timedelta(n)\n",
    "        \n",
    "def formatDate(aDate):\n",
    "    return aDate.strftime('%Y-%m-%d')\n",
    "\n",
    "def adjustTime(parts):\n",
    "    dtstring = parts['date'] + ' ' + parts['time']\n",
    "    dtformat = '%Y-%m-%d %H:%M:%S'\n",
    "    adjusted = datetime.strptime(dtstring, dtformat) - timedelta(hours = 4)\n",
    "    return adjusted\n",
    "\n",
    "def getHour(adjustedDatetime):\n",
    "    t = adjustedDatetime.time()\n",
    "    tstring = t.strftime('%H:%M:%S')\n",
    "    tparts = tstring.split(':', 2)\n",
    "    return tparts[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#HERE IS THE BIG KAHUNA\n",
    "#This will take a long-ass time to run (25 mins), because we have to loop over every day in the years 2008 - 2012\n",
    "from datetime import date\n",
    "#set up our json POST data\n",
    "params_list = [\n",
    "    dict(startDate = formatDate(date(2008, 1, 1)), endDate = formatDate(date(2008, 12, 31)), pnodeList = nodeList),\n",
    "    dict(startDate = formatDate(date(2009, 1, 1)), endDate = formatDate(date(2009, 12, 31)), pnodeList = nodeList),\n",
    "    dict(startDate = formatDate(date(2010, 1, 1)), endDate = formatDate(date(2010, 12, 31)), pnodeList = nodeList),\n",
    "    dict(startDate = formatDate(date(2011, 1, 1)), endDate = formatDate(date(2011, 12, 31)), pnodeList = nodeList),\n",
    "    dict(startDate = formatDate(date(2012, 1, 1)), endDate = formatDate(date(2012, 12, 31)), pnodeList = nodeList),\n",
    "    dict(startDate = formatDate(date(2013, 1, 1)), endDate = formatDate(date(2013, 12, 31)), pnodeList = nodeList),\n",
    "    dict(startDate = formatDate(date(2014, 1, 1)), endDate = formatDate(date(2014, 12, 31)), pnodeList = nodeList),\n",
    "    dict(startDate = formatDate(date(2015, 1, 1)), endDate = formatDate(date(2015, 11, 1)), pnodeList = nodeList)\n",
    "]\n",
    "\n",
    "results_dict = {}\n",
    "\n",
    "for i in range(0, len(params_list)):\n",
    "    \n",
    "    #make the API call\n",
    "    r = requests.post(url, json = params_list[i])\n",
    "    if r.status_code == requests.codes.ok:\n",
    "        results_dict[i] = r.json()\n",
    "    else:\n",
    "        r.raise_for_status()\n",
    "        \n",
    "    #be nice to the API, wait 2 seconds\n",
    "    time.sleep(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "recordsList = []\n",
    "for result in results_dict.values():\n",
    "    \n",
    "    #make a new row for each individual price\n",
    "    for record in result:\n",
    "        #we are only interested in Total LMP per Sam's email\n",
    "        if record['priceType'] == 'TotalLMP':\n",
    "            data = {}\n",
    "            data['pnodeId'] = record['pnodeId']\n",
    "            published = splitDateTime(record['publishDate'])\n",
    "            data['publishDate'] = published['date']\n",
    "            for p in record['prices']:\n",
    "                utcparts = splitDateTime(p['utchour'])\n",
    "                hour = getHour(adjustTime(utcparts))\n",
    "                if hour == '00':\n",
    "                    hour = '24'\n",
    "                key = 'price_' + hour\n",
    "                data[key] = p['price']\n",
    "            recordsList.append(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "31482\n"
     ]
    }
   ],
   "source": [
    "#let's see what we have. It's probably obscenely huge.\n",
    "#print rawdf.shape\n",
    "#results_dict[0][0:3]\n",
    "print len(recordsList)\n",
    "#recordsList[0:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rawdf = pd.DataFrame(recordsList)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "rawdf.to_csv('rawdf_pjm_realtime.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pnodeId</th>\n",
       "      <th>price_01</th>\n",
       "      <th>price_02</th>\n",
       "      <th>price_03</th>\n",
       "      <th>price_04</th>\n",
       "      <th>price_05</th>\n",
       "      <th>price_06</th>\n",
       "      <th>price_07</th>\n",
       "      <th>price_08</th>\n",
       "      <th>price_09</th>\n",
       "      <th>price_10</th>\n",
       "      <th>price_11</th>\n",
       "      <th>price_12</th>\n",
       "      <th>price_13</th>\n",
       "      <th>price_14</th>\n",
       "      <th>price_15</th>\n",
       "      <th>price_16</th>\n",
       "      <th>price_17</th>\n",
       "      <th>price_18</th>\n",
       "      <th>price_19</th>\n",
       "      <th>price_20</th>\n",
       "      <th>price_21</th>\n",
       "      <th>price_22</th>\n",
       "      <th>price_23</th>\n",
       "      <th>price_24</th>\n",
       "      <th>publishDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32417601</td>\n",
       "      <td>2.48</td>\n",
       "      <td>14.23</td>\n",
       "      <td>13.66</td>\n",
       "      <td>21.55</td>\n",
       "      <td>21.44</td>\n",
       "      <td>21.79</td>\n",
       "      <td>22.94</td>\n",
       "      <td>24.15</td>\n",
       "      <td>23.06</td>\n",
       "      <td>26.53</td>\n",
       "      <td>29.16</td>\n",
       "      <td>26.03</td>\n",
       "      <td>27.36</td>\n",
       "      <td>26.25</td>\n",
       "      <td>26.41</td>\n",
       "      <td>26.57</td>\n",
       "      <td>28.61</td>\n",
       "      <td>65.92</td>\n",
       "      <td>72.45</td>\n",
       "      <td>75.68</td>\n",
       "      <td>76.83</td>\n",
       "      <td>46.26</td>\n",
       "      <td>44.68</td>\n",
       "      <td>28.91</td>\n",
       "      <td>2008-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>32417601</td>\n",
       "      <td>26.73</td>\n",
       "      <td>31.48</td>\n",
       "      <td>30.66</td>\n",
       "      <td>27.92</td>\n",
       "      <td>35.38</td>\n",
       "      <td>31.03</td>\n",
       "      <td>41.06</td>\n",
       "      <td>90.86</td>\n",
       "      <td>108.73</td>\n",
       "      <td>138.98</td>\n",
       "      <td>102.04</td>\n",
       "      <td>89.39</td>\n",
       "      <td>80.84</td>\n",
       "      <td>92.72</td>\n",
       "      <td>52.36</td>\n",
       "      <td>28.21</td>\n",
       "      <td>52.29</td>\n",
       "      <td>85.35</td>\n",
       "      <td>129.36</td>\n",
       "      <td>153.18</td>\n",
       "      <td>161.66</td>\n",
       "      <td>102.92</td>\n",
       "      <td>98.31</td>\n",
       "      <td>65.93</td>\n",
       "      <td>2008-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>32417601</td>\n",
       "      <td>191.99</td>\n",
       "      <td>60.30</td>\n",
       "      <td>45.30</td>\n",
       "      <td>70.30</td>\n",
       "      <td>61.71</td>\n",
       "      <td>49.43</td>\n",
       "      <td>56.30</td>\n",
       "      <td>113.23</td>\n",
       "      <td>105.20</td>\n",
       "      <td>78.29</td>\n",
       "      <td>45.28</td>\n",
       "      <td>52.23</td>\n",
       "      <td>69.05</td>\n",
       "      <td>66.20</td>\n",
       "      <td>53.44</td>\n",
       "      <td>31.91</td>\n",
       "      <td>55.90</td>\n",
       "      <td>31.72</td>\n",
       "      <td>73.64</td>\n",
       "      <td>68.42</td>\n",
       "      <td>76.62</td>\n",
       "      <td>70.43</td>\n",
       "      <td>57.13</td>\n",
       "      <td>63.90</td>\n",
       "      <td>2008-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>32417601</td>\n",
       "      <td>40.36</td>\n",
       "      <td>35.20</td>\n",
       "      <td>33.61</td>\n",
       "      <td>27.98</td>\n",
       "      <td>24.98</td>\n",
       "      <td>30.54</td>\n",
       "      <td>41.90</td>\n",
       "      <td>66.48</td>\n",
       "      <td>78.02</td>\n",
       "      <td>65.31</td>\n",
       "      <td>68.80</td>\n",
       "      <td>38.89</td>\n",
       "      <td>47.09</td>\n",
       "      <td>33.41</td>\n",
       "      <td>24.32</td>\n",
       "      <td>26.75</td>\n",
       "      <td>28.74</td>\n",
       "      <td>60.48</td>\n",
       "      <td>44.26</td>\n",
       "      <td>38.80</td>\n",
       "      <td>38.36</td>\n",
       "      <td>29.07</td>\n",
       "      <td>30.21</td>\n",
       "      <td>26.46</td>\n",
       "      <td>2008-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>32417601</td>\n",
       "      <td>35.13</td>\n",
       "      <td>34.19</td>\n",
       "      <td>25.23</td>\n",
       "      <td>24.42</td>\n",
       "      <td>23.09</td>\n",
       "      <td>24.00</td>\n",
       "      <td>23.54</td>\n",
       "      <td>30.27</td>\n",
       "      <td>37.55</td>\n",
       "      <td>56.69</td>\n",
       "      <td>67.66</td>\n",
       "      <td>56.65</td>\n",
       "      <td>33.43</td>\n",
       "      <td>38.33</td>\n",
       "      <td>29.52</td>\n",
       "      <td>33.23</td>\n",
       "      <td>39.43</td>\n",
       "      <td>69.88</td>\n",
       "      <td>45.50</td>\n",
       "      <td>42.07</td>\n",
       "      <td>35.96</td>\n",
       "      <td>35.54</td>\n",
       "      <td>24.52</td>\n",
       "      <td>27.46</td>\n",
       "      <td>2008-01-05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    pnodeId  price_01  price_02  price_03  price_04  price_05  price_06  price_07  price_08  price_09  price_10  price_11  price_12  price_13  price_14  price_15  price_16  price_17  price_18  price_19  price_20  price_21  price_22  price_23  price_24 publishDate\n",
       "0  32417601      2.48     14.23     13.66     21.55     21.44     21.79     22.94     24.15     23.06     26.53     29.16     26.03     27.36     26.25     26.41     26.57     28.61     65.92     72.45     75.68     76.83     46.26     44.68     28.91  2008-01-01\n",
       "1  32417601     26.73     31.48     30.66     27.92     35.38     31.03     41.06     90.86    108.73    138.98    102.04     89.39     80.84     92.72     52.36     28.21     52.29     85.35    129.36    153.18    161.66    102.92     98.31     65.93  2008-01-02\n",
       "2  32417601    191.99     60.30     45.30     70.30     61.71     49.43     56.30    113.23    105.20     78.29     45.28     52.23     69.05     66.20     53.44     31.91     55.90     31.72     73.64     68.42     76.62     70.43     57.13     63.90  2008-01-03\n",
       "3  32417601     40.36     35.20     33.61     27.98     24.98     30.54     41.90     66.48     78.02     65.31     68.80     38.89     47.09     33.41     24.32     26.75     28.74     60.48     44.26     38.80     38.36     29.07     30.21     26.46  2008-01-04\n",
       "4  32417601     35.13     34.19     25.23     24.42     23.09     24.00     23.54     30.27     37.55     56.69     67.66     56.65     33.43     38.33     29.52     33.23     39.43     69.88     45.50     42.07     35.96     35.54     24.52     27.46  2008-01-05"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'Column not found: 0'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-82-95d73fffad97>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[0mgrouped\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mrawdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgroupby\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'publishDate'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mgrouped\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mC:\\Users\\Max DeCurtins\\Anaconda\\lib\\site-packages\\pandas\\core\\groupby.pyc\u001b[0m in \u001b[0;36m__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   3191\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3192\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mkey\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 3193\u001b[1;33m                 \u001b[1;32mraise\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"Column not found: %s\"\u001b[0m \u001b[1;33m%\u001b[0m \u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   3194\u001b[0m             \u001b[1;31m# kind of a kludge\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3195\u001b[0m             return SeriesGroupBy(self.obj[key], selection=key,\n",
      "\u001b[1;31mKeyError\u001b[0m: 'Column not found: 0'"
     ]
    }
   ],
   "source": [
    "grouped = rawdf.groupby('publishDate')\n",
    "for k, v in grouped[:3]:\n",
    "    print k"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}