1_4_cluster_load.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1. Obtain raw data from Graphite database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%run include/required_libs.py\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "http://graphite.rcc.uchicago.edu/render/?format=csv&from=20130512&target=summarize(slurm.midway.bypartition.sandyb.cores.*, \"1day\", \"avg\")\n",
      "http://graphite.rcc.uchicago.edu/render/?format=csv&from=20160910&target=summarize(slurm.midway.bypartition.broadwl.cores.*, \"1day\", \"avg\")\n"
     ]
    }
   ],
   "source": [
    "partitions = {\n",
    "    'sandyb' : '20130512',\n",
    "    'broadwl' : '20160910'\n",
    "}\n",
    "\n",
    "for part in partitions:\n",
    "\n",
    "    prefix = \"http://graphite.rcc.uchicago.edu/render/?format=csv\" + \\\n",
    "        \"&from=\" + partitions[part] + \\\n",
    "        \"&target=summarize(slurm.midway.bypartition.\" + part + \".cores.*, \\\"1day\\\", \\\"avg\\\")\"\n",
    "    \n",
    "    print(prefix)\n",
    "    \n",
    "    r = requests.get(prefix)\n",
    "    csv = r.text.replace(\"\\\"summarize(slurm.midway.bypartition.\" + part + \".cores.\", '')\n",
    "    csv = csv.replace(', \\\"\\\"1day\\\"\\\", \\\"\\\"avg\\\"\\\")\\\"', '')\n",
    "    csv = csv.replace(' 00:00:00', '')\n",
    "    \n",
    "    with open(\"rawdata/cluster_load_\" + part + \".csv\", \"w\") as f:\n",
    "        f.write(\"status,date,cores\\n\")\n",
    "        f.write(csv)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2. Check raw data and understand the columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "bro = pd.read_csv('rawdata/cluster_load_broadwl.csv').dropna()\n",
    "san = pd.read_csv('rawdata/cluster_load_sandyb.csv').dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Column 1:status\n",
    "\n",
    "Column 2:date\n",
    "\n",
    "Column 3:cores"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 3. Data gathering\n",
    "\n",
    "We notice that the first column is a categorial variable, and for each category there are same numbers of rows. So we can gather them into new columns. Save the results into \"rawdata/cluster_load_sandyb(browdwl)_gathered.csv\"\n",
    "\n",
    "Reference: https://hackernoon.com/reshaping-data-in-python-fa27dda2ff77"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bro.pivot(index='date', columns='status', values='cores').\\\n",
    "to_csv(\"rawdata/cluster_load_broadwl_gathered.csv\")\n",
    "\n",
    "san.pivot(index='date', columns='status', values='cores').\\\n",
    "to_csv(\"rawdata/cluster_load_sandyb_gathered.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 4. Refine data\n",
    "\n",
    "**4.1** For each table, choose \"date\" and \"total\" columns, add a new column labeling the partition name. Then bind two tables together. Save it as \"cluster_total_cores.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bro = pd.read_csv('rawdata/cluster_load_broadwl_gathered.csv')\n",
    "bro = bro[bro['date']>'2016-09-30']\n",
    "bro['partition'] = 'broadwl'\n",
    "san = pd.read_csv('rawdata/cluster_load_sandyb_gathered.csv')\n",
    "san['partition'] = 'sandyb'\n",
    "bro.append(san).to_csv('rawdata/cluster_total_cores.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**4.2** Add a new column of \"su\" calculated by \"total\" x 24"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "allcore = pd.read_csv('rawdata/cluster_total_cores.csv', parse_dates=['date'])\n",
    "allcore['su'] = allcore['total'] * 24"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**4.3** Modify su by multiplying 1.3 for broadwl partition from 04/01/2017 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "allcore.loc[(allcore.partition== 'broadwl') & (allcore.date >= '2017-04-01') ,'su'] = allcore['su'] * 1.3\n",
    "allcore = allcore.sort_values('date')\n",
    "allcore = allcore.reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**4.4** Create a new column \"cycle\" and calculate cycle id (2013-2018) by date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "allcore['cycle'] = (pd.to_numeric(allcore['date'].dt.strftime('%Y')) + \\\n",
    "    pd.to_numeric(allcore['date'].dt.strftime('%m'))/12 - 10/12 + 1).astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**4.5** Make subtotal of \"days\" and \"su\" grouped by cycle and partition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cycle</th>\n",
       "      <th>partition</th>\n",
       "      <th>days</th>\n",
       "      <th>su</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "      <td>sandyb</td>\n",
       "      <td>122</td>\n",
       "      <td>1.014189e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014</td>\n",
       "      <td>sandyb</td>\n",
       "      <td>365</td>\n",
       "      <td>3.962881e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>sandyb</td>\n",
       "      <td>365</td>\n",
       "      <td>3.493516e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016</td>\n",
       "      <td>sandyb</td>\n",
       "      <td>365</td>\n",
       "      <td>3.499911e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>broadwl</td>\n",
       "      <td>364</td>\n",
       "      <td>8.931714e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2017</td>\n",
       "      <td>sandyb</td>\n",
       "      <td>364</td>\n",
       "      <td>3.792839e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018</td>\n",
       "      <td>broadwl</td>\n",
       "      <td>241</td>\n",
       "      <td>7.446849e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018</td>\n",
       "      <td>sandyb</td>\n",
       "      <td>241</td>\n",
       "      <td>2.646682e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cycle partition  days            su\n",
       "0   2013    sandyb   122  1.014189e+07\n",
       "1   2014    sandyb   365  3.962881e+07\n",
       "2   2015    sandyb   365  3.493516e+07\n",
       "3   2016    sandyb   365  3.499911e+07\n",
       "4   2017   broadwl   364  8.931714e+07\n",
       "5   2017    sandyb   364  3.792839e+07\n",
       "6   2018   broadwl   241  7.446849e+07\n",
       "7   2018    sandyb   241  2.646682e+07"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allyear = allcore.groupby(['cycle', 'partition']).agg({'cycle':'count', 'su': 'sum'})\\\n",
    "    .rename(columns={'cycle':'days'}).reset_index()\n",
    "allyear"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**4.6** Create new column as \"su_projected\" to correct results projected in 365 days."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cycle</th>\n",
       "      <th>partition</th>\n",
       "      <th>days</th>\n",
       "      <th>su</th>\n",
       "      <th>su_projected</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "      <td>sandyb</td>\n",
       "      <td>122</td>\n",
       "      <td>1.014189e+07</td>\n",
       "      <td>3.034253e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014</td>\n",
       "      <td>sandyb</td>\n",
       "      <td>365</td>\n",
       "      <td>3.962881e+07</td>\n",
       "      <td>3.962881e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>sandyb</td>\n",
       "      <td>365</td>\n",
       "      <td>3.493516e+07</td>\n",
       "      <td>3.493516e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016</td>\n",
       "      <td>sandyb</td>\n",
       "      <td>365</td>\n",
       "      <td>3.499911e+07</td>\n",
       "      <td>3.499911e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>broadwl</td>\n",
       "      <td>364</td>\n",
       "      <td>8.931714e+07</td>\n",
       "      <td>8.956252e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2017</td>\n",
       "      <td>sandyb</td>\n",
       "      <td>364</td>\n",
       "      <td>3.792839e+07</td>\n",
       "      <td>3.803259e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018</td>\n",
       "      <td>broadwl</td>\n",
       "      <td>241</td>\n",
       "      <td>7.446849e+07</td>\n",
       "      <td>1.127842e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018</td>\n",
       "      <td>sandyb</td>\n",
       "      <td>241</td>\n",
       "      <td>2.646682e+07</td>\n",
       "      <td>4.008460e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cycle partition  days            su  su_projected\n",
       "0   2013    sandyb   122  1.014189e+07  3.034253e+07\n",
       "1   2014    sandyb   365  3.962881e+07  3.962881e+07\n",
       "2   2015    sandyb   365  3.493516e+07  3.493516e+07\n",
       "3   2016    sandyb   365  3.499911e+07  3.499911e+07\n",
       "4   2017   broadwl   364  8.931714e+07  8.956252e+07\n",
       "5   2017    sandyb   364  3.792839e+07  3.803259e+07\n",
       "6   2018   broadwl   241  7.446849e+07  1.127842e+08\n",
       "7   2018    sandyb   241  2.646682e+07  4.008460e+07"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allyear['su_projected'] = allyear['su'] * 365 / allyear['days']\n",
    "allyear"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**4.7** Aggrate to cycles and save the tabel as \"rawdata/cluster_cores_by_cycles.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cluster = allyear.pivot_table(index='cycle', columns='partition', values='su_projected')\\\n",
    "    .reset_index().fillna(0)\n",
    "cluster['total_su'] = cluster['sandyb'] + cluster['broadwl']\n",
    "cluster.to_csv('rawdata/cluster_cores_by_cycles.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>partition</th>\n",
       "      <th>cycle</th>\n",
       "      <th>broadwl</th>\n",
       "      <th>sandyb</th>\n",
       "      <th>total_su</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>2.792511e+07</td>\n",
       "      <td>27.925112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>3.962881e+07</td>\n",
       "      <td>39.628809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>3.493516e+07</td>\n",
       "      <td>34.935164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>3.499911e+07</td>\n",
       "      <td>34.999115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>8.956252e+07</td>\n",
       "      <td>3.803259e+07</td>\n",
       "      <td>127.595111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018</td>\n",
       "      <td>1.129426e+08</td>\n",
       "      <td>4.008448e+07</td>\n",
       "      <td>153.027033</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "partition  cycle       broadwl        sandyb    total_su\n",
       "0           2013  0.000000e+00  2.792511e+07   27.925112\n",
       "1           2014  0.000000e+00  3.962881e+07   39.628809\n",
       "2           2015  0.000000e+00  3.493516e+07   34.935164\n",
       "3           2016  0.000000e+00  3.499911e+07   34.999115\n",
       "4           2017  8.956252e+07  3.803259e+07  127.595111\n",
       "5           2018  1.129426e+08  4.008448e+07  153.027033"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cluster['total_su']=cluster['total_su']/1000000\n",
    "cluster"
   ]
  }
 ],
 "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}