1_1_usages_by_cycles_groups.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Usages by Cycles and Groups\n",
    "\n",
    "### Introduction\n",
    "\n",
    "The purpose of this script is to generate the data table of usage information by each group in each year. The data source is from the tables \"midway_usage\" and \"midway_usage_archived\" of RCC AcctTool database. The script will generate the 6 yearly data file 'years_detail_usages.csv' with six columns: \n",
    " - cycle: 201X means subtotals from job records ended between 10/01/201(X-1) to 09/30/201X\n",
    " - account: account name\n",
    " - jobs: total number of jobs\n",
    " - users: number of users having job records\n",
    " - su: total SUs being charged\n",
    " - cpuhour: total cpu(core)-hours being consumed. \n",
    "\n",
    "#### Sum up the jobs, users, usages and cuphours to get a data file 'usages_by_cycle_group.csv'. \n",
    "#### Each row is a record of usage data for a group in a year.\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%run include/required_libs.py\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "We firstly get yearly information from database \n",
    "2013-2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cycles = list(range(2013,2019))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "for year in cycles:\n",
    "    a = '''select account,count(*) as `jobs`,\n",
    "            sum(`service_units_charged`) as `su`,\n",
    "            count(distinct `user`) as `users`,\n",
    "            avg(ncpus) as `avg_cores`,\n",
    "            avg(nnodes) as `avg_nodes`,\n",
    "            sum(`cputimeraw`)/3600 as `cpuhours` '''\n",
    "    \n",
    "    if year == cycles[-1]:\n",
    "        b = '''from midway_usage '''\n",
    "        \n",
    "    else:\n",
    "        b = '''from midway_usage_archived '''\n",
    "        \n",
    "    c = '''where (partition='sandyb' or partition='broadwl') and end >' '''\n",
    "\n",
    "    d = '''-09-30' and end <' '''\n",
    "\n",
    "    e = '''-10-01 00:00:00' \n",
    "        group by account \n",
    "        order by `su` desc'''\n",
    "    \n",
    "    yearly = pd.read_sql(a + b + c + str(year-1) + d + str(year) + e, cnx)\n",
    "    yearly.to_csv('rawdata/usage_by_group_' + str(year) + '.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Then, add cycle name and append to get 2013-2018 usages_by_cycle_group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "usages_by_cycle_group = pd.DataFrame()\n",
    "\n",
    "for year in cycles:\n",
    "    yearly = pd.read_csv('rawdata/usage_by_group_' + str(year) + '.csv')\n",
    "    yearly['cycle'] = year\n",
    "    usages_by_cycle_group = usages_by_cycle_group.append(yearly)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "usages_by_cycle_group.to_csv('rawdata/usages_by_cycle_group.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}