1. HanzeFitbit Data preparation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Populating the interactive namespace from numpy and matplotlib\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: pylab import has clobbered these variables: ['datetime']\n",
      "`%matplotlib` prevents importing * from pylab and numpy\n"
     ]
    }
   ],
   "source": [
    "###\n",
    "# import packages\n",
    "###\n",
    "import os\n",
    "import pickle\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "#import matplotlib.pyplot as pl\n",
    "#from matplotlib import dates\n",
    "import seaborn as sns\n",
    "from datetime import datetime\n",
    "from datetime import date\n",
    "%pylab inline\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#collect number of files\n",
    "for file in os.listdir('Data\\.'):\n",
    "    if file.endswith(\"*.csv\"):\n",
    "        print(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import glob, os   \n",
    "path = r'C:\\Users\\talko\\OneDrive - Hanzehogeschool Groningen\\HanzeFitBit\\Analyse\\Github\\hanzefit-ml\\Data'                # use your own path\n",
    "all_files = glob.glob(os.path.join(path, \"*.csv\"))     # advisable to use os.path.join as this makes concatenation OS independent\n",
    "\n",
    "df_from_each_file = (pd.read_csv(f) for f in all_files)\n",
    "concatenated_df   = pd.concat(df_from_each_file, ignore_index=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# put candidate files in a dataframe\n",
    "df = concatenated_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#add Date_time column\n",
    "df['mdate'] = pd.to_datetime(df['date'])\n",
    "#df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#treatment_id's\n",
    "treatment_id=df['treatment_id'].unique()\n",
    "#print(treatment_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['year'] = df['mdate'].dt.year\n",
    "df['week'] = df['mdate'].dt.week\n",
    "df['weekday']=df['mdate'].dt.weekday\n",
    "df['hour'] = df['mdate'].dt.hour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for num in treatment_id:\n",
    "     globals()['df%s' % num]=df[(df.treatment_id==num)]\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#group by  use resample\n",
    "for num in treatment_id:\n",
    "    globals()['df_h_%s'% num] = globals()['df%s' %num].iloc[:,[6,8]]\n",
    "    globals()['df_h_%s'% num] = globals()['df_h_%s'% num].set_index('mdate')\n",
    "    globals()['df_h_m%s' % num] = globals()['df_h_%s'% num].resample('H').sum()\n",
    "#year_hour_means['treatment_id']=df1['treatment_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#add treatment id to the grouped dataframes\n",
    "for num in treatment_id:\n",
    "    globals()['df_h_m%s'% num] = globals()['df_h_m%s'% num].reset_index()\n",
    "    globals()['df_h_m%s'% num]['treatment_id']= num\n",
    "    globals()['df_h_m%s'% num]['year'] = globals()['df_h_m%s'% num]['mdate'].dt.year\n",
    "    globals()['df_h_m%s'% num]['week'] = globals()['df_h_m%s'% num]['mdate'].dt.week\n",
    "    globals()['df_h_m%s'% num]['weekday'] = globals()['df_h_m%s'% num]['mdate'].dt.weekday\n",
    "    globals()['df_h_m%s'% num]['hour'] = globals()['df_h_m%s'% num]['mdate'].dt.hour\n",
    "    globals()['df_h_m%s'% num]['date'] = globals()['df_h_m%s'% num]['mdate'].dt.date\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#concatenate one dataframe again\n",
    "loop_indicator=0\n",
    "for num in treatment_id:\n",
    "        if loop_indicator==0:\n",
    "            concatenate_df=globals()['df_h_m%s'% num]\n",
    "            loop_indicator=1\n",
    "        else:    \n",
    "            concatenate_df =concatenate_df.append(globals()['df_h_m%s'% num])\n",
    "        \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#remove empty steps\n",
    "concatenate_df['steps'].replace('',np.nan, inplace=True)\n",
    "concatenate_df.dropna(subset=['steps'], inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#export data to database\n",
    "import cx_Oracle\n",
    "con = cx_Oracle.connect('hanzefitbit/hanzefitbit@127.0.0.1/xe')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for i in treatment_id:\n",
    "    #numpy.int64 is not accepted by the cx_oracle interface thats the reason for changing the format in a float\n",
    "    i_i=i.astype(float)\n",
    "    cur = con.cursor()\n",
    "    sql = \"INSERT INTO HFT_TREATMENT_T(TREATMENT_ID) VALUES (:1)\"\n",
    "    cur.execute(sql,(i_i,))\n",
    "    con.commit()\n",
    "cur.close()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cur = con.cursor()\n",
    "rows = [tuple(x) for x in concatenate_df.values]\n",
    "cur.prepare(\"BEGIN hft_insert_into_hft_data_t_p(:1,:2,:3,:4,:5,:6,:7,:8);END;\")\n",
    "cur.executemany(None,rows)\n",
    "con.commit()\n",
    "cur.close()\n",
    "#print(rows)"
   ]
  },
  {
   "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}