ERCOT.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 2013_ERCOT_Hourly_Load_Data.xls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "List Comprehension\n",
      "data[3][2]\n",
      "1036.088697\n",
      "\n",
      "Cells in a nested loop\n",
      "41277.0833333\n",
      "9238.73731\n",
      "1438.20528\n",
      "1565.442856\n",
      "916.708348\n",
      "14010.903488\n",
      "3027.98334\n",
      "6165.211119\n",
      "1157.741663\n",
      "37520.933404\n",
      "\n",
      "ROWS, COLUMNS and CELLS:\n",
      "Number of rows in the sheet: 7296\n",
      "Type of data in cell(row 3, col 2):\n",
      "2\n",
      "Value in cell (row 3, col 2):\n",
      "1036.088697\n",
      "Get a slice of values in column 3, from rows 1-3:\n",
      "[1411.7505669999982, 1403.4722870000019, 1395.053150000001]\n",
      "\n",
      "DATES:\n",
      "Types of data in cell (row 1, col 0):\n",
      "3\n",
      "Time in Excel format:\n",
      "41275.0416667\n",
      "Convert time to a Python datetime tuple, from the Excel float:\n",
      "(2013, 1, 1, 1, 0, 0)\n"
     ]
    }
   ],
   "source": [
    "import xlrd\n",
    "datafile = \"2013_ERCOT_Hourly_Load_Data.xls\"\n",
    "\n",
    "def parse_file(datafile):\n",
    "    workbook = xlrd.open_workbook(datafile) #open workbook\n",
    "    sheet = workbook.sheet_by_index(0) #specify which sheet ie 0 we want to work with\n",
    "    \n",
    "    data = [[sheet.cell_value(r, col) # loop through all rows and columns\n",
    "            for col in range(sheet.ncols)] # read all data into python list\n",
    "               for r in range(sheet.nrows)]\n",
    "\n",
    "    print \"\\nList Comprehension\"\n",
    "    print \"data[3][2]\"\n",
    "    print data[3][2] # print out row three column 2 for list\n",
    "    \n",
    "    print \"\\nCells in a nested loop\"\n",
    "    for row in range(sheet.nrows): # loop through entire sheet one row a a time\n",
    "        for col in range(sheet.ncols): # move across columns\n",
    "            if row == 50: # at row 50, print out values in that row one value at a time\n",
    "                print sheet.cell_value(row, col)\n",
    "                \n",
    "    # Other useful methods:          \n",
    "    \n",
    "    print \"\\nROWS, COLUMNS and CELLS:\"\n",
    "    print \"Number of rows in the sheet:\",\n",
    "    print sheet.nrows # grab the number of rows \n",
    "    print \"Type of data in cell(row 3, col 2):\"\n",
    "    print sheet.cell_type(3, 2) # check cell type\n",
    "    print \"Value in cell (row 3, col 2):\"\n",
    "    print sheet.cell_value(3,2)\n",
    "    print \"Get a slice of values in column 3, from rows 1-3:\"\n",
    "    # up to but not including row 4\n",
    "    print sheet.col_values(3, start_rowx=1, end_rowx=4) # slice values of a particular column\n",
    "    \n",
    "    print \"\\nDATES:\"\n",
    "    print \"Types of data in cell (row 1, col 0):\"\n",
    "    print sheet.cell_type(1, 0)\n",
    "    exceltime = sheet.cell_value(1, 0)\n",
    "    print \"Time in Excel format:\"\n",
    "    print exceltime\n",
    "    print \"Convert time to a Python datetime tuple, from the Excel float:\"\n",
    "    print xlrd.xldate_as_tuple(exceltime, 0)\n",
    "    \n",
    "                \n",
    "    \n",
    "parse_file(datafile)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "18779.02551\n",
      "6602.113899\n",
      "Maxtime:\n",
      "41499.7083333\n",
      "Realtime:\n",
      "(2013, 8, 13, 17, 0, 0)\n",
      "Mintime:\n",
      "41308.1666667\n",
      "Realtime:\n",
      "(2013, 8, 13, 17, 0, 0)\n",
      "{'avgcoast:': 10976.933460679751,\n",
      " 'maxtime:': (2013, 8, 13, 17, 0, 0),\n",
      " 'maxvalue:': 18779.025510000003,\n",
      " 'mintime:': (2013, 2, 3, 4, 0, 0),\n",
      " 'minvalue:': 6602.113898999982}\n"
     ]
    }
   ],
   "source": [
    "## Find max value in column b \n",
    "## for that max value, find the time stamp one column to the left,\n",
    "## where that time is scored\n",
    "## report it as a tuple\n",
    "\n",
    "## find max value in column b \n",
    "def parse_file(datafile):\n",
    "    workbook = xlrd.open_workbook(datafile)\n",
    "    sheet = workbook.sheet_by_index(0)\n",
    "    \n",
    "    data = [[sheet.cell_value(r, col) # loop through all rows and columns\n",
    "            for col in range(sheet.ncols)] # read all data into python list\n",
    "               for r in range(sheet.nrows)]\n",
    "    cv = sheet.col_values(1, start_rowx=1, end_rowx=None)\n",
    "    \n",
    "    maxval = max(cv)\n",
    "    minval = min(cv)\n",
    "    \n",
    "    print maxval # got 18779.02551\n",
    "    print minval # 6602.113899\n",
    "    \n",
    "    maxpos = cv.index(maxval) + 1 #index method needs 1 because data begins 1\n",
    "    minpos = cv.index(minval) + 1 #index method need 1 because data begins 1\n",
    "    \n",
    "    maxtime = sheet.cell_value(maxpos, 0)\n",
    "    realtime = xlrd.xldate_as_tuple(maxtime, 0) # max time to time tuple\n",
    "    mintime = sheet.cell_value(minpos, 0)\n",
    "    realmintime = xlrd.xldate_as_tuple(mintime, 0) # min time to time tuple\n",
    "    \n",
    "    print \"Maxtime:\"\n",
    "    print maxtime # 41499.7083333\n",
    "    print \"Realtime:\"\n",
    "    print realtime # (2013, 8, 13, 17, 0, 0)\n",
    "    print \"Mintime:\"\n",
    "    print mintime # 41308.1666667\n",
    "    print \"Realtime:\"\n",
    "    print realtime # (2013, 8, 13, 17, 0, 0)\n",
    "    \n",
    "    data = {\n",
    "        'maxtime:': realtime,\n",
    "        'maxvalue:': maxval,\n",
    "        'mintime:': realmintime,\n",
    "        'minvalue:': minval,\n",
    "        'avgcoast:': sum(cv) / float(len(cv)) # 'avgcoast:': 10976.933460679751\n",
    "    }\n",
    "    return data\n",
    "    \n",
    "data = parse_file(datafile)\n",
    "import pprint\n",
    "pprint.pprint(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\n- items may have different fields\\n- may have nested objects\\n- may have nested arrays\\n'"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Data Modeling in JSON\n",
    "\"\"\"\n",
    "- items may have different fields\n",
    "- may have nested objects\n",
    "- may have nested arrays\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nimport requests\\ndef main():\\n    results = query_by_name(ARTIST_URL, query_type[\"simple\"], \"Lucero\")\\n    \\n    artist_id = results[\"artist\"][1][\"id\"]\\n    print \"\\nARTIST\"\\n    pretty_print(results[\"artist\"][1])\\n    \\n    artist_data = query_site(ARTIST_URL, query_type[\"releases\"], artist_id)\\n    releases = artist_data[\"releases\"]\\n    print \"\\nONE RELEASE:\"\\n    pretty_print(releases[0], indent=2)\\n    release_titles = [r[\"title\"] for r in releases]\\n    \\n    print \"\\nALL TITLES:\"\\n    for t in release_titles:\\n        print t\\n        \\nif __name__ == \\'__main__\\':\\n    main()\\n'"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"\n",
    "import requests\n",
    "def main():\n",
    "    results = query_by_name(ARTIST_URL, query_type[\"simple\"], \"Lucero\")\n",
    "    \n",
    "    artist_id = results[\"artist\"][1][\"id\"]\n",
    "    print \"\\nARTIST\"\n",
    "    pretty_print(results[\"artist\"][1])\n",
    "    \n",
    "    artist_data = query_site(ARTIST_URL, query_type[\"releases\"], artist_id)\n",
    "    releases = artist_data[\"releases\"]\n",
    "    print \"\\nONE RELEASE:\"\n",
    "    pretty_print(releases[0], indent=2)\n",
    "    release_titles = [r[\"title\"] for r in releases]\n",
    "    \n",
    "    print \"\\nALL TITLES:\"\n",
    "    for t in release_titles:\n",
    "        print t\n",
    "        \n",
    "if __name__ == '__main__':\n",
    "    main()\n",
    "\"\"\""
   ]
  },
  {
   "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.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}