analysis_script.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "pwd = os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>Respondent ID</th>\n",
       "      <th>Start Date</th>\n",
       "      <th>End Date</th>\n",
       "      <th>Email Address</th>\n",
       "      <th>First Name</th>\n",
       "      <th>Last Name</th>\n",
       "      <th>Custom Data 1</th>\n",
       "      <th>Identify which division you work in.-Response</th>\n",
       "      <th>Identify which division you work in.-Other (please specify)</th>\n",
       "      <th>Which of the following best describes your position level?-Response</th>\n",
       "      <th>...</th>\n",
       "      <th>Question 29-Response 8</th>\n",
       "      <th>Question 29-Response 9</th>\n",
       "      <th>Question 29-Response 10</th>\n",
       "      <th>Question 29-Response 11</th>\n",
       "      <th>Question 29-Response 12</th>\n",
       "      <th>Question 29-Response 13</th>\n",
       "      <th>Question 29-Response 14</th>\n",
       "      <th>Question 30-Response 1</th>\n",
       "      <th>Question 30-Response 2</th>\n",
       "      <th>Question 30-Response 3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5379192392</td>\n",
       "      <td>2021-01-22 12:01:17</td>\n",
       "      <td>2021-01-22 12:40:34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2658722536</td>\n",
       "      <td>2021-01-22 06:56:37</td>\n",
       "      <td>2021-01-22 07:34:10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4044163394</td>\n",
       "      <td>2021-01-22 06:35:18</td>\n",
       "      <td>2021-01-22 06:47:32</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5535865599</td>\n",
       "      <td>2021-01-21 21:29:32</td>\n",
       "      <td>2021-01-21 21:40:24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>...</td>\n",
       "      <td>Answer 2</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>Answer 6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3356802928</td>\n",
       "      <td>2021-01-21 17:26:39</td>\n",
       "      <td>2021-01-21 17:44:40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>7940065082</td>\n",
       "      <td>2021-01-11 06:19:18</td>\n",
       "      <td>2021-01-11 06:32:59</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>...</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>5157705612</td>\n",
       "      <td>2021-01-11 06:19:14</td>\n",
       "      <td>2021-01-11 06:33:29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>...</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>9920755555</td>\n",
       "      <td>2021-01-11 06:18:48</td>\n",
       "      <td>2021-01-11 06:27:27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>6638341389</td>\n",
       "      <td>2021-01-11 06:19:01</td>\n",
       "      <td>2021-01-11 06:33:06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>...</td>\n",
       "      <td>Answer 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>8114622230</td>\n",
       "      <td>2021-01-11 06:18:50</td>\n",
       "      <td>2021-01-11 06:26:46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>...</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>198 rows × 100 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Respondent ID          Start Date            End Date  Email Address  \\\n",
       "0       5379192392 2021-01-22 12:01:17 2021-01-22 12:40:34            NaN   \n",
       "1       2658722536 2021-01-22 06:56:37 2021-01-22 07:34:10            NaN   \n",
       "2       4044163394 2021-01-22 06:35:18 2021-01-22 06:47:32            NaN   \n",
       "3       5535865599 2021-01-21 21:29:32 2021-01-21 21:40:24            NaN   \n",
       "4       3356802928 2021-01-21 17:26:39 2021-01-21 17:44:40            NaN   \n",
       "..             ...                 ...                 ...            ...   \n",
       "193     7940065082 2021-01-11 06:19:18 2021-01-11 06:32:59            NaN   \n",
       "194     5157705612 2021-01-11 06:19:14 2021-01-11 06:33:29            NaN   \n",
       "195     9920755555 2021-01-11 06:18:48 2021-01-11 06:27:27            NaN   \n",
       "196     6638341389 2021-01-11 06:19:01 2021-01-11 06:33:06            NaN   \n",
       "197     8114622230 2021-01-11 06:18:50 2021-01-11 06:26:46            NaN   \n",
       "\n",
       "     First Name  Last Name  Custom Data 1  \\\n",
       "0           NaN        NaN            NaN   \n",
       "1           NaN        NaN            NaN   \n",
       "2           NaN        NaN            NaN   \n",
       "3           NaN        NaN            NaN   \n",
       "4           NaN        NaN            NaN   \n",
       "..          ...        ...            ...   \n",
       "193         NaN        NaN            NaN   \n",
       "194         NaN        NaN            NaN   \n",
       "195         NaN        NaN            NaN   \n",
       "196         NaN        NaN            NaN   \n",
       "197         NaN        NaN            NaN   \n",
       "\n",
       "    Identify which division you work in.-Response  \\\n",
       "0                                  Infrastructure   \n",
       "1                                         Finance   \n",
       "2                                  Infrastructure   \n",
       "3                                  Infrastructure   \n",
       "4                                 Port Operations   \n",
       "..                                            ...   \n",
       "193                                Infrastructure   \n",
       "194                                       Finance   \n",
       "195                               Port Operations   \n",
       "196                                Infrastructure   \n",
       "197                        Information Technology   \n",
       "\n",
       "    Identify which division you work in.-Other (please specify)  \\\n",
       "0                                                  NaN            \n",
       "1                                                  NaN            \n",
       "2                                                  NaN            \n",
       "3                                                  NaN            \n",
       "4                                                  NaN            \n",
       "..                                                 ...            \n",
       "193                                                NaN            \n",
       "194                                                NaN            \n",
       "195                                                NaN            \n",
       "196                                                NaN            \n",
       "197                                                NaN            \n",
       "\n",
       "    Which of the following best describes your position level?-Response  ...  \\\n",
       "0                                                Staff                   ...   \n",
       "1                                                Staff                   ...   \n",
       "2                                      Department Lead                   ...   \n",
       "3                                              Manager                   ...   \n",
       "4                                              Manager                   ...   \n",
       "..                                                 ...                   ...   \n",
       "193                                    Department Lead                   ...   \n",
       "194                                              Staff                   ...   \n",
       "195                                              Staff                   ...   \n",
       "196                                            Manager                   ...   \n",
       "197                                              Staff                   ...   \n",
       "\n",
       "    Question 29-Response 8 Question 29-Response 9 Question 29-Response 10  \\\n",
       "0                      NaN               Answer 8                Answer 8   \n",
       "1                      NaN               Answer 5                     NaN   \n",
       "2                      NaN                    NaN                Answer 4   \n",
       "3                 Answer 2               Answer 5                Answer 7   \n",
       "4                      NaN               Answer 5                Answer 4   \n",
       "..                     ...                    ...                     ...   \n",
       "193               Answer 1               Answer 1                Answer 3   \n",
       "194               Answer 5                    NaN                Answer 5   \n",
       "195                    NaN               Answer 7                     NaN   \n",
       "196               Answer 2                    NaN                Answer 3   \n",
       "197               Answer 3                    NaN                Answer 7   \n",
       "\n",
       "    Question 29-Response 11 Question 29-Response 12 Question 29-Response 13  \\\n",
       "0                  Answer 4                     NaN                     NaN   \n",
       "1                       NaN                Answer 2                     NaN   \n",
       "2                  Answer 4                Answer 6                     NaN   \n",
       "3                       NaN                Answer 6                     NaN   \n",
       "4                  Answer 4                Answer 7                Answer 7   \n",
       "..                      ...                     ...                     ...   \n",
       "193                Answer 6                Answer 6                Answer 8   \n",
       "194                     NaN                     NaN                Answer 2   \n",
       "195                     NaN                     NaN                     NaN   \n",
       "196                Answer 5                     NaN                Answer 8   \n",
       "197                     NaN                     NaN                     NaN   \n",
       "\n",
       "    Question 29-Response 14 Question 30-Response 1 Question 30-Response 2  \\\n",
       "0                  Answer 5                    NaN                    NaN   \n",
       "1                  Answer 5                    NaN                    NaN   \n",
       "2                  Answer 6                    NaN               Answer 1   \n",
       "3                  Answer 7               Answer 7               Answer 1   \n",
       "4                       NaN               Answer 7                    NaN   \n",
       "..                      ...                    ...                    ...   \n",
       "193                     NaN                    NaN                    NaN   \n",
       "194                     NaN                    NaN                    NaN   \n",
       "195                     NaN                    NaN               Answer 3   \n",
       "196                Answer 3               Answer 3                    NaN   \n",
       "197                     NaN               Answer 3                    NaN   \n",
       "\n",
       "    Question 30-Response 3  \n",
       "0                      NaN  \n",
       "1                 Answer 1  \n",
       "2                      NaN  \n",
       "3                 Answer 6  \n",
       "4                 Answer 8  \n",
       "..                     ...  \n",
       "193               Answer 8  \n",
       "194               Answer 6  \n",
       "195                    NaN  \n",
       "196                    NaN  \n",
       "197                    NaN  \n",
       "\n",
       "[198 rows x 100 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_excel(pwd + \"\\\\Survey Monkey Data Edited.xlsx\", sheet_name=\"EditedData\")\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>Respondent ID</th>\n",
       "      <th>Start Date</th>\n",
       "      <th>End Date</th>\n",
       "      <th>Email Address</th>\n",
       "      <th>First Name</th>\n",
       "      <th>Last Name</th>\n",
       "      <th>Custom Data 1</th>\n",
       "      <th>Identify which division you work in.-Response</th>\n",
       "      <th>Identify which division you work in.-Other (please specify)</th>\n",
       "      <th>Which of the following best describes your position level?-Response</th>\n",
       "      <th>...</th>\n",
       "      <th>Question 29-Response 8</th>\n",
       "      <th>Question 29-Response 9</th>\n",
       "      <th>Question 29-Response 10</th>\n",
       "      <th>Question 29-Response 11</th>\n",
       "      <th>Question 29-Response 12</th>\n",
       "      <th>Question 29-Response 13</th>\n",
       "      <th>Question 29-Response 14</th>\n",
       "      <th>Question 30-Response 1</th>\n",
       "      <th>Question 30-Response 2</th>\n",
       "      <th>Question 30-Response 3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5379192392</td>\n",
       "      <td>2021-01-22 12:01:17</td>\n",
       "      <td>2021-01-22 12:40:34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2658722536</td>\n",
       "      <td>2021-01-22 06:56:37</td>\n",
       "      <td>2021-01-22 07:34:10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4044163394</td>\n",
       "      <td>2021-01-22 06:35:18</td>\n",
       "      <td>2021-01-22 06:47:32</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5535865599</td>\n",
       "      <td>2021-01-21 21:29:32</td>\n",
       "      <td>2021-01-21 21:40:24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>...</td>\n",
       "      <td>Answer 2</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>Answer 6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3356802928</td>\n",
       "      <td>2021-01-21 17:26:39</td>\n",
       "      <td>2021-01-21 17:44:40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>7940065082</td>\n",
       "      <td>2021-01-11 06:19:18</td>\n",
       "      <td>2021-01-11 06:32:59</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>...</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>5157705612</td>\n",
       "      <td>2021-01-11 06:19:14</td>\n",
       "      <td>2021-01-11 06:33:29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>...</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>9920755555</td>\n",
       "      <td>2021-01-11 06:18:48</td>\n",
       "      <td>2021-01-11 06:27:27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>6638341389</td>\n",
       "      <td>2021-01-11 06:19:01</td>\n",
       "      <td>2021-01-11 06:33:06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>...</td>\n",
       "      <td>Answer 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>8114622230</td>\n",
       "      <td>2021-01-11 06:18:50</td>\n",
       "      <td>2021-01-11 06:26:46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>...</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>198 rows × 100 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Respondent ID          Start Date            End Date  Email Address  \\\n",
       "0       5379192392 2021-01-22 12:01:17 2021-01-22 12:40:34            NaN   \n",
       "1       2658722536 2021-01-22 06:56:37 2021-01-22 07:34:10            NaN   \n",
       "2       4044163394 2021-01-22 06:35:18 2021-01-22 06:47:32            NaN   \n",
       "3       5535865599 2021-01-21 21:29:32 2021-01-21 21:40:24            NaN   \n",
       "4       3356802928 2021-01-21 17:26:39 2021-01-21 17:44:40            NaN   \n",
       "..             ...                 ...                 ...            ...   \n",
       "193     7940065082 2021-01-11 06:19:18 2021-01-11 06:32:59            NaN   \n",
       "194     5157705612 2021-01-11 06:19:14 2021-01-11 06:33:29            NaN   \n",
       "195     9920755555 2021-01-11 06:18:48 2021-01-11 06:27:27            NaN   \n",
       "196     6638341389 2021-01-11 06:19:01 2021-01-11 06:33:06            NaN   \n",
       "197     8114622230 2021-01-11 06:18:50 2021-01-11 06:26:46            NaN   \n",
       "\n",
       "     First Name  Last Name  Custom Data 1  \\\n",
       "0           NaN        NaN            NaN   \n",
       "1           NaN        NaN            NaN   \n",
       "2           NaN        NaN            NaN   \n",
       "3           NaN        NaN            NaN   \n",
       "4           NaN        NaN            NaN   \n",
       "..          ...        ...            ...   \n",
       "193         NaN        NaN            NaN   \n",
       "194         NaN        NaN            NaN   \n",
       "195         NaN        NaN            NaN   \n",
       "196         NaN        NaN            NaN   \n",
       "197         NaN        NaN            NaN   \n",
       "\n",
       "    Identify which division you work in.-Response  \\\n",
       "0                                  Infrastructure   \n",
       "1                                         Finance   \n",
       "2                                  Infrastructure   \n",
       "3                                  Infrastructure   \n",
       "4                                 Port Operations   \n",
       "..                                            ...   \n",
       "193                                Infrastructure   \n",
       "194                                       Finance   \n",
       "195                               Port Operations   \n",
       "196                                Infrastructure   \n",
       "197                        Information Technology   \n",
       "\n",
       "    Identify which division you work in.-Other (please specify)  \\\n",
       "0                                                  NaN            \n",
       "1                                                  NaN            \n",
       "2                                                  NaN            \n",
       "3                                                  NaN            \n",
       "4                                                  NaN            \n",
       "..                                                 ...            \n",
       "193                                                NaN            \n",
       "194                                                NaN            \n",
       "195                                                NaN            \n",
       "196                                                NaN            \n",
       "197                                                NaN            \n",
       "\n",
       "    Which of the following best describes your position level?-Response  ...  \\\n",
       "0                                                Staff                   ...   \n",
       "1                                                Staff                   ...   \n",
       "2                                      Department Lead                   ...   \n",
       "3                                              Manager                   ...   \n",
       "4                                              Manager                   ...   \n",
       "..                                                 ...                   ...   \n",
       "193                                    Department Lead                   ...   \n",
       "194                                              Staff                   ...   \n",
       "195                                              Staff                   ...   \n",
       "196                                            Manager                   ...   \n",
       "197                                              Staff                   ...   \n",
       "\n",
       "    Question 29-Response 8 Question 29-Response 9 Question 29-Response 10  \\\n",
       "0                      NaN               Answer 8                Answer 8   \n",
       "1                      NaN               Answer 5                     NaN   \n",
       "2                      NaN                    NaN                Answer 4   \n",
       "3                 Answer 2               Answer 5                Answer 7   \n",
       "4                      NaN               Answer 5                Answer 4   \n",
       "..                     ...                    ...                     ...   \n",
       "193               Answer 1               Answer 1                Answer 3   \n",
       "194               Answer 5                    NaN                Answer 5   \n",
       "195                    NaN               Answer 7                     NaN   \n",
       "196               Answer 2                    NaN                Answer 3   \n",
       "197               Answer 3                    NaN                Answer 7   \n",
       "\n",
       "    Question 29-Response 11 Question 29-Response 12 Question 29-Response 13  \\\n",
       "0                  Answer 4                     NaN                     NaN   \n",
       "1                       NaN                Answer 2                     NaN   \n",
       "2                  Answer 4                Answer 6                     NaN   \n",
       "3                       NaN                Answer 6                     NaN   \n",
       "4                  Answer 4                Answer 7                Answer 7   \n",
       "..                      ...                     ...                     ...   \n",
       "193                Answer 6                Answer 6                Answer 8   \n",
       "194                     NaN                     NaN                Answer 2   \n",
       "195                     NaN                     NaN                     NaN   \n",
       "196                Answer 5                     NaN                Answer 8   \n",
       "197                     NaN                     NaN                     NaN   \n",
       "\n",
       "    Question 29-Response 14 Question 30-Response 1 Question 30-Response 2  \\\n",
       "0                  Answer 5                    NaN                    NaN   \n",
       "1                  Answer 5                    NaN                    NaN   \n",
       "2                  Answer 6                    NaN               Answer 1   \n",
       "3                  Answer 7               Answer 7               Answer 1   \n",
       "4                       NaN               Answer 7                    NaN   \n",
       "..                      ...                    ...                    ...   \n",
       "193                     NaN                    NaN                    NaN   \n",
       "194                     NaN                    NaN                    NaN   \n",
       "195                     NaN                    NaN               Answer 3   \n",
       "196                Answer 3               Answer 3                    NaN   \n",
       "197                     NaN               Answer 3                    NaN   \n",
       "\n",
       "    Question 30-Response 3  \n",
       "0                      NaN  \n",
       "1                 Answer 1  \n",
       "2                      NaN  \n",
       "3                 Answer 6  \n",
       "4                 Answer 8  \n",
       "..                     ...  \n",
       "193               Answer 8  \n",
       "194               Answer 6  \n",
       "195                    NaN  \n",
       "196                    NaN  \n",
       "197                    NaN  \n",
       "\n",
       "[198 rows x 100 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_modified = data.copy()\n",
    "data_modified"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Start Date',\n",
       " 'End Date',\n",
       " 'Email Address',\n",
       " 'First Name',\n",
       " 'Last Name',\n",
       " 'Custom Data 1']"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_drop = ['Start Date', 'End Date', 'Email Address',\n",
    "       'First Name', 'Last Name', 'Custom Data 1']\n",
    "columns_drop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>Respondent ID</th>\n",
       "      <th>Identify which division you work in.-Response</th>\n",
       "      <th>Identify which division you work in.-Other (please specify)</th>\n",
       "      <th>Which of the following best describes your position level?-Response</th>\n",
       "      <th>Which generation are you apart of?-Response</th>\n",
       "      <th>Please select the gender in which you identify.-Response</th>\n",
       "      <th>Which duration range best aligns with your tenure at your company?-Response</th>\n",
       "      <th>Which of the following best describes your employment type?-Response</th>\n",
       "      <th>Question 1-Response</th>\n",
       "      <th>Question 2-Response</th>\n",
       "      <th>...</th>\n",
       "      <th>Question 29-Response 8</th>\n",
       "      <th>Question 29-Response 9</th>\n",
       "      <th>Question 29-Response 10</th>\n",
       "      <th>Question 29-Response 11</th>\n",
       "      <th>Question 29-Response 12</th>\n",
       "      <th>Question 29-Response 13</th>\n",
       "      <th>Question 29-Response 14</th>\n",
       "      <th>Question 30-Response 1</th>\n",
       "      <th>Question 30-Response 2</th>\n",
       "      <th>Question 30-Response 3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5379192392</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Male</td>\n",
       "      <td>0-2 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2658722536</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Answer 2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4044163394</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Male</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5535865599</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Non-Binary</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>...</td>\n",
       "      <td>Answer 2</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>Answer 6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3356802928</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Female</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>7940065082</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>Baby Boomer (born between 1946-1964)</td>\n",
       "      <td>Male</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>5157705612</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>9920755555</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>6638341389</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Answer 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>8114622230</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Prefer not to answer</td>\n",
       "      <td>Male</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>...</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>198 rows × 94 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Respondent ID Identify which division you work in.-Response  \\\n",
       "0       5379192392                                Infrastructure   \n",
       "1       2658722536                                       Finance   \n",
       "2       4044163394                                Infrastructure   \n",
       "3       5535865599                                Infrastructure   \n",
       "4       3356802928                               Port Operations   \n",
       "..             ...                                           ...   \n",
       "193     7940065082                                Infrastructure   \n",
       "194     5157705612                                       Finance   \n",
       "195     9920755555                               Port Operations   \n",
       "196     6638341389                                Infrastructure   \n",
       "197     8114622230                        Information Technology   \n",
       "\n",
       "    Identify which division you work in.-Other (please specify)  \\\n",
       "0                                                  NaN            \n",
       "1                                                  NaN            \n",
       "2                                                  NaN            \n",
       "3                                                  NaN            \n",
       "4                                                  NaN            \n",
       "..                                                 ...            \n",
       "193                                                NaN            \n",
       "194                                                NaN            \n",
       "195                                                NaN            \n",
       "196                                                NaN            \n",
       "197                                                NaN            \n",
       "\n",
       "    Which of the following best describes your position level?-Response  \\\n",
       "0                                                Staff                    \n",
       "1                                                Staff                    \n",
       "2                                      Department Lead                    \n",
       "3                                              Manager                    \n",
       "4                                              Manager                    \n",
       "..                                                 ...                    \n",
       "193                                    Department Lead                    \n",
       "194                                              Staff                    \n",
       "195                                              Staff                    \n",
       "196                                            Manager                    \n",
       "197                                              Staff                    \n",
       "\n",
       "    Which generation are you apart of?-Response  \\\n",
       "0         Generation X (born between 1965-1980)   \n",
       "1                                           NaN   \n",
       "2         Generation X (born between 1965-1980)   \n",
       "3           Millennial (born between 1981-2000)   \n",
       "4         Generation X (born between 1965-1980)   \n",
       "..                                          ...   \n",
       "193        Baby Boomer (born between 1946-1964)   \n",
       "194         Millennial (born between 1981-2000)   \n",
       "195         Millennial (born between 1981-2000)   \n",
       "196         Millennial (born between 1981-2000)   \n",
       "197                        Prefer not to answer   \n",
       "\n",
       "    Please select the gender in which you identify.-Response  \\\n",
       "0                                                 Male         \n",
       "1                                                  NaN         \n",
       "2                                                 Male         \n",
       "3                                           Non-Binary         \n",
       "4                                               Female         \n",
       "..                                                 ...         \n",
       "193                                               Male         \n",
       "194                                             Female         \n",
       "195                                             Female         \n",
       "196                                             Female         \n",
       "197                                               Male         \n",
       "\n",
       "    Which duration range best aligns with your tenure at your company?-Response  \\\n",
       "0                                            0-2 years                            \n",
       "1                                            10+ years                            \n",
       "2                                            3-5 years                            \n",
       "3                                           5-10 years                            \n",
       "4                                            10+ years                            \n",
       "..                                                 ...                            \n",
       "193                                          10+ years                            \n",
       "194                                         5-10 years                            \n",
       "195                                          3-5 years                            \n",
       "196                                          3-5 years                            \n",
       "197                                         5-10 years                            \n",
       "\n",
       "    Which of the following best describes your employment type?-Response  \\\n",
       "0                                   Full time Employee                     \n",
       "1                                   Full time Employee                     \n",
       "2                                   Full time Employee                     \n",
       "3                                   Full time Employee                     \n",
       "4                                   Full time Employee                     \n",
       "..                                                 ...                     \n",
       "193                                 Full time Employee                     \n",
       "194                                 Full time Employee                     \n",
       "195                                 Full time Employee                     \n",
       "196                                 Full time Employee                     \n",
       "197                                 Full time Employee                     \n",
       "\n",
       "    Question 1-Response Question 2-Response  ... Question 29-Response 8  \\\n",
       "0                   NaN            Answer 6  ...                    NaN   \n",
       "1              Answer 4            Answer 2  ...                    NaN   \n",
       "2              Answer 5            Answer 7  ...                    NaN   \n",
       "3              Answer 1            Answer 1  ...               Answer 2   \n",
       "4                   NaN            Answer 3  ...                    NaN   \n",
       "..                  ...                 ...  ...                    ...   \n",
       "193            Answer 3                 NaN  ...               Answer 1   \n",
       "194                 NaN                 NaN  ...               Answer 5   \n",
       "195                 NaN            Answer 7  ...                    NaN   \n",
       "196                 NaN                 NaN  ...               Answer 2   \n",
       "197                 NaN            Answer 1  ...               Answer 3   \n",
       "\n",
       "    Question 29-Response 9 Question 29-Response 10 Question 29-Response 11  \\\n",
       "0                 Answer 8                Answer 8                Answer 4   \n",
       "1                 Answer 5                     NaN                     NaN   \n",
       "2                      NaN                Answer 4                Answer 4   \n",
       "3                 Answer 5                Answer 7                     NaN   \n",
       "4                 Answer 5                Answer 4                Answer 4   \n",
       "..                     ...                     ...                     ...   \n",
       "193               Answer 1                Answer 3                Answer 6   \n",
       "194                    NaN                Answer 5                     NaN   \n",
       "195               Answer 7                     NaN                     NaN   \n",
       "196                    NaN                Answer 3                Answer 5   \n",
       "197                    NaN                Answer 7                     NaN   \n",
       "\n",
       "    Question 29-Response 12 Question 29-Response 13 Question 29-Response 14  \\\n",
       "0                       NaN                     NaN                Answer 5   \n",
       "1                  Answer 2                     NaN                Answer 5   \n",
       "2                  Answer 6                     NaN                Answer 6   \n",
       "3                  Answer 6                     NaN                Answer 7   \n",
       "4                  Answer 7                Answer 7                     NaN   \n",
       "..                      ...                     ...                     ...   \n",
       "193                Answer 6                Answer 8                     NaN   \n",
       "194                     NaN                Answer 2                     NaN   \n",
       "195                     NaN                     NaN                     NaN   \n",
       "196                     NaN                Answer 8                Answer 3   \n",
       "197                     NaN                     NaN                     NaN   \n",
       "\n",
       "    Question 30-Response 1 Question 30-Response 2 Question 30-Response 3  \n",
       "0                      NaN                    NaN                    NaN  \n",
       "1                      NaN                    NaN               Answer 1  \n",
       "2                      NaN               Answer 1                    NaN  \n",
       "3                 Answer 7               Answer 1               Answer 6  \n",
       "4                 Answer 7                    NaN               Answer 8  \n",
       "..                     ...                    ...                    ...  \n",
       "193                    NaN                    NaN               Answer 8  \n",
       "194                    NaN                    NaN               Answer 6  \n",
       "195                    NaN               Answer 3                    NaN  \n",
       "196               Answer 3                    NaN                    NaN  \n",
       "197               Answer 3                    NaN                    NaN  \n",
       "\n",
       "[198 rows x 94 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_modified = data_modified.drop(columns=columns_drop)\n",
    "data_modified"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "id_var = list(data_modified.columns)[0:8]\n",
    "value_var = list(data_modified.columns)[8:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>Respondent ID</th>\n",
       "      <th>Identify which division you work in.-Response</th>\n",
       "      <th>Identify which division you work in.-Other (please specify)</th>\n",
       "      <th>Which of the following best describes your position level?-Response</th>\n",
       "      <th>Which generation are you apart of?-Response</th>\n",
       "      <th>Please select the gender in which you identify.-Response</th>\n",
       "      <th>Which duration range best aligns with your tenure at your company?-Response</th>\n",
       "      <th>Which of the following best describes your employment type?-Response</th>\n",
       "      <th>Questions+SubQuestions</th>\n",
       "      <th>Answer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5379192392</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Male</td>\n",
       "      <td>0-2 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2658722536</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4044163394</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Male</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5535865599</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Non-Binary</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3356802928</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Female</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17023</th>\n",
       "      <td>7940065082</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>Baby Boomer (born between 1946-1964)</td>\n",
       "      <td>Male</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>Answer 8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17024</th>\n",
       "      <td>5157705612</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>Answer 6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17025</th>\n",
       "      <td>9920755555</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17026</th>\n",
       "      <td>6638341389</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17027</th>\n",
       "      <td>8114622230</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Prefer not to answer</td>\n",
       "      <td>Male</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17028 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Respondent ID Identify which division you work in.-Response  \\\n",
       "0         5379192392                                Infrastructure   \n",
       "1         2658722536                                       Finance   \n",
       "2         4044163394                                Infrastructure   \n",
       "3         5535865599                                Infrastructure   \n",
       "4         3356802928                               Port Operations   \n",
       "...              ...                                           ...   \n",
       "17023     7940065082                                Infrastructure   \n",
       "17024     5157705612                                       Finance   \n",
       "17025     9920755555                               Port Operations   \n",
       "17026     6638341389                                Infrastructure   \n",
       "17027     8114622230                        Information Technology   \n",
       "\n",
       "      Identify which division you work in.-Other (please specify)  \\\n",
       "0                                                    NaN            \n",
       "1                                                    NaN            \n",
       "2                                                    NaN            \n",
       "3                                                    NaN            \n",
       "4                                                    NaN            \n",
       "...                                                  ...            \n",
       "17023                                                NaN            \n",
       "17024                                                NaN            \n",
       "17025                                                NaN            \n",
       "17026                                                NaN            \n",
       "17027                                                NaN            \n",
       "\n",
       "      Which of the following best describes your position level?-Response  \\\n",
       "0                                                  Staff                    \n",
       "1                                                  Staff                    \n",
       "2                                        Department Lead                    \n",
       "3                                                Manager                    \n",
       "4                                                Manager                    \n",
       "...                                                  ...                    \n",
       "17023                                    Department Lead                    \n",
       "17024                                              Staff                    \n",
       "17025                                              Staff                    \n",
       "17026                                            Manager                    \n",
       "17027                                              Staff                    \n",
       "\n",
       "      Which generation are you apart of?-Response  \\\n",
       "0           Generation X (born between 1965-1980)   \n",
       "1                                             NaN   \n",
       "2           Generation X (born between 1965-1980)   \n",
       "3             Millennial (born between 1981-2000)   \n",
       "4           Generation X (born between 1965-1980)   \n",
       "...                                           ...   \n",
       "17023        Baby Boomer (born between 1946-1964)   \n",
       "17024         Millennial (born between 1981-2000)   \n",
       "17025         Millennial (born between 1981-2000)   \n",
       "17026         Millennial (born between 1981-2000)   \n",
       "17027                        Prefer not to answer   \n",
       "\n",
       "      Please select the gender in which you identify.-Response  \\\n",
       "0                                                   Male         \n",
       "1                                                    NaN         \n",
       "2                                                   Male         \n",
       "3                                             Non-Binary         \n",
       "4                                                 Female         \n",
       "...                                                  ...         \n",
       "17023                                               Male         \n",
       "17024                                             Female         \n",
       "17025                                             Female         \n",
       "17026                                             Female         \n",
       "17027                                               Male         \n",
       "\n",
       "      Which duration range best aligns with your tenure at your company?-Response  \\\n",
       "0                                              0-2 years                            \n",
       "1                                              10+ years                            \n",
       "2                                              3-5 years                            \n",
       "3                                             5-10 years                            \n",
       "4                                              10+ years                            \n",
       "...                                                  ...                            \n",
       "17023                                          10+ years                            \n",
       "17024                                         5-10 years                            \n",
       "17025                                          3-5 years                            \n",
       "17026                                          3-5 years                            \n",
       "17027                                         5-10 years                            \n",
       "\n",
       "      Which of the following best describes your employment type?-Response  \\\n",
       "0                                     Full time Employee                     \n",
       "1                                     Full time Employee                     \n",
       "2                                     Full time Employee                     \n",
       "3                                     Full time Employee                     \n",
       "4                                     Full time Employee                     \n",
       "...                                                  ...                     \n",
       "17023                                 Full time Employee                     \n",
       "17024                                 Full time Employee                     \n",
       "17025                                 Full time Employee                     \n",
       "17026                                 Full time Employee                     \n",
       "17027                                 Full time Employee                     \n",
       "\n",
       "       Questions+SubQuestions    Answer  \n",
       "0         Question 1-Response       NaN  \n",
       "1         Question 1-Response  Answer 4  \n",
       "2         Question 1-Response  Answer 5  \n",
       "3         Question 1-Response  Answer 1  \n",
       "4         Question 1-Response       NaN  \n",
       "...                       ...       ...  \n",
       "17023  Question 30-Response 3  Answer 8  \n",
       "17024  Question 30-Response 3  Answer 6  \n",
       "17025  Question 30-Response 3       NaN  \n",
       "17026  Question 30-Response 3       NaN  \n",
       "17027  Question 30-Response 3       NaN  \n",
       "\n",
       "[17028 rows x 10 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_melt = data_modified.melt(id_vars=id_var, value_vars=value_var, var_name=\"Questions+SubQuestions\", value_name=\"Answer\")\n",
    "data_melt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>RawQuestions</th>\n",
       "      <th>RawSubQuestions</th>\n",
       "      <th>Questions</th>\n",
       "      <th>SubQuestions</th>\n",
       "      <th>Questions+SubQuestions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Respondent ID</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Respondent ID</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Respondent ID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Start Date</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Start Date</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Start Date</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>End Date</td>\n",
       "      <td>NaN</td>\n",
       "      <td>End Date</td>\n",
       "      <td>NaN</td>\n",
       "      <td>End Date</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Email Address</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Email Address</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Email Address</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>First Name</td>\n",
       "      <td>NaN</td>\n",
       "      <td>First Name</td>\n",
       "      <td>NaN</td>\n",
       "      <td>First Name</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Response 13</td>\n",
       "      <td>Question 29</td>\n",
       "      <td>Response 13</td>\n",
       "      <td>Question 29-Response 13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Response 14</td>\n",
       "      <td>Question 29</td>\n",
       "      <td>Response 14</td>\n",
       "      <td>Question 29-Response 14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Question 30</td>\n",
       "      <td>Response 1</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>Response 1</td>\n",
       "      <td>Question 30-Response 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Response 2</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>Response 2</td>\n",
       "      <td>Question 30-Response 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Response 3</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>Response 3</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     RawQuestions RawSubQuestions      Questions SubQuestions  \\\n",
       "0   Respondent ID             NaN  Respondent ID          NaN   \n",
       "1      Start Date             NaN     Start Date          NaN   \n",
       "2        End Date             NaN       End Date          NaN   \n",
       "3   Email Address             NaN  Email Address          NaN   \n",
       "4      First Name             NaN     First Name          NaN   \n",
       "..            ...             ...            ...          ...   \n",
       "95            NaN     Response 13    Question 29  Response 13   \n",
       "96            NaN     Response 14    Question 29  Response 14   \n",
       "97    Question 30      Response 1    Question 30   Response 1   \n",
       "98            NaN      Response 2    Question 30   Response 2   \n",
       "99            NaN      Response 3    Question 30   Response 3   \n",
       "\n",
       "     Questions+SubQuestions  \n",
       "0             Respondent ID  \n",
       "1                Start Date  \n",
       "2                  End Date  \n",
       "3             Email Address  \n",
       "4                First Name  \n",
       "..                      ...  \n",
       "95  Question 29-Response 13  \n",
       "96  Question 29-Response 14  \n",
       "97   Question 30-Response 1  \n",
       "98   Question 30-Response 2  \n",
       "99   Question 30-Response 3  \n",
       "\n",
       "[100 rows x 5 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "questions_import = pd.read_excel(pwd + \"\\\\Survey Monkey Data Edited.xlsx\", sheet_name=\"Questions\")\n",
    "questions_import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "questions = questions_import.copy()\n",
    "questions = questions.drop(columns=[\"RawQuestions\",\"RawSubQuestions\",\"SubQuestions\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>Questions</th>\n",
       "      <th>Questions+SubQuestions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Respondent ID</td>\n",
       "      <td>Respondent ID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Start Date</td>\n",
       "      <td>Start Date</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>End Date</td>\n",
       "      <td>End Date</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Email Address</td>\n",
       "      <td>Email Address</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>First Name</td>\n",
       "      <td>First Name</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>Question 29</td>\n",
       "      <td>Question 29-Response 13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>Question 29</td>\n",
       "      <td>Question 29-Response 14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Question 30</td>\n",
       "      <td>Question 30-Response 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Question 30</td>\n",
       "      <td>Question 30-Response 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Question 30</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Questions   Questions+SubQuestions\n",
       "0   Respondent ID            Respondent ID\n",
       "1      Start Date               Start Date\n",
       "2        End Date                 End Date\n",
       "3   Email Address            Email Address\n",
       "4      First Name               First Name\n",
       "..            ...                      ...\n",
       "95    Question 29  Question 29-Response 13\n",
       "96    Question 29  Question 29-Response 14\n",
       "97    Question 30   Question 30-Response 1\n",
       "98    Question 30   Question 30-Response 2\n",
       "99    Question 30   Question 30-Response 3\n",
       "\n",
       "[100 rows x 2 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "questions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>Respondent ID</th>\n",
       "      <th>Identify which division you work in.-Response</th>\n",
       "      <th>Identify which division you work in.-Other (please specify)</th>\n",
       "      <th>Which of the following best describes your position level?-Response</th>\n",
       "      <th>Which generation are you apart of?-Response</th>\n",
       "      <th>Please select the gender in which you identify.-Response</th>\n",
       "      <th>Which duration range best aligns with your tenure at your company?-Response</th>\n",
       "      <th>Which of the following best describes your employment type?-Response</th>\n",
       "      <th>Questions+SubQuestions</th>\n",
       "      <th>Answer</th>\n",
       "      <th>Questions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5379192392</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Male</td>\n",
       "      <td>0-2 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2658722536</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Question 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4044163394</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Male</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>Question 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5535865599</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Non-Binary</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>Question 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3356802928</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Female</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17023</th>\n",
       "      <td>7940065082</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>Baby Boomer (born between 1946-1964)</td>\n",
       "      <td>Male</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>Question 30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17024</th>\n",
       "      <td>5157705612</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>Question 30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17025</th>\n",
       "      <td>9920755555</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17026</th>\n",
       "      <td>6638341389</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17027</th>\n",
       "      <td>8114622230</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Prefer not to answer</td>\n",
       "      <td>Male</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17028 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Respondent ID Identify which division you work in.-Response  \\\n",
       "0         5379192392                                Infrastructure   \n",
       "1         2658722536                                       Finance   \n",
       "2         4044163394                                Infrastructure   \n",
       "3         5535865599                                Infrastructure   \n",
       "4         3356802928                               Port Operations   \n",
       "...              ...                                           ...   \n",
       "17023     7940065082                                Infrastructure   \n",
       "17024     5157705612                                       Finance   \n",
       "17025     9920755555                               Port Operations   \n",
       "17026     6638341389                                Infrastructure   \n",
       "17027     8114622230                        Information Technology   \n",
       "\n",
       "      Identify which division you work in.-Other (please specify)  \\\n",
       "0                                                    NaN            \n",
       "1                                                    NaN            \n",
       "2                                                    NaN            \n",
       "3                                                    NaN            \n",
       "4                                                    NaN            \n",
       "...                                                  ...            \n",
       "17023                                                NaN            \n",
       "17024                                                NaN            \n",
       "17025                                                NaN            \n",
       "17026                                                NaN            \n",
       "17027                                                NaN            \n",
       "\n",
       "      Which of the following best describes your position level?-Response  \\\n",
       "0                                                  Staff                    \n",
       "1                                                  Staff                    \n",
       "2                                        Department Lead                    \n",
       "3                                                Manager                    \n",
       "4                                                Manager                    \n",
       "...                                                  ...                    \n",
       "17023                                    Department Lead                    \n",
       "17024                                              Staff                    \n",
       "17025                                              Staff                    \n",
       "17026                                            Manager                    \n",
       "17027                                              Staff                    \n",
       "\n",
       "      Which generation are you apart of?-Response  \\\n",
       "0           Generation X (born between 1965-1980)   \n",
       "1                                             NaN   \n",
       "2           Generation X (born between 1965-1980)   \n",
       "3             Millennial (born between 1981-2000)   \n",
       "4           Generation X (born between 1965-1980)   \n",
       "...                                           ...   \n",
       "17023        Baby Boomer (born between 1946-1964)   \n",
       "17024         Millennial (born between 1981-2000)   \n",
       "17025         Millennial (born between 1981-2000)   \n",
       "17026         Millennial (born between 1981-2000)   \n",
       "17027                        Prefer not to answer   \n",
       "\n",
       "      Please select the gender in which you identify.-Response  \\\n",
       "0                                                   Male         \n",
       "1                                                    NaN         \n",
       "2                                                   Male         \n",
       "3                                             Non-Binary         \n",
       "4                                                 Female         \n",
       "...                                                  ...         \n",
       "17023                                               Male         \n",
       "17024                                             Female         \n",
       "17025                                             Female         \n",
       "17026                                             Female         \n",
       "17027                                               Male         \n",
       "\n",
       "      Which duration range best aligns with your tenure at your company?-Response  \\\n",
       "0                                              0-2 years                            \n",
       "1                                              10+ years                            \n",
       "2                                              3-5 years                            \n",
       "3                                             5-10 years                            \n",
       "4                                              10+ years                            \n",
       "...                                                  ...                            \n",
       "17023                                          10+ years                            \n",
       "17024                                         5-10 years                            \n",
       "17025                                          3-5 years                            \n",
       "17026                                          3-5 years                            \n",
       "17027                                         5-10 years                            \n",
       "\n",
       "      Which of the following best describes your employment type?-Response  \\\n",
       "0                                     Full time Employee                     \n",
       "1                                     Full time Employee                     \n",
       "2                                     Full time Employee                     \n",
       "3                                     Full time Employee                     \n",
       "4                                     Full time Employee                     \n",
       "...                                                  ...                     \n",
       "17023                                 Full time Employee                     \n",
       "17024                                 Full time Employee                     \n",
       "17025                                 Full time Employee                     \n",
       "17026                                 Full time Employee                     \n",
       "17027                                 Full time Employee                     \n",
       "\n",
       "       Questions+SubQuestions    Answer    Questions  \n",
       "0         Question 1-Response       NaN   Question 1  \n",
       "1         Question 1-Response  Answer 4   Question 1  \n",
       "2         Question 1-Response  Answer 5   Question 1  \n",
       "3         Question 1-Response  Answer 1   Question 1  \n",
       "4         Question 1-Response       NaN   Question 1  \n",
       "...                       ...       ...          ...  \n",
       "17023  Question 30-Response 3  Answer 8  Question 30  \n",
       "17024  Question 30-Response 3  Answer 6  Question 30  \n",
       "17025  Question 30-Response 3       NaN  Question 30  \n",
       "17026  Question 30-Response 3       NaN  Question 30  \n",
       "17027  Question 30-Response 3       NaN  Question 30  \n",
       "\n",
       "[17028 rows x 11 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_merged = pd.merge(left=data_melt, right=questions, how=\"left\", left_on=\"Questions+SubQuestions\", right_on=\"Questions+SubQuestions\")\n",
    "data_merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Data 17028\n",
      "Merged Data 17028\n"
     ]
    }
   ],
   "source": [
    "print(\"Original Data\", len(data_melt))\n",
    "print(\"Merged Data\", len(data_merged))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>Questions</th>\n",
       "      <th>Respondents</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Question 10</td>\n",
       "      <td>198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Question 11</td>\n",
       "      <td>164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Question 12</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Question 13</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Question 14</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Question 15</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Question 16</td>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Question 17</td>\n",
       "      <td>135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Question 18</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Question 19</td>\n",
       "      <td>157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Question 2</td>\n",
       "      <td>118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Question 20</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Question 21</td>\n",
       "      <td>127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Question 22</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Question 23</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Question 24</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Question 25</td>\n",
       "      <td>198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Question 26</td>\n",
       "      <td>193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Question 27</td>\n",
       "      <td>153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Question 28</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Question 29</td>\n",
       "      <td>198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Question 3</td>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Question 4</td>\n",
       "      <td>161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Question 5</td>\n",
       "      <td>194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Question 6</td>\n",
       "      <td>196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Question 7</td>\n",
       "      <td>162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Question 8</td>\n",
       "      <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Question 9</td>\n",
       "      <td>188</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Questions  Respondents\n",
       "0    Question 1          119\n",
       "1   Question 10          198\n",
       "2   Question 11          164\n",
       "3   Question 12          114\n",
       "4   Question 13          108\n",
       "5   Question 14          105\n",
       "6   Question 15          114\n",
       "7   Question 16          117\n",
       "8   Question 17          135\n",
       "9   Question 18          109\n",
       "10  Question 19          157\n",
       "11   Question 2          118\n",
       "12  Question 20          105\n",
       "13  Question 21          127\n",
       "14  Question 22          160\n",
       "15  Question 23          120\n",
       "16  Question 24          195\n",
       "17  Question 25          198\n",
       "18  Question 26          193\n",
       "19  Question 27          153\n",
       "20  Question 28          119\n",
       "21  Question 29          198\n",
       "22   Question 3          117\n",
       "23  Question 30          182\n",
       "24   Question 4          161\n",
       "25   Question 5          194\n",
       "26   Question 6          196\n",
       "27   Question 7          162\n",
       "28   Question 8          190\n",
       "29   Question 9          188"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "respondents = data_merged[data_merged[\"Answer\"].notna()]\n",
    "respondents = respondents.groupby(\"Questions\")[\"Respondent ID\"].nunique().reset_index()\n",
    "respondents.rename(columns={\"Respondent ID\":\"Respondents\"}, inplace=True)\n",
    "respondents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Data 17028\n",
      "Merged Data 17028\n"
     ]
    },
    {
     "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>Respondent ID</th>\n",
       "      <th>Identify which division you work in.-Response</th>\n",
       "      <th>Identify which division you work in.-Other (please specify)</th>\n",
       "      <th>Which of the following best describes your position level?-Response</th>\n",
       "      <th>Which generation are you apart of?-Response</th>\n",
       "      <th>Please select the gender in which you identify.-Response</th>\n",
       "      <th>Which duration range best aligns with your tenure at your company?-Response</th>\n",
       "      <th>Which of the following best describes your employment type?-Response</th>\n",
       "      <th>Questions+SubQuestions</th>\n",
       "      <th>Answer</th>\n",
       "      <th>Questions</th>\n",
       "      <th>Respondents</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5379192392</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Male</td>\n",
       "      <td>0-2 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2658722536</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4044163394</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Male</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5535865599</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Non-Binary</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3356802928</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Female</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17023</th>\n",
       "      <td>7940065082</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>Baby Boomer (born between 1946-1964)</td>\n",
       "      <td>Male</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17024</th>\n",
       "      <td>5157705612</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17025</th>\n",
       "      <td>9920755555</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17026</th>\n",
       "      <td>6638341389</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17027</th>\n",
       "      <td>8114622230</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Prefer not to answer</td>\n",
       "      <td>Male</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17028 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Respondent ID Identify which division you work in.-Response  \\\n",
       "0         5379192392                                Infrastructure   \n",
       "1         2658722536                                       Finance   \n",
       "2         4044163394                                Infrastructure   \n",
       "3         5535865599                                Infrastructure   \n",
       "4         3356802928                               Port Operations   \n",
       "...              ...                                           ...   \n",
       "17023     7940065082                                Infrastructure   \n",
       "17024     5157705612                                       Finance   \n",
       "17025     9920755555                               Port Operations   \n",
       "17026     6638341389                                Infrastructure   \n",
       "17027     8114622230                        Information Technology   \n",
       "\n",
       "      Identify which division you work in.-Other (please specify)  \\\n",
       "0                                                    NaN            \n",
       "1                                                    NaN            \n",
       "2                                                    NaN            \n",
       "3                                                    NaN            \n",
       "4                                                    NaN            \n",
       "...                                                  ...            \n",
       "17023                                                NaN            \n",
       "17024                                                NaN            \n",
       "17025                                                NaN            \n",
       "17026                                                NaN            \n",
       "17027                                                NaN            \n",
       "\n",
       "      Which of the following best describes your position level?-Response  \\\n",
       "0                                                  Staff                    \n",
       "1                                                  Staff                    \n",
       "2                                        Department Lead                    \n",
       "3                                                Manager                    \n",
       "4                                                Manager                    \n",
       "...                                                  ...                    \n",
       "17023                                    Department Lead                    \n",
       "17024                                              Staff                    \n",
       "17025                                              Staff                    \n",
       "17026                                            Manager                    \n",
       "17027                                              Staff                    \n",
       "\n",
       "      Which generation are you apart of?-Response  \\\n",
       "0           Generation X (born between 1965-1980)   \n",
       "1                                             NaN   \n",
       "2           Generation X (born between 1965-1980)   \n",
       "3             Millennial (born between 1981-2000)   \n",
       "4           Generation X (born between 1965-1980)   \n",
       "...                                           ...   \n",
       "17023        Baby Boomer (born between 1946-1964)   \n",
       "17024         Millennial (born between 1981-2000)   \n",
       "17025         Millennial (born between 1981-2000)   \n",
       "17026         Millennial (born between 1981-2000)   \n",
       "17027                        Prefer not to answer   \n",
       "\n",
       "      Please select the gender in which you identify.-Response  \\\n",
       "0                                                   Male         \n",
       "1                                                    NaN         \n",
       "2                                                   Male         \n",
       "3                                             Non-Binary         \n",
       "4                                                 Female         \n",
       "...                                                  ...         \n",
       "17023                                               Male         \n",
       "17024                                             Female         \n",
       "17025                                             Female         \n",
       "17026                                             Female         \n",
       "17027                                               Male         \n",
       "\n",
       "      Which duration range best aligns with your tenure at your company?-Response  \\\n",
       "0                                              0-2 years                            \n",
       "1                                              10+ years                            \n",
       "2                                              3-5 years                            \n",
       "3                                             5-10 years                            \n",
       "4                                              10+ years                            \n",
       "...                                                  ...                            \n",
       "17023                                          10+ years                            \n",
       "17024                                         5-10 years                            \n",
       "17025                                          3-5 years                            \n",
       "17026                                          3-5 years                            \n",
       "17027                                         5-10 years                            \n",
       "\n",
       "      Which of the following best describes your employment type?-Response  \\\n",
       "0                                     Full time Employee                     \n",
       "1                                     Full time Employee                     \n",
       "2                                     Full time Employee                     \n",
       "3                                     Full time Employee                     \n",
       "4                                     Full time Employee                     \n",
       "...                                                  ...                     \n",
       "17023                                 Full time Employee                     \n",
       "17024                                 Full time Employee                     \n",
       "17025                                 Full time Employee                     \n",
       "17026                                 Full time Employee                     \n",
       "17027                                 Full time Employee                     \n",
       "\n",
       "       Questions+SubQuestions    Answer    Questions  Respondents  \n",
       "0         Question 1-Response       NaN   Question 1          119  \n",
       "1         Question 1-Response  Answer 4   Question 1          119  \n",
       "2         Question 1-Response  Answer 5   Question 1          119  \n",
       "3         Question 1-Response  Answer 1   Question 1          119  \n",
       "4         Question 1-Response       NaN   Question 1          119  \n",
       "...                       ...       ...          ...          ...  \n",
       "17023  Question 30-Response 3  Answer 8  Question 30          182  \n",
       "17024  Question 30-Response 3  Answer 6  Question 30          182  \n",
       "17025  Question 30-Response 3       NaN  Question 30          182  \n",
       "17026  Question 30-Response 3       NaN  Question 30          182  \n",
       "17027  Question 30-Response 3       NaN  Question 30          182  \n",
       "\n",
       "[17028 rows x 12 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_merged_two = pd.merge(left=data_merged, right=respondents, how=\"left\", left_on=\"Questions\", right_on=\"Questions\")\n",
    "print(\"Original Data\", len(data_merged))\n",
    "print(\"Merged Data\", len(data_merged_two))\n",
    "data_merged_two"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "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>Questions+SubQuestions</th>\n",
       "      <th>Answer</th>\n",
       "      <th>Same Answer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 2</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 3</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>683</th>\n",
       "      <td>Question 9-Response 4</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>684</th>\n",
       "      <td>Question 9-Response 4</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>685</th>\n",
       "      <td>Question 9-Response 4</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>686</th>\n",
       "      <td>Question 9-Response 4</td>\n",
       "      <td>Answer 7</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>687</th>\n",
       "      <td>Question 9-Response 4</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>688 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Questions+SubQuestions    Answer  Same Answer\n",
       "0      Question 1-Response  Answer 1           14\n",
       "1      Question 1-Response  Answer 2           10\n",
       "2      Question 1-Response  Answer 3           13\n",
       "3      Question 1-Response  Answer 4           17\n",
       "4      Question 1-Response  Answer 5           22\n",
       "..                     ...       ...          ...\n",
       "683  Question 9-Response 4  Answer 4           16\n",
       "684  Question 9-Response 4  Answer 5           13\n",
       "685  Question 9-Response 4  Answer 6           14\n",
       "686  Question 9-Response 4  Answer 7           12\n",
       "687  Question 9-Response 4  Answer 8           19\n",
       "\n",
       "[688 rows x 3 columns]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "same_answer = data_merged\n",
    "same_answer = same_answer.groupby([\"Questions+SubQuestions\",\"Answer\"])[\"Respondent ID\"].nunique().reset_index()\n",
    "same_answer.rename(columns={\"Respondent ID\":\"Same Answer\"}, inplace=True)\n",
    "same_answer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Data 17028\n",
      "Merged Data 17028\n"
     ]
    },
    {
     "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>Respondent ID</th>\n",
       "      <th>Identify which division you work in.-Response</th>\n",
       "      <th>Identify which division you work in.-Other (please specify)</th>\n",
       "      <th>Which of the following best describes your position level?-Response</th>\n",
       "      <th>Which generation are you apart of?-Response</th>\n",
       "      <th>Please select the gender in which you identify.-Response</th>\n",
       "      <th>Which duration range best aligns with your tenure at your company?-Response</th>\n",
       "      <th>Which of the following best describes your employment type?-Response</th>\n",
       "      <th>Questions+SubQuestions</th>\n",
       "      <th>Answer</th>\n",
       "      <th>Questions</th>\n",
       "      <th>Respondents</th>\n",
       "      <th>Same Answer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5379192392</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Male</td>\n",
       "      <td>0-2 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2658722536</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4044163394</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Male</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5535865599</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Non-Binary</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3356802928</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Female</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17023</th>\n",
       "      <td>7940065082</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>Baby Boomer (born between 1946-1964)</td>\n",
       "      <td>Male</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17024</th>\n",
       "      <td>5157705612</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17025</th>\n",
       "      <td>9920755555</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17026</th>\n",
       "      <td>6638341389</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17027</th>\n",
       "      <td>8114622230</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Prefer not to answer</td>\n",
       "      <td>Male</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17028 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Respondent ID Identify which division you work in.-Response  \\\n",
       "0         5379192392                                Infrastructure   \n",
       "1         2658722536                                       Finance   \n",
       "2         4044163394                                Infrastructure   \n",
       "3         5535865599                                Infrastructure   \n",
       "4         3356802928                               Port Operations   \n",
       "...              ...                                           ...   \n",
       "17023     7940065082                                Infrastructure   \n",
       "17024     5157705612                                       Finance   \n",
       "17025     9920755555                               Port Operations   \n",
       "17026     6638341389                                Infrastructure   \n",
       "17027     8114622230                        Information Technology   \n",
       "\n",
       "      Identify which division you work in.-Other (please specify)  \\\n",
       "0                                                    NaN            \n",
       "1                                                    NaN            \n",
       "2                                                    NaN            \n",
       "3                                                    NaN            \n",
       "4                                                    NaN            \n",
       "...                                                  ...            \n",
       "17023                                                NaN            \n",
       "17024                                                NaN            \n",
       "17025                                                NaN            \n",
       "17026                                                NaN            \n",
       "17027                                                NaN            \n",
       "\n",
       "      Which of the following best describes your position level?-Response  \\\n",
       "0                                                  Staff                    \n",
       "1                                                  Staff                    \n",
       "2                                        Department Lead                    \n",
       "3                                                Manager                    \n",
       "4                                                Manager                    \n",
       "...                                                  ...                    \n",
       "17023                                    Department Lead                    \n",
       "17024                                              Staff                    \n",
       "17025                                              Staff                    \n",
       "17026                                            Manager                    \n",
       "17027                                              Staff                    \n",
       "\n",
       "      Which generation are you apart of?-Response  \\\n",
       "0           Generation X (born between 1965-1980)   \n",
       "1                                             NaN   \n",
       "2           Generation X (born between 1965-1980)   \n",
       "3             Millennial (born between 1981-2000)   \n",
       "4           Generation X (born between 1965-1980)   \n",
       "...                                           ...   \n",
       "17023        Baby Boomer (born between 1946-1964)   \n",
       "17024         Millennial (born between 1981-2000)   \n",
       "17025         Millennial (born between 1981-2000)   \n",
       "17026         Millennial (born between 1981-2000)   \n",
       "17027                        Prefer not to answer   \n",
       "\n",
       "      Please select the gender in which you identify.-Response  \\\n",
       "0                                                   Male         \n",
       "1                                                    NaN         \n",
       "2                                                   Male         \n",
       "3                                             Non-Binary         \n",
       "4                                                 Female         \n",
       "...                                                  ...         \n",
       "17023                                               Male         \n",
       "17024                                             Female         \n",
       "17025                                             Female         \n",
       "17026                                             Female         \n",
       "17027                                               Male         \n",
       "\n",
       "      Which duration range best aligns with your tenure at your company?-Response  \\\n",
       "0                                              0-2 years                            \n",
       "1                                              10+ years                            \n",
       "2                                              3-5 years                            \n",
       "3                                             5-10 years                            \n",
       "4                                              10+ years                            \n",
       "...                                                  ...                            \n",
       "17023                                          10+ years                            \n",
       "17024                                         5-10 years                            \n",
       "17025                                          3-5 years                            \n",
       "17026                                          3-5 years                            \n",
       "17027                                         5-10 years                            \n",
       "\n",
       "      Which of the following best describes your employment type?-Response  \\\n",
       "0                                     Full time Employee                     \n",
       "1                                     Full time Employee                     \n",
       "2                                     Full time Employee                     \n",
       "3                                     Full time Employee                     \n",
       "4                                     Full time Employee                     \n",
       "...                                                  ...                     \n",
       "17023                                 Full time Employee                     \n",
       "17024                                 Full time Employee                     \n",
       "17025                                 Full time Employee                     \n",
       "17026                                 Full time Employee                     \n",
       "17027                                 Full time Employee                     \n",
       "\n",
       "       Questions+SubQuestions    Answer    Questions  Respondents  Same Answer  \n",
       "0         Question 1-Response       NaN   Question 1          119          0.0  \n",
       "1         Question 1-Response  Answer 4   Question 1          119         17.0  \n",
       "2         Question 1-Response  Answer 5   Question 1          119         22.0  \n",
       "3         Question 1-Response  Answer 1   Question 1          119         14.0  \n",
       "4         Question 1-Response       NaN   Question 1          119          0.0  \n",
       "...                       ...       ...          ...          ...          ...  \n",
       "17023  Question 30-Response 3  Answer 8  Question 30          182         14.0  \n",
       "17024  Question 30-Response 3  Answer 6  Question 30          182         20.0  \n",
       "17025  Question 30-Response 3       NaN  Question 30          182          0.0  \n",
       "17026  Question 30-Response 3       NaN  Question 30          182          0.0  \n",
       "17027  Question 30-Response 3       NaN  Question 30          182          0.0  \n",
       "\n",
       "[17028 rows x 13 columns]"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_merged_three = pd.merge(left=data_merged_two, right=same_answer, how=\"left\", left_on=[\"Questions+SubQuestions\", \"Answer\"], right_on=[\"Questions+SubQuestions\", \"Answer\"])\n",
    "data_merged_three[\"Same Answer\"].fillna(0, inplace=True)\n",
    "print(\"Original Data\", len(data_merged_two))\n",
    "print(\"Merged Data\", len(data_merged_three))\n",
    "data_merged_three"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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>Respondent ID</th>\n",
       "      <th>Division Primary</th>\n",
       "      <th>Division Secondary</th>\n",
       "      <th>Position</th>\n",
       "      <th>Generation</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Tenure</th>\n",
       "      <th>Employment Type</th>\n",
       "      <th>Questions+SubQuestions</th>\n",
       "      <th>Answer</th>\n",
       "      <th>Questions</th>\n",
       "      <th>Respondents</th>\n",
       "      <th>Same Answer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5379192392</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Male</td>\n",
       "      <td>0-2 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2658722536</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 4</td>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4044163394</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Male</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 5</td>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5535865599</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Non-Binary</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>Answer 1</td>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3356802928</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Generation X (born between 1965-1980)</td>\n",
       "      <td>Female</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 1-Response</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 1</td>\n",
       "      <td>119</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17023</th>\n",
       "      <td>7940065082</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Department Lead</td>\n",
       "      <td>Baby Boomer (born between 1946-1964)</td>\n",
       "      <td>Male</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>Answer 8</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17024</th>\n",
       "      <td>5157705612</td>\n",
       "      <td>Finance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>Answer 6</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17025</th>\n",
       "      <td>9920755555</td>\n",
       "      <td>Port Operations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17026</th>\n",
       "      <td>6638341389</td>\n",
       "      <td>Infrastructure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Millennial (born between 1981-2000)</td>\n",
       "      <td>Female</td>\n",
       "      <td>3-5 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17027</th>\n",
       "      <td>8114622230</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Staff</td>\n",
       "      <td>Prefer not to answer</td>\n",
       "      <td>Male</td>\n",
       "      <td>5-10 years</td>\n",
       "      <td>Full time Employee</td>\n",
       "      <td>Question 30-Response 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Question 30</td>\n",
       "      <td>182</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17028 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Respondent ID        Division Primary Division Secondary  \\\n",
       "0         5379192392          Infrastructure                NaN   \n",
       "1         2658722536                 Finance                NaN   \n",
       "2         4044163394          Infrastructure                NaN   \n",
       "3         5535865599          Infrastructure                NaN   \n",
       "4         3356802928         Port Operations                NaN   \n",
       "...              ...                     ...                ...   \n",
       "17023     7940065082          Infrastructure                NaN   \n",
       "17024     5157705612                 Finance                NaN   \n",
       "17025     9920755555         Port Operations                NaN   \n",
       "17026     6638341389          Infrastructure                NaN   \n",
       "17027     8114622230  Information Technology                NaN   \n",
       "\n",
       "              Position                             Generation      Gender  \\\n",
       "0                Staff  Generation X (born between 1965-1980)        Male   \n",
       "1                Staff                                    NaN         NaN   \n",
       "2      Department Lead  Generation X (born between 1965-1980)        Male   \n",
       "3              Manager    Millennial (born between 1981-2000)  Non-Binary   \n",
       "4              Manager  Generation X (born between 1965-1980)      Female   \n",
       "...                ...                                    ...         ...   \n",
       "17023  Department Lead   Baby Boomer (born between 1946-1964)        Male   \n",
       "17024            Staff    Millennial (born between 1981-2000)      Female   \n",
       "17025            Staff    Millennial (born between 1981-2000)      Female   \n",
       "17026          Manager    Millennial (born between 1981-2000)      Female   \n",
       "17027            Staff                   Prefer not to answer        Male   \n",
       "\n",
       "           Tenure     Employment Type  Questions+SubQuestions    Answer  \\\n",
       "0       0-2 years  Full time Employee     Question 1-Response       NaN   \n",
       "1       10+ years  Full time Employee     Question 1-Response  Answer 4   \n",
       "2       3-5 years  Full time Employee     Question 1-Response  Answer 5   \n",
       "3      5-10 years  Full time Employee     Question 1-Response  Answer 1   \n",
       "4       10+ years  Full time Employee     Question 1-Response       NaN   \n",
       "...           ...                 ...                     ...       ...   \n",
       "17023   10+ years  Full time Employee  Question 30-Response 3  Answer 8   \n",
       "17024  5-10 years  Full time Employee  Question 30-Response 3  Answer 6   \n",
       "17025   3-5 years  Full time Employee  Question 30-Response 3       NaN   \n",
       "17026   3-5 years  Full time Employee  Question 30-Response 3       NaN   \n",
       "17027  5-10 years  Full time Employee  Question 30-Response 3       NaN   \n",
       "\n",
       "         Questions  Respondents  Same Answer  \n",
       "0       Question 1          119          0.0  \n",
       "1       Question 1          119         17.0  \n",
       "2       Question 1          119         22.0  \n",
       "3       Question 1          119         14.0  \n",
       "4       Question 1          119          0.0  \n",
       "...            ...          ...          ...  \n",
       "17023  Question 30          182         14.0  \n",
       "17024  Question 30          182         20.0  \n",
       "17025  Question 30          182          0.0  \n",
       "17026  Question 30          182          0.0  \n",
       "17027  Question 30          182          0.0  \n",
       "\n",
       "[17028 rows x 13 columns]"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "output = data_merged_three.copy()\n",
    "output.rename(columns={\"Identify which division you work in.-Response\":\"Division Primary\", \"Identify which division you work in.-Other (please specify)\":\"Division Secondary\", \"Which of the following best describes your position level?-Response\":\"Position\", \"Which generation are you apart of?-Response\":\"Generation\", \"Please select the gender in which you identify.-Response\":\"Gender\", \"Which duration range best aligns with your tenure at your company?-Response\":\"Tenure\", \"Which of the following best describes your employment type?-Response\":\"Employment Type\"}, inplace=True)\n",
    "output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "output.to_excel(pwd + \"\\\\Final Data.xlsx\", index=False)"
   ]
  },
  {
   "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.11.2"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}