AEU Network Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PART 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Industry member with:\n",
    "•         High percentage of supervisor transactions (include the average as a benchmark) \n",
    "•         High percentage of transactions with one staff member (include the average as a benchmark)\n",
    "•         High percentage of transactions with one staff member at multiple times of the day \n",
    "•         High percentage of transactions with one staff member over different transaction types\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 315,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Length before drop 761192\n",
      "lenght after drop 50915\n",
      "Delivered Service \n",
      "set(['CERTIFICATE OF CORRECTION', 'DROP OFF (MULTIPLE SUBMISSION)', 'TICKET DOES NOT MATCH ID', 'CERTIFICATE OF CORRECTION (IN-PERSON)', 'INQUIRY', 'PICK-UP RESULTS', 'UNPREPARED', 'DROP OFF', 'INQUIRY, PICK UP RESULTS', 'PICK UP RESULTS', 'DROP OFF (10X10)'])\n",
      "Service Catgories\n",
      "set(['1ST-REP- AEU PICK-UP', '1ST-RESP- AEU GENERAL', '1ST-RESP- AEU SERVICES', 'RESP - AEU SERVICES', '1ST-REP-AEU INQUIRIES', 'REP-AEU DROP-OFF', 'RESP-AEU INQUIRIES', 'REP-AEU PICK-UP', 'REP-AEU INQUIRIES', '1ST-RESP-AEU PICK-UP', '1ST-RESP-AEU INQUIRIES', '1ST-RESP-AEU DROP-OFF', 'REP -  AEU SERVICES', '1ST-REP- AEU SERVICES', 'RESP-AEU PICK-UP', 'RESP-AEU DROP-OFF', '1ST-REP-AEU DROP-OFF'])\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "import numpy as np\n",
    "\n",
    "path = \"C:\\\\Users\\\\dmehri\\\\Documents\\\\DATA\\\\AEU Network Analysis\\\\\"\n",
    "\n",
    "df = pd.read_csv(path + \"AEU Data Sept 2017 - Sept 2018.csv\")\n",
    "\n",
    "col_names = sorted(list(df.columns.values))\n",
    "\n",
    "#strip all columns of white space and convert to uppper\n",
    "for i in range(0, len(col_names)):\n",
    "    df[col_names[i]] = df[col_names[i]].astype(str)\n",
    "    df[col_names[i]] = df[col_names[i]].map(str.strip)\n",
    "    df[col_names[i]] = df[col_names[i]].map(str.upper)\n",
    "    \n",
    "df = df.rename(columns={'Service Category': 'Service_Category'})\n",
    "\n",
    "    \n",
    "#FILTER ONLY FOR AEU    \n",
    "print \"Length before drop\", len(df)\n",
    "df = df[df.Service_Category.str.contains(\"AEU\") == True]\n",
    "df = df.reset_index(drop=True)\n",
    "print \"lenght after drop\", len(df)\n",
    "\n",
    "print \"Delivered Service \"\n",
    "del_service = set(df[\"DELIVERED_SERVICE\"].tolist() )\n",
    "print del_service\n",
    "\n",
    "print \"Service Catgories\"\n",
    "serv_cat = set(df[\"Service_Category\"].tolist() )\n",
    "print serv_cat\n",
    "    \n",
    "    \n",
    "df[\"STAFF NAME\"] = df[\"STAFF FIRST_NAME\"] + \" \" + df[\"STAFF LAST_NAME\"]\n",
    "\n",
    "df = df.drop('STAFF FIRST_NAME', 1)\n",
    "df = df.drop('STAFF LAST_NAME', 1)  \n",
    "df = df.drop('SECONDS', 1)\n",
    "df = df.drop('Staff User Name', 1)\n",
    "\n",
    "df[\"CUSTOMER NAME RAW\"] = df['CUSTOMER NAME']\n",
    "\n",
    "#Clean customer name\n",
    "df['CUSTOMER NAME'] = df['CUSTOMER NAME'].str.replace(',', ' ')\n",
    "#df['CUSTOMER NAME'] = df['CUSTOMER NAME'].str.replace(',', ' ')\n",
    "df['CUSTOMER NAME'] = df['CUSTOMER NAME'].str.replace('CUSTOMER ', '')\n",
    "df['CUSTOMER NAME'] = df['CUSTOMER NAME'].str.replace('HOMEOWNER ', '')\n",
    "\n",
    "df[\"CUSTOMER NAME\"] = df[\"CUSTOMER NAME\"].map(str.strip)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 316,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#df.to_csv(path + \"testAEU.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CREATE INDUSTRY NAME DATAFRAME"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 317,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "number of customers 6704\n"
     ]
    }
   ],
   "source": [
    "pd.set_option('chained_assignment', None) \n",
    "\n",
    "industry_names =  list(set(df[\"CUSTOMER NAME\"].tolist() ) ) \n",
    "print \"number of customers\", len(industry_names)\n",
    "\n",
    "#industry_names_df = pd.DataFrame(industry_names)\n",
    "\n",
    "df_final = df[[\"CUSTOMER NAME\"] ] \n",
    "\n",
    "df_final[\"Transaction Count\"] = 1\n",
    "\n",
    "df_final = df_final.groupby(['CUSTOMER NAME']).sum()\n",
    "\n",
    "#Reset back to dataframe:\n",
    "df_final = df_final.add_suffix('').reset_index()\n",
    "\n",
    "\n",
    "#industry_names_df = industry_names_df.rename(columns={0: 'CUSTOMER NAME'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SUPERVISOR INTERACTION\n",
    "Two Supervisors: Declan Daly, Dominique Livingston"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 318,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2 = df.copy()\n",
    "\n",
    "df2['Livingston Transaction'] = np.where(df2['STAFF NAME'].str.contains(\"DOMINIQUE LIVINGSTON\", case=False, na=False), 1, 0)\n",
    "df2['Daly Transaction'] = np.where(df2['STAFF NAME'].str.contains(\"DECLAN DALY\", case=False, na=False), 1, 0)\n",
    "\n",
    "df2 = df2[[\"CUSTOMER NAME\", \"Livingston Transaction\", \"Daly Transaction\"]]\n",
    "\n",
    "df2G = (df2.groupby('CUSTOMER NAME').agg({'Livingston Transaction':'sum', 'Daly Transaction': 'sum'}).reset_index().rename(columns={'Livingston Transaction':'Livingston Transaction'}) )\n",
    "\n",
    "\n",
    "#MERGE WTIH INDUSTRY NAME DF\n",
    "df_final = pd.merge(df_final, df2G, how='left', on=['CUSTOMER NAME'])\n",
    "\n",
    "df_final[\"Total Supervisor Transaction\"] = df2G[\"Daly Transaction\"] + df2G[\"Livingston Transaction\"]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Summary Stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 319,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7.59471957041\n",
      "2.0\n"
     ]
    }
   ],
   "source": [
    "average_transactions = df_final[\"Transaction Count\"].mean()\n",
    "\n",
    "print average_transactions\n",
    "\n",
    "median_transactions = df_final[\"Transaction Count\"].median()\n",
    "\n",
    "print median_transactions\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SUBSET FOR >= 5 TRANSACTIONS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 320,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Length before subset 6704\n",
      "Length after subset 1447\n"
     ]
    }
   ],
   "source": [
    "df_final = df_final.sort_values(by = 'Transaction Count', ascending=False).reset_index(drop=True)\n",
    "\n",
    "print \"Length before subset\", len(df_final)\n",
    "\n",
    "df_final = df_final[df_final[\"Transaction Count\"] >= 5]\n",
    "df_final = df_final.reset_index(drop=True)\n",
    "\n",
    "print \"Length after subset\", len(df_final)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SUPERVISOR TRANSACTION PERCENT "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 321,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5.99488597097\n",
      "0.257014512785\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "count    1447.000000\n",
       "mean        5.994886\n",
       "std        10.526524\n",
       "min         0.000000\n",
       "25%         0.000000\n",
       "50%         0.000000\n",
       "75%         8.300000\n",
       "max        66.700000\n",
       "Name: % Daly, dtype: float64"
      ]
     },
     "execution_count": 321,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final[\"Transaction Count\"] = df_final[\"Transaction Count\"].astype(float)\n",
    "df_final[\"% Daly\"] = ( (df_final[\"Daly Transaction\"]/df_final[\"Transaction Count\"]).round(3) ) *100\n",
    "mean_daly = df_final[\"% Daly\"].mean()\n",
    "print mean_daly\n",
    "\n",
    "df_final[\"% Livingston\"] = ( (df_final[\"Livingston Transaction\"]/df_final[\"Transaction Count\"] ).round(3)) *100\n",
    "mean_liv = df_final[\"% Livingston\"].mean()\n",
    "print mean_liv\n",
    "\n",
    "df_final[\"% Daly\"].describe().transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 322,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    1447.000000\n",
       "mean        5.994886\n",
       "std        10.526524\n",
       "min         0.000000\n",
       "25%         0.000000\n",
       "50%         0.000000\n",
       "75%         8.300000\n",
       "max        66.700000\n",
       "Name: % Daly, dtype: float64"
      ]
     },
     "execution_count": 322,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "std = df_final[\"% Daly\"].describe().transpose()\n",
    "\n",
    "std"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "BAD ACTOR FLAG"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 323,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#badAllRoles.to_csv(path + \"test.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 324,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018-10-17 13:26:37.729000\n",
      "len before drop 1224\n",
      "len after drop 1072\n",
      "len after drop 965\n",
      "number of bad actors 47\n",
      "Start fuzzy matching\n",
      "iteration 0\n",
      "2018-10-17 13:26:37.797000\n",
      "iteration 500\n",
      "2018-10-17 13:26:37.994000\n",
      "iteration 1000\n",
      "2018-10-17 13:26:38.179000\n",
      "**********\n",
      "Total number of bad actors including via fuzzy logic 56\n"
     ]
    }
   ],
   "source": [
    "import ngram\n",
    "#ngram.NGram.compare('Ham','Spam',N=1)\n",
    "from datetime import datetime\n",
    "print str(datetime.now())\n",
    "from operator import itemgetter\n",
    "\n",
    "\n",
    "badAllRoles = pd.read_csv(\"C:\\\\Users\\\\dmehri\\\\Documents\\\\DATA\\\\AEU Network Analysis\\\\Bad Actors for Darius AEU Transaction Analysis.csv\")\n",
    "\n",
    "print \"len before drop\", len(badAllRoles)\n",
    "\n",
    "badAllRoles = badAllRoles.dropna(subset = ['First Name']).reset_index(drop=True)\n",
    "\n",
    "print \"len after drop\", len(badAllRoles)\n",
    "\n",
    "col_names = sorted(list(badAllRoles.columns.values))\n",
    "\n",
    "for i in range(0, len(col_names)):\n",
    "    badAllRoles[col_names[i]] = badAllRoles[col_names[i]].astype(str)\n",
    "    badAllRoles[col_names[i]] = badAllRoles[col_names[i]].map(str.strip)\n",
    "    badAllRoles[col_names[i]] = badAllRoles[col_names[i]].map(str.upper)\n",
    "\n",
    "\n",
    "badAllRoles[\"Name\"] = badAllRoles[\"First Name\"] + \" \" + badAllRoles[\"Last Name\"]\n",
    "\n",
    "badAllRoles = badAllRoles[[\"Name\", \"Role\"]]\n",
    "\n",
    "badAllRoles = badAllRoles.sort_values(by = 'Name', ascending=True).reset_index(drop=True)\n",
    "\n",
    "badAllRoles = badAllRoles.drop_duplicates(subset=['Name', 'Role']).reset_index(drop=True)\n",
    "\n",
    "print \"len after drop\", len(badAllRoles)\n",
    "\n",
    "\n",
    "#badBusiness = badBusiness[\"Name\"].tolist()\n",
    "#badPeople = badPeople[\"Name\"].tolist()\n",
    "\n",
    "#badActor = badBusiness + badPeople\n",
    "\n",
    "count_bad = 0\n",
    "\n",
    "\n",
    "badActor = badAllRoles[\"Name\"].tolist()\n",
    "\n",
    "df_final[\"Bad Actor Flag\"] = 0\n",
    "\n",
    "for i in range(0, len(df_final) ):\n",
    "    if df_final[\"CUSTOMER NAME\"][i] in badActor:\n",
    "        df_final[\"Bad Actor Flag\"][i] = 1\n",
    "        count_bad += 1 \n",
    "               \n",
    "print \"number of bad actors\", count_bad\n",
    "\n",
    "\n",
    "print \"Start fuzzy matching\"\n",
    "\n",
    "#FUZZY MATCHING\n",
    "\n",
    "#df_final[\"Name Match\"] = \"\"\n",
    "#df_final[\"Name Match Score\"] = \"\"\n",
    "\n",
    "\n",
    "def my_fuzzy(target, match):\n",
    "  \n",
    "    G = ngram.NGram(match)\n",
    "    \n",
    "    remove_list = []\n",
    "        \n",
    "    for i in range(0, len(target)):\n",
    "    #for i in range(0, 1):\n",
    "        #print \"*****************************\"\n",
    "        tname = target[\"CUSTOMER NAME\"][i]\n",
    "        #print i\n",
    "        #print tname\n",
    "    \n",
    "        if i%500 == 0:\n",
    "            print \"iteration\", i\n",
    "            print str(datetime.now())\n",
    "\n",
    "      \n",
    "        ratio_list =  G.search(tname,threshold=0.63)\n",
    "        #ratio_list =  G.search(tname)\n",
    "\n",
    "        \n",
    "        #print ratio_list[0][1]\n",
    "        \n",
    "        if len(ratio_list) > 0:\n",
    "            #target[\"Name Match\"][i] = ratio_list[0][0]\n",
    "            #target[\"Name Match Score\"][i] = ratio_list[0][1]\n",
    "            \n",
    "            if ratio_list[0][1] > 0.66:\n",
    "                target[\"Bad Actor Flag\"][i] = 1\n",
    "            \n",
    "            \n",
    "    return target\n",
    "\n",
    "df_final = my_fuzzy(df_final, badActor)\n",
    "\n",
    "count_bad = df_final[df_final[\"Bad Actor Flag\"] == 1]\n",
    "print \"**********\"\n",
    "print \"Total number of bad actors including via fuzzy logic\", len(count_bad)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 325,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#df_final.to_csv(path + \"test.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "PERCENT OF TRANSACTIONS WITH ONE STAFF MEMBER"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 326,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "500\n",
      "1000\n"
     ]
    }
   ],
   "source": [
    "industry_names = sorted(list(set(df_final[\"CUSTOMER NAME\"].tolist())))\n",
    "\n",
    "data_list = []\n",
    "\n",
    "df2 = df.copy()\n",
    "\n",
    "df2 = df2[[\"CUSTOMER NAME\", \"STAFF NAME\"]]\n",
    "\n",
    "df2[\"Count\"] = 1\n",
    "\n",
    "df2 = df2.groupby(['CUSTOMER NAME', 'STAFF NAME']).sum()\n",
    "df2 = df2.add_suffix('').reset_index()\n",
    "\n",
    "#for i in range(0, 1):\n",
    "for i in range(0, len(industry_names)):\n",
    "    \n",
    "    if i%500 == 0:\n",
    "        print i\n",
    "    \n",
    "    df3 = df2[df2[\"CUSTOMER NAME\"] == industry_names[i]]\n",
    "    df3 = df3.sort_values(by = 'Count', ascending=False).reset_index(drop=True)\n",
    "    \n",
    "    sum_trans = df3[\"Count\"].sum()\n",
    "\n",
    "    df3[\"p\"] = df3[\"Count\"]/float(sum_trans)\n",
    "    df3[\"p\"] = (df3[\"p\"].round(3)) * 100\n",
    "    \n",
    "    perc_list = []\n",
    "    \n",
    "    if len(df3) == 1:\n",
    "        for j in range(0, 3):\n",
    "            if (j==1 or j==2):\n",
    "                perc_list.append(['NA', 'NA'])\n",
    "            else:\n",
    "                perc_list.append([df3[\"STAFF NAME\"][j], df3[\"p\"][j] ])\n",
    "    \n",
    "    elif len(df3) == 2:\n",
    "        for j in range(0, 3):\n",
    "            if j == 2:\n",
    "                perc_list.append(['NA', 'NA'])\n",
    "            else:\n",
    "                perc_list.append([df3[\"STAFF NAME\"][j], df3[\"p\"][j] ])\n",
    "    \n",
    "    elif len(df3) >= 3:\n",
    "        for j in range(0, 3):\n",
    "            perc_list.append([df3[\"STAFF NAME\"][j], df3[\"p\"][j] ])\n",
    "    \n",
    "    #df3 = df3.reset_index()\n",
    "    #data_list.append([industry_names[i], perc_list[0][0], perc_list[0][1], perc_list[1][0], perc_list[1][1], perc_list[2][0], perc_list[2][1]  ])\n",
    "    #data_list.append([industry_names[i], perc_list[0][0], perc_list[0][1], perc_list[1][0], perc_list[1][1]  ])\n",
    "    data_list.append([industry_names[i], perc_list[0][0], perc_list[0][1]])\n",
    "    \n",
    "    #print \"sum of trans\", sum_trans\n",
    "    #print perc_list\n",
    "    #print df3\n",
    "    \n",
    "    #print len(df3)\n",
    "\n",
    "    \n",
    "#trans = pd.DataFrame(data_list, columns=('CUSTOMER NAME', 'High Staff Trans 1', 'Percent 1', 'High Staff Trans 2', 'Percent 2', 'High Staff Trans 3', 'Percent 3'))\n",
    "#trans = pd.DataFrame(data_list, columns=('CUSTOMER NAME', 'High Staff Trans 1', 'Percent 1', 'High Staff Trans 2', 'Percent 2'))\n",
    "trans = pd.DataFrame(data_list, columns=('CUSTOMER NAME', 'High Staff Trans', 'Percent'))\n",
    "\n",
    "#MERGE WTIH INDUSTRY NAME DF\n",
    "df_final = pd.merge(df_final, trans, how='left', on=['CUSTOMER NAME'])\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "HIGH PERCENT OF TRANSACTION TYPE WITH ONE STAFF TRANS HIGHEST PERCENT "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 327,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11\n",
      "0\n",
      "500\n",
      "1000\n"
     ]
    }
   ],
   "source": [
    "del_serv = set(df[\"DELIVERED_SERVICE\"].tolist() )\n",
    "\n",
    "print len(del_serv)\n",
    "\n",
    "df2 = df.copy()\n",
    "\n",
    "#get only the names in df_final\n",
    "industry_names = sorted(list(set(df_final[\"CUSTOMER NAME\"].tolist())))\n",
    "\n",
    "df2 = df2[df2['CUSTOMER NAME'].isin(industry_names)]\n",
    "df2 = df2.reset_index(drop=True)\n",
    "\n",
    "\n",
    "df2 = df2[[\"CUSTOMER NAME\", \"STAFF NAME\", \"DELIVERED_SERVICE\"]]\n",
    "\n",
    "df2[\"Count\"] = 1\n",
    "df2G = df2.groupby(['CUSTOMER NAME', 'STAFF NAME', 'DELIVERED_SERVICE']).sum()\n",
    "df2G = df2G.add_suffix('').reset_index()\n",
    "\n",
    "\n",
    "df_final[\"High Staff Trans Delivered Servc\"] = \"\"\n",
    "df_final[\"# Delivered Servc\"] = \"\"\n",
    "df_final[\"% Delivered Servc\"] = \"\"\n",
    "\n",
    "for i in range(0, len(df_final)):\n",
    "    if (i%500) == 0:\n",
    "        print i\n",
    "    \n",
    "    cname = df_final[\"CUSTOMER NAME\"][i]\n",
    "    hsname = df_final[\"High Staff Trans\"][i]\n",
    "    \n",
    "     \n",
    "    df3G = df2G[df2G['CUSTOMER NAME'].isin([cname])]\n",
    "    df3G = df3G[df3G['STAFF NAME'].isin([hsname])]\n",
    "    df3G = df3G.reset_index(drop=True)\n",
    "    \n",
    "    df3G = df3G.sort_values(by = 'Count', ascending=False).reset_index(drop=True)\n",
    "    \n",
    "    df_final[\"High Staff Trans Delivered Servc\"][i] = df3G[\"DELIVERED_SERVICE\"][0]\n",
    "    df_final[\"# Delivered Servc\"][i] = df3G[\"Count\"][0]\n",
    "    \n",
    "    sum_ds = df3G[\"Count\"].sum()\n",
    "    \n",
    "    df_final[\"% Delivered Servc\"][i] = (df3G[\"Count\"][0]/float(sum_ds) ) * 100\n",
    "    \n",
    "    \n",
    "    #print df3G\n",
    "    \n",
    "df_final[\"% Delivered Servc\"] = df_final[\"% Delivered Servc\"].astype(float)\n",
    "df_final =   df_final.round(1)  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "HIGH PERCENT OF TRANSACTIONS WITH ONE STAFF MEMBER MEETING MORE THAN 2 TIMES ON ONE DAY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 328,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Length of session dummies 41540\n",
      "Length of df2 41540\n",
      "Difference 0\n",
      "0\n",
      "500\n",
      "1000\n"
     ]
    }
   ],
   "source": [
    "df2 = df.copy()\n",
    "\n",
    "#get only the names in df_final\n",
    "industry_names = sorted(list(set(df_final[\"CUSTOMER NAME\"].tolist())))\n",
    "\n",
    "df2 = df2[df2['CUSTOMER NAME'].isin(industry_names)]\n",
    "df2 = df2.reset_index(drop=True)\n",
    "\n",
    "df2[\"DateTime\"] = df2[\"YEAR\"] + \"-\" + df2[\"MONTH\"] + \"-\" + df2[\"DAY\"] + \" \" + df2[\"HOUR\"] + \":\" + df2[\"MINUTE\"]\n",
    "\n",
    "df2[\"Date\"] = df2[\"YEAR\"] + \"-\" + df2[\"MONTH\"] + \"-\" + df2[\"DAY\"] \n",
    "\n",
    "df2[\"DateTime\"] = pd.to_datetime(df2[\"DateTime\"])\n",
    "\n",
    "df2[\"Date\"] = pd.to_datetime(df2[\"Date\"])\n",
    "\n",
    "\n",
    "df2 = df2.assign(session=pd.cut(df2.DateTime.dt.hour,[0,6,12,18,24],labels=['Night','Morning','Afternoon','Evening']))\n",
    "\n",
    "session_dummies = pd.get_dummies(df2.session, prefix='session')\n",
    "print \"Length of session dummies\", len(session_dummies)\n",
    "print \"Length of df2\", len(df2)\n",
    "print \"Difference\", len(df2) - len(session_dummies)\n",
    "df2 = pd.concat([df2, session_dummies], axis=1)\n",
    "#df2 = df2.drop('JobType', 1)\n",
    "\n",
    "df2 = df2.drop('session_Night', 1)\n",
    "df2 = df2.drop('session_Evening', 1)\n",
    "\n",
    "df2 = df2[[\"CUSTOMER NAME\", \"STAFF NAME\", \"Date\", \"session_Morning\", \"session_Afternoon\"]]\n",
    "\n",
    "df2G = df2.groupby(['CUSTOMER NAME', 'STAFF NAME', 'Date']).sum()\n",
    "\n",
    "df2G = df2G.add_suffix('').reset_index()\n",
    "\n",
    "df2G[\"Total Day\"] = df2G[\"session_Morning\"] + df2G[\"session_Afternoon\"]\n",
    "\n",
    "\n",
    "#Merge df_final with df2G\n",
    "df_final[\"High Staff Day Trans\"] = \"\"\n",
    "df_final[\"Total Day\"] = \"\"\n",
    "df_final[\"session_Morning\"] = \"\"\n",
    "df_final[\"session_Afternoon\"] = \"\"\n",
    "\n",
    "\n",
    "\n",
    "for i in range(0, len(df_final)):\n",
    "#for i in range(0, 30):\n",
    "    \n",
    "    if (i%500) == 0:\n",
    "        print i\n",
    "    \n",
    "    cname = df_final[\"CUSTOMER NAME\"][i]\n",
    "    #hsname = df_final[\"High Staff Trans\"][i]\n",
    "    \n",
    "    df3G = df2G[df2G['CUSTOMER NAME'].isin([cname])]\n",
    "    #df3G = df3G[df3G['STAFF NAME'].isin([hsname])]\n",
    "    df3G = df3G.reset_index(drop=True)\n",
    "    \n",
    "    df3G = df3G.sort_values(by = 'Total Day', ascending=False).reset_index(drop=False)\n",
    "\n",
    "\n",
    "    df_final[\"High Staff Day Trans\"][i] = df3G[\"STAFF NAME\"][0]\n",
    "    df_final[\"Total Day\"][i] = df3G[\"Total Day\"][0]\n",
    "    df_final[\"session_Morning\"][i] = df3G[\"session_Morning\"][0]\n",
    "    df_final[\"session_Afternoon\"][i] = df3G[\"session_Afternoon\"][0]\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 329,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#df_final.to_csv(path + \"test.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 330,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#df.to_csv(path + \"test.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 331,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#qn.to_csv(path + \"customer_names.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# PART 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Industry member with more than two cerfificat of corrections and drop offs in one day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 332,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Length of service_dummies dummies 41540\n",
      "Length of df2 41540\n",
      "Difference 0\n",
      "0\n",
      "500\n",
      "1000\n"
     ]
    }
   ],
   "source": [
    "df2 = df.copy()\n",
    "\n",
    "#print \"Delivered Service \"\n",
    "#del_service = set(df[\"DELIVERED_SERVICE\"].tolist() )\n",
    "#print del_service\n",
    "\n",
    "industry_names = sorted(list(set(df_final[\"CUSTOMER NAME\"].tolist())))\n",
    "\n",
    "df2 = df2[df2['CUSTOMER NAME'].isin(industry_names)]\n",
    "df2 = df2.reset_index(drop=True)\n",
    "\n",
    "df2[\"Date\"] = df2[\"YEAR\"] + \"-\" + df2[\"MONTH\"] + \"-\" + df2[\"DAY\"] \n",
    "\n",
    "df2[\"Date\"] = pd.to_datetime(df2[\"Date\"])\n",
    "\n",
    "df2[\"Date\"]  = df2[\"Date\"].apply(lambda x: x.date())\n",
    "\n",
    "df2 = df2[[\"CUSTOMER NAME\", \"DELIVERED_SERVICE\", \"Date\"]]\n",
    "\n",
    "service_dummies = pd.get_dummies(df2.DELIVERED_SERVICE, prefix='Delivered_Service')\n",
    "print \"Length of service_dummies dummies\", len(service_dummies)\n",
    "print \"Length of df2\", len(df2)\n",
    "print \"Difference\", len(df2) - len(service_dummies)\n",
    "df2 = pd.concat([df2, service_dummies], axis=1)\n",
    "\n",
    "#aggregate drop off and certificate of corrections in person\n",
    "df2[\"Drop-off & Cert Corr\"] = df2[\"Delivered_Service_DROP OFF\"] + df2[\"Delivered_Service_DROP OFF (10X10)\"] + df2[\"Delivered_Service_DROP OFF (MULTIPLE SUBMISSION)\"] + df2[\"Delivered_Service_CERTIFICATE OF CORRECTION (IN-PERSON)\"]\n",
    "\n",
    "df2 = df2.drop('Delivered_Service_DROP OFF', 1)\n",
    "df2 = df2.drop('Delivered_Service_DROP OFF (10X10)', 1)\n",
    "df2 = df2.drop('Delivered_Service_DROP OFF (MULTIPLE SUBMISSION)', 1)\n",
    "df2 = df2.drop('Delivered_Service_CERTIFICATE OF CORRECTION (IN-PERSON)', 1)\n",
    "\n",
    "\n",
    "#df2 = df2[[\"CUSTOMER NAME\",\"Date\", \"Delivered_Service_CERTIFICATE OF CORRECTION (IN-PERSON)\", \"Drop-off All\"]]\n",
    "df2 = df2[[\"CUSTOMER NAME\",\"Date\", \"Drop-off & Cert Corr\"]]\n",
    "\n",
    "df2G = df2.groupby(['CUSTOMER NAME',  'Date']).sum()\n",
    "\n",
    "df2G = df2G.add_suffix('').reset_index()\n",
    "\n",
    "#df_final[\">2 Cert Corr Flag\"] = \"No\"\n",
    "#df_final[\">2 Cert Corr Dates\"]  = \"\"\n",
    "\n",
    "#df_final[\">2 Drop Off Flag\"] = \"No\"\n",
    "#df_final[\">2 Drop Off Dates\"]  = \"\"\n",
    "\n",
    "df_final[\">2 COC & DO Flag\"] = \"No\"\n",
    "df_final[\">2 COC & DO Dates\"]  = \"\"\n",
    "\n",
    "\n",
    "\n",
    "for i in range(0, len(df_final)):\n",
    "#for i in range(0, 3):\n",
    "    \n",
    "    if (i%500) == 0:\n",
    "        print i\n",
    "    \n",
    "    cname = df_final[\"CUSTOMER NAME\"][i]\n",
    "    \n",
    "    df3G = df2G[df2G['CUSTOMER NAME'].isin([cname])]\n",
    "    df3G = df3G.reset_index(drop=True)\n",
    "    \n",
    "    #df3G = df3G.sort_values(by = 'Delivered_Service_CERTIFICATE OF CORRECTION (IN-PERSON)', ascending=False).reset_index(drop=False)\n",
    "    \n",
    "    #df4G = df3G[df3G[\"Delivered_Service_CERTIFICATE OF CORRECTION (IN-PERSON)\"] > 2]\n",
    "    \n",
    "    df4G = df3G[df3G[\"Drop-off & Cert Corr\"] > 2]\n",
    "    \n",
    "    if len(df4G) > 0:\n",
    "        df_final[\">2 COC & DO Flag\"][i] = \"Yes\"\n",
    "        date_list = df4G[\"Date\"].tolist()\n",
    "        \n",
    "        #convert date to string\n",
    "        date_list2 = []\n",
    "        for j in range(0, len(date_list)):\n",
    "            date_list2.append(date_list[j].strftime('%m/%d/%Y'))\n",
    "            #print date_list[j].strftime('%m/%d/%Y')\n",
    "        \n",
    "        df_final[\">2 COC & DO Dates\"][i] =  date_list2\n",
    "        \n",
    "    #df4G = df3G[df3G[\"Drop-off All\"] > 2]\n",
    "    \n",
    "    #if len(df4G) > 0:\n",
    "    #    df_final[\">2 Drop Off Flag\"][i] = \"Yes\"\n",
    "    #    date_list = df4G[\"Date\"].tolist()\n",
    "    #    df_final[\">2 Drop Off Dates\"][i] =  date_list\n",
    "        \n",
    "\n",
    "    #print df3G"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 333,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#df_final.to_csv(path + \"test.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 334,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#df2G.to_csv(path + \"test.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "HIGH PERCENTAGE OF CERTIFICATE OF CORRECTIONS (IN PERSON) AND DROP OFF TRANSACTIONS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 335,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Length of service_dummies dummies 41540\n",
      "Length of df2 41540\n",
      "Difference 0\n"
     ]
    }
   ],
   "source": [
    "df2 = df.copy()\n",
    "\n",
    "#print \"Delivered Service \"\n",
    "#del_service = set(df[\"DELIVERED_SERVICE\"].tolist() )\n",
    "#print del_service\n",
    "\n",
    "industry_names = sorted(list(set(df_final[\"CUSTOMER NAME\"].tolist())))\n",
    "\n",
    "df2 = df2[df2['CUSTOMER NAME'].isin(industry_names)]\n",
    "df2 = df2.reset_index(drop=True)\n",
    "\n",
    "df2 = df2[[\"CUSTOMER NAME\", \"DELIVERED_SERVICE\"]]\n",
    "\n",
    "service_dummies = pd.get_dummies(df2.DELIVERED_SERVICE, prefix='Delivered_Service')\n",
    "print \"Length of service_dummies dummies\", len(service_dummies)\n",
    "print \"Length of df2\", len(df2)\n",
    "print \"Difference\", len(df2) - len(service_dummies)\n",
    "df2 = pd.concat([df2, service_dummies], axis=1)\n",
    "\n",
    "#df2 = df2[[\"CUSTOMER NAME\",\"Delivered_Service_CERTIFICATE OF CORRECTION (IN-PERSON)\", \"Delivered_Service_DROP OFF\"]]\n",
    "\n",
    "#aggregate drop off and certificate of corrections in person\n",
    "df2[\"Drop-off & Cert Corr\"] = df2[\"Delivered_Service_DROP OFF\"] + df2[\"Delivered_Service_DROP OFF (10X10)\"] + df2[\"Delivered_Service_DROP OFF (MULTIPLE SUBMISSION)\"] + df2[\"Delivered_Service_CERTIFICATE OF CORRECTION (IN-PERSON)\"]\n",
    "\n",
    "df2 = df2.drop('Delivered_Service_DROP OFF', 1)\n",
    "df2 = df2.drop('Delivered_Service_DROP OFF (10X10)', 1)\n",
    "df2 = df2.drop('Delivered_Service_DROP OFF (MULTIPLE SUBMISSION)', 1)\n",
    "df2 = df2.drop('Delivered_Service_CERTIFICATE OF CORRECTION (IN-PERSON)', 1)\n",
    "\n",
    "df2G = df2.groupby(['CUSTOMER NAME']).sum()\n",
    "df2G = df2G.add_suffix('').reset_index()\n",
    "\n",
    "\n",
    "\n",
    "#Add all of the rows of data\n",
    "col_names = sorted(list(df2G.columns.values))\n",
    "df2G[\"Total\"] = 0\n",
    "#strip all columns of white space and convert to uppper\n",
    "for i in range(1, len(col_names)):\n",
    "    df2G[\"Total\"] = df2G[\"Total\"] + df2G[col_names[i]]\n",
    "    \n",
    "    \n",
    "#df2G[\"%Certificat of Corr in-Person\"] = (df2G[\"Delivered_Service_CERTIFICATE OF CORRECTION (IN-PERSON)\"]/df2G[\"Total\"]) *100\n",
    "#df2G[\"%Drop-off\"] = (df2G[\"Drop-off All\"]/df2G[\"Total\"]) *100\n",
    "\n",
    "df2G[\"%COC & DO\"] = (df2G[\"Drop-off & Cert Corr\"]/df2G[\"Total\"]) *100\n",
    "\n",
    "\n",
    "df2G = df2G.round(1)\n",
    "df2G = df2G[[\"CUSTOMER NAME\",  \"%COC & DO\"]]\n",
    "\n",
    "df_final = pd.merge(df_final, df2G, how='left', on=['CUSTOMER NAME'])\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "HIGH PERCENTAGE OF CERTIFICATE OF CORRECTIONS AND DROP OFF WITH ONE STAFF MEMBER"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 336,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Length of service_dummies dummies 41540\n",
      "Length of df2 41540\n",
      "Difference 0\n",
      "0\n",
      "500\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda2\\lib\\site-packages\\ipykernel\\__main__.py:69: RuntimeWarning: invalid value encountered in divide\n",
      "C:\\ProgramData\\Anaconda2\\lib\\site-packages\\ipykernel\\__main__.py:69: RuntimeWarning: invalid value encountered in rint\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1000\n"
     ]
    }
   ],
   "source": [
    "df2 = df.copy()\n",
    "\n",
    "industry_names = sorted(list(set(df_final[\"CUSTOMER NAME\"].tolist())))\n",
    "\n",
    "df2 = df2[df2['CUSTOMER NAME'].isin(industry_names)]\n",
    "df2 = df2.reset_index(drop=True)\n",
    "\n",
    "df2 = df2[[\"CUSTOMER NAME\",\"STAFF NAME\", \"DELIVERED_SERVICE\"]]\n",
    "\n",
    "service_dummies = pd.get_dummies(df2.DELIVERED_SERVICE, prefix='Delivered_Service')\n",
    "print \"Length of service_dummies dummies\", len(service_dummies)\n",
    "print \"Length of df2\", len(df2)\n",
    "print \"Difference\", len(df2) - len(service_dummies)\n",
    "df2 = pd.concat([df2, service_dummies], axis=1)\n",
    "\n",
    "\n",
    "#aggregate drop off and certificate of corrections in person\n",
    "df2[\"Drop-off & Cert Corr\"] = df2[\"Delivered_Service_DROP OFF\"] + df2[\"Delivered_Service_DROP OFF (10X10)\"] + df2[\"Delivered_Service_DROP OFF (MULTIPLE SUBMISSION)\"] + df2[\"Delivered_Service_CERTIFICATE OF CORRECTION (IN-PERSON)\"]\n",
    "\n",
    "df2 = df2.drop('Delivered_Service_DROP OFF', 1)\n",
    "df2 = df2.drop('Delivered_Service_DROP OFF (10X10)', 1)\n",
    "df2 = df2.drop('Delivered_Service_DROP OFF (MULTIPLE SUBMISSION)', 1)\n",
    "df2 = df2.drop('Delivered_Service_CERTIFICATE OF CORRECTION (IN-PERSON)', 1)\n",
    "\n",
    "df2G = df2.groupby(['CUSTOMER NAME', \"STAFF NAME\"]).sum()\n",
    "df2G = df2G.add_suffix('').reset_index()\n",
    "\n",
    "#Add all of the rows of data\n",
    "col_names = sorted(list(df2G.columns.values))\n",
    "df2G[\"Total\"] = 0\n",
    "#strip all columns of white space and convert to uppper\n",
    "for i in range(2, len(col_names)):\n",
    "    if df2G[col_names[i]].dtypes != 'O':\n",
    "        df2G[\"Total\"] = df2G[\"Total\"] + df2G[col_names[i]]\n",
    "    \n",
    "    \n",
    "#df2G[\"%Certificat of Corr in-Person\"] = (df2G[\"Delivered_Service_CERTIFICATE OF CORRECTION (IN-PERSON)\"]/df2G[\"Total\"]) *100\n",
    "#df2G[\"%Drop-off\"] = (df2G[\"Drop-off All\"]/df2G[\"Total\"]) *100\n",
    "#df2G = df2G.round(1)\n",
    "df2G = df2G[[\"CUSTOMER NAME\", \"STAFF NAME\",  \"Drop-off & Cert Corr\"]]\n",
    "\n",
    "\n",
    "df_final[\"High % COC & DO Staff\"] = \"\"\n",
    "df_final[\"% COC & DO Staff\"] = \"\"\n",
    "\n",
    "#df_final[\"High % Drop-off Staff\"] = \"\"\n",
    "#df_final[\"% Drop-off Staff\"] = \"\"\n",
    "\n",
    "\n",
    "for i in range(0, len(df_final)):\n",
    "#for i in range(0, 1):\n",
    "    if (i%500) == 0:\n",
    "        print i\n",
    "    \n",
    "    cname = df_final[\"CUSTOMER NAME\"][i]\n",
    "\n",
    "     \n",
    "    df3G = df2G[df2G['CUSTOMER NAME'].isin([cname])]\n",
    "    df3G = df3G.reset_index(drop=True)\n",
    "    df3G = df3G.sort_values(by = 'Drop-off & Cert Corr', ascending=False).reset_index(drop=True)\n",
    "    \n",
    "    sum_cert = df3G[\"Drop-off & Cert Corr\"].sum()\n",
    "    \n",
    "\n",
    "    \n",
    "    #print sum_cert\n",
    "    \n",
    "    df_final[\"High % COC & DO Staff\"][i] = df3G[\"STAFF NAME\"][0]\n",
    "    df_final[\"% COC & DO Staff\"][i] = ( (df3G[\"Drop-off & Cert Corr\"][0]/float(sum_cert) ).round(3) ) *100\n",
    " \n",
    "    #Drop-offs\n",
    "    #sum_drop = df3G[\"Drop-off All\"].sum()\n",
    "    #df3G = df3G.sort_values(by = 'Drop-off All', ascending=False).reset_index(drop=True)\n",
    "\n",
    "    #df_final[\"High % Drop-off Staff\"][i] = df3G[\"STAFF NAME\"][0]\n",
    "    #df_final[\"% Drop-off Staff\"][i] = ( (df3G[\"Drop-off All\"][0]/float(sum_drop) ).round(3) ) *100\n",
    " \n",
    "\n",
    "        \n",
    "    #print\n",
    "    #print df3G\n",
    "   \n",
    "    \n",
    "    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "HIGH PERCENTAGE OF TRANSACTIONS IN A SINGLE DAY WITH ONE STAFF MEMBER"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 337,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Length of session dummies 41540\n",
      "Length of df2 41540\n",
      "Difference 0\n",
      "Length of service_dummies dummies 41540\n",
      "Length of df2 41540\n",
      "Difference 0\n"
     ]
    }
   ],
   "source": [
    "df2 = df.copy()\n",
    "\n",
    "#get only the names in df_final\n",
    "industry_names = sorted(list(set(df_final[\"CUSTOMER NAME\"].tolist())))\n",
    "\n",
    "df2 = df2[df2['CUSTOMER NAME'].isin(industry_names)]\n",
    "df2 = df2.reset_index(drop=True)\n",
    "\n",
    "df2[\"DateTime\"] = df2[\"YEAR\"] + \"-\" + df2[\"MONTH\"] + \"-\" + df2[\"DAY\"] + \" \" + df2[\"HOUR\"] + \":\" + df2[\"MINUTE\"]\n",
    "\n",
    "df2[\"Date\"] = df2[\"YEAR\"] + \"-\" + df2[\"MONTH\"] + \"-\" + df2[\"DAY\"] \n",
    "\n",
    "df2[\"DateTime\"] = pd.to_datetime(df2[\"DateTime\"])\n",
    "\n",
    "df2[\"Date\"] = pd.to_datetime(df2[\"Date\"])\n",
    "\n",
    "\n",
    "df2 = df2.assign(session=pd.cut(df2.DateTime.dt.hour,[0,6,12,18,24],labels=['Night','Morning','Afternoon','Evening']))\n",
    "\n",
    "session_dummies = pd.get_dummies(df2.session, prefix='session')\n",
    "print \"Length of session dummies\", len(session_dummies)\n",
    "print \"Length of df2\", len(df2)\n",
    "print \"Difference\", len(df2) - len(session_dummies)\n",
    "df2 = pd.concat([df2, session_dummies], axis=1)\n",
    "#df2 = df2.drop('JobType', 1)\n",
    "\n",
    "df2 = df2.drop('session_Night', 1)\n",
    "df2 = df2.drop('session_Evening', 1)\n",
    "\n",
    "\n",
    "\n",
    "df2 = df2[[\"CUSTOMER NAME\", \"STAFF NAME\",\"DELIVERED_SERVICE\", \"Date\", \"session_Morning\", \"session_Afternoon\"]]\n",
    "\n",
    "#create service dummies\n",
    "service_dummies = pd.get_dummies(df2.DELIVERED_SERVICE, prefix='Delivered_Service')\n",
    "print \"Length of service_dummies dummies\", len(service_dummies)\n",
    "print \"Length of df2\", len(df2)\n",
    "print \"Difference\", len(df2) - len(service_dummies)\n",
    "df2 = pd.concat([df2, service_dummies], axis=1)\n",
    "\n",
    "#aggregate drop off and certificate of corrections in person\n",
    "df2[\"Drop-off & Cert Corr\"] = df2[\"Delivered_Service_DROP OFF\"] + df2[\"Delivered_Service_DROP OFF (10X10)\"] + df2[\"Delivered_Service_DROP OFF (MULTIPLE SUBMISSION)\"] + df2[\"Delivered_Service_CERTIFICATE OF CORRECTION (IN-PERSON)\"]\n",
    "\n",
    "df2 = df2.drop('Delivered_Service_DROP OFF', 1)\n",
    "df2 = df2.drop('Delivered_Service_DROP OFF (10X10)', 1)\n",
    "df2 = df2.drop('Delivered_Service_DROP OFF (MULTIPLE SUBMISSION)', 1)\n",
    "df2 = df2.drop('Delivered_Service_CERTIFICATE OF CORRECTION (IN-PERSON)', 1)\n",
    "\n",
    "df2 = df2[[\"CUSTOMER NAME\", \"STAFF NAME\",\"Date\", \"session_Morning\", \"session_Afternoon\", \"Drop-off & Cert Corr\"]]\n",
    "\n",
    "df2 = df2.rename(columns={'Drop-off & Cert Corr': '#COC & DO'})\n",
    "\n",
    "\n",
    "df2G = df2.groupby(['CUSTOMER NAME', 'STAFF NAME', 'Date']).sum()\n",
    "df2G = df2G.add_suffix('').reset_index()\n",
    "\n",
    "df2G = df2G[df2G[\"#COC & DO\"] > 0].reset_index(drop=True)\n",
    "\n",
    "#df2G[\"Total Day\"] = df2G[\"session_Morning\"] + df2G[\"session_Afternoon\"]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "EXPORT HIGH PERCENT OF TRANSACTION IN A SINGLE DAY, DIFF TIMES, WITH ONE STAFF MEMBER"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2G.to_csv(\"C:\\\\Users\\\\dmehri\\\\Documents\\\\DATA\\\\AEU Network Analysis\\\\High Perc Trans Single Day Diff Time w Staff Member.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "EXPORT NETWORK ANALYSIS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 373,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_final.to_csv(path + \"AEU Network Analysis.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PART 3: OUTLIER ANALYSIS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Z-score calculations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Normal distributed variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 338,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "std\n",
      "1.72132549975\n",
      "mean\n",
      "1.46993780235\n",
      "\n",
      "1.84127739433\n",
      "1.08154803041\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "\n",
    "df_final[\"Percent\"] = df_final[\"Percent\"].astype(float)\n",
    "df_final[\"Percent Z-score\"] = (df_final[\"Percent\"] - df_final[\"Percent\"].mean() )/df_final[\"Percent\"].std()\n",
    "\n",
    "df_final[\"% COC & DO Staff\"] = df_final[\"% COC & DO Staff\"].astype(float)\n",
    "df_final[\"% COC & DO Staff Z-score\"] = (df_final[\"% COC & DO Staff\"] - df_final[\"% COC & DO Staff\"].mean() )/df_final[\"% COC & DO Staff\"].std()\n",
    "\n",
    "\n",
    "#take natural log of morning and afternoon sessions and take z-score\n",
    "#df_final[\"session_Morning\"] = df_final[\"session_Morning\"].astype(str)\n",
    "#df_final[\"session_Afternoon\"] = df_final[\"session_Afternoon\"].astype(str)\n",
    "\n",
    "#df_final[\"session_Morning\"] = df_final[\"session_Morning\"].str.replace('0', '')\n",
    "#df_final[\"session_Afternoon\"] = df_final[\"session_Afternoon\"].str.replace('0', '')\n",
    "\n",
    "df_final[\"session_Morning\"] = df_final[\"session_Morning\"].astype(float)\n",
    "df_final[\"session_Afternoon\"] = df_final[\"session_Afternoon\"].astype(float)\n",
    "\n",
    "\n",
    "std = df_final[df_final[\"session_Morning\"] != 0.0]\n",
    "std = df_final[\"session_Morning\"].std()\n",
    "mean = df_final[df_final[\"session_Morning\"] != 0.0]\n",
    "mean = df_final[\"session_Morning\"].mean()\n",
    "\n",
    "print \"std\"\n",
    "print std\n",
    "print \"mean\"\n",
    "print mean\n",
    "\n",
    "df_final[\"session_Morning\"] = df_final[\"session_Morning\"].replace(0, np.nan)\n",
    "#df_final[\"LN session_Morning\"] = np.log(df_final[\"session_Morning\"])\n",
    "df_final[\"session_Morning Z-score\"] = (df_final[\"session_Morning\"] - mean )/std\n",
    "\n",
    "\n",
    "std = df_final[df_final[\"session_Afternoon\"] != 0]\n",
    "std = df_final[\"session_Afternoon\"].std()\n",
    "mean = df_final[df_final[\"session_Afternoon\"] != 0]\n",
    "mean = df_final[\"session_Afternoon\"].mean()\n",
    "\n",
    "print \n",
    "print std\n",
    "print mean\n",
    "\n",
    "df_final[\"session_Afternoon\"] = df_final[\"session_Afternoon\"].replace(0, np.nan)\n",
    "#df_final[\"LN session_Afternoon\"] = np.log(df_final[\"session_Afternoon\"])\n",
    "df_final[\"session_Afternoon Z-score\"] = (df_final[\"session_Afternoon\"] - mean )/std\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Z-Score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 377,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda2\\lib\\site-packages\\pandas\\core\\series.py:1342: RuntimeWarning: invalid value encountered in rint\n",
      "  result = _values_from_object(self).round(decimals)\n"
     ]
    }
   ],
   "source": [
    "\n",
    "zscore = df_final[[ \"CUSTOMER NAME\",\"Percent Z-score\", \"% COC & DO Staff Z-score\", \"session_Morning Z-score\", \"session_Afternoon Z-score\"]]\n",
    "\n",
    "#zscore\n",
    "\n",
    "#zscore[\"Z-score Avg\"] = \"\"\n",
    "\n",
    "zscore.index = zscore[\"CUSTOMER NAME\"]\n",
    "\n",
    "zscore = zscore.drop('CUSTOMER NAME', 1)\n",
    "\n",
    "zscore['Z-score Avg'] = zscore.mean(axis=1)\n",
    "\n",
    "zscore = zscore.sort_values(by = 'Z-score Avg', ascending=False).reset_index(drop=False)\n",
    "\n",
    "zscore = zscore.round(2)\n",
    "\n",
    "#zscore = zscore.reset_index(drop=False)\n",
    "\n",
    "#for i in range(0, len(zscore)):\n",
    "#    if zscore[\"session_Afternoon Z-score\"][i] is null:\n",
    "#        print \"nan\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 378,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "zscore.to_csv(path + \"Outlier Analysis.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 341,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CUSTOMER NAME                        object\n",
       "Transaction Count                   float64\n",
       "Daly Transaction                      int32\n",
       "Livingston Transaction                int32\n",
       "Total Supervisor Transaction          int32\n",
       "% Daly                              float64\n",
       "% Livingston                        float64\n",
       "Bad Actor Flag                        int64\n",
       "High Staff Trans                     object\n",
       "Percent                             float64\n",
       "High Staff Trans Delivered Servc     object\n",
       "# Delivered Servc                    object\n",
       "% Delivered Servc                   float64\n",
       "High Staff Day Trans                 object\n",
       "Total Day                            object\n",
       "session_Morning                     float64\n",
       "session_Afternoon                   float64\n",
       ">2 COC & DO Flag                     object\n",
       ">2 COC & DO Dates                    object\n",
       "%COC & DO                           float64\n",
       "High % COC & DO Staff                object\n",
       "% COC & DO Staff                    float64\n",
       "Percent Z-score                     float64\n",
       "% COC & DO Staff Z-score            float64\n",
       "session_Morning Z-score             float64\n",
       "session_Afternoon Z-score           float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 341,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Modified Z-score for non normal distributions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 312,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2.0\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "\n",
    "#M_z-score = 0.6745 * (xi - xmedian)/median(abs(xi - xmedian))\n",
    "#MAD = median(abs(xi - xmedian))\n",
    "\n",
    "#df_final[\"session_Morning\"] = df_final[\"session_Morning\"].astype(int)\n",
    "df_final[\"session_Morning\"] = df_final[\"session_Morning\"].replace(0, np.nan)\n",
    "#df_final[\"session_Afternoon\"] = df_final[\"session_Afternoon\"].astype(int)\n",
    "df_final[\"session_Afternoon\"] = df_final[\"session_Afternoon\"].replace(0, np.nan)\n",
    "\n",
    "df_final[\"Total Day\"] = df_final[\"Total Day\"].astype(int)\n",
    "\n",
    "\n",
    "\n",
    "#Morning\n",
    "#median = df_final[df_final[\"session_Morning\"] != 0]\n",
    "median = df_final[\"session_Morning\"].median()\n",
    "\n",
    "print median\n",
    "\n",
    "df_final[\"session_Morning Modified Z-score\"] =  0.6745 * (df_final[\"session_Morning\"] - median ) \n",
    "\n",
    "df_final[\"MAD\"] = df_final[\"session_Morning\"] - median\n",
    "#df_final[\"MAD\"] = df_final[\"MAD\"].abs()\n",
    "#df_final[\"MAD\"] = df_final[\"MAD\"].median()\n",
    "\n",
    "#df_final[\"session_Morning Modified Z-score\"] = df_final[\"session_Morning Modified Z-score\"] / df_final[\"MAD\"]  \n",
    "\n",
    "\n",
    "#Afternoon\n",
    "#df_final[\"session_Afternoon Modified Z-score\"] =  0.6745 * (df_final[\"session_Afternoon\"] - df_final[\"session_Afternoon\"].median() ) \n",
    "\n",
    "#df_final[\"MAD\"] = df_final[\"session_Afternoon\"] - df_final[\"session_Afternoon\"].median()\n",
    "#df_final[\"MAD\"] = df_final[\"MAD\"].abs()\n",
    "#df_final[\"MAD\"] = df_final[\"MAD\"].median()\n",
    "\n",
    "#df_final[\"session_Afternoon Modified Z-score\"] = df_final[\"session_Afternoon Modified Z-score\"] / df_final[\"MAD\"]  \n",
    "\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total day\n",
      "3692\n",
      "total morning\n",
      "2127\n",
      "total afternoon\n",
      "1565\n",
      "afternoon median\n",
      "0.0\n",
      "\n",
      "len of df final\n",
      "1447\n",
      "\n",
      "3692\n",
      "\n",
      "\n",
      "3692\n"
     ]
    }
   ],
   "source": [
    "print \"total day\"\n",
    "total = df_final[\"Total Day\"].sum()\n",
    "print total\n",
    "\n",
    "print \"total morning\"\n",
    "total = df_final[\"session_Morning\"].sum()\n",
    "print total\n",
    "\n",
    "print \"total afternoon\"\n",
    "total = df_final[\"session_Afternoon\"].sum()\n",
    "print total\n",
    "\n",
    "print \"afternoon median\"\n",
    "median = df_final[\"session_Afternoon\"].median()\n",
    "print median\n",
    "print\n",
    "print \"len of df final\"\n",
    "print len(df_final)\n",
    "\n",
    "print \n",
    "df_final[\"Total Day\"] = df_final[\"Total Day\"].astype(int)\n",
    "total = df_final[\"Total Day\"].sum()\n",
    "print total\n",
    "\n",
    "print \n",
    "total = 0\n",
    "\n",
    "print\n",
    "\n",
    "total = 0\n",
    "\n",
    "for i in range(0, len(df_final)):\n",
    "    #print df_final[\"Total Day\"][i]\n",
    "    \n",
    "    total = total + df_final[\"Total Day\"][i]\n",
    "    \n",
    "print total"
   ]
  },
  {
   "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}