cleaning-merging-labeled-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merging Labeled Data Back into Data Frame 🐙"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading Original Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "original_df = pd.read_csv(\"data/cleaned-non-quantifier-data.csv\")"
   ]
  },
  {
   "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>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>Avg %</th>\n",
       "      <th>...</th>\n",
       "      <th>v3</th>\n",
       "      <th>period</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\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>0.001963</td>\n",
       "      <td>...</td>\n",
       "      <td>Edu</td>\n",
       "      <td>#17 May 7</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\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>0.001663</td>\n",
       "      <td>...</td>\n",
       "      <td>Edu</td>\n",
       "      <td>#17 May 7</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\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>0.001342</td>\n",
       "      <td>...</td>\n",
       "      <td>Edu</td>\n",
       "      <td>#17 May 7</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\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>0.001043</td>\n",
       "      <td>...</td>\n",
       "      <td>Edu</td>\n",
       "      <td>#17 May 7</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\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>0.001043</td>\n",
       "      <td>...</td>\n",
       "      <td>Edu</td>\n",
       "      <td>#17 May 7</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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          To       From                                 Reason for dishing  \\\n",
       "0  zeptimusQ    Tam2140            for hosting this kicking params party!    \n",
       "1  zeptimusQ  iviangita   for hosting and leading a lot of params parties    \n",
       "2  zeptimusQ  JuankBell  for testing and deploying the bot to record an...   \n",
       "3  zeptimusQ  iviangita           for the huge success of the MVV process    \n",
       "4  zeptimusQ  iviangita  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",
       "      Avg %  ...   v3     period Cred per Praise Cred per person To.1  \\\n",
       "0  0.001963  ...  Edu  #17 May 7             NaN             NaN  NaN   \n",
       "1  0.001663  ...  Edu  #17 May 7             NaN             NaN  NaN   \n",
       "2  0.001342  ...  Edu  #17 May 7             NaN             NaN  NaN   \n",
       "3  0.001043  ...  Edu  #17 May 7             NaN             NaN  NaN   \n",
       "4  0.001043  ...  Edu  #17 May 7             NaN             NaN  NaN   \n",
       "\n",
       "  Room-NoEmoji                            Source  Year  Month  Day  \n",
       "0       praise  Token Engineering Commons:praise  2021      5    7  \n",
       "1       praise  Token Engineering Commons:praise  2021      5    7  \n",
       "2       praise  Token Engineering Commons:praise  2021      4   28  \n",
       "3       praise  Token Engineering Commons:praise  2021      4   30  \n",
       "4       praise  Token Engineering Commons:praise  2021      4   30  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Processing Labeled Data: Taking from Our Initial Labs Session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Category Code</th>\n",
       "      <th>QUESTIONS</th>\n",
       "      <th>Unnamed: 2</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>...</th>\n",
       "      <th>period</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>Unnamed: 28</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TEC15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6077</td>\n",
       "      <td>liviade</td>\n",
       "      <td>cranders71</td>\n",
       "      <td>always caring for our finicky bot and valued c...</td>\n",
       "      <td>Telegram</td>\n",
       "      <td>2021-01-13</td>\n",
       "      <td>TE Praise</td>\n",
       "      <td>30</td>\n",
       "      <td>...</td>\n",
       "      <td>#9 Jan 15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TE Praise</td>\n",
       "      <td>Telegram:TE Praise</td>\n",
       "      <td>2021</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TEC12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>421</td>\n",
       "      <td>chuygarcia92</td>\n",
       "      <td>Tam2140</td>\n",
       "      <td>for the Masana Temples recommendation. Nice mi...</td>\n",
       "      <td>Token Engineering Commons</td>\n",
       "      <td>2021-04-30</td>\n",
       "      <td>🙏praise</td>\n",
       "      <td>1000</td>\n",
       "      <td>...</td>\n",
       "      <td>#17 May 7</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TEC12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70</td>\n",
       "      <td>iviangita</td>\n",
       "      <td>iviangita</td>\n",
       "      <td>for joining the Legal weekly sync</td>\n",
       "      <td>Token Engineering Commons</td>\n",
       "      <td>2021-04-30</td>\n",
       "      <td>🙏praise</td>\n",
       "      <td>1000</td>\n",
       "      <td>...</td>\n",
       "      <td>#17 May 7</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TEC12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7141</td>\n",
       "      <td>ygganderson</td>\n",
       "      <td>JessicaZartler</td>\n",
       "      <td>their presence, energy and smiles in the Comms...</td>\n",
       "      <td>Telegram</td>\n",
       "      <td>2020-11-24</td>\n",
       "      <td>TE Praise</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>#6 Dec 4</td>\n",
       "      <td>1.005.185.129</td>\n",
       "      <td>4.404.111.808</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TE Praise</td>\n",
       "      <td>Telegram:TE Praise</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>24</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TEC12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5293</td>\n",
       "      <td>metaverde</td>\n",
       "      <td>iviangita</td>\n",
       "      <td>mentioning or retweeting TE Commons on the soc...</td>\n",
       "      <td>Telegram</td>\n",
       "      <td>2021-01-22</td>\n",
       "      <td>TE Commons</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>#10 Jan 29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TE Commons</td>\n",
       "      <td>Telegram:TE Commons</td>\n",
       "      <td>2021</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Category Code QUESTIONS  Unnamed: 2            To            From  \\\n",
       "0         TEC15       NaN        6077       liviade      cranders71   \n",
       "1         TEC12       NaN         421  chuygarcia92         Tam2140   \n",
       "2         TEC12       NaN          70     iviangita       iviangita   \n",
       "3         TEC12       NaN        7141   ygganderson  JessicaZartler   \n",
       "4         TEC12       NaN        5293     metaverde       iviangita   \n",
       "\n",
       "                                  Reason for dishing  \\\n",
       "0  always caring for our finicky bot and valued c...   \n",
       "1  for the Masana Temples recommendation. Nice mi...   \n",
       "2                  for joining the Legal weekly sync   \n",
       "3  their presence, energy and smiles in the Comms...   \n",
       "4  mentioning or retweeting TE Commons on the soc...   \n",
       "\n",
       "                      Server        Date        Room v1 norm  ...      period  \\\n",
       "0                   Telegram  2021-01-13   TE Praise      30  ...   #9 Jan 15   \n",
       "1  Token Engineering Commons  2021-04-30     🙏praise    1000  ...   #17 May 7   \n",
       "2  Token Engineering Commons  2021-04-30     🙏praise    1000  ...   #17 May 7   \n",
       "3                   Telegram  2020-11-24   TE Praise      20  ...    #6 Dec 4   \n",
       "4                   Telegram  2021-01-22  TE Commons     NaN  ...  #10 Jan 29   \n",
       "\n",
       "   Cred per Praise Cred per person To.1 Room-NoEmoji  \\\n",
       "0              NaN             NaN  NaN    TE Praise   \n",
       "1              NaN             NaN  NaN       praise   \n",
       "2              NaN             NaN  NaN       praise   \n",
       "3    1.005.185.129   4.404.111.808  NaN    TE Praise   \n",
       "4              NaN             NaN  NaN   TE Commons   \n",
       "\n",
       "                             Source  Year Month Day Unnamed: 28  \n",
       "0                Telegram:TE Praise  2021     1  13         NaN  \n",
       "1  Token Engineering Commons:praise  2021     4  30         NaN  \n",
       "2  Token Engineering Commons:praise  2021     4  30         NaN  \n",
       "3                Telegram:TE Praise  2020    11  24         NaN  \n",
       "4               Telegram:TE Commons  2021     1  22         NaN  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_labeled_df = pd.read_csv('data/sample_praise.csv')\n",
    "first_labeled_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_labeled_df[\"Original Index\"] = first_labeled_df[\"Unnamed: 2\"]\n",
    "first_labeled_df.set_index(\"Original Index\", inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter Only Down to Data That Actually Got Labeled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_labeled_df = first_labeled_df[~(first_labeled_df[\"Category Code\"].isna())]\n",
    "first_labeled_df = first_labeled_df[[\"Category Code\", \"QUESTIONS\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Category Code</th>\n",
       "      <th>QUESTIONS</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Original Index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6077</th>\n",
       "      <td>TEC15</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>421</th>\n",
       "      <td>TEC12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>TEC12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7141</th>\n",
       "      <td>TEC12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5293</th>\n",
       "      <td>TEC12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Category Code QUESTIONS\n",
       "Original Index                        \n",
       "6077                   TEC15       NaN\n",
       "421                    TEC12       NaN\n",
       "70                     TEC12       NaN\n",
       "7141                   TEC12       NaN\n",
       "5293                   TEC12       NaN"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_labeled_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Processing Labeled Data: Read the Data from Our Crowdsourced Google Sheet "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "tagged_by_info = pd.read_excel('data/Praise to Label.xlsx', sheet_name='tagged-by',engine='openpyxl', usecols=\"A:D\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## It appears some of the labelings got off, so we can't use them. If we ever do this again, we'll do a midway check/scrape to give feedback to labelers. \n",
    "\n",
    "* only keep data from sheets that appear in \"sample #\" on \"tagged-by\"\n",
    "* drop \"TEC12\" column that was inadvertently introduced (this is why separating worksheets was a good idea, so \n",
    "* drop anything where \"Original Index\" does not match \"Unnamed: 0\" (which was actually the index)\n",
    "* drop anything that didn't get a label\n",
    "* we are keeping data with questions, but we can drop that later if we wish"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1, 2, 3, 4, 5, 6, 20, 7, 11, 22, 8, 14, 41, 28, 40]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "NUM_SHEETS = 56\n",
    "sheets_with_info = [x for x in tagged_by_info[\"Sample Number\"].unique() if x in range(NUM_SHEETS)]\n",
    "sheets_with_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = []\n",
    "for sheet in sheets_with_info:\n",
    "    df = pd.read_excel('data/Praise to Label.xlsx',  sheet_name=\"new-sample-\" + str(sheet),engine='openpyxl', usecols=\"A:F\")\n",
    "    data.append(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "label_df = pd.concat(data)\n",
    "label_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "label_df.drop(columns = \"TEC12\", inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "label_df = label_df[label_df[\"Unnamed: 0\"] == label_df[\"Original Index\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "label_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "label_df[\"Category Code\"].isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "label_df = label_df[~(label_df[\"Category Code\"].isna())]\n",
    "label_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "label_df[\"Original Index\"] = [int(x) for x in label_df[\"Original Index\"]]\n",
    "label_df.set_index(\"Original Index\", inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "label_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "label_df.drop(columns = [\"Unnamed: 0\"], inplace = True)\n",
    "label_df = label_df[[\"Category Code\", \"QUESTIONS\"]]\n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "label_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine Data from Meeting with Crowdsourced Asynchonous Data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_labeled_data = pd.concat([first_labeled_df, label_df])\n",
    "all_labeled_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine Labeled Data with Original Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = original_df.merge(all_labeled_data, how = \"outer\", right_index = True, left_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Some Extremely Obsessive and Probably Unnecessary Reality Check "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(final_df) == len(original_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "all([final_df.loc[k, \"Category Code\"] == label_df.loc[k, \"Category Code\"] for k in label_df.index])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "all([final_df.loc[k, \"Category Code\"] == first_labeled_df.loc[k, \"Category Code\"] for k in first_labeled_df.index])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.to_csv(\"data/all-hand-labeled-data.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
}