05merge_with_positive.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "import re\n",
    "import time\n",
    "import datetime\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import func\n",
    "# inline plot\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "all_trains = pd.read_csv(\"data/weather-trains_2015-06-08 16:57:272016-01-25 23:42:14.csv\",sep='\\t',error_bad_lines=False)\n",
    "delayed = pd.read_csv(\"data/merged_delay.csv\",sep='\\t',error_bad_lines=False)\n",
    "del delayed['Unnamed: 0']\n",
    "del all_trains['Unnamed: 0']\n",
    "del all_trains['time_pa']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print all_trains.shape\n",
    "print delayed.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "delayed.columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# merged = all_trains.merge(atw,left_on='time_pa',right_on='timestamp',how='outer')\n",
    "# print merged.shape\n",
    "# merged.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# all_trains.merge(delayed,left_on=['train_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "all_trains['timestamp'] = pd.to_datetime(all_trains['timestamp'],format=\"%Y-%m-%d %H:%M\")\n",
    "delayed['timestamp'] = pd.to_datetime(delayed['timestamp'],format=\"%Y-%m-%d %H:%M\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "max_date = delayed['timestamp'].max()\n",
    "min_date = delayed['timestamp'].min()\n",
    "print \"Delayed\"\n",
    "print max_date\n",
    "print min_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "max_date = all_trains['timestamp'].max()\n",
    "min_date = all_trains['timestamp'].min()\n",
    "print \"All_trains\"\n",
    "print max_date\n",
    "print min_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "all_trains.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "all_trains['timestamp'][0] + datetime.timedelta(days=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import calendar\n",
    "def populate_all_trains(t_row):\n",
    "    t=t_row['timestamp']\n",
    "#     next_mo = True if t.day >= calendar.monthrange(t.year,t.month)[1] else False\n",
    "    nd = t_row['timestamp']+ datetime.timedelta(days=1)\n",
    "    \n",
    "#     year2 = t.year if next_mo is False & t.month != 12 else t.year+1\n",
    "#     month2 = t.month if next_mo is False else (t.month + 1 if (t.month+1)<=12 else 1)\n",
    "#     day2 = t.day+1 if next_mo is False else 1\n",
    "    \n",
    "    d = delayed[(delayed['timestamp'] > datetime.datetime(t.year,t.month,t.day)) \n",
    "           & (delayed['timestamp'] <= datetime.datetime(nd.year,nd.month,nd.day))\n",
    "            & (delayed['train_id_x'] == t_row['train_id'])]\n",
    "\n",
    "    if d.empty is False:\n",
    "        return pd.Series([\n",
    "            d.t_bullet.max(),\n",
    "            d.t_limited.max(),\n",
    "            d.uid.max(),\n",
    "            d.is_delay.max(),\n",
    "            d.delay_minor.max(),\n",
    "            d.delay_med.max(),\n",
    "            d.delay_major.max(),\n",
    "            d.delay_catastrophic.max(),\n",
    "            d.is_backlog.max(),\n",
    "            d.is_canceled.max(),\n",
    "            d.is_passing.max(),\n",
    "            d.is_accident.max(),\n",
    "            d.is_medical.max(),\n",
    "            d.is_mechanical.max(),\n",
    "            d.is_customer.max(),\n",
    "            d.is_event.max()])\n",
    "    else:\n",
    "        return pd.Series([\n",
    "            1 if str(t_row.train_id)[0] == 3 else 0,\n",
    "            1 if str(t_row.train_id)[0] == 2 else 0,\n",
    "            0,\n",
    "            0,\n",
    "            0,\n",
    "            0,\n",
    "            0,\n",
    "            0,\n",
    "            0,\n",
    "            0,\n",
    "            0,\n",
    "            0,\n",
    "            0,\n",
    "            0,\n",
    "            0,\n",
    "            0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "test = all_trains.apply(lambda x:populate_all_trains(x),axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test.columns = ['is_bullet','is_limited','tweet_id','is_delay','del_min','del_med','del_maj',\n",
    "                'del_cat','is_backlog','is_canceled','is_passing','is_accident','is_medical',\n",
    "                'is_mechanical','is_customer','is_event']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "merged = pd.concat([all_trains, test], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "merged.timestamp[100].weekday()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_days(r):\n",
    "    #Monday is 0 and Sunday is 6.\n",
    "        return pd.Series([\n",
    "            1 if r['timestamp'].weekday() == 0 else 0, #monday\n",
    "            1 if r['timestamp'].weekday() == 1 else 0, \n",
    "            1 if r['timestamp'].weekday() == 2 else 0,\n",
    "            1 if r['timestamp'].weekday() == 3 else 0,\n",
    "            1 if r['timestamp'].weekday() == 4 else 0,\n",
    "            1 if r['timestamp'].weekday() == 5 else 0,\n",
    "            1 if r['timestamp'].weekday() == 6 else 0 #sunday\n",
    "    ])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "days = all_trains.apply(lambda x:get_days(x),axis=1)\n",
    "days.columns = ['d_monday','d_tuesday','d_wednesday','d_thursday','d_friday','d_saturday','d_sunday',]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "merged = pd.concat([merged, days], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "filename = \"./data/merged_concat_final.csv\"\n",
    "merged.to_csv(filename, sep='\\t', encoding='utf-8')\n",
    "print filename"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "merged.groupby('timestamp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "merged[(merged['is_delay']==1)]"
   ]
  },
  {
   "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.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}