De-identification.ipynb

{
 "metadata": {
  "name": ""
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Import de-identification functions from datafly_v4.py"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from de_id_functions import *\n",
      "import numpy as np\n",
      "import pylab as P\n",
      "import pandas as pd\n",
      "from decimal import *"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Additional functions not included in the de_id_functions.py file."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def utilValues(cursor, tableName, varName):\n",
      "    \"\"\"\n",
      "    cursor: sqlite cursor object\n",
      "    tableName: string, name of sqlite table\n",
      "    varName: string, name of variable to analyze\n",
      "    takes values of an integer or float variable and returns the \n",
      "    mean, standard deviation, and entropy\n",
      "    \"\"\"\n",
      "    entQry = selUnique(cursor, tableName, varName)\n",
      "    entropy = shannonEntropy(entQry)\n",
      "    cursor.execute(\"SELECT \"+varName+\" FROM \"+tableName)\n",
      "    qry = cursor.fetchall()\n",
      "    qry = colToList(qry)\n",
      "    qry2 = textToFloat(qry)\n",
      "    if len(qry2)==0:\n",
      "        print \"No values could be converted to numbers\"\n",
      "        return\n",
      "    qryArray = np.array(qry2)\n",
      "    mean = qryArray.mean()\n",
      "    sd = qryArray.std()\n",
      "    return entropy, mean, sd"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def binAvg(cursor, tableName, nomVarName, numVarName):\n",
      "    \"\"\"\n",
      "    cursor: sqlite cursor object\n",
      "    tableName: string, name of sqlite table\n",
      "    nomVarName: string, name of variable with nominal categories\n",
      "    numVarName: string, name of corresponding variable with numeric values\n",
      "    For two columns, one a categorical string representation (generalization)\n",
      "    of the numeric values in another column (for example column A \n",
      "    contains \"10-15\" and then column B contains the actual values \n",
      "    that are in that bin), will give a bin-level average of the true values\n",
      "    in that bin. Designed as a tool to help improve the quality of a \n",
      "    binned (aka 'generalized') dataset. \n",
      "    \"\"\"\n",
      "    newVarName = nomVarName+\"_avg\"\n",
      "    getcontext().prec = 2\n",
      "    bins = selUnique(cursor,tableName,nomVarName)\n",
      "    avgDic = {}\n",
      "    for cat in bins:\n",
      "        cursor.execute(\"SELECT \"+numVarName+\" FROM \"+tableName+\" WHERE \"+nomVarName+\" = '\"+cat[0]+\"'\")\n",
      "        qry = cursor.fetchall()\n",
      "        qry = colToList(qry)\n",
      "        qry2 = textToFloat(qry)\n",
      "        if len(qry2)==0:\n",
      "            print \"No values could be converted to numbers: \"+str(cat[0])\n",
      "            continue\n",
      "        qryArray = np.array(qry2)\n",
      "        mean = qryArray.mean()\n",
      "        mean = Decimal(mean)\n",
      "        mean = round(mean,2)\n",
      "        avgDic[cat[0]] = str(mean)\n",
      "    try:\n",
      "        addColumn(cursor,tableName,newVarName)\n",
      "        varIndex(cursor,tableName,newVarName)\n",
      "    except:\n",
      "        print \"column \"+newVarName+\" already exists, overwriting...\"\n",
      "        cursor.execute(\"UPDATE \"+tableName+\" SET \"+newVarName+\" = 'null'\")\n",
      "    dataUpdate(cursor,tableName,nomVarName,avgDic,True,newVarName)\n",
      "        "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def utilMatrix(cursor, tableName, varList):\n",
      "    \"\"\"\n",
      "    cursor: sqlite cursor object\n",
      "    tableName: string, name of sqlite table\n",
      "    varList: list of utility variables, in format indigenous\n",
      "    to this program, which is the format that results\n",
      "    from the sqlite \"Pragma table_info()\" command.\n",
      "    This function creates a Pandas dataframe/matrix of the entropy,\n",
      "    mean, and standard deviation of the utility variables, \n",
      "    index is the variable name, and columns are the statistics\n",
      "    \"\"\"\n",
      "    varNames = []\n",
      "    for var in varList:\n",
      "        varNames.append(var[1])\n",
      "    entropies = np.array([])\n",
      "    sds = np.array([])\n",
      "    means = np.array([])\n",
      "    uMatrix = pd.DataFrame(columns = [\"Entropy\",\"Mean\",\"SD\"], index = varNames)\n",
      "    for var in varNames:\n",
      "        ent, mean, sd = utilValues(cursor, tableName, var)\n",
      "        uMatrix.ix[var] = [ent, mean, sd]\n",
      "    return uMatrix\n",
      "    "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def textToFloat(txtList):\n",
      "    \"\"\"\n",
      "    txtList: list of text values\n",
      "    returns a list of float values, \n",
      "    skips values that cannot be converted\n",
      "    \"\"\"\n",
      "    numList = []\n",
      "    for i in txtList:\n",
      "        try: numList.append(float(i))\n",
      "        except: pass\n",
      "    return numList"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def lDiversity(cursor, tableName, kkeyVar, senVar):\n",
      "    \"\"\"\n",
      "    cursor: sqlite3 cursor object                                                                                                                                                                \n",
      "    tableName: string, name of main table\n",
      "    kkeyVar: string, name of variable that contains concatenation of all quasi-identifiers\n",
      "    senVar: string, name of variable whose value you do not want disclosed\n",
      "    Checks a dataset for \"l-diversity\", namely that in a k-anonymous block of records\n",
      "    if the sensitive value is homogeonous, then you have effectively disclosed the \n",
      "    value of the sensitive record. Bluntly sets sensitive variable to blank if not l-diverse\n",
      "    \"\"\"\n",
      "    qry = selUnique(cursor, tableName, kkeyVar)\n",
      "    for i in qry:\n",
      "        cursor.execute('SELECT '+senVar+' FROM '+tableName+' WHERE '+kkeyVar+' = \"'+i[0]+'\" GROUP BY '+senVar)\n",
      "        qry2 = cursor.fetchall()\n",
      "        if len(qry2) == 1:\n",
      "            cursor.execute('UPDATE '+tableName+' SET '+senVar+' = \" \" WHERE '+kkeyVar+' = \"'+i[0]+'\"')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 6
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def optimumDrop2(cursor, tableName, userVar, k, nonUniqueList, nComb=1):\n",
      "    \"\"\"                                                                                                                                                                                          \n",
      "    cursor: sqlite3 cursor object                                                                                                                                                                \n",
      "    tableName: string, name of main table                                                                                                                                                        \n",
      "    userVar: string, name of userid var                                                                                                                                                          \n",
      "    k: int, minimum cell size                                                                                                                                                                    \n",
      "    nonUniqueList: list of course_combo values already cleared for k-anonymity                                                                                                                   \n",
      "    nComb: int, number of courses to try to drop, default 1                                                                                                                                      \n",
      "    iteratively tries 'dropping' one course for all of the records                                                                                                                               \n",
      "    that are flagged as having a unique combo of courses                                                                                                                                         \n",
      "    then measures the entropy of the resulting group, and                                                                                                                                        \n",
      "    returns the position in courseList of the course to drop, along with the                                                                                                                     \n",
      "    course_combo values that will benefit from the drop                                                                                                                                          \n",
      "    \"\"\"\n",
      "    qry = courseUserQry(cursor, tableName, userVar, 'True')\n",
      "    if len(qry)==0:\n",
      "        return qry\n",
      "    posLen = len(qry[0][0]) #assumes first variable in each tuple is the course combo, finds num of positions to change                                                                          \n",
      "    preList = qry[:]\n",
      "    preCombos = []\n",
      "    for i in preList:\n",
      "        preCombos.append(i[0])\n",
      "    preEntropy = shannonEntropy(preList)\n",
      "    postEntList = []\n",
      "    preCount = 0\n",
      "    for n in qry:\n",
      "        preCount += n[1]\n",
      "    print preCount\n",
      "    iterTemp = itertools.combinations(range(posLen),nComb)\n",
      "    dropCombos = []\n",
      "    while True:\n",
      "        try: dropCombos.append(iterTemp.next())\n",
      "        except: break\n",
      "    for i in dropCombos:\n",
      "        #print \"dropCombo:\"\n",
      "        #print i\n",
      "        postList = []\n",
      "        tmpList = qry[:]\n",
      "        for j in tmpList:\n",
      "            newString = \"\"\n",
      "            for l in range(posLen):\n",
      "                if l in i:\n",
      "                    newString+=\"0\"\n",
      "                else:\n",
      "                    newString+=j[0][l]\n",
      "            postList.append((newString,j[1]))\n",
      "        try:\n",
      "            cursor.execute(\"DROP TABLE coursedrop\")\n",
      "            cursor.execute(\"CREATE TABLE coursedrop (course_combo text, Count integer)\")\n",
      "        except:\n",
      "            cursor.execute(\"CREATE TABLE coursedrop (course_combo text, Count integer)\")\n",
      "        cursor.executemany(\"INSERT INTO coursedrop VALUES (?,?)\",postList)\n",
      "        cursor.execute(\"SELECT course_combo, SUM(Count) FROM coursedrop GROUP BY course_combo\")\n",
      "        postQry = cursor.fetchall()\n",
      "        postEntropy = shannonEntropy(postQry)\n",
      "        postCount = 0\n",
      "        for item in postQry:\n",
      "            postCount += item[1]\n",
      "        changeVals = []\n",
      "        for k in range(len(i)):\n",
      "            oldSpots = []\n",
      "            iterTemp = itertools.combinations(i,k+1)\n",
      "            while True:\n",
      "                try: oldSpots.append(iterTemp.next())\n",
      "                except: break\n",
      "            for l in oldSpots:\n",
      "                for m in postQry:\n",
      "                    mList = list(m[0])\n",
      "                    for n in l:\n",
      "                        mList[n] = '1'\n",
      "                    oldString = ''\n",
      "                    for p in mList:\n",
      "                        oldString+=p\n",
      "                    if m[1]>=k and oldString in preCombos:\n",
      "                        changeVals.append(oldString)\n",
      "                    elif (m[0] in nonUniqueList) and oldString in preCombos:\n",
      "                        changeVals.append(oldString)\n",
      "        #print \"Length of ChangeVals: \"+str(len(changeVals))\n",
      "        if len(changeVals)>0:\n",
      "            postEntList.append((i,preEntropy-postEntropy,changeVals))\n",
      "    if len(postEntList) == 0:\n",
      "        return []\n",
      "    first = True\n",
      "    low = (99,99,[])\n",
      "    for n in postEntList:\n",
      "        if n[1]<low[1] and n[1] > 0.0:\n",
      "            low = n\n",
      "    return low"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def userKanon2(cursor, tableName, userVar, courseVar, k):\n",
      "    \"\"\"                                                                                                                                                                                          \n",
      "    cursor: sqlite cursor object                                                                                                                                                                 \n",
      "    tableName: string, name of table                                                                                                                                                             \n",
      "    userVar: string, name of userid variable                                                                                                                                                     \n",
      "    courseVar: string, name of course variable                                                                                                                                                   \n",
      "    k: minimum group size                                                                                                                                                                        \n",
      "    creates a unique row record that is combo of                                                                                                                                                 \n",
      "    courseid and userid, and then creates another variable                                                                                                                                       \n",
      "    that says which courses someone has taken                                                                                                                                                    \n",
      "    then checks for unique count of courses taken                                                                                                                                                \n",
      "    and unique combinations of courses                                                                                                                                                           \n",
      "    \"\"\"\n",
      "    courseList = courseComboUpdate(cursor,tableName,userVar,courseVar)\n",
      "    value, uniqueList, nonUniqueList = uniqUserCheck(cursor,tableName,userVar,k)\n",
      "    uniqUserFlag(cursor, tableName, uniqueList)\n",
      "    dropNum = 1\n",
      "    courseDrops = {}\n",
      "    while value != 0.0 and dropNum != 16:  \n",
      "        print \"DropNum: \"+str(dropNum)\n",
      "        print \"non-anon value: \"+str(value)\n",
      "        courseTup = optimumDrop2(cursor, tableName, userVar, k, nonUniqueList,dropNum)\n",
      "        #print \"courseTup returned from OptimumDrop:\"\n",
      "        if len(courseTup) == 0 or len(courseTup[2])==0:\n",
      "            dropNum +=1 \n",
      "            print \"no more changes can be made, trying \"+str(dropNum)+\" courses at a time\"   \n",
      "            return courseDrops\n",
      "        #print courseTup[:2]  \n",
      "        courseNums = courseTup[0]\n",
      "        #print \"courseNums:\"\n",
      "        #print courseNums\n",
      "        changeVals = courseTup[2]\n",
      "        print \"length of changeVals\"\n",
      "        print len(changeVals)\n",
      "        for i in courseNums:\n",
      "            courseName = courseList[i]\n",
      "            print \"dropping courseName:\"\n",
      "            print courseName\n",
      "            courseDrops = courseDropper2(cursor, tableName, courseVar, courseName, changeVals, courseDrops)\n",
      "        courseList = courseComboUpdate(cursor,tableName,userVar,courseVar)\n",
      "        value, uniqueList, nonUniqueList = uniqUserCheck(cursor,tableName,userVar,k)\n",
      "        uniqUserFlag(cursor, tableName, uniqueList)\n",
      "    return courseDrops"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def courseDropper2(cursor, tableName, courseVar, courseName, changeVals, courseDict={}):\n",
      "    \"\"\"                                                                                                                                                                                          \n",
      "    courseName: string, name of course to be dropped                                                                                                                                             \n",
      "    changeVals: list of strings, values of course_combo to drop                                                                                                                                  \n",
      "    courseDict: dictionary of courses and running tally of rows dropped                                                                                                                          \n",
      "    drops course record where course equals courseName                                                                                                                                           \n",
      "    AND uniqUserFlag = \"True\"                                                                                                                                                                    \n",
      "    \"\"\"\n",
      "    delCount = 0\n",
      "    #print \"len of changeVals: \"+str(len(changeVals))\n",
      "    for val in changeVals:\n",
      "        cursor.execute(\"SELECT SUM(Count) FROM \"+tableName+\" WHERE (\"+courseVar+\" = '\"+courseName+\"' AND uniqUserFlag = 'True' AND course_combo = '\"+val+\"')\")\n",
      "        qry = cursor.fetchall()\n",
      "        #print \"changeVal qry length:\"+str(len(qry))\n",
      "        if (qry[0][0]): delCount += qry[0][0]\n",
      "    print \"delCount: \"+str(delCount)\n",
      "    if delCount == 0:\n",
      "        return courseDict\n",
      "    if courseName in courseDict.keys():\n",
      "        courseDict[courseName] += delCount\n",
      "    else:\n",
      "        courseDict[courseName] = delCount\n",
      "    #confirm = raw_input(\"Confirm you want to delete \"+str(delCount)+\" records associated with \"+courseName+\" (y/n): \")\n",
      "    #if confirm == 'n':\n",
      "    #    return\n",
      "    #elif confirm == 'y':\n",
      "    for val in changeVals:\n",
      "        cursor.execute(\"DELETE FROM \"+tableName+\" WHERE (\"+courseVar+\" = '\"+courseName+\"' AND uniqUserFlag = 'True' AND course_combo = '\"+val+\"')\")\n",
      "    #else:\n",
      "    #    print \"invalid choice, exiting function\"\n",
      "    return courseDict"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def kAnonIter(cursor, tableName, k, outFile):\n",
      "    \"\"\"                                                                                                                                                                                          \n",
      "    cursor: sqlite cursor object                                                                                                                                                                 \n",
      "    tableName: string, name of table                                                                                                                                                             \n",
      "    k: minimum group size                                                                                                                                                                        \n",
      "    wrapper function, gets list of variables from user input,                                                                                                                                    \n",
      "    updates kkey, checks for k-anonymity                                                                                                                                                         \n",
      "    \"\"\"\n",
      "    coreVarList = qiPicker(cursor, tableName)\n",
      "    optVarList = qiPicker(cursor, tableName)\n",
      "    iterVarList = coreVarList\n",
      "    addList = []\n",
      "    kkeyUpdate(cursor, tableName, iterVarList)\n",
      "    varIndex = 0\n",
      "    a,b = isTableKanonymous(cursor, tableName,k)\n",
      "    results = [('core',b)]\n",
      "    for var in optVarList:\n",
      "        iterVarList.append(optVarList[varIndex])\n",
      "        print iterVarList\n",
      "        addList.append(optVarList[varIndex])\n",
      "        print addList\n",
      "        results.append((addList,))\n",
      "        kkeyUpdate(cursor, tableName, iterVarList)\n",
      "        a,b = isTableKanonymous(cursor,tableName,k)\n",
      "        varIndex += 1\n",
      "        results[varIndex] += (b,)\n",
      "    outFile.write(str(results))\n",
      "    return results"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 10
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Name the file containing the data,\n",
      "name the database,\n",
      "and name commonly-used database variables.\n",
      "NOTE: make updates here if file specification changes"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "file = \"HMXPC13_DI_binned_061714_k3_QIall.csv\"\n",
      "table = \"source\"\n",
      "userVar = \"user_id\"\n",
      "courseVar = \"course_id\"\n",
      "countryVar = \"final_cc\"\n",
      "k=5"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 17
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#choose a name for the database and then connect to it\n",
      "db = 'kaPC_1-17-4-17-14-3.db'\n",
      "c = dbOpen(db)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 18
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Load data into SQLite database"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      " \n",
      "def sourceLoad(cursor, fname, tableName):\n",
      "    \"\"\"\n",
      "    cursor: sqlite3 cursor object\n",
      "    fname: string, file name/path for loading, .csv format\n",
      "    takes a .csv file and reads it into a sqlite database defined by the \n",
      "    cursor object. \n",
      "    CAUTION: will DELETE any existing table with same name\n",
      "    \"\"\"\n",
      "    try:\n",
      "        cursor.execute(\"DROP TABLE \"+tableName)\n",
      "        cursor.execute(\"DROP TABLE original\")\n",
      "    except:\n",
      "        pass\n",
      "    with open(fname, \"rU\") as inFile:\n",
      "        csvIn = csv.reader(inFile)\n",
      "        row = csvIn.next()\n",
      "        headers = row\n",
      "        tableCreate = \"CREATE TABLE \"+tableName+\" (\"\n",
      "        tableInsert = \"INSERT INTO \"+tableName+\" VALUES (\"\n",
      "        for col in headers[:-1]:\n",
      "            tableCreate += col+\" text, \"\n",
      "            tableInsert += \"?,\"\n",
      "        tableCreate += headers[-1]+\" text, kkey text)\"\n",
      "        cursor.execute(tableCreate)\n",
      "\n",
      "        tableInsert += \"?, ?)\"\n",
      "        #varList = qiPicker(cursor, tableName)\n",
      "        for row in csvIn:\n",
      "            if (row[25] == 'instructor') or (row[25] == 'staff' ):\n",
      "                continue\n",
      "            #for i in varList:\n",
      "            #    lastVar += row[int(i[0])]\n",
      "            row[14] = splitDate(row[14])\n",
      "            row[15] = splitDate(row[15])\n",
      "            trow = tuple(row)\n",
      "            trow += (\"\",)\n",
      "            cursor.execute(tableInsert, trow)\n",
      "            \n",
      "    cursor.execute(\"ALTER TABLE \"+tableName+\" ADD COLUMN Count integer\")\n",
      "    cursor.execute(\"UPDATE \"+tableName+\" SET Count =1\")\n",
      "    #return varList\n",
      "\n",
      "\n",
      "c.execute('DROP TABLE IF EXISTS '+table)\n",
      "sourceLoad(c,file,table)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "ename": "IndexError",
       "evalue": "list index out of range",
       "output_type": "pyerr",
       "traceback": [
        "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[0;31mIndexError\u001b[0m                                Traceback (most recent call last)",
        "\u001b[0;32m<ipython-input-19-1a3b64c4db45>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     44\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     45\u001b[0m \u001b[0mc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'DROP TABLE IF EXISTS '\u001b[0m\u001b[0;34m+\u001b[0m\u001b[0mtable\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 46\u001b[0;31m \u001b[0msourceLoad\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mc\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mfile\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mtable\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
        "\u001b[0;32m<ipython-input-19-1a3b64c4db45>\u001b[0m in \u001b[0;36msourceLoad\u001b[0;34m(cursor, fname, tableName)\u001b[0m\n\u001b[1;32m     28\u001b[0m        \u001b[0;31m#varList = qiPicker(cursor, tableName)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     29\u001b[0m        \u001b[0;32mfor\u001b[0m \u001b[0mrow\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mcsvIn\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 30\u001b[0;31m            \u001b[0;32mif\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mrow\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m25\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m'instructor'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mrow\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m25\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m'staff'\u001b[0m \u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     31\u001b[0m                \u001b[0;32mcontinue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     32\u001b[0m            \u001b[0;31m#for i in varList:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
        "\u001b[0;31mIndexError\u001b[0m: list index out of range"
       ]
      }
     ],
     "prompt_number": 19
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Load data into another table to make comparisons to the original data"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute('DROP TABLE IF EXISTS original')\n",
      "c.execute('CREATE TABLE original AS SELECT * from ' + table) "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "ename": "OperationalError",
       "evalue": "no such table: source",
       "output_type": "pyerr",
       "traceback": [
        "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
        "\u001b[0;32m<ipython-input-15-4aac92fdc65b>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0mc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'DROP TABLE IF EXISTS original'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'CREATE TABLE original AS SELECT * from '\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0mtable\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
        "\u001b[0;31mOperationalError\u001b[0m: no such table: source"
       ]
      }
     ],
     "prompt_number": 15
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Get sqlite table names"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"SELECT name FROM sqlite_master WHERE type='table';\")\n",
      "c.fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 501,
       "text": [
        "[(u'coursedrop',),\n",
        " (u'idhash',),\n",
        " (u'userkcheck',),\n",
        " (u'kacheck',),\n",
        " (u'source',),\n",
        " (u'original',)]"
       ]
      }
     ],
     "prompt_number": 501
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Load column names into a variable called varList"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"Pragma table_info(\"+table+\")\")\n",
      "varList = c.fetchall()\n",
      "varList"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 502,
       "text": [
        "[(0, u'course_id', u'text', 0, None, 0),\n",
        " (1, u'user_id', u'text', 0, None, 0),\n",
        " (2, u'username', u'text', 0, None, 0),\n",
        " (3, u'registered', u'text', 0, None, 0),\n",
        " (4, u'viewed', u'text', 0, None, 0),\n",
        " (5, u'explored', u'text', 0, None, 0),\n",
        " (6, u'certified', u'text', 0, None, 0),\n",
        " (7, u'ip', u'text', 0, None, 0),\n",
        " (8, u'cc_by_ip', u'text', 0, None, 0),\n",
        " (9, u'cc_address', u'text', 0, None, 0),\n",
        " (10, u'LoE', u'text', 0, None, 0),\n",
        " (11, u'YoB', u'text', 0, None, 0),\n",
        " (12, u'gender', u'text', 0, None, 0),\n",
        " (13, u'grade', u'text', 0, None, 0),\n",
        " (14, u'start_time', u'text', 0, None, 0),\n",
        " (15, u'last_event', u'text', 0, None, 0),\n",
        " (16, u'nevents', u'text', 0, None, 0),\n",
        " (17, u'modal_ip', u'text', 0, None, 0),\n",
        " (18, u'modal_ip_cc', u'text', 0, None, 0),\n",
        " (19, u'final_cc', u'text', 0, None, 0),\n",
        " (20, u'final_cc_source', u'text', 0, None, 0),\n",
        " (21, u'ndays_act', u'text', 0, None, 0),\n",
        " (22, u'nplay_video', u'text', 0, None, 0),\n",
        " (23, u'nchapters', u'text', 0, None, 0),\n",
        " (24, u'nforum_posts', u'text', 0, None, 0),\n",
        " (25, u'roles', u'text', 0, None, 0),\n",
        " (26, u'kkey', u'text', 0, None, 0),\n",
        " (27, u'Count', u'integer', 0, None, 0)]"
       ]
      }
     ],
     "prompt_number": 502
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Add indices"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "varIndex(c,table,courseVar)\n",
      "varIndex(c,table,userVar)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 503
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"CREATE INDEX \"+courseVar+\"_idx2 ON original (\"+courseVar+\")\")\n",
      "c.execute(\"CREATE INDEX \"+userVar+\"_idx2 ON original (\"+userVar+\")\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 504,
       "text": [
        "<sqlite3.Cursor at 0x109cfb500>"
       ]
      }
     ],
     "prompt_number": 504
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Get initial count of records loaded"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"SELECT SUM(Count) FROM \"+table)\n",
      "c.fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 505,
       "text": [
        "[(419174,)]"
       ]
      }
     ],
     "prompt_number": 505
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Map country codes to country names, load table of country name to continent mappings"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "countryNamer(c,table,countryVar)\n",
      "contImport(c, table, \"country_continent\", countryVar+\"_cname\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Err '' on: cc=\n",
        "Err 'A1' on: cc=A1\n",
        "Err 'A2' on: cc=A2\n",
        "Err 'AP' on: cc=AP\n",
        "Err 'EU' on: cc=EU\n"
       ]
      }
     ],
     "prompt_number": 506
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Delete staff"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"DELETE FROM \"+table+\" WHERE (roles = 'instructor' or roles = 'staff')\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 507,
       "text": [
        "<sqlite3.Cursor at 0x109cfb500>"
       ]
      }
     ],
     "prompt_number": 507
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"DELETE FROM original WHERE (roles = 'instructor' or roles = 'staff')\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 508,
       "text": [
        "<sqlite3.Cursor at 0x109cfb500>"
       ]
      }
     ],
     "prompt_number": 508
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Generate anonymous userIDs, choose prefix that will describe the data release, here 'MHxPC13' refers to MITx/HarvardX Person-Course AY2013"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "idGen(c,table,userVar,\"MHxPC13\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "ids: 363425\n"
       ]
      }
     ],
     "prompt_number": 509
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Get initial entropy reading"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "addColumn(c,table,'entropy')\n",
      "varIndex(c,table,'entropy')\n",
      "kkeyUpdate(c, table,varList,'entropy')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 510
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "qry = selUnique(c,table,'entropy')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 511
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "beginEntropy = shannonEntropy(qry)\n",
      "beginEntropy"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 512,
       "text": [
        "18.677189708501597"
       ]
      }
     ],
     "prompt_number": 512
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Create utility Matrix (both for unmodified dataset and current dataset)"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "utilVars = varList[4:7]+[varList[13]]+[varList[16]]+varList[21:25]\n",
      "utilVars"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 513,
       "text": [
        "[(4, u'viewed', u'text', 0, None, 0),\n",
        " (5, u'explored', u'text', 0, None, 0),\n",
        " (6, u'certified', u'text', 0, None, 0),\n",
        " (13, u'grade', u'text', 0, None, 0),\n",
        " (16, u'nevents', u'text', 0, None, 0),\n",
        " (21, u'ndays_act', u'text', 0, None, 0),\n",
        " (22, u'nplay_video', u'text', 0, None, 0),\n",
        " (23, u'nchapters', u'text', 0, None, 0),\n",
        " (24, u'nforum_posts', u'text', 0, None, 0)]"
       ]
      }
     ],
     "prompt_number": 513
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "preUmatrix = utilMatrix(c,\"original\",utilVars)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 514
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "preUmatrix"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>Entropy</th>\n",
        "      <th>Mean</th>\n",
        "      <th>SD</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>viewed</th>\n",
        "      <td> 0.9711086</td>\n",
        "      <td>  0.5997295</td>\n",
        "      <td> 0.4899531</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>explored</th>\n",
        "      <td>  0.422227</td>\n",
        "      <td> 0.08578061</td>\n",
        "      <td> 0.2800398</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>certified</th>\n",
        "      <td> 0.2347492</td>\n",
        "      <td> 0.03836593</td>\n",
        "      <td> 0.1920781</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>grade</th>\n",
        "      <td>  1.461635</td>\n",
        "      <td>  0.0447237</td>\n",
        "      <td> 0.1789314</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>nevents</th>\n",
        "      <td>  5.699504</td>\n",
        "      <td>   489.2225</td>\n",
        "      <td>  1780.657</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>ndays_act</th>\n",
        "      <td>  3.183227</td>\n",
        "      <td>   7.011011</td>\n",
        "      <td>  13.73271</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>nplay_video</th>\n",
        "      <td>  1.588071</td>\n",
        "      <td>   302.4309</td>\n",
        "      <td>  1152.513</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>nchapters</th>\n",
        "      <td>  2.826516</td>\n",
        "      <td>   4.388149</td>\n",
        "      <td>  6.050422</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>nforum_posts</th>\n",
        "      <td> 0.6015475</td>\n",
        "      <td>  0.4572636</td>\n",
        "      <td>  8.060734</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>9 rows \u00d7 3 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 515,
       "text": [
        "                Entropy        Mean         SD\n",
        "viewed        0.9711086   0.5997295  0.4899531\n",
        "explored       0.422227  0.08578061  0.2800398\n",
        "certified     0.2347492  0.03836593  0.1920781\n",
        "grade          1.461635   0.0447237  0.1789314\n",
        "nevents        5.699504    489.2225   1780.657\n",
        "ndays_act      3.183227    7.011011   13.73271\n",
        "nplay_video    1.588071    302.4309   1152.513\n",
        "nchapters      2.826516    4.388149   6.050422\n",
        "nforum_posts  0.6015475   0.4572636   8.060734\n",
        "\n",
        "[9 rows x 3 columns]"
       ]
      }
     ],
     "prompt_number": 515
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "uMatrix = utilMatrix(c,table,utilVars)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 516
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "uMatrix"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>Entropy</th>\n",
        "      <th>Mean</th>\n",
        "      <th>SD</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>viewed</th>\n",
        "      <td> 0.9711086</td>\n",
        "      <td>  0.5997295</td>\n",
        "      <td> 0.4899531</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>explored</th>\n",
        "      <td>  0.422227</td>\n",
        "      <td> 0.08578061</td>\n",
        "      <td> 0.2800398</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>certified</th>\n",
        "      <td> 0.2347492</td>\n",
        "      <td> 0.03836593</td>\n",
        "      <td> 0.1920781</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>grade</th>\n",
        "      <td>  1.461635</td>\n",
        "      <td>  0.0447237</td>\n",
        "      <td> 0.1789314</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>nevents</th>\n",
        "      <td>  5.699504</td>\n",
        "      <td>   489.2225</td>\n",
        "      <td>  1780.657</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>ndays_act</th>\n",
        "      <td>  3.183227</td>\n",
        "      <td>   7.011011</td>\n",
        "      <td>  13.73271</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>nplay_video</th>\n",
        "      <td>  1.588071</td>\n",
        "      <td>   302.4309</td>\n",
        "      <td>  1152.513</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>nchapters</th>\n",
        "      <td>  2.826516</td>\n",
        "      <td>   4.388149</td>\n",
        "      <td>  6.050422</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>nforum_posts</th>\n",
        "      <td> 0.6015475</td>\n",
        "      <td>  0.4572636</td>\n",
        "      <td>  8.060734</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>9 rows \u00d7 3 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 517,
       "text": [
        "                Entropy        Mean         SD\n",
        "viewed        0.9711086   0.5997295  0.4899531\n",
        "explored       0.422227  0.08578061  0.2800398\n",
        "certified     0.2347492  0.03836593  0.1920781\n",
        "grade          1.461635   0.0447237  0.1789314\n",
        "nevents        5.699504    489.2225   1780.657\n",
        "ndays_act      3.183227    7.011011   13.73271\n",
        "nplay_video    1.588071    302.4309   1152.513\n",
        "nchapters      2.826516    4.388149   6.050422\n",
        "nforum_posts  0.6015475   0.4572636   8.060734\n",
        "\n",
        "[9 rows x 3 columns]"
       ]
      }
     ],
     "prompt_number": 517
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "uMatrix - preUmatrix\n",
      "#removed rows for user k-anonymity"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>Entropy</th>\n",
        "      <th>Mean</th>\n",
        "      <th>SD</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>viewed</th>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>explored</th>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>certified</th>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>grade</th>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>nevents</th>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>ndays_act</th>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>nplay_video</th>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>nchapters</th>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>nforum_posts</th>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>9 rows \u00d7 3 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 518,
       "text": [
        "             Entropy Mean SD\n",
        "viewed             0    0  0\n",
        "explored           0    0  0\n",
        "certified          0    0  0\n",
        "grade              0    0  0\n",
        "nevents            0    0  0\n",
        "ndays_act          0    0  0\n",
        "nplay_video        0    0  0\n",
        "nchapters          0    0  0\n",
        "nforum_posts       0    0  0\n",
        "\n",
        "[9 rows x 3 columns]"
       ]
      }
     ],
     "prompt_number": 518
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Establish user-wise k-anonymity (the removal of registrations that uniquely identify someone based on combination of courses registered for)"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": true,
     "input": [
      "courseDrops = userKanon2(c, table, userVar, courseVar, k)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "generating course list\n",
        "23:00:15.061596\n",
        "creating/overwriting course_combo"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n",
        "23:00:40.748037\n",
        "no. of unique users to update: 363425"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n",
        "23:01:09.359834\n"
       ]
      }
     ],
     "prompt_number": 519
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for course in courseDrops.keys():\n",
      "    print \"Dropped \"+str(courseDrops[course])+\" rows for course \"+course"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 520
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"SELECT SUM(Count) FROM \"+table+\" WHERE uniqUserFlag = 'True'\")                                                                                                                \n",
      "qry = c.fetchall()                                                                                                                           \n",
      "print \"Deleted \"+str(qry[0][0])+\" additional records for users with unique combinations of courses.\\n\"                                                                                   \n",
      "c.execute(\"DELETE FROM \"+table+\" WHERE uniqUserFlag = 'True'\")                                                                                                                                   "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Deleted None additional records for users with unique combinations of courses.\n",
        "\n"
       ]
      },
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 521,
       "text": [
        "<sqlite3.Cursor at 0x109cfb500>"
       ]
      }
     ],
     "prompt_number": 521
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "kkeyUpdate(c, table,varList[:26],'entropy')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 522
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "qry = selUnique(c,table,'entropy')\n",
      "tmpEntropy = shannonEntropy(qry)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 523
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tmpEntropy"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 524,
       "text": [
        "18.677189708501597"
       ]
      }
     ],
     "prompt_number": 524
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "entChg = 2**beginEntropy - 2**tmpEntropy"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 525
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "entChg\n",
      "#This one after User-K-Anonymity"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 528,
       "text": [
        "0.0"
       ]
      }
     ],
     "prompt_number": 528
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Replace country names with continent names"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "initContVal = 5000\n",
      "contSwap(c,table,\"final_cc_cname\",\"continent\",initContVal)\n",
      "#outFile.write(\"Inserting continent names for countries with fewer than \"+str(initContVal)+\"\\n\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "countries: 229\n",
        "categories after swap: 22"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n"
       ]
      }
     ],
     "prompt_number": 527
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Make gender variable that treats NA and missing as same"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "try:\n",
      "    addColumn(c,table,\"gender_DI\")\n",
      "    varIndex(c,table,\"gender_DI\")\n",
      "    simpleUpdate(c,table,\"gender_DI\",\"NULL\")\n",
      "    c.execute(\"UPDATE \"+table+\" SET gender_DI = gender\")\n",
      "    c.execute(\"UPDATE \"+table+\" SET gender_DI = '' WHERE gender_DI = 'NA'\")\n",
      "except:\n",
      "    c.execute(\"UPDATE \"+table+\" SET gender_DI = gender\")\n",
      "    c.execute(\"UPDATE \"+table+\" SET gender_DI = '' WHERE gender_DI = 'NA'\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 529
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Get k-anonymity reading"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "status, value = kAnonWrap(c,table,k)\n",
      "print \"Percent of records that will need to be deleted to be k-anonymous: \"+str(value)+\"\\n\"\n",
      "#outFile.write( \"Percent of records that will need to be deleted to be k-anonymous: \"+str(value)+\"\\n\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Please choose the QI variables from the list below:\n",
        "0. course_id\n",
        "1. user_id\n",
        "2. username\n",
        "3. registered\n",
        "4. viewed\n",
        "5. explored\n",
        "6. certified\n",
        "7. ip\n",
        "8. cc_by_ip\n",
        "9. cc_address\n",
        "10. LoE\n",
        "11. YoB\n",
        "12. gender\n",
        "13. grade\n",
        "14. start_time\n",
        "15. last_event\n",
        "16. nevents\n",
        "17. modal_ip\n",
        "18. modal_ip_cc\n",
        "19. final_cc\n",
        "20. final_cc_source\n",
        "21. ndays_act\n",
        "22. nplay_video\n",
        "23. nchapters\n",
        "24. nforum_posts\n",
        "25. roles\n",
        "26. kkey\n",
        "27. Count\n",
        "28. final_cc_cname\n",
        "29. continent\n",
        "30. userid_DI\n",
        "31. entropy\n",
        "32. course_combo\n",
        "33. uniqUserFlag\n",
        "34. final_cc_cname_DI\n",
        "35. gender_DI\n"
       ]
      },
      {
       "name": "stdout",
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Enter your choices by number, separated by commas: 0,10,11,35,34\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Percent of records that will need to be deleted to be k-anonymous: 0.122960870665\n",
        "\n"
       ]
      }
     ],
     "prompt_number": 530
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Check k-anonymity for records with some null values"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print \"checking k-anonymity for records with some null values\"\n",
      "print datetime.datetime.now().time()\n",
      "#outFile.write(\"checking k-anonymity for records with some null values\\n\")\n",
      "#outFile.write(str(datetime.datetime.now().time())+\"\\n\")\n",
      "iterKcheck(c,table,k)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "checking k-anonymity for records with some null values\n",
        "23:31:08.995990\n",
        "Please choose the QI variables from the list below:\n",
        "0. course_id\n",
        "1. user_id\n",
        "2. username\n",
        "3. registered\n",
        "4. viewed\n",
        "5. explored\n",
        "6. certified\n",
        "7. ip\n",
        "8. cc_by_ip\n",
        "9. cc_address\n",
        "10. LoE\n",
        "11. YoB\n",
        "12. gender\n",
        "13. grade\n",
        "14. start_time\n",
        "15. last_event\n",
        "16. nevents\n",
        "17. modal_ip\n",
        "18. modal_ip_cc\n",
        "19. final_cc\n",
        "20. final_cc_source\n",
        "21. ndays_act\n",
        "22. nplay_video\n",
        "23. nchapters\n",
        "24. nforum_posts\n",
        "25. roles\n",
        "26. kkey\n",
        "27. Count\n",
        "28. final_cc_cname\n",
        "29. continent\n",
        "30. userid_DI\n",
        "31. entropy\n",
        "32. course_combo\n",
        "33. uniqUserFlag\n",
        "34. final_cc_cname_DI\n",
        "35. gender_DI\n"
       ]
      },
      {
       "name": "stdout",
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Enter your choices by number, separated by commas: 0,10,11,35,34\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Checking 10001...\n",
        "[(0, u'course_id'), (34, u'final_cc_cname_DI')]\n",
        "23:53:45.015561\n",
        "rows in qry: 110, time:"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n",
        "23:57:43.969309\n",
        "Checking 10011..."
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n",
        "[(0, u'course_id'), (35, u'gender_DI'), (34, u'final_cc_cname_DI')]\n",
        "23:59:31.776197\n",
        "rows in qry: 409, time:"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n",
        "00:02:11.194228\n",
        "Checking 10111..."
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n",
        "[(0, u'course_id'), (11, u'YoB'), (35, u'gender_DI'), (34, u'final_cc_cname_DI')]\n",
        "00:02:55.882376\n",
        "rows in qry: 12911, time:"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n",
        "00:04:54.769503\n",
        "Checking 11001..."
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n",
        "[(0, u'course_id'), (10, u'LoE'), (34, u'final_cc_cname_DI')]\n",
        "00:05:36.068703\n",
        "rows in qry: 1187, time:"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n",
        "00:07:36.048080\n",
        "Checking 11011..."
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n",
        "[(0, u'course_id'), (10, u'LoE'), (35, u'gender_DI'), (34, u'final_cc_cname_DI')]\n",
        "00:08:33.073726\n",
        "rows in qry: 2697, time:"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n",
        "00:10:54.703361\n",
        "Checking 11101..."
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n",
        "[(0, u'course_id'), (10, u'LoE'), (11, u'YoB'), (34, u'final_cc_cname_DI')]\n",
        "00:11:47.161347\n",
        "rows in qry: 28385, time:"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n",
        "00:14:42.024954\n"
       ]
      }
     ],
     "prompt_number": 531
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def eduClean(cursor, tableName, loeVar):\n",
      "    try: \n",
      "        addColumn(cursor,tableName,loeVar+\"_DI\")\n",
      "        varIndex(cursor,tableName,loeVar+\"_DI\")\n",
      "    except:\n",
      "        simpleUpdate(cursor,tableName,loeVar+\"_DI\",\"NULL\")\n",
      "    ed_dict = {'':'', 'NA':'NA','a':'Secondary','b':\"Bachelor's\",'el':'Less than Secondary',\n",
      "               'hs':'Secondary','jhs':'Less than Secondary','learn':'','m':\"Master's\",'none':'',\n",
      "               'other':'','p':'Doctorate','p_oth':'Doctorate','p_se':'Doctorate'}\n",
      "    qry = selUnique(cursor,tableName,loeVar)\n",
      "    for row in qry:\n",
      "        if row[0] in ed_dict.keys():\n",
      "            cursor.execute('UPDATE '+tableName+' SET '+loeVar+'_DI = \"'+ed_dict[row[0]]+'\" WHERE '+loeVar+' = \"'+row[0]+'\"')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 532
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "eduClean(c,table,\"LoE\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 533
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "selUnique(c,table,\"LoE\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 534,
       "text": [
        "[(u'', 16885),\n",
        " (u'NA', 20223),\n",
        " (u'a', 3555),\n",
        " (u'b', 140527),\n",
        " (u'el', 1870),\n",
        " (u'hs', 96684),\n",
        " (u'jhs', 10260),\n",
        " (u'learn', 1),\n",
        " (u'm', 93454),\n",
        " (u'none', 2914),\n",
        " (u'other', 13634),\n",
        " (u'p', 3535),\n",
        " (u'p_oth', 7320),\n",
        " (u'p_se', 8312)]"
       ]
      }
     ],
     "prompt_number": 534
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "selUnique(c,table,\"LoE_DI\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 535,
       "text": [
        "[(u'', 33434),\n",
        " (u\"Bachelor's\", 140527),\n",
        " (u'Doctorate', 19167),\n",
        " (u'Less than Secondary', 12130),\n",
        " (u\"Master's\", 93454),\n",
        " (u'NA', 20223),\n",
        " (u'Secondary', 100239)]"
       ]
      }
     ],
     "prompt_number": 535
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#change 0 values to text in order to exclude them from the binning procedure\n",
      "c.execute(\"UPDATE \"+table+\" SET nforum_posts = '0' WHERE nforum_posts = 'zero'\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 536,
       "text": [
        "<sqlite3.Cursor at 0x109cfb500>"
       ]
      }
     ],
     "prompt_number": 536
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "The Tailfinder function can help to group a long tail of one variable into a text field (see more documentation in the de_id_functions.py file)"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tailFinder(c,table,\"nforum_posts\",1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "name": "stdout",
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Would you like to trim the tails? (y/n): y\n"
       ]
      },
      {
       "name": "stdout",
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "High (h), Low (l), or Both (b)?: h\n"
       ]
      },
      {
       "name": "stdout",
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Choose the high tail: 7\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "High tail for nforum_posts: 7\n"
       ]
      }
     ],
     "prompt_number": 537
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "numBinner(c,table,\"nforum_posts\",bw=1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "name": "stdout",
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Copy into (n)ew variable or (o)verwrite?: n\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "column nforum_posts_DI already exists, overwriting...\n"
       ]
      }
     ],
     "prompt_number": 260
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "binAvg(c,table,\"nforum_posts_DI\",\"nforum_posts\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 538
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "selUnique(c,table,\"nforum_posts_DI\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 539,
       "text": [
        "[(u'0', 390647),\n",
        " (u'1', 9801),\n",
        " (u'2', 4932),\n",
        " (u'3', 2843),\n",
        " (u'4', 2129),\n",
        " (u'5', 1439),\n",
        " (u'6', 1101),\n",
        " (u'>= 7', 6282)]"
       ]
      }
     ],
     "prompt_number": 539
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tailFinder(c,table,\"YoB\",1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "non int value: NA, skipping\n"
       ]
      },
      {
       "name": "stdout",
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Would you like to trim the tails? (y/n): y\n"
       ]
      },
      {
       "name": "stdout",
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "High (h), Low (l), or Both (b)?: l\n"
       ]
      },
      {
       "name": "stdout",
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Choose the low tail: 1931\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Low tail for YoB: 1931\n"
       ]
      }
     ],
     "prompt_number": 540
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "numBinner(c,table,\"YoB_DI\",bw=1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "name": "stdout",
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Copy into (n)ew variable or (o)verwrite?: n\n"
       ]
      }
     ],
     "prompt_number": 541
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "selUnique(c,table,\"YoB_DI\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 542,
       "text": [
        "[(None, 42152),\n",
        " (u'1932', 63),\n",
        " (u'1933', 55),\n",
        " (u'1934', 58),\n",
        " (u'1935', 94),\n",
        " (u'1936', 112),\n",
        " (u'1937', 155),\n",
        " (u'1938', 150),\n",
        " (u'1939', 186),\n",
        " (u'1940', 201),\n",
        " (u'1941', 232),\n",
        " (u'1942', 309),\n",
        " (u'1943', 360),\n",
        " (u'1944', 431),\n",
        " (u'1945', 408),\n",
        " (u'1946', 530),\n",
        " (u'1947', 678),\n",
        " (u'1948', 648),\n",
        " (u'1949', 767),\n",
        " (u'1950', 907),\n",
        " (u'1951', 984),\n",
        " (u'1952', 1035),\n",
        " (u'1953', 1077),\n",
        " (u'1954', 1224),\n",
        " (u'1955', 1457),\n",
        " (u'1956', 1535),\n",
        " (u'1957', 1573),\n",
        " (u'1958', 1670),\n",
        " (u'1959', 1769),\n",
        " (u'1960', 2200),\n",
        " (u'1961', 2225),\n",
        " (u'1962', 2315),\n",
        " (u'1963', 2475),\n",
        " (u'1964', 2633),\n",
        " (u'1965', 2812),\n",
        " (u'1966', 2919),\n",
        " (u'1967', 3093),\n",
        " (u'1968', 3440),\n",
        " (u'1969', 3625),\n",
        " (u'1970', 4260),\n",
        " (u'1971', 4168),\n",
        " (u'1972', 4699),\n",
        " (u'1973', 4822),\n",
        " (u'1974', 5362),\n",
        " (u'1975', 5844),\n",
        " (u'1976', 6524),\n",
        " (u'1977', 7161),\n",
        " (u'1978', 8031),\n",
        " (u'1979', 9062),\n",
        " (u'1980', 10707),\n",
        " (u'1981', 10991),\n",
        " (u'1982', 12443),\n",
        " (u'1983', 13469),\n",
        " (u'1984', 15261),\n",
        " (u'1985', 17100),\n",
        " (u'1986', 18553),\n",
        " (u'1987', 19801),\n",
        " (u'1988', 20985),\n",
        " (u'1989', 21297),\n",
        " (u'1990', 21460),\n",
        " (u'1991', 19637),\n",
        " (u'1992', 18333),\n",
        " (u'1993', 16425),\n",
        " (u'1994', 12377),\n",
        " (u'1995', 8004),\n",
        " (u'1996', 5158),\n",
        " (u'1997', 3153),\n",
        " (u'1998', 1817),\n",
        " (u'1999', 889),\n",
        " (u'2000', 466),\n",
        " (u'2001', 209),\n",
        " (u'2002', 138),\n",
        " (u'2003', 71),\n",
        " (u'2004', 56),\n",
        " (u'2005', 46),\n",
        " (u'2006', 52),\n",
        " (u'2007', 62),\n",
        " (u'2008', 81),\n",
        " (u'2009', 97),\n",
        " (u'2010', 122),\n",
        " (u'2011', 152),\n",
        " (u'2012', 669),\n",
        " (u'2013', 258),\n",
        " (u'<= 1931', 345)]"
       ]
      }
     ],
     "prompt_number": 542
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "kAnonWrap(c,table,k)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Please choose the QI variables from the list below:\n",
        "0. course_id\n",
        "1. user_id\n",
        "2. username\n",
        "3. registered\n",
        "4. viewed\n",
        "5. explored\n",
        "6. certified\n",
        "7. ip\n",
        "8. cc_by_ip\n",
        "9. cc_address\n",
        "10. LoE\n",
        "11. YoB\n",
        "12. gender\n",
        "13. grade\n",
        "14. start_time\n",
        "15. last_event\n",
        "16. nevents\n",
        "17. modal_ip\n",
        "18. modal_ip_cc\n",
        "19. final_cc\n",
        "20. final_cc_source\n",
        "21. ndays_act\n",
        "22. nplay_video\n",
        "23. nchapters\n",
        "24. nforum_posts\n",
        "25. roles\n",
        "26. kkey\n",
        "27. Count\n",
        "28. final_cc_cname\n",
        "29. continent\n",
        "30. userid_DI\n",
        "31. entropy\n",
        "32. course_combo\n",
        "33. uniqUserFlag\n",
        "34. final_cc_cname_DI\n",
        "35. gender_DI\n",
        "36. course_id_NF\n",
        "37. LoE_NF\n",
        "38. YoB_NF\n",
        "39. gender_DI_NF\n",
        "40. final_cc_cname_DI_NF\n",
        "41. nullSum\n",
        "42. kCheckFlag\n",
        "43. nullkkey\n",
        "44. LoE_DI\n",
        "45. nforum_posts_DI\n",
        "46. nforum_posts_DI_avg\n",
        "47. YoB_DI\n",
        "48. YoB_DI_DI\n"
       ]
      },
      {
       "name": "stdout",
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Enter your choices by number, separated by commas: 0,34,35,44,47\n"
       ]
      },
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 544,
       "text": [
        "(False, 0.10952981301341685)"
       ]
      }
     ],
     "prompt_number": 544
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "lDiversity(c,table,\"kkey\",\"grade\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 545
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Needed an incomplete flag for internally inconsistent records. This is described more in the documentation with the data release."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "addColumn(c,table,\"incomplete_flag\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 546
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "varIndex(c,table,\"incomplete_flag\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 547
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"SELECT SUM(Count) FROM source WHERE nevents = '' AND nchapters != ''\")\n",
      "qry = c.fetchall()\n",
      "print qry\n",
      "c.execute(\"SELECT SUM(Count) FROM source WHERE nevents = '' AND nforum_posts != '0'\")\n",
      "qry = c.fetchall()\n",
      "print qry\n",
      "c.execute(\"SELECT SUM(Count) FROM source WHERE nevents = '' AND ndays_act != ''\")\n",
      "qry = c.fetchall()\n",
      "print qry"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[(None,)]\n",
        "[(None,)]"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n",
        "[(None,)]"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n"
       ]
      }
     ],
     "prompt_number": 548
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"UPDATE source SET incomplete_flag = '1' WHERE nevents = '' AND nchapters != ''\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 549,
       "text": [
        "<sqlite3.Cursor at 0x109cfb500>"
       ]
      }
     ],
     "prompt_number": 549
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"UPDATE source SET incomplete_flag = '1' WHERE nevents = '' AND nforum_posts != '0'\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 550,
       "text": [
        "<sqlite3.Cursor at 0x109cfb500>"
       ]
      }
     ],
     "prompt_number": 550
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"UPDATE source SET incomplete_flag = '1' WHERE nevents = '' AND ndays_act != ''\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 551,
       "text": [
        "<sqlite3.Cursor at 0x109cfb500>"
       ]
      }
     ],
     "prompt_number": 551
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"SELECT * FROM source WHERE incomplete_flag = '1'\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 552,
       "text": [
        "<sqlite3.Cursor at 0x109cfb500>"
       ]
      }
     ],
     "prompt_number": 552
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "qry = c.fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 553
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "len(qry)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 554,
       "text": [
        "0"
       ]
      }
     ],
     "prompt_number": 554
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"Pragma table_info(source)\")\n",
      "varList = c.fetchall()\n",
      "varList"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 555,
       "text": [
        "[(0, u'course_id', u'text', 0, None, 0),\n",
        " (1, u'user_id', u'text', 0, None, 0),\n",
        " (2, u'username', u'text', 0, None, 0),\n",
        " (3, u'registered', u'text', 0, None, 0),\n",
        " (4, u'viewed', u'text', 0, None, 0),\n",
        " (5, u'explored', u'text', 0, None, 0),\n",
        " (6, u'certified', u'text', 0, None, 0),\n",
        " (7, u'ip', u'text', 0, None, 0),\n",
        " (8, u'cc_by_ip', u'text', 0, None, 0),\n",
        " (9, u'cc_address', u'text', 0, None, 0),\n",
        " (10, u'LoE', u'text', 0, None, 0),\n",
        " (11, u'YoB', u'text', 0, None, 0),\n",
        " (12, u'gender', u'text', 0, None, 0),\n",
        " (13, u'grade', u'text', 0, None, 0),\n",
        " (14, u'start_time', u'text', 0, None, 0),\n",
        " (15, u'last_event', u'text', 0, None, 0),\n",
        " (16, u'nevents', u'text', 0, None, 0),\n",
        " (17, u'modal_ip', u'text', 0, None, 0),\n",
        " (18, u'modal_ip_cc', u'text', 0, None, 0),\n",
        " (19, u'final_cc', u'text', 0, None, 0),\n",
        " (20, u'final_cc_source', u'text', 0, None, 0),\n",
        " (21, u'ndays_act', u'text', 0, None, 0),\n",
        " (22, u'nplay_video', u'text', 0, None, 0),\n",
        " (23, u'nchapters', u'text', 0, None, 0),\n",
        " (24, u'nforum_posts', u'text', 0, None, 0),\n",
        " (25, u'roles', u'text', 0, None, 0),\n",
        " (26, u'kkey', u'text', 0, None, 0),\n",
        " (27, u'Count', u'integer', 0, None, 0),\n",
        " (28, u'final_cc_cname', u'text', 0, None, 0),\n",
        " (29, u'continent', u'text', 0, None, 0),\n",
        " (30, u'userid_DI', u'text', 0, None, 0),\n",
        " (31, u'entropy', u'text', 0, None, 0),\n",
        " (32, u'course_combo', u'text', 0, None, 0),\n",
        " (33, u'uniqUserFlag', u'text', 0, None, 0),\n",
        " (34, u'final_cc_cname_DI', u'text', 0, None, 0),\n",
        " (35, u'gender_DI', u'text', 0, None, 0),\n",
        " (36, u'course_id_NF', u'text', 0, None, 0),\n",
        " (37, u'LoE_NF', u'text', 0, None, 0),\n",
        " (38, u'YoB_NF', u'text', 0, None, 0),\n",
        " (39, u'gender_DI_NF', u'text', 0, None, 0),\n",
        " (40, u'final_cc_cname_DI_NF', u'text', 0, None, 0),\n",
        " (41, u'nullSum', u'text', 0, None, 0),\n",
        " (42, u'kCheckFlag', u'text', 0, None, 0),\n",
        " (43, u'nullkkey', u'text', 0, None, 0),\n",
        " (44, u'LoE_DI', u'text', 0, None, 0),\n",
        " (45, u'nforum_posts_DI', u'text', 0, None, 0),\n",
        " (46, u'nforum_posts_DI_avg', u'text', 0, None, 0),\n",
        " (47, u'YoB_DI', u'text', 0, None, 0),\n",
        " (48, u'YoB_DI_DI', u'text', 0, None, 0),\n",
        " (49, u'incomplete_flag', u'text', 0, None, 0)]"
       ]
      }
     ],
     "prompt_number": 555
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"SELECT uniqUserFlag FROM source LIMIT 1\")\n",
      "c.fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 556,
       "text": [
        "[(u'False',)]"
       ]
      }
     ],
     "prompt_number": 556
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "kkeyList = []\n",
      "kkeyList.append(varList[0]) # course ID\n",
      "kkeyList.append(varList[34]) # country name DI\n",
      "kkeyList.append(varList[35]) # gender DI\n",
      "kkeyList.append(varList[44]) # LoE DI\n",
      "kkeyList.append(varList[46]) # nforum posts DI\n",
      "kkeyList.append(varList[47]) # YoB DI\n",
      "kkeyList "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 560,
       "text": [
        "[(0, u'course_id', u'text', 0, None, 0),\n",
        " (34, u'final_cc_cname_DI', u'text', 0, None, 0),\n",
        " (35, u'gender_DI', u'text', 0, None, 0),\n",
        " (44, u'LoE_DI', u'text', 0, None, 0),\n",
        " (47, u'YoB_DI', u'text', 0, None, 0)]"
       ]
      }
     ],
     "prompt_number": 560
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "kkeyUpdate(c,table,kkeyList)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 561
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"SELECT SUM(Count), kkey FROM source GROUP BY kkey\")\n",
      "qry2 = c.fetchall()\n",
      "#lessThanK = []\n",
      "#badCount = 0\n",
      "c.execute(\"UPDATE \"+table+\" SET kCheckFlag = 'False'\")\n",
      "for row in qry2:\n",
      "    if row[0] >= k:\n",
      "        c.execute('UPDATE '+table+' SET kCheckFlag = \"True\" WHERE kkey = \"'+row[1]+'\"')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 562
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "selUnique(c,table,\"kCheckFlag\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 563,
       "text": [
        "[(u'False', 42855), (u'True', 376319)]"
       ]
      }
     ],
     "prompt_number": 563
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print k"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "5\n"
       ]
      }
     ],
     "prompt_number": 564
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "The fateful step where non-k-anonymous records are removed."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"DELETE FROM source WHERE kCheckFlag = 'False'\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 565,
       "text": [
        "<sqlite3.Cursor at 0x109cfb500>"
       ]
      }
     ],
     "prompt_number": 565
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Be careful to only export the columns you are ok with others seeing. Don't export IP address, original user_id, etc."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "csvExport(c,table,\"HMXPC13_DI_binned_061714_k5_QIall.csv\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Please choose the QI variables from the list below:\n",
        "0. course_id\n",
        "1. user_id\n",
        "2. username\n",
        "3. registered\n",
        "4. viewed\n",
        "5. explored\n",
        "6. certified\n",
        "7. ip\n",
        "8. cc_by_ip\n",
        "9. cc_address\n",
        "10. LoE\n",
        "11. YoB\n",
        "12. gender\n",
        "13. grade\n",
        "14. start_time\n",
        "15. last_event\n",
        "16. nevents\n",
        "17. modal_ip\n",
        "18. modal_ip_cc\n",
        "19. final_cc\n",
        "20. final_cc_source\n",
        "21. ndays_act\n",
        "22. nplay_video\n",
        "23. nchapters\n",
        "24. nforum_posts\n",
        "25. roles\n",
        "26. kkey\n",
        "27. Count\n",
        "28. final_cc_cname\n",
        "29. continent\n",
        "30. userid_DI\n",
        "31. entropy\n",
        "32. course_combo\n",
        "33. uniqUserFlag\n",
        "34. final_cc_cname_DI\n",
        "35. gender_DI\n",
        "36. course_id_NF\n",
        "37. LoE_NF\n",
        "38. YoB_NF\n",
        "39. gender_DI_NF\n",
        "40. final_cc_cname_DI_NF\n",
        "41. nullSum\n",
        "42. kCheckFlag\n",
        "43. nullkkey\n",
        "44. LoE_DI\n",
        "45. nforum_posts_DI\n",
        "46. nforum_posts_DI_avg\n",
        "47. YoB_DI\n",
        "48. YoB_DI_DI\n",
        "49. incomplete_flag\n"
       ]
      },
      {
       "name": "stdout",
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Enter your choices by number, separated by commas: 0,30,3,4,5,6,34,44,48,35,13,14,15,16,21,22,23,25,49\n"
       ]
      }
     ],
     "prompt_number": 566
    },
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Stats on Original File"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "dbClose(c)\n",
      "db = 'kaPC_1-17-4-17-14-orig.db'\n",
      "c = dbOpen(db)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"Pragma table_info(source)\")\n",
      "c.fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"UPDATE source SET Count = 1\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"SELECT SUM(Count) FROM source\")\n",
      "total = c.fetchall()[0][0]\n",
      "total"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "view_qry = selUnique(c,table,\"viewed\")\n",
      "view_dic = {}\n",
      "for row in view_qry:\n",
      "    view_dic[row[0]] = float(row[1])/float(total)\n",
      "view_dic"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "exp_qry = selUnique(c,table,\"explored\")\n",
      "exp_dic = {}\n",
      "for row in exp_qry:\n",
      "    exp_dic[row[0]] = float(row[1])/float(total)\n",
      "exp_dic"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cert_qry = selUnique(c,table,\"certified\")\n",
      "cert_dic = {}\n",
      "for row in cert_qry:\n",
      "    cert_dic[row[0]] = float(row[1])/float(total)\n",
      "cert_dic"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "gen_qry = selUnique(c,table,\"gender\")\n",
      "gen_dic = {}\n",
      "gen_total = total\n",
      "for row in gen_qry:\n",
      "    if row[0] == '' or row[0] == 'NA' or row[0] == 'o':\n",
      "        gen_total -= row[1]\n",
      "    else:\n",
      "        gen_dic[row[0]] = float(row[1])/float(gen_total)\n",
      "gen_dic"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "age_qry = selUnique(c,table,\"YoB\")\n",
      "num = 0\n",
      "denom = 0\n",
      "for row in age_qry:\n",
      "    try: age = 2013 - int(row[0])\n",
      "    except: continue\n",
      "    num += age * row[1]\n",
      "    denom += row[1]\n",
      "avg_age = float(num)/float(denom)\n",
      "avg_age"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Stats on De-identified file"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "dbClose(c)\n",
      "db = 'kaPC_1-17-4-17-14-3.db'\n",
      "c = dbOpen(db)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"SELECT SUM(Count) FROM source\")\n",
      "total = c.fetchall()[0][0]\n",
      "total"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "view_qry = selUnique(c,table,\"viewed\")\n",
      "view_dic = {}\n",
      "for row in view_qry:\n",
      "    view_dic[row[0]] = float(row[1])/float(total)\n",
      "view_dic"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "exp_qry = selUnique(c,table,\"explored\")\n",
      "exp_dic = {}\n",
      "for row in exp_qry:\n",
      "    exp_dic[row[0]] = float(row[1])/float(total)\n",
      "exp_dic"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cert_qry = selUnique(c,table,\"certified\")\n",
      "cert_dic = {}\n",
      "for row in cert_qry:\n",
      "    cert_dic[row[0]] = float(row[1])/float(total)\n",
      "cert_dic"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "gen_qry = selUnique(c,table,\"gender\")\n",
      "gen_dic = {}\n",
      "gen_total = total\n",
      "for row in gen_qry:\n",
      "    if row[0] == '' or row[0] == 'NA' or row[0] == 'o':\n",
      "        gen_total -= row[1]\n",
      "    else:\n",
      "        gen_dic[row[0]] = float(row[1])/float(gen_total)\n",
      "gen_dic"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "age_qry = selUnique(c,table,\"YoB\")\n",
      "num = 0\n",
      "denom = 0\n",
      "for row in age_qry:\n",
      "    try: age = 2013 - int(row[0])\n",
      "    except: continue\n",
      "    num += age * row[1]\n",
      "    denom += row[1]\n",
      "avg_age = float(num)/float(denom)\n",
      "avg_age"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute(\"Pragma database_list\")\n",
      "c.fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "selUnique(c,table,\"YoB\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "uMatrix - preUmatrix\n",
      "#This one taken after K-Anonymous"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": "*"
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Good to close the db between uses, it compacts the data and prevents an error if you leave a cursor dangling."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "dbClose(db)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "ename": "AttributeError",
       "evalue": "'str' object has no attribute 'execute'",
       "output_type": "pyerr",
       "traceback": [
        "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
        "\u001b[0;32m<ipython-input-107-a4b83afd1bf7>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdbClose\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
        "\u001b[0;32m/Users/oangiuli/Dropbox/Sr Thesis - Olivia Angiuli/code/de_id/de_id_functions.pyc\u001b[0m in \u001b[0;36mdbClose\u001b[0;34m(cursor, closeFlag)\u001b[0m\n\u001b[1;32m     71\u001b[0m     \u001b[0mdatabase\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     72\u001b[0m     \"\"\"\n\u001b[0;32m---> 73\u001b[0;31m     \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"VACUUM\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     74\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0mcloseFlag\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     75\u001b[0m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
        "\u001b[0;31mAttributeError\u001b[0m: 'str' object has no attribute 'execute'"
       ]
      }
     ],
     "prompt_number": 107
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}