Find duplicate users.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:3f121220cab5eea42d555ded456cdfd4afaf99bbd55c319b04ec8e7cc5aaa5b9"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Find Duplicate Users\n",
      "\n",
      "### 18th June 2014 Alan Saul\n",
      "\n",
      "This script loads the users from an excel sheet of all users, `Users And Roles > View All Users and Role > Export list` to Excel, using the custom built `cmtutil` library.\n",
      "\n",
      "It then finds users with the same First and Last name. User accounts are easily created in CMT by co-authors. We need to ensure that these conflicts have their correct domain names listed. The aim should be to merge these accounts, or delete the surplus ones.\n",
      "\n",
      "We need to also pay particular care to those users who have submitted on one, and are reviewing/metareviewing on another, as they may end up reviewing their own papers!\n",
      "\n",
      "It then finds users which really don't belong in the system (not chair, associate chair, reviewer, external-reviewer, meta-reviewer, editor and have also not submitted a paper this year), these could be deleted."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import cmtutils\n",
      "import pandas as pd\n",
      "data_dir = cmtutils.cmt_data_directory\n",
      "cmt=cmtutils.cmt_reviewers_read(filename=\"2014-10-26_full_user_list.xls\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 39
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Get list of all users and booleanise their categorical Yes, No fields"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "users = cmt.reviewers\n",
      "\n",
      "#Booleanise the variables\n",
      "d = {'Yes': True, 'No': False}\n",
      "for col in ['IsAssociateChair', 'IsAuthor', 'IsChair', 'IsExternalReviewer','ConflictDomainsNotEnteredForSubmittedPapers',\n",
      "            'IsMetaReviewer', 'IsProceedingsEditor','IsReviewer', 'IsSubmissionOwner']:\n",
      "    users[col] = users[col].map(d)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 40
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Update the Submitters List\n",
      "\n",
      "Submissions column only contains those that submitted the paper. However, we can copy and paste the emails of all authors from CMT by attempting to email all authors. Then we can tell whether they submitted from that list. Go to  `Submissions -> View Active papers -> Email -> All Authors` and then copy the recipient list. Save it as `submitted_email_list.txt`.\n"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import re\n",
      "import os\n",
      "users_tmp = users\n",
      "with open(os.path.join(cmtutils.cmt_data_directory, 'submitted_email_list.txt')) as f:\n",
      "    file_str = f.read().lower()\n",
      "    matches = re.findall(r'<(.+?)>', file_str) \n",
      "\n",
      "submitted_users = users.set_index('Email').loc[matches]\n",
      "submitted_users.index.name = 'Email'\n",
      "user_by_email = users.set_index('Email')\n",
      "\n",
      "print \"Found {} real submission owners on list of authors from email list, \\\n",
      "and {} listed as have submitted in User and Roles list\".format(len(submitted_users), users['IsSubmissionOwner'].sum())\n",
      "\n",
      "print \"That is a mismatch of {}\".format(abs(users['IsSubmissionOwner'].sum() - len(submitted_users)))\n",
      "\n",
      "#Remove all \"submitted\" for users in list, then re-add them from the email list\n",
      "users['IsSubmissionOwner'] = False\n",
      "submitted_users['IsSubmissionOwner'] = True\n",
      "#Make email the primary key\n",
      "users = users.set_index('Email')\n",
      "users.loc[matches,'IsSubmissionOwner'] = True\n",
      "\n",
      "#Sanity check\n",
      "assert len(users) == len(users_tmp)\n",
      "assert users['IsSubmissionOwner'].sum() == len(submitted_users)\n",
      "\n",
      "print \"Users now has {} submission authors\".format(users['IsSubmissionOwner'].sum())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Found 3596 real submission owners on list of authors from email list, and 3780 listed as have submitted in User and Roles list\n",
        "That is a mismatch of 184\n",
        "Users now has 3596 submission authors\n"
       ]
      }
     ],
     "prompt_number": 41
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "users.IsSubmissionOwner.sum()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 42,
       "text": [
        "3596"
       ]
      }
     ],
     "prompt_number": 42
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Add column for first name last name as a key"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "users['Name'] = users['FirstName'] + users['LastName']\n",
      "users.sort_index(inplace=True)\n",
      "cols = users.columns.tolist()\n",
      "cols = cols[-1:] + cols[:-1]\n",
      "users = users[cols]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 43
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Find Duplicated Users\n",
      "\n",
      "Get dataframes of users with no role (might want to delete them in the future), and those with duplicate \"FirstName LastName\" combos."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Roleless\n",
      "no_roles = users[~users.IsAssociateChair & ~users.IsChair & ~users.IsExternalReviewer \n",
      "                 & ~users.IsMetaReviewer & ~users.IsProceedingsEditor \n",
      "                 & ~users.IsReviewer & ~users.IsSubmissionOwner]\n",
      "\n",
      "#Get duplicates with roles\n",
      "roles = users[users.IsAssociateChair | users.IsChair | users.IsExternalReviewer | users.IsMetaReviewer | users.IsProceedingsEditor \n",
      "              | users.IsReviewer | users.IsSubmissionOwner]\n",
      "NameCol = roles[\"Name\"]\n",
      "#Get the duplicated rows (first time it is repeated) get these rows from the name column then get a boolean matrix\n",
      "#for whether each name is contained in the row of duplicate names, then sort by names\n",
      "dupes_with_roles = roles[NameCol.isin(NameCol[NameCol.duplicated()])].sort(\"Name\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 44
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Filter out all those which aren't a submitter and another role combined between their accounts\n",
      "That is, duplicates are okay as long as they are just submitters and not also reviewers, \n",
      "and duplicate reviewers are the same as long as they are not also submitting"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Group Names together\n",
      "grouped = dupes_with_roles.groupby('Name')\n",
      "other_roles = ['IsAssociateChair', 'IsChair', 'IsExternalReviewer', 'IsMetaReviewer', 'IsProceedingsEditor','IsReviewer']\n",
      "\n",
      "#Filter\n",
      "submitter_and_reviewer = grouped.filter(lambda x: (x['IsSubmissionOwner'].sum() > 0) \n",
      "                                        and (sum(x[other_roles].sum()) > 0)\n",
      "                                       )"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 45
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Save the list to a csv file for later processing."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "submitter_and_reviewer_reindex = submitter_and_reviewer.reindex_axis(['FirstName', 'LastName', 'Institute', 'IsReviewer', 'IsSubmissionOwner', 'IsAssociateChair', 'IsChair', 'IsExternalReviewer', 'IsMetaReviewer', 'IsProceedingsEditor', 'LastLoginDate'], axis=1)\n",
      "submitter_and_reviewer_reindex.to_csv(os.path.join(data_dir, \"submitters_and_reviewers_dupes.csv\"), encoding='utf-8')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 46
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 46
    }
   ],
   "metadata": {}
  }
 ]
}