Evaluation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Evaluation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This note book provides anlytics and comparison of the created \"esoc_all_data_analytics.xlsx\" and the \"75_12_Master_v4rc03_full.xlsx\" document provided in the research documents."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning the master to fit the analytics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This code simply modifies the master list to fit the date range and column names of the analytics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(4497, 75)\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "#First cut and make new csv with only year, this is done first so large xslx does not need to be reloaded later\n",
    "esoc_master_raw = pd.read_excel(\"esoc_master_raw.xlsx\")\n",
    "esoc_master_raw_2010_2012 = esoc_master_raw.loc[(esoc_master_raw['year'] == 2010) | (esoc_master_raw['year'] == 2011) | (esoc_master_raw['year'] == 2012)]\n",
    "\n",
    "#Print number of entries in this range for later verification\n",
    "print esoc_master_raw_2010_2012.shape\n",
    "\n",
    "#Print to csv\n",
    "esoc_master_raw_2010_2012.to_csv(\"Master_2010_2012.csv\",header=True, index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "27\n",
      "4483\n"
     ]
    }
   ],
   "source": [
    "#Get cut data from 2010-2012\n",
    "esoc_master = pd.read_csv(\"Master_2010_2012.csv\")\n",
    "\n",
    "#Duplicated Journal Numbers in the master file\n",
    "#jxid has no duplicates, but its origins are unknown\n",
    "master_duplicates = pd.concat(g for _, g in esoc_master.groupby(\"jnmbr\") if len(g) > 1)\n",
    "print len(master_duplicates)\n",
    "\n",
    "#Remove duplicates for comparison adn then put numbers into series\n",
    "master_unique = esoc_master.drop_duplicates(\"jnmbr\")\n",
    "master_journal_nums = master_unique[\"jnmbr\"]\n",
    "print len(master_journal_nums)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6\n",
      "9838\n"
     ]
    }
   ],
   "source": [
    "#Get data from In-Processing and then drop possible duplicates \n",
    "#Only one seen in trials\n",
    "esoc_all_data_raw = pd.read_csv(\"ESOC_All_Data_2010_2012_Analytics.csv\")\n",
    "raw_duplicates = pd.concat(g for _, g in esoc_all_data_raw.groupby(\"Journal Code\") if len(g) > 1)\n",
    "print len(raw_duplicates)\n",
    "\n",
    "#Add uniques to series\n",
    "raw_unique = esoc_all_data_raw.drop_duplicates(\"Journal Code\")\n",
    "raw_journal_nums = raw_unique[\"Journal Code\"]\n",
    "print len(raw_journal_nums)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3408\n",
      "3408\n",
      "3408\n"
     ]
    }
   ],
   "source": [
    "#Find number of matching journal numbers between raw and master and then put them into a list\n",
    "mathcing_journal_nums = pd.Series(list(set(master_journal_nums) & set(raw_journal_nums)))\n",
    "print len(mathcing_journal_nums)\n",
    "\n",
    "#Make new dataframes with only the matched journals for companrison and sort by, and set as index\n",
    "raw_matched_journals = raw_unique[raw_unique['Journal Code'].isin(mathcing_journal_nums)]\n",
    "master_matched_journals = master_unique[master_unique[\"jnmbr\"].isin(mathcing_journal_nums)]\n",
    "\n",
    "print len(raw_matched_journals)\n",
    "print len(master_matched_journals)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Set Journal Number as the Index and sort\n",
    "#Make new dataframes with only the matched journals for companrison and sort by, and set as index\n",
    "raw_matched_journals = raw_matched_journals.set_index(\"Journal Code\")\n",
    "master_matched_journals = master_matched_journals.set_index(\"jnmbr\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>govtwia</th>\n",
       "      <th>GS WIA</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10001</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10005</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010-06-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10007</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2010-06-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10009</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10012</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10013</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10015</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10017</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10019</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10021</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10024</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010-06-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10026</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10032</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10033</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10034</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10046</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10047</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10051</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10053</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10054</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10058</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010-06-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10059</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10071</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10072</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10073</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10078</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10081</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10082</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10085</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10086</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMC12738</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-12-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMC12740</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-12-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMC12744</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-12-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMC12753</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-12-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMC12759</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMC12760</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMC12773</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-12-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMC12777</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-12-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMC12778</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-12-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMC12781</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-12-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMC12790</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-12-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMC13748</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-12-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMC16311</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-10-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WMC10363</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-07-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WMC10364</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-07-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WMC10366</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-06-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emc02019</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2012-02-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emc05840</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-05-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emc08212</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-08-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emc08221</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-08-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emc08638</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2011-08-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emc10241</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-10-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emc10368</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-10-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emc10404</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-10-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emc11041</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2011-11-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emc11625</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-11-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emc11629</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-11-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emc12144</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2011-12-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emc12720</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-12-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emc12783</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-12-26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3408 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          govtwia  GS WIA        Date\n",
       "10001           0       0  2010-06-03\n",
       "10005           1       1  2010-06-04\n",
       "10007           0       2  2010-06-04\n",
       "10009           0       0  2010-06-05\n",
       "10012           0       0  2010-06-05\n",
       "10013           0       0  2010-06-05\n",
       "10015           0       0  2010-06-05\n",
       "10017           0       0  2010-06-05\n",
       "10019           0       0  2010-06-06\n",
       "10021           0       0  2010-06-05\n",
       "10024           1       1  2010-06-05\n",
       "10026           0       0  2010-06-06\n",
       "10032           0       0  2010-06-06\n",
       "10033           5       6  2010-06-07\n",
       "10034           0       0  2010-06-06\n",
       "10046           0       0  2010-06-07\n",
       "10047           0       0  2010-06-07\n",
       "10051           0       0  2010-06-08\n",
       "10053           0       0  2010-06-08\n",
       "10054           0       0  2010-06-08\n",
       "10058           1       1  2010-06-08\n",
       "10059           0       0  2010-06-08\n",
       "10071           0       0  2010-06-10\n",
       "10072           0       0  2010-06-10\n",
       "10073           0       0  2010-06-10\n",
       "10078           0       0  2010-06-11\n",
       "10081           0       0  2010-06-11\n",
       "10082           0       0  2010-06-11\n",
       "10085           0       0  2010-06-11\n",
       "10086           0       0  2010-06-11\n",
       "...           ...     ...         ...\n",
       "EMC12738        0       0  2012-12-12\n",
       "EMC12740        0       0  2012-12-12\n",
       "EMC12744        0       0  2012-12-13\n",
       "EMC12753        0       0  2012-12-16\n",
       "EMC12759        0       0  2012-12-18\n",
       "EMC12760        0       0  2012-12-18\n",
       "EMC12773        0       0  2012-12-22\n",
       "EMC12777        0       0  2012-12-25\n",
       "EMC12778        0       0  2012-12-25\n",
       "EMC12781        0       0  2012-12-27\n",
       "EMC12790        0       0  2012-12-27\n",
       "EMC13748        0       0  2012-12-13\n",
       "EMC16311        0       0  2012-10-16\n",
       "WMC10363        0       0  2010-07-08\n",
       "WMC10364        0       0  2010-07-07\n",
       "WMC10366        0       0  2010-06-29\n",
       "emc02019        1       1  2012-02-05\n",
       "emc05840        0       0  2012-05-30\n",
       "emc08212        0       0  2012-08-24\n",
       "emc08221        0       0  2012-08-24\n",
       "emc08638        0       0  2011-08-26\n",
       "emc10241        0       0  2012-10-06\n",
       "emc10368        0       0  2012-10-22\n",
       "emc10404        1       0  2012-10-29\n",
       "emc11041        0       0  2011-11-20\n",
       "emc11625        0       0  2012-11-28\n",
       "emc11629        0       0  2012-11-28\n",
       "emc12144        0       0  2011-12-05\n",
       "emc12720        0       0  2012-12-10\n",
       "emc12783        0       0  2012-12-26\n",
       "\n",
       "[3408 rows x 3 columns]"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([master_matched_journals[\"govtwia\"],raw_matched_journals[\"GS WIA\"],raw_matched_journals[\"Date\"]], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After review of this data the master list seems to have an error for Journal Numner 10007: \n",
    "<img src=\"journal_10007.png\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}