cleaning-actual-IH.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cleaning the Impact Hours Column 🐙\n",
    "Date: June 21, 2021"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading Packages and Data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from scipy.stats import mode\n",
    "\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import plotly.express as px "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>To</th>\n",
       "      <th>From</th>\n",
       "      <th>Reason for dishing</th>\n",
       "      <th>Server</th>\n",
       "      <th>Date</th>\n",
       "      <th>Room</th>\n",
       "      <th>v1 norm</th>\n",
       "      <th>v2 norm</th>\n",
       "      <th>v3 norm</th>\n",
       "      <th>...</th>\n",
       "      <th>Cred per Praise</th>\n",
       "      <th>Cred per person</th>\n",
       "      <th>To.1</th>\n",
       "      <th>Room-NoEmoji</th>\n",
       "      <th>Source</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Category Code</th>\n",
       "      <th>QUESTIONS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>zeptimusQ</td>\n",
       "      <td>Tam2140</td>\n",
       "      <td>for hosting this kicking params party!</td>\n",
       "      <td>Token Engineering Commons</td>\n",
       "      <td>2021-05-07</td>\n",
       "      <td>🙏praise</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>praise</td>\n",
       "      <td>Token Engineering Commons:praise</td>\n",
       "      <td>2021</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>zeptimusQ</td>\n",
       "      <td>iviangita</td>\n",
       "      <td>for hosting and leading a lot of params parties</td>\n",
       "      <td>Token Engineering Commons</td>\n",
       "      <td>2021-05-07</td>\n",
       "      <td>🙏praise</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>praise</td>\n",
       "      <td>Token Engineering Commons:praise</td>\n",
       "      <td>2021</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>zeptimusQ</td>\n",
       "      <td>JuankBell</td>\n",
       "      <td>for testing and deploying the bot to record an...</td>\n",
       "      <td>Token Engineering Commons</td>\n",
       "      <td>2021-04-28</td>\n",
       "      <td>🙏praise</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>praise</td>\n",
       "      <td>Token Engineering Commons:praise</td>\n",
       "      <td>2021</td>\n",
       "      <td>4</td>\n",
       "      <td>28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>zeptimusQ</td>\n",
       "      <td>iviangita</td>\n",
       "      <td>for the huge success of the MVV process</td>\n",
       "      <td>Token Engineering Commons</td>\n",
       "      <td>2021-04-30</td>\n",
       "      <td>🙏praise</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>praise</td>\n",
       "      <td>Token Engineering Commons:praise</td>\n",
       "      <td>2021</td>\n",
       "      <td>4</td>\n",
       "      <td>30</td>\n",
       "      <td>TEC2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>zeptimusQ</td>\n",
       "      <td>iviangita</td>\n",
       "      <td>for his awesome work on the recorder bot, for ...</td>\n",
       "      <td>Token Engineering Commons</td>\n",
       "      <td>2021-04-30</td>\n",
       "      <td>🙏praise</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>praise</td>\n",
       "      <td>Token Engineering Commons:praise</td>\n",
       "      <td>2021</td>\n",
       "      <td>4</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0         To       From  \\\n",
       "0           0  zeptimusQ    Tam2140   \n",
       "1           1  zeptimusQ  iviangita   \n",
       "2           2  zeptimusQ  JuankBell   \n",
       "3           3  zeptimusQ  iviangita   \n",
       "4           4  zeptimusQ  iviangita   \n",
       "\n",
       "                                  Reason for dishing  \\\n",
       "0            for hosting this kicking params party!    \n",
       "1   for hosting and leading a lot of params parties    \n",
       "2  for testing and deploying the bot to record an...   \n",
       "3           for the huge success of the MVV process    \n",
       "4  for his awesome work on the recorder bot, for ...   \n",
       "\n",
       "                      Server        Date     Room  v1 norm  v2 norm  v3 norm  \\\n",
       "0  Token Engineering Commons  2021-05-07  🙏praise  10000.0    100.0    200.0   \n",
       "1  Token Engineering Commons  2021-05-07  🙏praise  10000.0    100.0    100.0   \n",
       "2  Token Engineering Commons  2021-04-28  🙏praise   1000.0    200.0    200.0   \n",
       "3  Token Engineering Commons  2021-04-30  🙏praise   1000.0    200.0    100.0   \n",
       "4  Token Engineering Commons  2021-04-30  🙏praise   1000.0    200.0    100.0   \n",
       "\n",
       "   ...  Cred per Praise  Cred per person  To.1 Room-NoEmoji  \\\n",
       "0  ...              NaN              NaN   NaN       praise   \n",
       "1  ...              NaN              NaN   NaN       praise   \n",
       "2  ...              NaN              NaN   NaN       praise   \n",
       "3  ...              NaN              NaN   NaN       praise   \n",
       "4  ...              NaN              NaN   NaN       praise   \n",
       "\n",
       "                             Source  Year Month Day  Category Code  QUESTIONS  \n",
       "0  Token Engineering Commons:praise  2021     5   7            NaN        NaN  \n",
       "1  Token Engineering Commons:praise  2021     5   7            NaN        NaN  \n",
       "2  Token Engineering Commons:praise  2021     4  28            NaN        NaN  \n",
       "3  Token Engineering Commons:praise  2021     4  30           TEC2        NaN  \n",
       "4  Token Engineering Commons:praise  2021     4  30            NaN        NaN  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "praise_df = pd.read_csv(\"data/all-hand-labeled-data.csv\")\n",
    "praise_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'To', 'From', 'Reason for dishing', 'Server', 'Date',\n",
       "       'Room', 'v1 norm', 'v2 norm', 'v3 norm', 'Avg %', 'IH per Praise',\n",
       "       'IH per person', 'Unnamed: 12', 'v1', 'v2', 'v3', 'period',\n",
       "       'Cred per Praise', 'Cred per person', 'To.1', 'Room-NoEmoji', 'Source',\n",
       "       'Year', 'Month', 'Day', 'Category Code', 'QUESTIONS'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "praise_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7625"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(praise_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2787"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(praise_df[\"Reason for dishing\"].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning the Data "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### We drop anything for which 'IH per Praise' is not a number, since it can't yield Quantitative information about Impact Hours. We also drop anything that received 0 IH. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "praise_df = praise_df[~(praise_df[\"IH per Praise\"].isna())]\n",
    "praise_df = praise_df[praise_df[\"IH per Praise\"] > 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6948"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(praise_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2533"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(praise_df[\"Reason for dishing\"].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## We clean the period that the praise was received to an integer. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_period(period_string):\n",
    "    new_str = period_string.replace(\"#\",\"\")\n",
    "    new_str = new_str.split()\n",
    "    final = int(new_str[0])\n",
    "    return final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([17, 16, 15, 14, 13, 12, 11, 10,  9,  8,  7], dtype=int64)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "praise_df[\"period\"] = praise_df[\"period\"].apply(clean_period)\n",
    "praise_df[\"period\"].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Notice that we only have periods 7-17 in our data. \n",
    "\n",
    "This is actually good, as it covers:\n",
    "- the period with the contemporary Praise process\n",
    "- the period when the TEC Discord Server existed"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleaning\n",
    "\n",
    "There is an issue that has been repeatedly raised, and that now we want to address. There are times where exceptional contributions for an individual were recognized by adjusting an individual's Impact Hours (indirectly by a quantifier adjusting their own score). \n",
    "\n",
    "We are working off the following assumption:\n",
    "> **Assumption:** if two Praises had *both*\n",
    "> - the same \"Reason for dishing\"\n",
    "> - the same time period\n",
    "> then they should have the same value. \n",
    "\n",
    "We will make the following fix:\n",
    "> **Procedure:** If a Praise is part of a group that came from the same time period **and** the exact same \"Reason for dishing\", we will assign it the mode of that group's Impact Hours. \n",
    "\n",
    "Now we add a new column called \"clean_IH_per_praise\" that adjusts for this. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.355251551"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def find_mode_by_period_and_reason(df = praise_df, period = 0, reason = \"\"):\n",
    "    new_df = df[(df[\"period\"] == period) & (df[\"Reason for dishing\"] == reason)]\n",
    "    mode_of_new_df_IH = new_df[\"IH per Praise\"].mode().to_list() #surely there's a better way\n",
    "    mode_of_new_df_IH = mode_of_new_df_IH[0] #than this\n",
    "    return mode_of_new_df_IH\n",
    "\n",
    "def mode_by_period_and_reason(x):\n",
    "    obs = praise_df.iloc[x]\n",
    "    my_reason = obs[\"Reason for dishing\"]\n",
    "    my_period = obs[\"period\"]\n",
    "    my_mode = find_mode_by_period_and_reason(df = praise_df, period = my_period, reason = my_reason)\n",
    "    return my_mode\n",
    "\n",
    "mode_by_period_and_reason(0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned_IH = [mode_by_period_and_reason(x) for x in range(len(praise_df))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "praise_df[\"IH_per_praise_clean\"] = cleaned_IH"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       2.355252\n",
       "1       1.995539\n",
       "2       1.610872\n",
       "3       1.251160\n",
       "4       1.251160\n",
       "          ...   \n",
       "7135    0.267460\n",
       "7136    0.267460\n",
       "7137    0.267460\n",
       "7138    0.048312\n",
       "7139    0.048312\n",
       "Name: IH_per_praise_clean, Length: 6948, dtype: float64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "praise_df[\"IH_per_praise_clean\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1000"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(praise_df[\"IH_per_praise_clean\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "praise_df.to_csv(\"data/praise-df-hand-labeled-with-clean-IH.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}