data-exercises.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:581417cb676e268e7b5d35fa06a3061d16f4ec0015bdd6cbc6406739d67975e2"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": true,
     "input": [
      "from __future__ import division\n",
      "import matplotlib.pyplot as plt\n",
      "from sqlalchemy import *\n",
      "import numpy as np\n",
      "from sklearn.linear_model import LogisticRegression as LR\n",
      "from sklearn.ensemble import RandomForestClassifier as RF\n",
      "from sklearn.ensemble import GradientBoostingClassifier as GBC\n",
      "from sklearn.svm import SVC\n",
      "from sqlalchemy.orm import sessionmaker\n",
      "from sqlalchemy import func\n",
      "from sklearn.metrics import confusion_matrix\n",
      "from sklearn.cross_validation import train_test_split\n",
      "from churndata import *\n",
      "from sklearn.preprocessing import StandardScaler\n",
      "from pandas import DataFrame,Series\n",
      "from pandas.core.groupby import GroupBy\n",
      "import pandas as pd\n",
      "from util import query_to_df\n",
      "from util import *\n",
      "import matplotlib.pyplot as plt\n",
      "import time\n",
      "from  datetime import datetime"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 40
    },
    {
     "cell_type": "code",
     "collapsed": true,
     "input": [
      "\n",
      "db = create_engine('sqlite:///forjar.db')\n",
      "\n",
      "\n",
      "metadata = MetaData(db)\n",
      "\n",
      "Session = sessionmaker(bind=db)\n",
      "\n",
      "\n",
      "session = Session()\n",
      "\n",
      "\n",
      "#used for later; defines the numerical mappings for each category\n",
      "campaign_to_num = {\n",
      "\t'TW' : 1,\n",
      "\t'RE' : 2,\n",
      "    'FB' : 3,\n",
      "    'PI' : 4\n",
      "}\n",
      "\n",
      "event_to_num = {\n",
      "   'like' : 1,\n",
      "   'share' : 2,\n",
      "   'nothing' : 3,\n",
      "   'bought' : 4\n",
      "}\n",
      "\n",
      "\n",
      "\n",
      "meal_to_num = {\n",
      "   'japanese':  1,\n",
      "   'chinese' : 2,\n",
      "   'french' : 3,\n",
      "    'german' : 4,\n",
      "    'italian' : 5,\n",
      "    'mexican' : 6,\n",
      "    'vietnamese' : 7\n",
      "}\n",
      "\n",
      "\n",
      "\n",
      "campaign_to_cost = {\n",
      "    'TW' : .25,\n",
      "    'RE' : .35,\n",
      "    'FB' : .45,\n",
      "    'PI' : .55\n",
      "}\n",
      "\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 3
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "\n",
      "Overview\n",
      "========================\n",
      "\n",
      "The core objective of this exercise is to understand sql queries, pandas data frames, and general exploratory data analysis.\n",
      "\n",
      "Towards the end, we will learn how to take our basic tools for exploratory data analysis and transform the inputs in to something\n",
      "\n",
      "appropriate for a machine learning algorithm.\n",
      "\n",
      "\n",
      "For our baseline classes, we will be using the classes from churndata.py . This contains all of the necessary classes from which\n",
      "\n",
      "we will derive all of our analysis.\n",
      "\n",
      "The associated database forjar.db contains our actual data set. We will need to use sql alchemy to load the data in.\n"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "\n",
      "Loading Data Via SQLAlchemy\n",
      "====================================\n",
      "\n",
      "First we will be loading the data from our sql lite database and doing a simple join.\n",
      "\n",
      "Our objective here will be to get a list of the users who have bought something.\n",
      "\n",
      "If we think in terms of our objectives of the site, it is to maximize revenue.\n",
      "\n",
      "In any given day, we need to understand which of our users actually tend to buy things on the site.\n",
      "\n",
      "If they aren't buying anything, we should do something about it given the data we know about them.\n",
      "\n",
      "\n",
      "Exercise:\n",
      "\n",
      "     1.  Goal: Load data from an sql database such that each you load a join of the users and events. We only want \n",
      "          the users who bought something.\n",
      "\n",
      "         Steps:\n",
      "              1. Load data via sqlalchemy from the sql lite database forjar.db\n",
      "              2. Create a query that contains a join on events and a filter on bought. Look in to sqlalchemy \n",
      "                 sessions.\n",
      "\n",
      "\n",
      "     2. Load the results of a call to query.all() in to a data frame.\n",
      "\n",
      "         Steps:\n",
      "              1. Create a dataframe with pandas\n",
      "              2. Set the columns to the query keys\n",
      "\n",
      "\n",
      "\n",
      "The goal with this particular exercise is to understand which users are buying things so we can understand what attributes are successful.\n",
      "\n",
      "Resources:\n",
      "--------------\n",
      "\n",
      "[SQLLite/SQLAlchemy](http://docs.sqlalchemy.org/en/rel_0_9/dialects/sqlite.html)\n",
      "\n",
      "[Basic Querying](http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html)\n",
      "\n",
      "[Pandas Data Structures](http://pandas.pydata.org/pandas-docs/stable/dsintro.html)\n",
      "\n",
      "\n",
      "\n",
      "\n",
      "\n",
      "\n",
      "[Solution](https://gist.github.com/agibsonccc/c5f34f6a5d24e041d535)\n",
      "---------------------------------------------------------------------------\n",
      "\n",
      "\n",
      "\n",
      "\n",
      "\n"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "\n",
      " Load data via sqlalchemy from the sql lite database forjar.db\n",
      " ===================================================================================\n",
      "\n",
      "\n"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "\n",
      "\n",
      " Create a query that contains a join on events and a filter on bought. Look in to sqlalchemy sessions.\n",
      " ------------------------------------------------------------------------------------------------------------\n"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "\n",
      ". Load the results of a call to query.all() in to a data frame.\n",
      "=============================================================================\n"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Set the columns to the query keys\n",
      "----------------------------------------------------------"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Ranking\n",
      "======================\n",
      "\n",
      "Now we will want to perform some sort of ranking, understanding attributes of who bought the most will allow us\n",
      "\n",
      "to understand who our most profitable users are. Users who buy the most do not necessarily have the highest life time value,\n",
      "\n",
      "but it is a great low hanging fruit for understanding where to begin understanding your users.\n",
      "\n",
      "\n",
      "Exercise:\n",
      "         Create a ranked grouping of the users who bought the most\n",
      "\n",
      "         Steps:\n",
      "                1. Using our previous query, we should be able to also rank the users who bought the most.\n",
      "                2.  Again put the data in to a dataframe. RUn your aggregations and group bys via the data frame.\n",
      "\n",
      "Resources\n",
      "------------\n",
      "[Queries](http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html)\n",
      "\n",
      "\n",
      "\n",
      "[Solution](https://gist.github.com/agibsonccc/7798e0908ab975a5f376)\n",
      "-----------------------------------------------------------------------------\n",
      "\n"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Using our previous query, we should be able to also rank the users who bought the most.\n",
      "-------------------------------------------------------------------------------------------------\n"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "\n",
      "Again put the data in to a dataframe. RUn your aggregations and group bys via the data frame.\n",
      "-----------------------------------------------------------------------------------------------------------\n"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Counts on campaign wise event actions\n",
      "========================================\n",
      "\n",
      "\n",
      "Now we will use Pandas to start doing some exploratory analysis.  Let's compute some fairly simple statistics on our data.\n",
      "\n",
      "Exercise:\n",
      "\n",
      "     Steps:\n",
      "           1. Load the data from sql alchemy in to a pandas data frame\n",
      "\n",
      "           2. Using pandas, calculate the mean number of times each user\n",
      "\n",
      "           3. Do a dual group by from each campaign type (facebook,twitter,pinterest,...) and each event type\n",
      "\n",
      "\n",
      "\n",
      "Resources:\n",
      "----------------------------\n",
      "\n",
      "[Pandas Blog post](http://wesmckinney.com/blog/?p=125)\n",
      "\n",
      "[Solution](https://gist.github.com/agibsonccc/f5538976ed3782cb0441)\n",
      "-----------------------------------------------------------------------\n",
      "\n",
      "\n",
      "\n",
      "\"\"\""
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Load the data from sql alchemy in to a pandas data frame\n",
      "---------------------------------------------------------------\n",
      "  "
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Using pandas, calculate the mean number of times each user\n",
      "-----------------------------------------------------------------\n"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Do a dual group by from each campaign type (facebook,twitter,pinterest,...) and each event type\n",
      "--------------------------------------------------------------------------------------------------------"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Visualizing Data\n",
      "============================\n",
      "\n",
      "Pandas has very powerful plotting built in to it alongside matplotlib. Let's generate scatterplots for all of the various user campaign to event types.\n",
      "\n",
      "Pandas has a lot of built in tools for data vis. Underneath it uses matplot lib. One key thing of note here is that pandas will not actually render your plots for you.\n",
      "\n",
      "To render plots after a call to something like dataframe.hist(), do the following:\n",
      "\n",
      "     import matplotlib.pyplot as plt\n",
      "     plt.show()\n",
      "\n",
      "This will allow us to see correlations in events all at once.\n",
      "\n",
      "Steps:\n",
      "       1. Load the data and do a join on: (Users.Campaign_ID,Event.Type,Users.id,Event.User_Id\n",
      "       2. If you have more than these columns in your dataframe subset them to this list of columns\n",
      "       3. Run an ordinal transform on each column. This is done via a 1 of k encoding mentioned earlier. \n",
      "       (Look in to the dict.get function alongside df[columnname].apply()\n",
      "       4. Now iterate over every possible combination of columns and plot a scatter plot of each.\n",
      "       Render these on the screen all at once.\n",
      "          Look in to plt.subplot for this. The end goal here is to transform the string values in \n",
      "          each column in the data frame to a numerical representation.\n",
      "\n",
      "\n"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Load the data and do a join on: Users.Campaign_ID,Event.Type,Users.id,Event.User_Id\n",
      "--------------------------------------------------------------------------------------------\n"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "If you have more than these columns in your dataframe subset them to this list of columns\n",
      "------------------------------------------------------------------------------------------------"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Run an ordinal transform on each column. This is done via a 1 of k encoding mentioned earlier.\n",
      "---------------------------------------------------------------------------------------------------\n",
      "\n",
      " Look in to the two below functions:\n",
      " -------------------------------------------------------------------------------------------\n",
      "           \n",
      "                      dict.get\n",
      "                      df[columnname].apply()\n"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Now iterate over every possible combination of columns and plot a scatter plot of each.\n",
      "---------------------------------------------------------------------------------------------------\n",
      "Render these on the screen all at once.\n",
      "-------------------------------------------------------------------------------------------------\n",
      "\n",
      "Look in to \n",
      "--------------\n",
      "\n",
      "           plt.subplot \n",
      "\n",
      "for this. The end goal here is to transform the string values in \n",
      "------------------------------------------------------------------------------------------\n",
      "each column in the data frame to a numerical representation.\n",
      "---------------------------------------------------------------------------------------------\n"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Machine Learning Data input prep\n",
      "===========================================\n",
      "\n",
      "For Machine Learning Algorithms, they can only accept numbers. Our specific task here will be to predict a label.\n",
      "\n",
      "\n",
      "Exercise:\n",
      "\n",
      "Let's build out a data frame such that we have an outcome label. Set the outcome label to be event type.\n",
      "\n",
      "From here, binarize the event type outcome to be == bought or not.\n",
      "\n",
      "[Solution](https://gist.github.com/agibsonccc/9c54fbdc8d6f9b3f53fb)\n",
      "---------------------------------------------------------------------------\n",
      "\n",
      "\n"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Thinking about this we need to break up the dataset, this is simply isolating the target column and putting it in\n",
      "-------------------------------------------------------------------------------------------------------------------\n",
      "to its own data frame and dropping the column from the intended input set.\n",
      "-------------------------------------------------------------------------------------------------------------------"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Data Normalization\n",
      "=====================================================\n",
      "\n",
      "Machine Learning algorithms typically work better if you scale the data (squish the data in to 0,1 range)\n",
      "\n",
      "Exercise:\n",
      "\n",
      "\n",
      "Query for Users.Campaign_ID,Meal.Type,Event.Type and load it in to a dataframe\n",
      "\n",
      "Transform the data in to numerical (ordinal etc, think about what we did before)\n",
      "\n",
      "Split out the feature set columns from the outcome label and normalize the given features.\n",
      "\n",
      "Resources:\n",
      "----------------------\n",
      "\n",
      "[Scikit learn preprocessing](http://scikit-learn.org/stable/modules/preprocessing.html)\n",
      "\n",
      "[Solution](https://gist.github.com/agibsonccc/ec5062e81a4817cf35d4)\n",
      "---------------------------------------------------------------------------\n",
      "\n"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "\n",
      "\n",
      "\n",
      "   Split out the feature set columns from the outcome label and normalize the giv\n"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "  Query for Users.Campaign_ID,Meal.Type,Event.Type and load it in to a dataframe\n",
      "  -------------------------------------------------------------------------------------\n"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Transform the data in to numerical (ordinal etc, think about what we did before)\n",
      "--------------------------------------------------------------------------------------------- "
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "\n",
      "\n",
      "def transform_column(df,column_name,fn):\n",
      "    \"\"\"\n",
      "    Transforms a column with the given function\n",
      "    \"\"\"\n",
      "    df[column_name] = df[column_name].apply(fn)\n",
      " \n",
      "\n",
      "    \n",
      "\n",
      "def occurred_in_last_k_days(datetime,k):\n",
      "     now = datetime.now()\n",
      "     days = now - timedelta(days=k)\n",
      "     return datetime >= days\n",
      "    \n",
      "\n",
      "def vectorize_feature_index(df,label_column):\n",
      "    feature_names = []\n",
      "    global X\n",
      "    for feature_name in df.columns.values:\n",
      "        print feature_name\n",
      "        if feature_name != label_column:\n",
      "            if feature_name not in feature_names:\n",
      "                feature_names.append(feature_name)\n",
      "    \n",
      "    X = df[feature_names].index\n",
      "    scaler = StandardScaler()\n",
      "    X = scaler.fit_transform(X)\n",
      "    train_index,test_index = train_test_split(df.index)\n",
      "    X = df[feature_names].as_matrix().astype(np.float)\n",
      "    y = df[label_column].index\n",
      "    y_test = y[test_index].astype(float)\n",
      "\n",
      "q = session.query(Users).join(Event).add_entity(Event)\n",
      "df= query_to_df(session,q)\n",
      "df = df.drop(['Users_id','Event_id','Event_User_Id','Event_Meal_Id','Users_Created_Date'],1)\n",
      "print df.columns\n",
      "print df.Users_date.dtype\n",
      "def to_epoch(time_input):\n",
      "    return (time_input - datetime(1970,1,1)).total_seconds()\n",
      "\n",
      "transform_column(df,'Event_Type',event_to_num.get)\n",
      "transform_column(df,'Users_Campaign_ID',campaign_to_num.get)\n",
      "transform_column(df,'Users_date',to_epoch)\n",
      "transform_column(df,'Event_date',to_epoch)\n",
      "print df\n",
      "vectorize_feature_index(df,'Event_Type')\n",
      "\n",
      "print X\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "ename": "NameError",
       "evalue": "name 'session' is not defined",
       "output_type": "pyerr",
       "traceback": [
        "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
        "\u001b[1;32m<ipython-input-2-79dc604e9bd0>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m     33\u001b[0m     \u001b[0my_test\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0my\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mtest_index\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfloat\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     34\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 35\u001b[1;33m \u001b[0mq\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msession\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mUsers\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mjoin\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mEvent\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0madd_entity\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mEvent\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     36\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m=\u001b[0m \u001b[0mquery_to_df\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msession\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mq\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     37\u001b[0m \u001b[0mdf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdrop\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'Users_id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'Event_id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'Event_User_Id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'Event_Meal_Id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'Users_Created_Date'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
        "\u001b[1;31mNameError\u001b[0m: name 'session' is not defined"
       ]
      }
     ],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}