pisa2012_data_cleanup.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PISA 2012 DATA CLEANING"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\jsamala\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3018: DtypeWarning: Columns (15,16,17,21,22,23,24,25,26,30,31,36,37,45,65,123,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,475) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\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>Unnamed: 0</th>\n",
       "      <th>CNT</th>\n",
       "      <th>SUBNATIO</th>\n",
       "      <th>STRATUM</th>\n",
       "      <th>OECD</th>\n",
       "      <th>NC</th>\n",
       "      <th>SCHOOLID</th>\n",
       "      <th>STIDSTD</th>\n",
       "      <th>ST01Q01</th>\n",
       "      <th>ST02Q01</th>\n",
       "      <th>...</th>\n",
       "      <th>W_FSTR75</th>\n",
       "      <th>W_FSTR76</th>\n",
       "      <th>W_FSTR77</th>\n",
       "      <th>W_FSTR78</th>\n",
       "      <th>W_FSTR79</th>\n",
       "      <th>W_FSTR80</th>\n",
       "      <th>WVARSTRR</th>\n",
       "      <th>VAR_UNIT</th>\n",
       "      <th>SENWGT_STU</th>\n",
       "      <th>VER_STU</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Albania</td>\n",
       "      <td>80000</td>\n",
       "      <td>ALB0006</td>\n",
       "      <td>Non-OECD</td>\n",
       "      <td>Albania</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>13.7954</td>\n",
       "      <td>13.9235</td>\n",
       "      <td>13.1249</td>\n",
       "      <td>13.1249</td>\n",
       "      <td>4.3389</td>\n",
       "      <td>13.0829</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>0.2098</td>\n",
       "      <td>22NOV13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Albania</td>\n",
       "      <td>80000</td>\n",
       "      <td>ALB0006</td>\n",
       "      <td>Non-OECD</td>\n",
       "      <td>Albania</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>13.7954</td>\n",
       "      <td>13.9235</td>\n",
       "      <td>13.1249</td>\n",
       "      <td>13.1249</td>\n",
       "      <td>4.3389</td>\n",
       "      <td>13.0829</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>0.2098</td>\n",
       "      <td>22NOV13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Albania</td>\n",
       "      <td>80000</td>\n",
       "      <td>ALB0006</td>\n",
       "      <td>Non-OECD</td>\n",
       "      <td>Albania</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12.7307</td>\n",
       "      <td>12.7307</td>\n",
       "      <td>12.7307</td>\n",
       "      <td>12.7307</td>\n",
       "      <td>4.2436</td>\n",
       "      <td>12.7307</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>0.1999</td>\n",
       "      <td>22NOV13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Albania</td>\n",
       "      <td>80000</td>\n",
       "      <td>ALB0006</td>\n",
       "      <td>Non-OECD</td>\n",
       "      <td>Albania</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12.7307</td>\n",
       "      <td>12.7307</td>\n",
       "      <td>12.7307</td>\n",
       "      <td>12.7307</td>\n",
       "      <td>4.2436</td>\n",
       "      <td>12.7307</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>0.1999</td>\n",
       "      <td>22NOV13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Albania</td>\n",
       "      <td>80000</td>\n",
       "      <td>ALB0006</td>\n",
       "      <td>Non-OECD</td>\n",
       "      <td>Albania</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12.7307</td>\n",
       "      <td>12.7307</td>\n",
       "      <td>12.7307</td>\n",
       "      <td>12.7307</td>\n",
       "      <td>4.2436</td>\n",
       "      <td>12.7307</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>0.1999</td>\n",
       "      <td>22NOV13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 636 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0      CNT  SUBNATIO  STRATUM      OECD       NC  SCHOOLID  \\\n",
       "0           1  Albania     80000  ALB0006  Non-OECD  Albania         1   \n",
       "1           2  Albania     80000  ALB0006  Non-OECD  Albania         1   \n",
       "2           3  Albania     80000  ALB0006  Non-OECD  Albania         1   \n",
       "3           4  Albania     80000  ALB0006  Non-OECD  Albania         1   \n",
       "4           5  Albania     80000  ALB0006  Non-OECD  Albania         1   \n",
       "\n",
       "   STIDSTD  ST01Q01  ST02Q01   ...     W_FSTR75  W_FSTR76 W_FSTR77 W_FSTR78  \\\n",
       "0        1       10      1.0   ...      13.7954   13.9235  13.1249  13.1249   \n",
       "1        2       10      1.0   ...      13.7954   13.9235  13.1249  13.1249   \n",
       "2        3        9      1.0   ...      12.7307   12.7307  12.7307  12.7307   \n",
       "3        4        9      1.0   ...      12.7307   12.7307  12.7307  12.7307   \n",
       "4        5        9      1.0   ...      12.7307   12.7307  12.7307  12.7307   \n",
       "\n",
       "   W_FSTR79 W_FSTR80 WVARSTRR VAR_UNIT SENWGT_STU  VER_STU  \n",
       "0    4.3389  13.0829       19        1     0.2098  22NOV13  \n",
       "1    4.3389  13.0829       19        1     0.2098  22NOV13  \n",
       "2    4.2436  12.7307       19        1     0.1999  22NOV13  \n",
       "3    4.2436  12.7307       19        1     0.1999  22NOV13  \n",
       "4    4.2436  12.7307       19        1     0.1999  22NOV13  \n",
       "\n",
       "[5 rows x 636 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_csv('pisa2012.csv/pisa2012.csv', encoding='latin-1')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(485490, 636)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This dataset contains information from 485,490 students and 646 features."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Due to having such large amount of features, I wish to limit the number of features.\n",
    "\n",
    "\n",
    "For my analysis, I wanted to focus on how gender plays a role in not only students' mathematics scores but also students' attitude towards mathematics. I also wanted to take a look at if the parents attitude towards children's mathematics differ among the gender. For these reasons, I have decided to focus on the following features:\n",
    "\n",
    "**Student Information**\n",
    "+ CNT: Country\n",
    "+ ST04Q01: Gender\n",
    "\n",
    "\n",
    "**Student Mathematics Scores**\n",
    "+ PV1MATH:\tOverall Mathematics Score\n",
    "\n",
    "\n",
    "**Student's Attitude Towards Mathematics**\n",
    "+ **Instrumental Motivation**\n",
    "    + ST29Q02:\tWorthwhile for Work\n",
    "    + ST29Q05:\tWorthwhile for Career Chances\n",
    "    + ST29Q07:\tImportant for Future Study\n",
    "    + ST29Q08:\tHelps to Get a Job\n",
    "\n",
    "\n",
    "+ **Math Anxiety**\n",
    "    + ST42Q01:\tWorry That It Will Be Difficult\n",
    "    + ST42Q03:\tGet Very Tense\n",
    "    + ST42Q05:\tGet Very Nervous\n",
    "    + ST42Q08:\tFeel Helpless\n",
    "    + ST42Q10:\tWorry About Getting Poor <Grades>\n",
    "\t\n",
    "+ **Math Self-Concept**\n",
    "    + ST42Q02:\tNot Good at Maths\n",
    "    + ST42Q04:\tGet Good (Grades)\n",
    "    + ST42Q06:\tLearn Quickly\n",
    "    + ST42Q07:\tOne of Best Subjects\n",
    "    + ST42Q09:\tUnderstand Difficult Work\n",
    "\n",
    "+ **Math Interest**\n",
    "    + ST29Q01:\tEnjoy Reading\n",
    "    + ST29Q03:\tLook Forward to Lessons\n",
    "    + ST29Q04:\tEnjoy Maths\n",
    "    + ST29Q06:\tInterested\n",
    "    \n",
    "+ **Math Work Ethic**\t\n",
    "    + ST46Q01:\tHomework Completed in Time\n",
    "    + ST46Q02:\tWork Hard on Homework\n",
    "    + ST46Q03:\tPrepared for Exams\n",
    "    + ST46Q04:\tStudy Hard for Quizzes\n",
    "    + ST46Q05:\tStudy Until I Understand Everything\n",
    "    + ST46Q06:\tPay Attention in Classes\n",
    "    + ST46Q07:\tListen in Classes\n",
    "    + ST46Q08:\tAvoid Distractions When Studying\n",
    "    + ST46Q09:\tKeep Work Organized\n",
    "    \n",
    "+ **Math Behaviour**\t\n",
    "    + ST49Q01:\tTalk about Maths with Friends\n",
    "    + ST49Q02:\tHelp Friends with Maths\n",
    "    + ST49Q03:\t(Extracurricular) Activity\n",
    "    + ST49Q04:\tParticipate in Competitions\n",
    "    + ST49Q05:\tStudy More Than 2 Extra Hours a Day\n",
    "    + ST49Q06:\tPlay Chess\n",
    "    + ST49Q07:\tComputer programming\n",
    "    + ST49Q09:\tParticipate in Math Club\n",
    "\n",
    "\n",
    "**Subjective Norms**\n",
    "   + ST35Q04:\tParents Believe Studying Mathematics Is Important\n",
    "   + ST35Q05:\tParents Believe Mathematics Is Important for Career\n",
    "   + ST35Q06:\tParents Like Mathematics\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Assessment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**To-Do Notes:**\n",
    "+ create more descriptive feature labels\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.duplicated().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#organize features by category list\n",
    "\n",
    "#CNT: country, ST04Q01: gender\n",
    "student_info=['CNT','ST04Q01']\n",
    "\n",
    "#PV1MATH: Overall Math Score\n",
    "scores=['PV1MATH']\n",
    "\n",
    "#ST29Q02: Worthwhile for Work, ST29Q05: Worthwhile for Career Chances, ST29Q07: Important for Future Study\n",
    "#ST29Q08: Helps to Get a Job\n",
    "motivation=['ST29Q02','ST29Q05','ST29Q07','ST29Q08']\n",
    "\n",
    "#ST42Q01: Worry That It Will Be Difficult, ST42Q03: Get Very Tense, ST42Q05: Get Very Nervous\n",
    "#ST42Q08: Feel Helpless, ST42Q10: Worry About Getting Poor <Grades>\n",
    "anxiety=['ST42Q01','ST42Q03','ST42Q05','ST42Q08','ST42Q10']\n",
    "\n",
    "#ST42Q02: Not Good at Maths, ST42Q04: Get Good (Grades), ST42Q06: Learn Quickly\n",
    "#ST42Q07: One of Best Subjects, ST42Q09: Understand Difficult Work\n",
    "self=['ST42Q02','ST42Q04','ST42Q06','ST42Q07','ST42Q09']\n",
    "\n",
    "#ST29Q01: Enjoy Reading, ST29Q03: Look Forward to Lessons, ST29Q04: Enjoy Maths, ST29Q06: Interested\n",
    "interest=['ST29Q01','ST29Q03','ST29Q04','ST29Q06']\n",
    "\n",
    "#ST46Q01: Homework Completed in Time, ST46Q02: Work Hard on Homework, ST46Q03: Prepared for Exams\n",
    "#ST46Q04: Study Hard for Quizzes, ST46Q05: Study Until I Understand Everything, ST46Q06: Pay Attention in Classes\n",
    "#ST46Q07: Listen in Classes, ST46Q08: Avoid Distractions When Studying, ST46Q09: Keep Work Organized\n",
    "work_ethic= ['ST46Q01','ST46Q02','ST46Q03','ST46Q04','ST46Q05','ST46Q06','ST46Q07','ST46Q08','ST46Q09']\n",
    "\n",
    "#ST49Q01: Talk about Maths with Friends,ST49Q02: Help Friends with Maths, ST49Q03: (Extracurricular) Activity\n",
    "#ST49Q04: Participate in Competitions, ST49Q05: Study More Than 2 Extra Hours a Day, ST49Q06: Play Chess\n",
    "#ST49Q07: Computer programming, ST49Q09: Participate in Math Club\n",
    "behavior=['ST49Q01','ST49Q02','ST49Q03','ST49Q04','ST49Q05','ST49Q06','ST49Q07','ST49Q09']\n",
    "\n",
    "#ST35Q04: Parents Believe Studying Mathematics Is Important\n",
    "#ST35Q05: Parents Believe Mathematics Is Important for Career\n",
    "#ST35Q06:Parents Like Mathematics\n",
    "parents=['ST35Q04','ST35Q05','ST35Q06']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STUDENT INFO: Country, Gender"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Mexico                      33806\n",
       "Italy                       31073\n",
       "Spain                       25313\n",
       "Canada                      21544\n",
       "Brazil                      19204\n",
       "Australia                   14481\n",
       "United Kingdom              12659\n",
       "United Arab Emirates        11500\n",
       "Switzerland                 11229\n",
       "Qatar                       10966\n",
       "Colombia                     9073\n",
       "Finland                      8829\n",
       "Belgium                      8597\n",
       "Denmark                      7481\n",
       "Jordan                       7038\n",
       "Chile                        6856\n",
       "Thailand                     6606\n",
       "Japan                        6351\n",
       "Chinese Taipei               6046\n",
       "Peru                         6035\n",
       "Slovenia                     5911\n",
       "Argentina                    5908\n",
       "Kazakhstan                   5808\n",
       "Portugal                     5722\n",
       "Indonesia                    5622\n",
       "Singapore                    5546\n",
       "Macao-China                  5335\n",
       "Czech Republic               5327\n",
       "Uruguay                      5315\n",
       "Bulgaria                     5282\n",
       "                            ...  \n",
       "Ireland                      5016\n",
       "Croatia                      5008\n",
       "Germany                      5001\n",
       "United States of America     4978\n",
       "Vietnam                      4959\n",
       "Turkey                       4848\n",
       "Hungary                      4810\n",
       "Estonia                      4779\n",
       "Austria                      4755\n",
       "Montenegro                   4744\n",
       "Albania                      4743\n",
       "Sweden                       4736\n",
       "Norway                       4686\n",
       "Serbia                       4684\n",
       "Slovak Republic              4678\n",
       "Hong Kong-China              4670\n",
       "Lithuania                    4618\n",
       "France                       4613\n",
       "Poland                       4607\n",
       "Costa Rica                   4602\n",
       "Netherlands                  4460\n",
       "Tunisia                      4407\n",
       "Latvia                       4306\n",
       "New Zealand                  4291\n",
       "Iceland                      3508\n",
       "Florida (USA)                1896\n",
       "Perm(Russian Federation)     1761\n",
       "Massachusetts (USA)          1723\n",
       "Connecticut (USA)            1697\n",
       "Liechtenstein                 293\n",
       "Name: CNT, Length: 68, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['CNT'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Looks like some are incorrectly labeled as a country when is a state or city (ie. Floria (USA))**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "http://www.oecd.org/pisa/aboutpisa/pisa-2012-participants.htm\n",
    "\n",
    "**The above link informs us that they have a total of 65 participating countries.**\n",
    "\n",
    "**Since our data is indicting 68 unique values under countries, this means that 3 values are not correct. Good thing we can easily see (at the bottom of our value count list) which 3 values are incorrectly labels! They are 'Florida (USA)', 'Massachusetts (USA)', 'Connecticut (USA)'.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10294"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['CNT'].value_counts()[['United States of America','Florida (USA)','Massachusetts (USA)','Connecticut (USA)']].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**There should be a total of 10,294 students under 'United States of America'**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 485490 entries, 0 to 485489\n",
      "Data columns (total 2 columns):\n",
      "CNT        485490 non-null object\n",
      "ST04Q01    485490 non-null object\n",
      "dtypes: object(2)\n",
      "memory usage: 7.4+ MB\n"
     ]
    }
   ],
   "source": [
    "df[student_info].info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**No missing values for countries and gender**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Female    245064\n",
       "Male      240426\n",
       "Name: ST04Q01, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['ST04Q01'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Looks like gender is almost evenly split**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Mathematics Scores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>PV1MATH</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>406.8469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>486.1427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>533.2684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>412.2215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>381.9209</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    PV1MATH\n",
       "0  406.8469\n",
       "1  486.1427\n",
       "2  533.2684\n",
       "3  412.2215\n",
       "4  381.9209"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[scores].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>PV1MATH</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>485490.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>469.621653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>103.265391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>19.792800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>395.318600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>466.201900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>541.057800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>962.229300</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             PV1MATH\n",
       "count  485490.000000\n",
       "mean      469.621653\n",
       "std       103.265391\n",
       "min        19.792800\n",
       "25%       395.318600\n",
       "50%       466.201900\n",
       "75%       541.057800\n",
       "max       962.229300"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[scores].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 485490 entries, 0 to 485489\n",
      "Data columns (total 1 columns):\n",
      "PV1MATH    485490 non-null float64\n",
      "dtypes: float64(1)\n",
      "memory usage: 3.7 MB\n"
     ]
    }
   ],
   "source": [
    "df[scores].info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**We have no missing values under students' overall score (PV1MATH)**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Student's Attitude Towards Mathematics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#list of features\n",
    "attitudes=[motivation,anxiety,self,interest,work_ethic,behavior]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 \n",
      " Agree                150479\n",
      "Strongly agree        97133\n",
      "Disagree              49792\n",
      "Strongly disagree     18069\n",
      "Name: ST29Q02, dtype: int64 \n",
      "\n",
      "1 \n",
      " Agree                153313\n",
      "Strongly agree        99425\n",
      "Disagree              44218\n",
      "Strongly disagree     18275\n",
      "Name: ST29Q05, dtype: int64 \n",
      "\n",
      "1 \n",
      " Agree                132131\n",
      "Strongly agree        90470\n",
      "Disagree              65853\n",
      "Strongly disagree     26612\n",
      "Name: ST29Q07, dtype: int64 \n",
      "\n",
      "1 \n",
      " Agree                150347\n",
      "Strongly agree        85236\n",
      "Disagree              57949\n",
      "Strongly disagree     21700\n",
      "Name: ST29Q08, dtype: int64 \n",
      "\n",
      "2 \n",
      " Agree                134489\n",
      "Disagree              83359\n",
      "Strongly agree        68696\n",
      "Strongly disagree     27311\n",
      "Name: ST42Q01, dtype: int64 \n",
      "\n",
      "2 \n",
      " Disagree             135714\n",
      "Agree                 80655\n",
      "Strongly disagree     60345\n",
      "Strongly agree        35462\n",
      "Name: ST42Q03, dtype: int64 \n",
      "\n",
      "2 \n",
      " Disagree             140694\n",
      "Agree                 84811\n",
      "Strongly disagree     54941\n",
      "Strongly agree        32178\n",
      "Name: ST42Q05, dtype: int64 \n",
      "\n",
      "2 \n",
      " Disagree             144420\n",
      "Agree                 75175\n",
      "Strongly disagree     63461\n",
      "Strongly agree        29400\n",
      "Name: ST42Q08, dtype: int64 \n",
      "\n",
      "2 \n",
      " Agree                117927\n",
      "Strongly agree        97287\n",
      "Disagree              63513\n",
      "Strongly disagree     34126\n",
      "Name: ST42Q10, dtype: int64 \n",
      "\n",
      "3 \n",
      " Disagree             118658\n",
      "Agree                 91811\n",
      "Strongly disagree     51624\n",
      "Strongly agree        51409\n",
      "Name: ST42Q02, dtype: int64 \n",
      "\n",
      "3 \n",
      " Agree                135358\n",
      "Disagree              96860\n",
      "Strongly agree        50867\n",
      "Strongly disagree     28895\n",
      "Name: ST42Q04, dtype: int64 \n",
      "\n",
      "3 \n",
      " Agree                119008\n",
      "Disagree             111521\n",
      "Strongly agree        46023\n",
      "Strongly disagree     35775\n",
      "Name: ST42Q06, dtype: int64 \n",
      "\n",
      "3 \n",
      " Disagree             110599\n",
      "Agree                 77922\n",
      "Strongly disagree     74462\n",
      "Strongly agree        49600\n",
      "Name: ST42Q07, dtype: int64 \n",
      "\n",
      "3 \n",
      " Disagree             130944\n",
      "Agree                 93920\n",
      "Strongly disagree     57153\n",
      "Strongly agree        30206\n",
      "Name: ST42Q09, dtype: int64 \n",
      "\n",
      "4 \n",
      " Disagree             122898\n",
      "Agree                100609\n",
      "Strongly disagree     63638\n",
      "Strongly agree        28766\n",
      "Name: ST29Q01, dtype: int64 \n",
      "\n",
      "4 \n",
      " Disagree             118509\n",
      "Agree                105206\n",
      "Strongly disagree     56142\n",
      "Strongly agree        35071\n",
      "Name: ST29Q03, dtype: int64 \n",
      "\n",
      "4 \n",
      " Disagree             116952\n",
      "Agree                101308\n",
      "Strongly disagree     54428\n",
      "Strongly agree        42049\n",
      "Name: ST29Q04, dtype: int64 \n",
      "\n",
      "4 \n",
      " Agree                137294\n",
      "Disagree              89259\n",
      "Strongly agree        56059\n",
      "Strongly disagree     32134\n",
      "Name: ST29Q06, dtype: int64 \n",
      "\n",
      "5 \n",
      " Agree                148211\n",
      "Strongly agree        77022\n",
      "Disagree              70473\n",
      "Strongly disagree     18192\n",
      "Name: ST46Q01, dtype: int64 \n",
      "\n",
      "5 \n",
      " Agree                145945\n",
      "Disagree              88884\n",
      "Strongly agree        59954\n",
      "Strongly disagree     18784\n",
      "Name: ST46Q02, dtype: int64 \n",
      "\n",
      "5 \n",
      " Agree                150143\n",
      "Disagree              81369\n",
      "Strongly agree        65401\n",
      "Strongly disagree     16081\n",
      "Name: ST46Q03, dtype: int64 \n",
      "\n",
      "5 \n",
      " Agree                128614\n",
      "Disagree             109033\n",
      "Strongly agree        53222\n",
      "Strongly disagree     22128\n",
      "Name: ST46Q04, dtype: int64 \n",
      "\n",
      "5 \n",
      " Agree                141965\n",
      "Disagree              92308\n",
      "Strongly agree        61741\n",
      "Strongly disagree     17029\n",
      "Name: ST46Q05, dtype: int64 \n",
      "\n",
      "5 \n",
      " Agree                171776\n",
      "Strongly agree        82713\n",
      "Disagree              48412\n",
      "Strongly disagree      9999\n",
      "Name: ST46Q06, dtype: int64 \n",
      "\n",
      "5 \n",
      " Agree                177887\n",
      "Strongly agree        90494\n",
      "Disagree              35911\n",
      "Strongly disagree      8562\n",
      "Name: ST46Q07, dtype: int64 \n",
      "\n",
      "5 \n",
      " Agree                145004\n",
      "Disagree              91444\n",
      "Strongly agree        60995\n",
      "Strongly disagree     15546\n",
      "Name: ST46Q08, dtype: int64 \n",
      "\n",
      "5 \n",
      " Agree                143101\n",
      "Disagree              87760\n",
      "Strongly agree        63513\n",
      "Strongly disagree     18666\n",
      "Name: ST46Q09, dtype: int64 \n",
      "\n",
      "6 \n",
      " Sometimes                  127348\n",
      "Never or rarely            107498\n",
      "Often                       56808\n",
      "Always or almost always     21841\n",
      "Name: ST49Q01, dtype: int64 \n",
      "\n",
      "6 \n",
      " Sometimes                  138306\n",
      "Never or rarely             78062\n",
      "Often                       72985\n",
      "Always or almost always     23672\n",
      "Name: ST49Q02, dtype: int64 \n",
      "\n",
      "6 \n",
      " Never or rarely            160292\n",
      "Sometimes                   92310\n",
      "Often                       41786\n",
      "Always or almost always     17780\n",
      "Name: ST49Q03, dtype: int64 \n",
      "\n",
      "6 \n",
      " Never or rarely            220282\n",
      "Sometimes                   55073\n",
      "Often                       23584\n",
      "Always or almost always     13439\n",
      "Name: ST49Q04, dtype: int64 \n",
      "\n",
      "6 \n",
      " Never or rarely            179413\n",
      "Sometimes                   88330\n",
      "Often                       30141\n",
      "Always or almost always     14698\n",
      "Name: ST49Q05, dtype: int64 \n",
      "\n",
      "6 \n",
      " Never or rarely            183262\n",
      "Sometimes                   75817\n",
      "Often                       35141\n",
      "Always or almost always     18351\n",
      "Name: ST49Q06, dtype: int64 \n",
      "\n",
      "6 \n",
      " Never or rarely            185257\n",
      "Sometimes                   68441\n",
      "Often                       37373\n",
      "Always or almost always     21354\n",
      "Name: ST49Q07, dtype: int64 \n",
      "\n",
      "6 \n",
      " Never or rarely            267109\n",
      "Sometimes                   24229\n",
      "Often                       12923\n",
      "Always or almost always      8491\n",
      "Name: ST49Q09, dtype: int64 \n",
      "\n"
     ]
    }
   ],
   "source": [
    "#finds the different categories under each feature\n",
    "i=0\n",
    "for _ in attitudes:\n",
    "    i+=1\n",
    "    for feature in _:\n",
    "        print(i,'\\n',df[feature].value_counts(),'\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Features under Motivation, Anxiety, Self, Interest, Work_Ethic contain categories Strongly Agree, Agree, Disagree and Strongly Agree.**\n",
    "\n",
    "\n",
    "**Features under Behavior contains categories Always or Almost Always, Often, Sometimes, Never or Rarely**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 485490 entries, 0 to 485489\n",
      "Data columns (total 4 columns):\n",
      "ST29Q02    315473 non-null object\n",
      "ST29Q05    315231 non-null object\n",
      "ST29Q07    315066 non-null object\n",
      "ST29Q08    315232 non-null object\n",
      "dtypes: object(4)\n",
      "memory usage: 14.8+ MB\n"
     ]
    }
   ],
   "source": [
    "df[motivation].info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**We have some missing values here**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "172796"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[motivation].isnull().any(axis=1).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**About 172,796 students have at least one feature under motivation left Null**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    312694\n",
       "4    168774\n",
       "1      2822\n",
       "3       640\n",
       "2       560\n",
       "dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[motivation].isnull().sum(axis=1).value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**A total of 168,774 students left all of these answers Null under Motivation**\n",
    "\n",
    "\n",
    "**The question is what should we do about these students when calculating Motivation as well as our other features (that describe students' attitudes) that have a Null value?**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 485490 entries, 0 to 485489\n",
      "Data columns (total 5 columns):\n",
      "ST42Q01    313855 non-null object\n",
      "ST42Q03    312176 non-null object\n",
      "ST42Q05    312624 non-null object\n",
      "ST42Q08    312456 non-null object\n",
      "ST42Q10    312853 non-null object\n",
      "dtypes: object(5)\n",
      "memory usage: 18.5+ MB\n"
     ]
    }
   ],
   "source": [
    "df[anxiety].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 485490 entries, 0 to 485489\n",
      "Data columns (total 5 columns):\n",
      "ST42Q02    313502 non-null object\n",
      "ST42Q04    311980 non-null object\n",
      "ST42Q06    312327 non-null object\n",
      "ST42Q07    312583 non-null object\n",
      "ST42Q09    312223 non-null object\n",
      "dtypes: object(5)\n",
      "memory usage: 18.5+ MB\n"
     ]
    }
   ],
   "source": [
    "df[self].info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note to self: The self section asks students whether they believe they are:**\n",
    "\n",
    "**1) Not good at Math**\n",
    "\n",
    "**2) Get Good (Grades)**\n",
    "\n",
    "**3) Learn Quickly**\n",
    "\n",
    "**4) One of Best Subjects**\n",
    "\n",
    "**5) Understand Difficult Work**\n",
    "\n",
    "**Becareful when assigning point values due to fact that \"Not Good at Math\" is sometime negative while the remainder are positive. In this case, for 'Not good at Math' (ST42Q02) we would assign 'Strongly Agree' a point value of 1, 'Agree' a point value of 2 and so on.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 485490 entries, 0 to 485489\n",
      "Data columns (total 4 columns):\n",
      "ST29Q01    315911 non-null object\n",
      "ST29Q03    314928 non-null object\n",
      "ST29Q04    314737 non-null object\n",
      "ST29Q06    314746 non-null object\n",
      "dtypes: object(4)\n",
      "memory usage: 14.8+ MB\n"
     ]
    }
   ],
   "source": [
    "df[interest].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 485490 entries, 0 to 485489\n",
      "Data columns (total 9 columns):\n",
      "ST46Q01    313898 non-null object\n",
      "ST46Q02    313567 non-null object\n",
      "ST46Q03    312994 non-null object\n",
      "ST46Q04    312997 non-null object\n",
      "ST46Q05    313043 non-null object\n",
      "ST46Q06    312900 non-null object\n",
      "ST46Q07    312854 non-null object\n",
      "ST46Q08    312989 non-null object\n",
      "ST46Q09    313040 non-null object\n",
      "dtypes: object(9)\n",
      "memory usage: 33.3+ MB\n"
     ]
    }
   ],
   "source": [
    "df[work_ethic].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 485490 entries, 0 to 485489\n",
      "Data columns (total 8 columns):\n",
      "ST49Q01    313495 non-null object\n",
      "ST49Q02    313025 non-null object\n",
      "ST49Q03    312168 non-null object\n",
      "ST49Q04    312378 non-null object\n",
      "ST49Q05    312582 non-null object\n",
      "ST49Q06    312571 non-null object\n",
      "ST49Q07    312425 non-null object\n",
      "ST49Q09    312752 non-null object\n",
      "dtypes: object(8)\n",
      "memory usage: 29.6+ MB\n"
     ]
    }
   ],
   "source": [
    "df[behavior].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n",
      "{0: 312694, 4: 168774, 1: 2822, 3: 640, 2: 560}\n",
      "% Complete: 0.6440791777379554\n",
      "\n",
      "\n",
      "2\n",
      "{0: 307425, 5: 170577, 1: 5846, 2: 733, 4: 562, 3: 347}\n",
      "% Complete: 0.6332262250509795\n",
      "\n",
      "\n",
      "3\n",
      "{0: 306931, 5: 170736, 1: 5949, 2: 939, 4: 523, 3: 412}\n",
      "% Complete: 0.6322086963686173\n",
      "\n",
      "\n",
      "4\n",
      "{0: 311611, 4: 168590, 1: 3999, 3: 699, 2: 591}\n",
      "% Complete: 0.6418484417804692\n",
      "\n",
      "\n",
      "5\n",
      "{0: 306731, 9: 170312, 1: 5393, 2: 1207, 8: 677, 3: 452, 4: 235, 5: 223, 6: 134, 7: 126}\n",
      "% Complete: 0.6317967414364869\n",
      "\n",
      "\n",
      "6\n",
      "{0: 307112, 8: 170977, 1: 5158, 2: 843, 7: 666, 3: 228, 4: 187, 6: 175, 5: 144}\n",
      "% Complete: 0.6325815155821953\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#Calculates the number of null values for each section \n",
    "#as well as percent of students who answered all questions under each section\n",
    "i=0\n",
    "for _ in attitudes:\n",
    "    i+=1\n",
    "    print(i)\n",
    "    d=dict(df[_].isnull().sum(axis=1).value_counts())\n",
    "    print(d)\n",
    "    print('% Complete: {}'.format(d[0]/485490))\n",
    "    print('\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**For each section, at least 60% of students answer all the questions**\n",
    "\n",
    "\n",
    "**To-Do List:**\n",
    "  + If student did not answer at least one feature under each section, then fill in Null Value for section's overall score"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SUBJECTIVE NORMS: Parents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "#ST35Q04: Parents Believe Studying Mathematics Is Important\n",
    "#ST35Q05: Parents Believe Mathematics Is Important for Career\n",
    "#ST35Q06:Parents Like Mathematics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "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>ST35Q04</th>\n",
       "      <th>ST35Q05</th>\n",
       "      <th>ST35Q06</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Agree</td>\n",
       "      <td>Agree</td>\n",
       "      <td>Agree</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Agree</td>\n",
       "      <td>Disagree</td>\n",
       "      <td>Agree</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Agree</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Agree</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          ST35Q04         ST35Q05 ST35Q06\n",
       "0           Agree           Agree   Agree\n",
       "1           Agree        Disagree   Agree\n",
       "2  Strongly agree  Strongly agree   Agree\n",
       "3             NaN             NaN     NaN\n",
       "4  Strongly agree  Strongly agree   Agree"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[parents].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ST35Q04\n",
      "Agree                153693\n",
      "Strongly agree       132309\n",
      "Disagree              23316\n",
      "Strongly disagree      5842\n",
      "Name: ST35Q04, dtype: int64 \n",
      "\n",
      "ST35Q05\n",
      "Agree                144854\n",
      "Strongly agree       115112\n",
      "Disagree              46278\n",
      "Strongly disagree      8599\n",
      "Name: ST35Q05, dtype: int64 \n",
      "\n",
      "ST35Q06\n",
      "Agree                148876\n",
      "Disagree              93913\n",
      "Strongly agree        49488\n",
      "Strongly disagree     21112\n",
      "Name: ST35Q06, dtype: int64 \n",
      "\n"
     ]
    }
   ],
   "source": [
    "for _ in parents:\n",
    "    print(_)\n",
    "    print(df[_].value_counts(),'\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**This section is categorized by:**\n",
    "   + Strongly Agree\n",
    "   + Agree\n",
    "   + Disagree\n",
    "   + Strongly Agree\n",
    "   \n",
    "**We would want to combine these features by assiging point values and taking its mean.**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(173808, 0.35800531421862447)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[parents].isnull().any(axis=1).sum(), df[parents].isnull().any(axis=1).sum()/df.shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**About 35.8% of students had left at least one of these questions blank**\n",
    "   + When combining scores under parent's section, we should leave these 35.8% null"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DATA ASSESSMENT SUMMARY\n",
    "\n",
    "+ Delete unneccessary features\n",
    "\n",
    "\n",
    "+ Rename feature labels\n",
    "\n",
    "\n",
    "+ Correctly categorize countries under CNT (country): Replace values of 'Florida (USA)','Massachusetts (USA)', and 'Connecticut (USA)' with 'United States of America'\n",
    "\n",
    "\n",
    "+ Student and Parent's Attitudes: Combine the features in each section into one feature by assigning point values and taking average.\n",
    "  + If student did not answer at least one feature under each section, then fill in Null Value for section's overall score\n",
    "  + Becareful when assigning point values due to fact that \"Not Good at Math\" is sometime negative while the remainder are positive. In this case, for 'Not good at Math' (ST42Q02) we would assign 'Strongly Agree' a point value of 1, 'Agree' a point value of 2 and so on.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DATA CLEANING"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**1. Delete unneccessary features by making copy of dataframe with neccessary features**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "keep=['CNT', 'ST04Q01', 'PV1MATH','ST29Q02', 'ST29Q05', 'ST29Q07', 'ST29Q08', 'ST42Q01',\n",
    "      'ST42Q03', 'ST42Q05', 'ST42Q08','ST42Q10', 'ST42Q02', 'ST42Q04', 'ST42Q06',\n",
    "      'ST42Q07', 'ST42Q09', 'ST29Q01', 'ST29Q03', 'ST29Q04', 'ST29Q06','ST46Q01',\n",
    "      'ST46Q02', 'ST46Q03', 'ST46Q04', 'ST46Q05', 'ST46Q06', 'ST46Q07', 'ST46Q08',\n",
    "      'ST46Q09','ST49Q01', 'ST49Q02', 'ST49Q03', 'ST49Q04', 'ST49Q05', 'ST49Q06',\n",
    "      'ST49Q07', 'ST49Q09','ST35Q04', 'ST35Q05', 'ST35Q06']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_clean=df[keep]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": true
   },
   "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>CNT</th>\n",
       "      <th>ST04Q01</th>\n",
       "      <th>PV1MATH</th>\n",
       "      <th>ST29Q02</th>\n",
       "      <th>ST29Q05</th>\n",
       "      <th>ST29Q07</th>\n",
       "      <th>ST29Q08</th>\n",
       "      <th>ST42Q01</th>\n",
       "      <th>ST42Q03</th>\n",
       "      <th>ST42Q05</th>\n",
       "      <th>...</th>\n",
       "      <th>ST49Q02</th>\n",
       "      <th>ST49Q03</th>\n",
       "      <th>ST49Q04</th>\n",
       "      <th>ST49Q05</th>\n",
       "      <th>ST49Q06</th>\n",
       "      <th>ST49Q07</th>\n",
       "      <th>ST49Q09</th>\n",
       "      <th>ST35Q04</th>\n",
       "      <th>ST35Q05</th>\n",
       "      <th>ST35Q06</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>406.8469</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Agree</td>\n",
       "      <td>Agree</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Agree</td>\n",
       "      <td>Agree</td>\n",
       "      <td>Agree</td>\n",
       "      <td>...</td>\n",
       "      <td>Sometimes</td>\n",
       "      <td>Sometimes</td>\n",
       "      <td>Sometimes</td>\n",
       "      <td>Sometimes</td>\n",
       "      <td>Never or rarely</td>\n",
       "      <td>Never or rarely</td>\n",
       "      <td>Never or rarely</td>\n",
       "      <td>Agree</td>\n",
       "      <td>Agree</td>\n",
       "      <td>Agree</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>486.1427</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Agree</td>\n",
       "      <td>Disagree</td>\n",
       "      <td>Disagree</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Often</td>\n",
       "      <td>Always or almost always</td>\n",
       "      <td>Sometimes</td>\n",
       "      <td>Always or almost always</td>\n",
       "      <td>Never or rarely</td>\n",
       "      <td>Never or rarely</td>\n",
       "      <td>Often</td>\n",
       "      <td>Agree</td>\n",
       "      <td>Disagree</td>\n",
       "      <td>Agree</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>533.2684</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Always or almost always</td>\n",
       "      <td>Sometimes</td>\n",
       "      <td>Never or rarely</td>\n",
       "      <td>Always or almost always</td>\n",
       "      <td>Never or rarely</td>\n",
       "      <td>Never or rarely</td>\n",
       "      <td>Never or rarely</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Agree</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>412.2215</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Disagree</td>\n",
       "      <td>Agree</td>\n",
       "      <td>...</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>381.9209</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Agree</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>...</td>\n",
       "      <td>Always or almost always</td>\n",
       "      <td>Often</td>\n",
       "      <td>Often</td>\n",
       "      <td>Sometimes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sometimes</td>\n",
       "      <td>Sometimes</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Strongly agree</td>\n",
       "      <td>Agree</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       CNT ST04Q01   PV1MATH         ST29Q02         ST29Q05         ST29Q07  \\\n",
       "0  Albania  Female  406.8469  Strongly agree           Agree           Agree   \n",
       "1  Albania  Female  486.1427  Strongly agree           Agree        Disagree   \n",
       "2  Albania  Female  533.2684  Strongly agree  Strongly agree  Strongly agree   \n",
       "3  Albania  Female  412.2215             NaN             NaN             NaN   \n",
       "4  Albania  Female  381.9209  Strongly agree  Strongly agree  Strongly agree   \n",
       "\n",
       "          ST29Q08         ST42Q01   ST42Q03         ST42Q05   ...    \\\n",
       "0  Strongly agree           Agree     Agree           Agree   ...     \n",
       "1        Disagree             NaN       NaN             NaN   ...     \n",
       "2  Strongly agree             NaN       NaN             NaN   ...     \n",
       "3             NaN             NaN  Disagree           Agree   ...     \n",
       "4  Strongly agree  Strongly agree     Agree  Strongly agree   ...     \n",
       "\n",
       "                   ST49Q02                  ST49Q03          ST49Q04  \\\n",
       "0                Sometimes                Sometimes        Sometimes   \n",
       "1                    Often  Always or almost always        Sometimes   \n",
       "2  Always or almost always                Sometimes  Never or rarely   \n",
       "3                      NaN                      NaN              NaN   \n",
       "4  Always or almost always                    Often            Often   \n",
       "\n",
       "                   ST49Q05          ST49Q06          ST49Q07          ST49Q09  \\\n",
       "0                Sometimes  Never or rarely  Never or rarely  Never or rarely   \n",
       "1  Always or almost always  Never or rarely  Never or rarely            Often   \n",
       "2  Always or almost always  Never or rarely  Never or rarely  Never or rarely   \n",
       "3                      NaN              NaN              NaN              NaN   \n",
       "4                Sometimes              NaN        Sometimes        Sometimes   \n",
       "\n",
       "          ST35Q04         ST35Q05 ST35Q06  \n",
       "0           Agree           Agree   Agree  \n",
       "1           Agree        Disagree   Agree  \n",
       "2  Strongly agree  Strongly agree   Agree  \n",
       "3             NaN             NaN     NaN  \n",
       "4  Strongly agree  Strongly agree   Agree  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**2. Combine the features in each section into one feature by assigning point values and taking average.**\n",
    "\n",
    "(If student did not answer at least one feature under each section, then fill in Null Value for section's overall score)\n",
    "\n",
    "Sections\n",
    "+ Instrumental Motivation \n",
    "     + worthwhile for work, worthwhile for career changes, important for future study\n",
    "        \n",
    "        \n",
    "+ Math Anxiety \n",
    "     + worry that it will be difficult, get very tense, get very nervous, feel helpless, worry about getting poor grades\n",
    "        \n",
    "        \n",
    "+ Math Self-Concept \n",
    "     + not good at Math, get good grades, learn quickly, one of best subjects, understand difficult work\n",
    "      \n",
    "      \n",
    "+ Math Interest \n",
    "     + enjoy reading, look forward to lessons, enjoy Math, interested in Math\n",
    "        \n",
    "        \n",
    "+ Math Work Ethic \n",
    "     + homework completed on time, work hard on homework, prepared for exams, study hard for quizzes, study until I understand everything, pay attention in classes, listen in classes, avoid distractions when studying, keep work organized\n",
    "        \n",
    "        \n",
    "+ Math Behaviour \n",
    "     + talk about Math with friends, help friends with Math, Extracurricular Activity, participate in competitions, study more than 2 extra hours a day, play chess, computer programming, participate in math club\n",
    "\n",
    "\n",
    "+ Student's Parental View of Mathematics\n",
    "    + parents believe studying mathematics is important, parents believe mathematics is important for career, parents like mathematics\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Sections Instrumental Motivation, Math Anxiety, Math Interest, Math Work Ethic, Student's Parental Attitude of Mathematics will be using the same scale where 'Strongly agree' would be 4 points and 'Strongly disagree' would be 1 point**\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sections Instrumental Motivation, Math Anxiety, Math Interest, and Math Work Ethic\n",
    "# will be using the same scale\n",
    "points_1={'Strongly agree':4,'Agree': 3,'Disagree': 2, 'Strongly disagree':1}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function that will assign point values to each feature category\n",
    "# and create/combine into one feature\n",
    "# features variabe will be a list argument\n",
    "# cmb_name variable will be the name of the new combined feature (string form)\n",
    "# scale variable is what dictionary point system we will be using\n",
    "\n",
    "def cmb_features(features,cmb_name, scale):\n",
    "    for feature in features:\n",
    "    \n",
    "        df_clean.loc[:,feature]=df_clean.loc[:,feature].map(scale)\n",
    "\n",
    "    df_clean[cmb_name]=df_clean.loc[:,features].sum(axis=1, skipna=False)/len(features)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\jsamala\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:543: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self.obj[item] = s\n",
      "C:\\Users\\jsamala\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:12: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  if sys.path[0] == '':\n"
     ]
    }
   ],
   "source": [
    "# Instrumental Motivation\n",
    "cmb_features(motivation,'motivation',points_1)\n",
    "\n",
    "# Math Anxiety\n",
    "cmb_features(anxiety, 'anxiety', points_1)\n",
    "\n",
    "# Math Interest\n",
    "cmb_features(interest, 'interest', points_1)\n",
    "\n",
    "# Math Work Ethic\n",
    "cmb_features(work_ethic, 'work_ethic', points_1)\n",
    "\n",
    "cmb_features(parents, 'parents', points_1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "scrolled": false
   },
   "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>motivation</th>\n",
       "      <th>anxiety</th>\n",
       "      <th>interest</th>\n",
       "      <th>work_ethic</th>\n",
       "      <th>parents</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.50</td>\n",
       "      <td>2.6</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.25</td>\n",
       "      <td>3.222222</td>\n",
       "      <td>2.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.25</td>\n",
       "      <td>3.888889</td>\n",
       "      <td>3.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.00</td>\n",
       "      <td>3.2</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.777778</td>\n",
       "      <td>3.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3.50</td>\n",
       "      <td>2.4</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.666667</td>\n",
       "      <td>3.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3.00</td>\n",
       "      <td>2.4</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   motivation  anxiety  interest  work_ethic   parents\n",
       "0        3.50      2.6      3.00    3.000000  3.000000\n",
       "1        2.75      NaN      2.25    3.222222  2.666667\n",
       "2        4.00      NaN      3.25    3.888889  3.666667\n",
       "3         NaN      NaN       NaN         NaN       NaN\n",
       "4        4.00      3.2      2.50    3.777778  3.666667\n",
       "5         NaN      2.2       NaN         NaN       NaN\n",
       "6         NaN      2.8       NaN         NaN       NaN\n",
       "7        3.50      2.4      3.00    3.666667  3.333333\n",
       "8        3.00      2.4      3.00    3.000000  3.333333\n",
       "9         NaN      1.4       NaN         NaN       NaN"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean[['motivation','anxiety','interest','work_ethic', 'parents']].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Section Math Behaviour would have a different points system due to being categorized differently. Features under this section contains categories Always or Almost Always, Often, Sometimes, Never or Rarely. In this scenario, we would have 'Always or Almost Always' assigned to 4 points and 'Often' to 3 points, and so on.**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# points system for Math Behavious\n",
    "points_2={'Always or almost always': 4, 'Often':3, 'Sometimes':2, 'Never or rarely':1}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\jsamala\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:12: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  if sys.path[0] == '':\n"
     ]
    }
   ],
   "source": [
    "cmb_features(behavior,'behavior',points_2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1.75\n",
       "1    2.50\n",
       "2    2.00\n",
       "3     NaN\n",
       "4     NaN\n",
       "Name: behavior, dtype: float64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean['behavior'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>ST49Q01</th>\n",
       "      <th>ST49Q02</th>\n",
       "      <th>ST49Q03</th>\n",
       "      <th>ST49Q04</th>\n",
       "      <th>ST49Q05</th>\n",
       "      <th>ST49Q06</th>\n",
       "      <th>ST49Q07</th>\n",
       "      <th>ST49Q09</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ST49Q01  ST49Q02  ST49Q03  ST49Q04  ST49Q05  ST49Q06  ST49Q07  ST49Q09\n",
       "0      3.0      2.0      2.0      2.0      2.0      1.0      1.0      1.0\n",
       "1      2.0      3.0      4.0      2.0      4.0      1.0      1.0      3.0\n",
       "2      2.0      4.0      2.0      1.0      4.0      1.0      1.0      1.0\n",
       "3      NaN      NaN      NaN      NaN      NaN      NaN      NaN      NaN\n",
       "4      4.0      4.0      3.0      3.0      2.0      NaN      2.0      2.0"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean[behavior].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "**Section Math Self-Concept would be an exception from the above scale system due to one feature asks students if they believe they are not good at Math meaning 'Strongly agree' would be a negative view of themselves. The other features under Math Self Concept have 'Strongly agree' as a positive view of themselves. This would be we would reverse the point system for just the feature 'Not Good at Maths' (ST42Q02)**\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reverse point system for 'Not Good at Math' feature\n",
    "df_clean.loc[:,'ST42Q02']=df_clean.loc[:,'ST42Q02'].map({'Strongly agree':1, \n",
    "                                             'Agree': 2,'Disagree': 3, 'Strongly disagree':4})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# regular point system\n",
    "\n",
    "for _ in ['ST42Q04','ST42Q06','ST42Q07','ST42Q09']:\n",
    "    df_clean.loc[:,_]=df_clean.loc[:,_].map({'Strongly agree':4,'Agree': 3,'Disagree': 2, 'Strongly disagree':1})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "scrolled": true
   },
   "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>ST42Q02</th>\n",
       "      <th>ST42Q04</th>\n",
       "      <th>ST42Q06</th>\n",
       "      <th>ST42Q07</th>\n",
       "      <th>ST42Q09</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ST42Q02  ST42Q04  ST42Q06  ST42Q07  ST42Q09\n",
       "0      3.0      3.0      3.0      3.0      2.0\n",
       "1      NaN      NaN      NaN      NaN      NaN\n",
       "2      NaN      NaN      NaN      NaN      NaN\n",
       "3      1.0      3.0      2.0      4.0      3.0\n",
       "4      1.0      4.0      2.0      2.0      3.0"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean[self].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\jsamala\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "#create new feature that takes average of Self Features\n",
    "df_clean['self']=df_clean.loc[:,self].sum(axis=1, skipna=False)/len(self)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2.8\n",
       "1    NaN\n",
       "2    NaN\n",
       "3    2.6\n",
       "4    2.4\n",
       "Name: self, dtype: float64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean['self'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>ST42Q02</th>\n",
       "      <th>ST42Q04</th>\n",
       "      <th>ST42Q06</th>\n",
       "      <th>ST42Q07</th>\n",
       "      <th>ST42Q09</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ST42Q02  ST42Q04  ST42Q06  ST42Q07  ST42Q09\n",
       "0      3.0      3.0      3.0      3.0      2.0\n",
       "1      NaN      NaN      NaN      NaN      NaN\n",
       "2      NaN      NaN      NaN      NaN      NaN\n",
       "3      1.0      3.0      2.0      4.0      3.0\n",
       "4      1.0      4.0      2.0      2.0      3.0"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean[self].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**3. Delete unneccessary features**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\jsamala\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py:3697: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  errors=errors)\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>CNT</th>\n",
       "      <th>ST04Q01</th>\n",
       "      <th>PV1MATH</th>\n",
       "      <th>motivation</th>\n",
       "      <th>anxiety</th>\n",
       "      <th>interest</th>\n",
       "      <th>work_ethic</th>\n",
       "      <th>parents</th>\n",
       "      <th>behavior</th>\n",
       "      <th>self</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>406.8469</td>\n",
       "      <td>3.50</td>\n",
       "      <td>2.6</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.75</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>486.1427</td>\n",
       "      <td>2.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.25</td>\n",
       "      <td>3.222222</td>\n",
       "      <td>2.666667</td>\n",
       "      <td>2.50</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>533.2684</td>\n",
       "      <td>4.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.25</td>\n",
       "      <td>3.888889</td>\n",
       "      <td>3.666667</td>\n",
       "      <td>2.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>412.2215</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>2.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>381.9209</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.2</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.777778</td>\n",
       "      <td>3.666667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       CNT ST04Q01   PV1MATH  motivation  anxiety  interest  work_ethic  \\\n",
       "0  Albania  Female  406.8469        3.50      2.6      3.00    3.000000   \n",
       "1  Albania  Female  486.1427        2.75      NaN      2.25    3.222222   \n",
       "2  Albania  Female  533.2684        4.00      NaN      3.25    3.888889   \n",
       "3  Albania  Female  412.2215         NaN      NaN       NaN         NaN   \n",
       "4  Albania  Female  381.9209        4.00      3.2      2.50    3.777778   \n",
       "\n",
       "    parents  behavior  self  \n",
       "0  3.000000      1.75   2.8  \n",
       "1  2.666667      2.50   NaN  \n",
       "2  3.666667      2.00   NaN  \n",
       "3       NaN       NaN   2.6  \n",
       "4  3.666667       NaN   2.4  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean.drop(motivation+anxiety+self+interest+work_ethic+behavior+parents, axis=1,inplace=True)\n",
    "df_clean.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**4. Rename feature labels**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "col_names={'CNT':'country', 'ST04Q01':'gender', 'PV1MATH':'score_overall','PV1MACC':'score_1','PV1MACQ':'score_2','PV1MACS':'score_3','PV1MACU':'score_4','PV1MAPE':'score_5',\n",
    "      'PV1MAPF':'score_6','PV1MAPI':'score_7'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\jsamala\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py:3781: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  return super(DataFrame, self).rename(**kwargs)\n"
     ]
    }
   ],
   "source": [
    "df_clean.rename(columns=col_names,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>country</th>\n",
       "      <th>gender</th>\n",
       "      <th>score_overall</th>\n",
       "      <th>motivation</th>\n",
       "      <th>anxiety</th>\n",
       "      <th>interest</th>\n",
       "      <th>work_ethic</th>\n",
       "      <th>parents</th>\n",
       "      <th>behavior</th>\n",
       "      <th>self</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>406.8469</td>\n",
       "      <td>3.50</td>\n",
       "      <td>2.6</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.75</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>486.1427</td>\n",
       "      <td>2.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.25</td>\n",
       "      <td>3.222222</td>\n",
       "      <td>2.666667</td>\n",
       "      <td>2.50</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>533.2684</td>\n",
       "      <td>4.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.25</td>\n",
       "      <td>3.888889</td>\n",
       "      <td>3.666667</td>\n",
       "      <td>2.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>412.2215</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>2.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>381.9209</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.2</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.777778</td>\n",
       "      <td>3.666667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   country  gender  score_overall  motivation  anxiety  interest  work_ethic  \\\n",
       "0  Albania  Female       406.8469        3.50      2.6      3.00    3.000000   \n",
       "1  Albania  Female       486.1427        2.75      NaN      2.25    3.222222   \n",
       "2  Albania  Female       533.2684        4.00      NaN      3.25    3.888889   \n",
       "3  Albania  Female       412.2215         NaN      NaN       NaN         NaN   \n",
       "4  Albania  Female       381.9209        4.00      3.2      2.50    3.777778   \n",
       "\n",
       "    parents  behavior  self  \n",
       "0  3.000000      1.75   2.8  \n",
       "1  2.666667      2.50   NaN  \n",
       "2  3.666667      2.00   NaN  \n",
       "3       NaN       NaN   2.6  \n",
       "4  3.666667       NaN   2.4  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**5. Correctly categorize countries under CNT (country): Replace values of 'Florida (USA)','Massachusetts (USA)', and 'Connecticut (USA)' with 'United States of America'**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "United States of America    4978\n",
       "Florida (USA)               1896\n",
       "Massachusetts (USA)         1723\n",
       "Connecticut (USA)           1697\n",
       "Name: country, dtype: int64"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean.country.value_counts()[['United States of America',\n",
    "                                 'Florida (USA)','Massachusetts (USA)',\n",
    "                                 'Connecticut (USA)' ]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\jsamala\\Anaconda3\\lib\\site-packages\\pandas\\core\\generic.py:5890: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self._update_inplace(new_data)\n"
     ]
    }
   ],
   "source": [
    "states=['Florida (USA)','Massachusetts (USA)','Connecticut (USA)']\n",
    "\n",
    "df_clean['country'].replace(states, 'United States of America', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\jsamala\\Anaconda3\\lib\\site-packages\\pandas\\core\\series.py:851: FutureWarning: \n",
      "Passing list-likes to .loc or [] with any missing label will raise\n",
      "KeyError in the future, you can use .reindex() as an alternative.\n",
      "\n",
      "See the documentation here:\n",
      "https://pandas.pydata.org/pandas-docs/stable/indexing.html#deprecate-loc-reindex-listlike\n",
      "  return self.loc[key]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "United States of America    10294.0\n",
       "Florida (USA)                   NaN\n",
       "Massachusetts (USA)             NaN\n",
       "Connecticut (USA)               NaN\n",
       "Name: country, dtype: float64"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean.country.value_counts()[['United States of America',\n",
    "                                 'Florida (USA)','Massachusetts (USA)',\n",
    "                                 'Connecticut (USA)' ]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>country</th>\n",
       "      <th>gender</th>\n",
       "      <th>score_overall</th>\n",
       "      <th>motivation</th>\n",
       "      <th>anxiety</th>\n",
       "      <th>interest</th>\n",
       "      <th>work_ethic</th>\n",
       "      <th>parents</th>\n",
       "      <th>behavior</th>\n",
       "      <th>self</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>406.8469</td>\n",
       "      <td>3.50</td>\n",
       "      <td>2.6</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.75</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>486.1427</td>\n",
       "      <td>2.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.25</td>\n",
       "      <td>3.222222</td>\n",
       "      <td>2.666667</td>\n",
       "      <td>2.50</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>533.2684</td>\n",
       "      <td>4.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.25</td>\n",
       "      <td>3.888889</td>\n",
       "      <td>3.666667</td>\n",
       "      <td>2.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>412.2215</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>2.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Female</td>\n",
       "      <td>381.9209</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.2</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.777778</td>\n",
       "      <td>3.666667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   country  gender  score_overall  motivation  anxiety  interest  work_ethic  \\\n",
       "0  Albania  Female       406.8469        3.50      2.6      3.00    3.000000   \n",
       "1  Albania  Female       486.1427        2.75      NaN      2.25    3.222222   \n",
       "2  Albania  Female       533.2684        4.00      NaN      3.25    3.888889   \n",
       "3  Albania  Female       412.2215         NaN      NaN       NaN         NaN   \n",
       "4  Albania  Female       381.9209        4.00      3.2      2.50    3.777778   \n",
       "\n",
       "    parents  behavior  self  \n",
       "0  3.000000      1.75   2.8  \n",
       "1  2.666667      2.50   NaN  \n",
       "2  3.666667      2.00   NaN  \n",
       "3       NaN       NaN   2.6  \n",
       "4  3.666667       NaN   2.4  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Complete! Our dataset is looking good! :)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_clean.to_csv('pisa2012_clean.csv', index_label=False)"
   ]
  }
 ],
 "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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}