3 score aggregation v2.1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# remove  fraudulent workers, and aggregate scores of` remaining workers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "%matplotlib inline\n",
    "import numpy as np\n",
    "import matplotlib as mpl\n",
    "from scipy.stats import mode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy_utils import database_exists, create_database\n",
    "import psycopg2\n",
    "\n",
    "dbname = 'urx'\n",
    "username = 'noahburbank'\n",
    "\n",
    "engine = create_engine('postgres://%s@localhost/%s'%(username,dbname))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index([u'index', u'_worker_id', u'job_count', u'variance', u'mean',\n",
      "       u'diversity_score', u'minority_percentage', u'is_bad',\n",
      "       u'is_bad_cluster'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# import worker data \n",
    "# connect:\n",
    "con = None\n",
    "con = psycopg2.connect(database = dbname, user = username)\n",
    "\n",
    "#adjust for experiment \n",
    "# query:\n",
    "sql_query = \"\"\"\n",
    "SELECT * FROM worker_table_exp2;\n",
    "\"\"\"\n",
    "workers = pd.read_sql_query(sql_query,con)\n",
    "\n",
    "print workers.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# import score data\n",
    "# choose the data from the relevant experiment \n",
    "#raw_df = pd.read_csv('experiment1_data.csv')\n",
    "raw_df = pd.read_csv('experiment2_data.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## First get rid of the fraudsters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "you are only losing :\n",
      "5.91715976331\n",
      "percent of your data!!!\n"
     ]
    }
   ],
   "source": [
    "old_length = len(raw_df)\n",
    "\n",
    "# create set of bad worker ids\n",
    "for group in workers.groupby('is_bad'):\n",
    "    if group[0] == 1:\n",
    "        bad_id_list = list(group[1]['_worker_id'])\n",
    "        bad_id_set = set(bad_id_list)\n",
    "\n",
    "# identify the rows from bad workers\n",
    "raw_df['bad_worker'] = raw_df['_worker_id'].apply(lambda x: x in bad_id_set)\n",
    "\n",
    "# remove those rows \n",
    "raw_df = raw_df[raw_df['bad_worker'] == 0]\n",
    "\n",
    "new_length = len(raw_df)\n",
    "\n",
    "print 'you are only losing :'\n",
    "print (1 - float(new_length)/old_length) * 100\n",
    "print 'percent of your data!!!'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# score the individual pub/ad pairs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "## detect majority judgments (and how much of a majority)\n",
    "# score each unit id base\n",
    "plurality_judgment = dict()\n",
    "job_count = {}\n",
    "\n",
    "for line in raw_df.groupby('_unit_id'):\n",
    "    unit_id = line[0]\n",
    "    judgments = line[1]['please_make_your_relevancy_judgment']\n",
    "    mode = judgments.mode()\n",
    "    mode = int(mode[0])\n",
    "    mode_count = 0\n",
    "    judgment_count = 0\n",
    "    for judgment in judgments:\n",
    "        judgment_count += 1\n",
    "        if judgment == mode:\n",
    "            mode_count += 1\n",
    "    plurality_percentage = (mode_count +0.0)/judgment_count\n",
    "    job_count[line[0]] = judgment_count\n",
    "    plurality_judgment[line[0]] = mode, plurality_percentage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_unit_id</th>\n",
       "      <th>majority</th>\n",
       "      <th>score</th>\n",
       "      <th>judgments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>866237364</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>866237380</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261</th>\n",
       "      <td>866237476</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>654</th>\n",
       "      <td>866237969</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>675</th>\n",
       "      <td>866237994</td>\n",
       "      <td>0.375000</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>935</th>\n",
       "      <td>866238290</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      _unit_id  majority  score  judgments\n",
       "162  866237364  0.555556      2          9\n",
       "176  866237380  0.555556      2          9\n",
       "261  866237476  0.500000      2         10\n",
       "654  866237969  0.555556      2          9\n",
       "675  866237994  0.375000      2          8\n",
       "935  866238290  0.400000      2         10"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# aggregate scores into a dataframe \n",
    "unit_id_scores = pd.DataFrame()\n",
    "unit_id_scores['_unit_id'] = plurality_judgment.keys()\n",
    "unit_id_scores['majority'] = zip(*plurality_judgment.values())[1]\n",
    "unit_id_scores['score'] = zip(*plurality_judgment.values())[0]\n",
    "unit_id_scores['judgments'] = job_count.values()\n",
    "\n",
    "# sometimes 2 is the majority/plurality, but the .6 threshold eliminates them\n",
    "unit_id_scores[unit_id_scores.score == 2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# filter out those without a 60% majority"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "unit_id_scores['has_enough_majority'] = unit_id_scores['majority'].apply(lambda x: x >= .6) == 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "and you lose 49 pub/ad pairs\n",
      "you now have 901 pub/ad pairs\n",
      "you lose 458 more observations\n",
      "you now have 8605 total observations\n"
     ]
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXgAAAEACAYAAAC57G0KAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAFIBJREFUeJzt3X2MZQdZx/HvU5YCpei0IttXWGIpsEBYRCr4EiaRYEFo\nq8S2aEg3gNFUpSGGsAuGNjGSFkPcBMJfCLsxtLLKi9UA3W3tBU2ABm2lsJRSwwhb6RSLSyBI7LaP\nf8zZzu2wOzPnzj1zzn36/SSbnnPm3jPPc87ps2d+d+7dyEwkSfWc1HcBkqRuOOAlqSgHvCQV5YCX\npKIc8JJUlANekopa14CPiIWI+HJE3B4RtzXbTo+IgxFxd0QciIi5scfvjohvRMRdEfHKroqXJJ3Y\neu/gE5jPzBdl5gXNtl3Awcw8H7ilWScitgOXAduBC4EPRIQ/KUjSJmszeGPF+kXAvmZ5H3BJs3wx\ncENmPpiZC8A9wAVIkjZVmzv4myPiSxHxe822rZm52CwvAlub5bOAw2PPPQycveFKJUmtbFnn4345\nM78TET8LHIyIu8a/mJkZEat95oGfhyBJm2xdAz4zv9P897sR8QmWIpfFiDgjM++LiDOB+5uH3wuc\nO/b0c5ptj1jjLwNJ0glk5sq4/ITWjGgi4pSIeEqz/GTglcCdwI3AFc3DrgA+2SzfCFweESdHxDOB\nZwG3HafIsn+uvvrq3muwP/t7rPX2WOivrfXcwW8FPhERxx7/kcw8EBFfAvZHxJuABeDSZnAfioj9\nwCHgKHBlTlLZDFtYWOi7hE7Z3+yq3BvU76+tNQd8Zn4T2HGc7d8DXnGC57wbePeGq5MkTczfT+/A\nzp07+y6hU/Y3uyr3BvX7ayv6SE8i4rGW2miGNHFkJ7zutRERQU7zRVa1NxqN+i6hU9X7W5Id/Olf\n9XNXvb+2HPCSVJQRjbTCUkTTxfUZRjTaECMaSRLggO9E9Rywen+VVT931ftrywEvSUWZwUsrmMFr\nqMzgJUmAA74T1XPA6v1VVv3cVe+vLQe8JBVlBi+tYAavoTKDlyQBDvhOVM8Bq/dXWfVzV72/thzw\nklSUGby0ghm8hsoMXpIEOOA7UT0HrN5fZdXPXfX+2nLAS1JRZvDSCmbwGiozeEkS4IDvRPUcsHp/\nlVU/d9X7a8sBL0lFmcFLK5jBa6jM4CVJgAO+E9VzwOr9VVb93FXvry0HvCQVZQYvrWAGr6Eyg5ck\nAQ74TlTPAav3V1n1c1e9v7Yc8JJUlBm8tIIZvIbKDF6SBDjgO1E9B6zeX2XVz131/tpywEtSUevK\n4CPiccCXgMOZ+dqIOB34KPAMYAG4NDOPNI/dDbwReAh4S2YeOM7+zOA1WGbwGqquMvirgEMsX/W7\ngIOZeT5wS7NORGwHLgO2AxcCH4gIf0qQpB6sOXwj4hzg1cAHgWN/c1wE7GuW9wGXNMsXAzdk5oOZ\nuQDcA1wwzYJnQfUcsHp/lVU/d9X7a2s9d9d/CbwNeHhs29bMXGyWF4GtzfJZwOGxxx0Gzt5okZKk\n9ras9sWIeA1wf2beHhHzx3tMZmZErBYsHvdrO3fuZNu2bQDMzc2xY8cO5ueXvsWxv4Vndf3YtqHU\nY3/t+4MRMD+2zBTW2ZT6V1ufn5/v/fja3/rXR6MRe/fuBXhkXrax6ousEfFu4A3AUeCJwE8BHwde\nAsxn5n0RcSZwa2Y+JyJ2AWTmtc3zPwNcnZlfXLFfX2TVYPkiq4Zqqi+yZuY7MvPczHwmcDnwT5n5\nBuBG4IrmYVcAn2yWbwQuj4iTI+KZwLOA29o2MeuO/Q1cVfX+Kqt+7qr319aqEc1xHLv9uBbYHxFv\novk1SYDMPBQR+1n6jZujwJXeqktSP/wsGmkFIxoNlZ9FI0kCHPCdqJ4DVu+vsurnrnp/bTngJako\nM3hpBTN4DZUZvCQJcMB3onoOWL2/yqqfu+r9teWAl6SizOClFczgNVRm8JIkwAHfieo5YPX+Kqt+\n7qr315YDXpKKMoOXVjCD11CZwUuSAAd8J6rngNX7q6z6uaveX1sOeEkqygxeWsEMXkNlBi9JAhzw\nnaieA1bvr7Lq5656f2054CWpKDN4aQUzeA2VGbwkCXDAd6J6Dli9v8qqn7vq/bXlgJekoszgpRXM\n4DVUZvCSJMAB34nqOWD1/iqrfu6q99fWlr4L0DAsxRLdMJaQ+mEGL8DceZzHQkNlBi9JAhzwnaif\nA476LkATqn5tVu+vLQe8JBVlBi/A3Hmcx0JDZQYvSQIc8J2onwOO+i5AE6p+bVbvry0HvCQVtWoG\nHxFPBD4LPIGlN0X9XWZeExGnAx8FngEsAJdm5pHmObuBNwIPAW/JzAPH2a8Z/MCYOy/zWGio2mbw\na77IGhGnZOaPImIL8C/AVcDrgP/OzPdExNuB0zJzV0RsB64HXgKcDdwMnJ+ZD6/YpwN+YBxqyzwW\nGqqpv8iamT9qFk8GHs/SlX8RsK/Zvg+4pFm+GLghMx/MzAXgHuCC9RZTRf0ccNR3AZpQ9Wuzen9t\nrTngI+KkiLgDWAQOZOZtwNbMXGwesghsbZbPAg6PPf0wS3fykqRNtp47+IczcwdwDvCLEfH8FV9P\nVv959jH3M+n8/HzfJXRsvu8CNKHq12b1/tpa96dJZub3I+JW4NeBxYg4IzPvi4gzgfubh90LnDv2\ntHOabT9h586dbNu2DYC5uTl27NjxyMk59mOW65u7vuzY+vxU1ofS33rXl3tYX3/rX2dT6ne9zvpo\nNGLv3r0Aj8zLNtb6LZqnAkcz80hEPAm4CbiWpSv3gcy8LiJ2AXMrXmS9gOUXWc9b+Ypq9RdZR6PR\nimExfO1eWByx/rv42XthsfKLrLN4bbZRvb+2L7KudQd/JrAvIh7HUpzz0cz8VER8AdgfEW+i+TVJ\ngMw8FBH7gUPAUeDK0pNckgbMz6IRUPuutS2PhYbKz6KRJAEO+E785IuW1Yz6LkATqn5tVu+vLQe8\nJBVlBi/A3Hmcx0JDZQYvSQIc8J2onwOO+i5AE6p+bVbvry0HvCQVZQYvwNx5nMdCQ2UGL0kCHPCd\nqJ8DjvouQBOqfm1W768tB7wkFWUGL8DceZzHQkNlBi9JAhzwnaifA476LkATqn5tVu+vLQe8JBVl\nBi/A3Hmcx0JDZQYvSQIc8J2onwOO+i5AE6p+bVbvry0HvCQVZQYvwNx5nMdCQ2UGL0kCHPCdqJ8D\njvouQBOqfm1W768tB7wkFWUGL8DceZzHQkNlBi9JAhzwnaifA476LkATqn5tVu+vLQe8JBVlBi/A\n3Hmcx0JDZQYvSQIc8J2onwOO+i5AE6p+bVbvry0HvCQVZQYvwNx5nMdCQ2UGL0kCHPCdqJ8Djvou\nQBOqfm1W768tB7wkFWUGL8DceZzHQkM19Qw+Is6NiFsj4qsR8ZWIeEuz/fSIOBgRd0fEgYiYG3vO\n7oj4RkTcFRGvnKwVSdJGrCeieRB4a2Y+D3gp8IcR8VxgF3AwM88HbmnWiYjtwGXAduBC4AMR8ZiK\ngurngKO+C9CEql+b1ftra83Bm5n3ZeYdzfIPga8BZwMXAfuah+0DLmmWLwZuyMwHM3MBuAe4YMp1\nS5LW0CqDj4htwGeB5wPfyszTmu0BfC8zT4uI9wFfyMyPNF/7IPDpzPzY2H7M4AfG3HmZx0JD1dnv\nwUfEqcDHgKsy8wfjX2um9WpXrle1JG2yLet5UEQ8nqXh/teZ+clm82JEnJGZ90XEmcD9zfZ7gXPH\nnn5Os+1Rdu7cybZt2wCYm5tjx44dzM/PA8s52qyu79mzZyb7WXZsff4E63uAHat8/dHrQ+lvvevL\nPayvv/Wvsyn1r7Y+fq6Hcrztb/V+9u7dC/DIvGxjzYimiV/2AQ9k5lvHtr+n2XZdROwC5jJzV/Mi\n6/Us5e5nAzcD541nMtUjmtFotGJYDF+7WGLE8vBac88zF0tUjmhm8dpso3p/bSOa9Qz4XwE+B3yZ\n5at+N3AbsB94OrAAXJqZR5rnvAN4I3CUpUjnphX7LD3gZ1HlodaWx0JDNfUB3wUH/PA41JZ5LDRU\nftjYAPxkpl3NqO8CNKHq12b1/tpywEtSUUY0AowlxnksNFRGNJIkwAHfifo54KjvAjSh6tdm9f7a\ncsBLUlFm8ALMncd5LDRUZvCSJMAB34n6OeCo7wI0oerXZvX+2nLAS1JRZvACzJ3HeSw0VGbwkiTA\nAd+J+jngqO8CNKHq12b1/tpywEtSUWbwAsydx3ksNFRm8JIkwAHfifo54KjvAjSh6tdm9f7acsBL\nUlFm8ALMncd5LDRUZvCSJMAB34n6OeCo7wI0oerXZvX+2nLAS1JRZvACzJ3HeSw0VGbwkiTAAd+J\n+jngqO8CNKHq12b1/tpywEtSUWbwAsydx3ksNFRm8JIkwAHfifo54KjvAjSh6tdm9f7acsBLUlFm\n8ALMncd5LDRUZvCSJMAB34n6OeCo7wI0oerXZvX+2nLAS1JRZvACzJ3HeSw0VGbwkiRgHQM+Ij4U\nEYsRcefYttMj4mBE3B0RByJibuxruyPiGxFxV0S8sqvCh6x+DjjquwBNqPq1Wb2/ttZzB/9h4MIV\n23YBBzPzfOCWZp2I2A5cBmxvnvOBiPCnBEnqwboy+IjYBvxDZr6gWb8LeHlmLkbEGcAoM58TEbuB\nhzPzuuZxnwGuycwvrNifGfzAmDsv81hoqDYrg9+amYvN8iKwtVk+Czg89rjDwNkTfg9J0gZs2egO\nMjMjYrXbkuN+befOnWzbtg2Aubk5duzYwfz8PLCco83q+p49e2ayn2XH1udPsL4H2LHK1x+9PpT+\n1ru+3MP6+lv/OptS/2rr4+d6KMfb/lbvZ+/evQCPzMs2NhLRzGfmfRFxJnBrE9HsAsjMa5vHfQa4\nOjO/uGJ/pSOa0Wi0YlgMX7tYYsTy8FpzzzMXS1SOaGbx2myjen9tI5pJB/x7gAcy87pmqM9l5q7m\nRdbrgQtYimZuBs5bOc2rD/hZVHmoteWx0FC1HfBrRjQRcQPwcuCpEfFt4F3AtcD+iHgTsABcCpCZ\nhyJiP3AIOApc6SSXpH74TtYOzOKPiUY0yyrfwc/itdlG9f58J6skCfAOXo3Kd61teSw0VN7BS5IA\nB3wnfvL3yqsZ9V2AJlT92qzeX1sOeEkqygxegLnzOI+FhsoMXpIEOOA7UT8HHPVdgCZU/dqs3l9b\nDnhJKsoMXoC58ziPhYbKDF6SBDjgO1E/Bxz1XYAmVP3arN5fWw54SSrKDF6AufM4j4WGygxekgQ4\n4DtRPwcc9V2AJlT92qzeX1sb/ke39Wi7d/8pe/a8ny1bTp76vl/2spdy4MCNU9+vpJoc8FN25MgP\n+fGP/wT4/Snv+fPcd997p7zPSc33XYAmVPlfO4L6/bXlgO/EU4CnTXmfp015f5KqM4PvxD19F9Cx\nUd8FaELVM+rq/bXlgJekohzwnTiv7wI6Nt93AZpQ9Yy6en9tOeAlqSgHfCfM4DVM1TPq6v215YCX\npKIc8J0wg9cwVc+oq/fXlgNekopywHfCDF7DVD2jrt5fW76TVZpxSx9v3A0/3ni2OeA7YQavzdbN\n59fPGjP4RzOikaSiHPCdMIPXUI36LqBTZvCP5oCXpKIc8J0wg9dQzfddQKfM4B/NAS9JRXUy4CPi\nwoi4KyK+ERFv7+J7DJsZvIZq1HcBnTKDf7SpD/iIeBzwfuBCYDvw+oh47rS/z7Dd23cBHbuj7wI0\nsdrn7o47avfXVhd38BcA92TmQmY+CPwNcHEH32fA/rfvAjp2pO8CNLHa5+7Ikdr9tdXFgD8b+PbY\n+uFmmyRpE3XxTtbH9HubTzoJTjrpC5x66munut+HHnqALVuG8sbjhb4L0MQW+i4A6PbjFa655prO\n9j1rYtqfNRERLwWuycwLm/XdwMOZed3YYx7TfwlI0qQyc91/O3Yx4LcAXwd+Dfgv4Dbg9Zn5tal+\nI0nSqqb+M39mHo2IPwJuAh4H/JXDXZI239Tv4CVJw7Cp72SNiGdHxO1jf74fEW/ZzBq6FBFvjYiv\nRMSdEXF9RDyh75qmKSKuanr7SkRc1Xc9GxURH4qIxYi4c2zb6RFxMCLujogDETHXZ40bcYL+fjsi\nvhoRD0XEz/dZ30adoL+/iIivRcS/R8THI+Kn+6xxI07Q3581vd0eETdFxJmr7WNTB3xmfj0zX5SZ\nLwJeDPwI+MRm1tCViDgb+GPgxZn5Apbiqcv7rWp6IuL5wJuBlwAvBF4TET/Xb1Ub9mGW3pA3bhdw\nMDPPB25p1mfV8fq7E/hN4HObX87UHa+/A8DzMvOFwN3A7k2vanqO1997MvOFzQz9R+Bdq+2gz8+i\neQXwH5n57TUfOTu2AKc0LzSfQq23tD4H+GJm/jgzHwI+C/xWzzVtSGb+M/A/KzZfBOxrlvcBl2xq\nUVN0vP4y867MvLunkqbqBP0dzMyHm9UvAudsemFTcoL+fjC2eirwMKvoc8BfDlzf4/efqsy8F3gv\n8C2WfnvoSGbe3G9VU/UV4FebCOMU4DeY4f95VrE1Mxeb5UVga5/FaEPeCHyq7yKmLSL+PCK+BfwO\nQ7yDj4iTgdcCf9vH9+9CRJzG0t3fNuAs4NSI+N1ei5qizLwLuI6lH4E/DdzOGncPsy6XfgPB30KY\nQRHxTuD/MrPMTeQxmfnOzHw68BGWYuET6usO/lXAv2bmd3v6/l14BfDNzHwgM48CHwd+qeeapioz\nP5SZv5CZL2fpQ02+3ndNHViMiDMAmhew7u+5HrUUETuBVwNlbrBO4Hrgdas9oK8B/3rghp6+d1f+\nE3hpRDwplt6H/QrgUM81TVVEPK3579NZeqGu3N0RcCNwRbN8BfDJHmvp2uz9q9priIgLgbcBF2fm\nj/uuZ9oi4lljqxcDq77HaNN/Dz4inszSMHzmihcMZl5EXANcBhwF/g14c/OJmiVExOeAnwEeBN6a\nmbf2XNKGRMQNwMuBp7KUt78L+HtgP/B0lj645dLMnMmPKDxOf1cD3wPe12z7PnB7Zr6qtyI34AT9\n7QZOZqlPgM9n5pX9VLgxJ+jv1cCzWYpHF4A/yMzvnHAfvtFJkmryn+yTpKIc8JJUlANekopywEtS\nUQ54SSrKAS9JRTngJakoB7wkFfX/oW2sUdkAJMkAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x10ab63690>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#identify those with and without a 60% majority\n",
    "unit_id_scores['has_enough_majority'] = unit_id_scores['majority'].apply(lambda x: x >= .6) == 1\n",
    "no_majority_unit_id_set = set(unit_id_scores[unit_id_scores['has_enough_majority'] != 1]._unit_id)\n",
    "\n",
    "# remove ad/pub pairs\n",
    "old_pair_count = len(unit_id_scores._unit_id.unique())\n",
    "\n",
    "unit_id_scores = unit_id_scores[unit_id_scores['has_enough_majority'] == 1]\n",
    "new_pair_count = len(unit_id_scores._unit_id.unique())\n",
    "lost_pairs = old_pair_count - new_pair_count\n",
    "\n",
    "print 'and you lose ' + str(lost_pairs) + ' pub/ad pairs'\n",
    "print 'you now have ' + str(len(unit_id_scores)) + ' pub/ad pairs'\n",
    "\n",
    "# hist\n",
    "unit_id_scores['judgments'].apply(lambda x: x if (x>5) else False).hist()\n",
    "\n",
    "# remove observations \n",
    "old_num_obs = len(raw_df)\n",
    "raw_df['lack_of_majority'] = raw_df['_unit_id'].apply(lambda x: x in no_majority_unit_id_set)\n",
    "raw_df = raw_df[raw_df['lack_of_majority'] != 1]\n",
    "new_num_obs = len(raw_df)\n",
    "lost_observations = old_num_obs - new_num_obs\n",
    "\n",
    "print 'you lose ' + str(lost_observations) + ' more observations'\n",
    "print 'you now have ' + str(new_num_obs) + ' total observations'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(901, 6)\n",
      "(901, 6)\n",
      "no loss additional loss from pub/ad pairs with insufficient worker counts\n"
     ]
    }
   ],
   "source": [
    "# remove the underscored elements \n",
    "unit_id_scores['min_job_req'] = unit_id_scores['judgments'].apply(lambda x: 1 if (x>4) else 0)\n",
    "new_df = unit_id_scores[unit_id_scores['min_job_req'] == True]\n",
    "print new_df.shape\n",
    "print unit_id_scores.shape\n",
    "if len(new_df) == len(unit_id_scores):\n",
    "    print \"no loss additional loss from pub/ad pairs with insufficient worker counts\" "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "source": [
    "### output to database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# output to unit pair scores table\n",
    "\n",
    "# put this into the database, so that i don't need to process it each time, \n",
    "#becuase it is very slow\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy_utils import database_exists, create_database\n",
    "import psycopg2\n",
    "\n",
    "dbname = 'urx'\n",
    "username = 'noahburbank'\n",
    "\n",
    "engine = create_engine('postgres://%s@localhost/%s'%(username,dbname))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# adjust to make sure you have the right experiment for the database \n",
    "unit_id_scores.to_sql('scores_exp1', engine, if_exists = 'replace')\n",
    "\n",
    "# output as csv as well for conveineience needs \n",
    "unit_id_scores.to_csv('scores_exp2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}