hw1_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "21c25e4f8bb31570029fd6b069131a5d00f84cf1"
   },
   "source": [
    "# Домашнее задание 1 (5 баллов).\n",
    "\n",
    "Все задания ниже имеют равный вес (5/16)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "_uuid": "568eb2d31004b87d22e119112ae01a1e75105f1d"
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Описание данных\n",
    "\n",
    "В папке Data находится информация о студентах. Всего 10 групп студентов. Файлы делятся на две категории:\n",
    "    * Students_info_i - информация о студентах из группы i\n",
    "    * Students_marks_i - оценки студентов из группы i за экзамены"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Одно из важных достоинств pandas $-$ это удобные методы реляционного взаимодействия с данными, аналогичные, например, возможностям SQL для слияния и конкатенации таблиц: merge, join, concat. Наличие готовых методов позволяет не реализовывать самостоятельно поэлементную обработку данных и оперировать сразу целыми таблицами данных.\n",
    "\n",
    "Подробно об этих методах посмотрите тут: https://www.kaggle.com/residentmario/renaming-and-combining#Combining"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Задание 1. Соберите всю информацию о студентах в одну таблицу df. В получившейся таблице должна быть информация и оценки всех студентов из всех групп. Напечатайте несколько строк таблицы для демонстрации результата.¶"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>index</th>\n",
       "      <th>gender</th>\n",
       "      <th>race/ethnicity</th>\n",
       "      <th>parental level of education</th>\n",
       "      <th>lunch</th>\n",
       "      <th>test preparation course</th>\n",
       "      <th>group</th>\n",
       "      <th>math score</th>\n",
       "      <th>reading score</th>\n",
       "      <th>writing score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>bachelor's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>72</td>\n",
       "      <td>72</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>group C</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>completed</td>\n",
       "      <td>group1</td>\n",
       "      <td>69</td>\n",
       "      <td>90</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>master's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>90</td>\n",
       "      <td>95</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>male</td>\n",
       "      <td>group A</td>\n",
       "      <td>associate's degree</td>\n",
       "      <td>free/reduced</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>47</td>\n",
       "      <td>57</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>male</td>\n",
       "      <td>group C</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>associate's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>71</td>\n",
       "      <td>83</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  gender race/ethnicity parental level of education         lunch  \\\n",
       "0      0  female        group B           bachelor's degree      standard   \n",
       "1      1  female        group C                some college      standard   \n",
       "2      2  female        group B             master's degree      standard   \n",
       "3      3    male        group A          associate's degree  free/reduced   \n",
       "4      4    male        group C                some college      standard   \n",
       "5      5  female        group B          associate's degree      standard   \n",
       "\n",
       "  test preparation course   group  math score  reading score  writing score  \n",
       "0                    none  group1          72             72             74  \n",
       "1               completed  group1          69             90             88  \n",
       "2                    none  group1          90             95             93  \n",
       "3                    none  group1          47             57             44  \n",
       "4                    none  group1          76             78             75  \n",
       "5                    none  group1          71             83             78  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = []\n",
    "for i in range(10):\n",
    "    std = '../Downloads/Data/Students_info_' + str(i) + '.csv'\n",
    "    table_std = pd.read_csv(std)\n",
    "    mrks = '../Downloads/Data/Students_marks_' + str(i) + '.csv'\n",
    "    table_mrks = pd.read_csv(mrks)\n",
    "    table_std = table_std.merge(table_mrks, how='left')\n",
    "    a.append(table_std)\n",
    "df = pd.concat(a)\n",
    "df.head(6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "8524af4cbd2f7b7810e8a3095a74c9fe1df7eafa"
   },
   "source": [
    "#### Задание 2. Удалите столбец index у полученной таблицы. Напечатайте первые 10 строк таблицы."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "_uuid": "fe62eee87b9b94ab923f57b55eaf0554612aa9e9"
   },
   "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>gender</th>\n",
       "      <th>race/ethnicity</th>\n",
       "      <th>parental level of education</th>\n",
       "      <th>lunch</th>\n",
       "      <th>test preparation course</th>\n",
       "      <th>group</th>\n",
       "      <th>math score</th>\n",
       "      <th>reading score</th>\n",
       "      <th>writing score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>bachelor's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>72</td>\n",
       "      <td>72</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>female</td>\n",
       "      <td>group C</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>completed</td>\n",
       "      <td>group1</td>\n",
       "      <td>69</td>\n",
       "      <td>90</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>master's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>90</td>\n",
       "      <td>95</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>male</td>\n",
       "      <td>group A</td>\n",
       "      <td>associate's degree</td>\n",
       "      <td>free/reduced</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>47</td>\n",
       "      <td>57</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>male</td>\n",
       "      <td>group C</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>associate's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>71</td>\n",
       "      <td>83</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>completed</td>\n",
       "      <td>group1</td>\n",
       "      <td>88</td>\n",
       "      <td>95</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>male</td>\n",
       "      <td>group B</td>\n",
       "      <td>some college</td>\n",
       "      <td>free/reduced</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>40</td>\n",
       "      <td>43</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>male</td>\n",
       "      <td>group D</td>\n",
       "      <td>high school</td>\n",
       "      <td>free/reduced</td>\n",
       "      <td>completed</td>\n",
       "      <td>group1</td>\n",
       "      <td>64</td>\n",
       "      <td>64</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>high school</td>\n",
       "      <td>free/reduced</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>38</td>\n",
       "      <td>60</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender race/ethnicity parental level of education         lunch  \\\n",
       "0  female        group B           bachelor's degree      standard   \n",
       "1  female        group C                some college      standard   \n",
       "2  female        group B             master's degree      standard   \n",
       "3    male        group A          associate's degree  free/reduced   \n",
       "4    male        group C                some college      standard   \n",
       "5  female        group B          associate's degree      standard   \n",
       "6  female        group B                some college      standard   \n",
       "7    male        group B                some college  free/reduced   \n",
       "8    male        group D                 high school  free/reduced   \n",
       "9  female        group B                 high school  free/reduced   \n",
       "\n",
       "  test preparation course   group  math score  reading score  writing score  \n",
       "0                    none  group1          72             72             74  \n",
       "1               completed  group1          69             90             88  \n",
       "2                    none  group1          90             95             93  \n",
       "3                    none  group1          47             57             44  \n",
       "4                    none  group1          76             78             75  \n",
       "5                    none  group1          71             83             78  \n",
       "6               completed  group1          88             95             92  \n",
       "7                    none  group1          40             43             39  \n",
       "8               completed  group1          64             64             67  \n",
       "9                    none  group1          38             60             50  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.drop('index', axis=1)\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "5fcd622942b7ecfc318934c6a245d3bb3bf01e84"
   },
   "source": [
    "#### Задание 3. Выведите на экран размеры полученной таблицы"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "_uuid": "16281cd7b4e941ebed8ca2e1b42f62f55d838684"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1000, 9)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "fe8e3d58d29ca25dbbcf001392377643e6ff9a36"
   },
   "source": [
    "#### Задание 4. Выведите на экран статистические характеристики числовых столбцов таблицы (минимум, максимум, среднее значение, стандартное отклонение)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "_uuid": "e3d4bb80d2c4251fe517c24fcf08c5ff2d67b8aa"
   },
   "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>math score</th>\n",
       "      <th>reading score</th>\n",
       "      <th>writing score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1000.00000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>66.08900</td>\n",
       "      <td>69.169000</td>\n",
       "      <td>68.054000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>15.16308</td>\n",
       "      <td>14.600192</td>\n",
       "      <td>15.195657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.00000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>57.00000</td>\n",
       "      <td>59.000000</td>\n",
       "      <td>57.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>66.00000</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>69.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>77.00000</td>\n",
       "      <td>79.000000</td>\n",
       "      <td>79.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>100.00000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       math score  reading score  writing score\n",
       "count  1000.00000    1000.000000    1000.000000\n",
       "mean     66.08900      69.169000      68.054000\n",
       "std      15.16308      14.600192      15.195657\n",
       "min       0.00000      17.000000      10.000000\n",
       "25%      57.00000      59.000000      57.750000\n",
       "50%      66.00000      70.000000      69.000000\n",
       "75%      77.00000      79.000000      79.000000\n",
       "max     100.00000     100.000000     100.000000"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "b26b7de83485ecb3b8a0a7865a596e24527c7b2f"
   },
   "source": [
    "#### Задание 5. Проверьте, есть ли в таблице пропущенные значения"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "_uuid": "4eac3f8f83d07c6e58ad1576fe0e773f37034a2f"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "gender                         0\n",
       "race/ethnicity                 0\n",
       "parental level of education    0\n",
       "lunch                          0\n",
       "test preparation course        0\n",
       "group                          0\n",
       "math score                     0\n",
       "reading score                  0\n",
       "writing score                  0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Задание 6. Выведите на экран средние баллы студентов по каждому предмету (math, reading, writing)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "math score       66.089\n",
       "reading score    69.169\n",
       "writing score    68.054\n",
       "dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Задание 7. Как зависят оценки от того, проходил ли студент курс для подготовки к сдаче экзамена (test preparation course)? Выведите на экран для каждого предмета в отдельности средний балл студентов, проходивших курс для подготовки к экзамену и не проходивших курс.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "test preparation course\n",
      "completed    69.695531\n",
      "none         64.077882\n",
      "Name: math score, dtype: float64\n",
      "test preparation course\n",
      "completed    73.893855\n",
      "none         66.534268\n",
      "Name: reading score, dtype: float64\n",
      "test preparation course\n",
      "completed    74.418994\n",
      "none         64.504673\n",
      "Name: writing score, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "\n",
    "print(df.groupby('test preparation course')['math score'].apply(lambda x: x.mean()))\n",
    "\n",
    "print(df.groupby('test preparation course')['reading score'].apply(lambda x: x.mean()))\n",
    "\n",
    "print(df.groupby('test preparation course')['writing score'].apply(lambda x: x.mean()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Задание 8. Выведите на экран все различные значения из столбца lunch.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "lunch\n",
       "free/reduced    355\n",
       "standard        645\n",
       "Name: lunch, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('lunch')['lunch'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Задание 9. Переименуйте колонку \"parental level of education\" в \"education\", а \"test preparation course\" в \"test preparation\" с помощью метода pandas rename**\n",
    "https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.rename.html"
   ]
  },
  {
   "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>gender</th>\n",
       "      <th>race/ethnicity</th>\n",
       "      <th>education</th>\n",
       "      <th>lunch</th>\n",
       "      <th>test preparation</th>\n",
       "      <th>group</th>\n",
       "      <th>math score</th>\n",
       "      <th>reading score</th>\n",
       "      <th>writing score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>bachelor's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>72</td>\n",
       "      <td>72</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>female</td>\n",
       "      <td>group C</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>completed</td>\n",
       "      <td>group1</td>\n",
       "      <td>69</td>\n",
       "      <td>90</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>master's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>90</td>\n",
       "      <td>95</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>male</td>\n",
       "      <td>group A</td>\n",
       "      <td>associate's degree</td>\n",
       "      <td>free/reduced</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>47</td>\n",
       "      <td>57</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>male</td>\n",
       "      <td>group C</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender race/ethnicity           education         lunch test preparation  \\\n",
       "0  female        group B   bachelor's degree      standard             none   \n",
       "1  female        group C        some college      standard        completed   \n",
       "2  female        group B     master's degree      standard             none   \n",
       "3    male        group A  associate's degree  free/reduced             none   \n",
       "4    male        group C        some college      standard             none   \n",
       "\n",
       "    group  math score  reading score  writing score  \n",
       "0  group1          72             72             74  \n",
       "1  group1          69             90             88  \n",
       "2  group1          90             95             93  \n",
       "3  group1          47             57             44  \n",
       "4  group1          76             78             75  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.rename(columns={\"parental level of education\": \"education\", \"test preparation course\": \"test preparation\"})\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Зафиксируем минимальный балл для сдачи экзамена**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "passmark = 50"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Задание 10. Ответьте на вопросы:**\n",
    "    * Какая доля студентов сдала экзамен по математике (passmark >= 50)?\n",
    "    * Какая доля студентов, проходивших курс подготовки к экзамену, сдала экзамен по математике?\n",
    "    * Какая доля женщин, не проходивших курс подготовки к экзамену, не сдала экзамен по математике? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a = 0.865\n",
      "b = 0.9217877094972067\n",
      "c = 0.08695652173913043\n"
     ]
    }
   ],
   "source": [
    "print('a =', df[df['math score']>=passmark].gender.count()/df.gender.count())\n",
    "print('b =', df[(df['math score']>=passmark) & (df['test preparation'] == 'completed')].gender.count()/df[df['test preparation'] == 'completed'].gender.count())\n",
    "print('c =', df[(df.gender == 'female') & (df['test preparation'] == 'completed') & (df['math score'] < passmark)].gender.count()/df[(df.gender == 'female') & (df['test preparation'] == 'completed')].gender.count())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Задание 11. С помощью groupby выполните задания ниже. Также выведите время выполнения каждого из заданий.**\n",
    "    * Для каждой этнической группы выведите средний балл за экзамен по чтению\n",
    "    * Для каждого уровня образования выведите минимальный балл за экзамен по письму"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "race/ethnicity\n",
      "group A    64.674157\n",
      "group B    67.352632\n",
      "group C    69.103448\n",
      "group D    70.030534\n",
      "group E    73.028571\n",
      "Name: reading score, dtype: float64\n",
      "education\n",
      "associate's degree    35\n",
      "bachelor's degree     38\n",
      "high school           15\n",
      "master's degree       46\n",
      "some college          19\n",
      "some high school      10\n",
      "Name: writing score, dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0.007066011428833008"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import time\n",
    "st_tm = time.time()\n",
    "print(df.groupby('race/ethnicity')['reading score'].apply(lambda x: x.mean()))\n",
    "print(df.groupby('education')['writing score'].apply(lambda x: x.min()))\n",
    "time.time() - st_tm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Задание 12. Выполните задание 11 с помощью циклов. Сравните время выполнения.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "group B 67.35263157894737\n",
      "group C 69.10344827586206\n",
      "group A 64.67415730337079\n",
      "group D 70.03053435114504\n",
      "group E 73.02857142857142\n",
      "bachelor's degree 38\n",
      "some college 19\n",
      "master's degree 46\n",
      "associate's degree 35\n",
      "high school 15\n",
      "some high school 10\n",
      "0.015845060348510742\n"
     ]
    }
   ],
   "source": [
    "st_time = time.time()\n",
    "for i in df['race/ethnicity'].unique():\n",
    "    print(i, df[df['race/ethnicity'] == i]['reading score'].mean())\n",
    "for i in df['education'].unique():\n",
    "    print(i, df[df['education'] == i]['writing score'].min())\n",
    "print(time.time()-st_time)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Задание 13. Выведите на экран средние баллы студентов по каждому предмету в зависимости от пола и уровня образования. То есть должно получиться количество групп, равных 2 * (число уровней образования), и для каждой такой группы выыведите средний балл по каждому из предметов.**\n",
    "\n",
    "Это можно сделать с помощью сводных таблиц (pivot_table):\n",
    "\n",
    "https://www.kaggle.com/kamilpolak/tutorial-how-to-use-pivot-table-in-pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "education           gender\n",
      "associate's degree  female    65.250000\n",
      "                    male      70.764151\n",
      "bachelor's degree   female    68.349206\n",
      "                    male      70.581818\n",
      "high school         female    59.351064\n",
      "                    male      64.705882\n",
      "master's degree     female    66.500000\n",
      "                    male      74.826087\n",
      "some college        female    65.406780\n",
      "                    male      69.009259\n",
      "some high school    female    59.296703\n",
      "                    male      67.840909\n",
      "Name: math score, dtype: float64\n",
      "education           gender\n",
      "associate's degree  female    74.120690\n",
      "                    male      67.433962\n",
      "bachelor's degree   female    77.285714\n",
      "                    male      68.090909\n",
      "high school         female    68.202128\n",
      "                    male      61.480392\n",
      "master's degree     female    76.805556\n",
      "                    male      73.130435\n",
      "some college        female    73.550847\n",
      "                    male      64.990741\n",
      "some high school    female    69.109890\n",
      "                    male      64.693182\n",
      "Name: reading score, dtype: float64\n",
      "education           gender\n",
      "associate's degree  female    74.000000\n",
      "                    male      65.405660\n",
      "bachelor's degree   female    78.380952\n",
      "                    male      67.654545\n",
      "high school         female    66.691489\n",
      "                    male      58.539216\n",
      "master's degree     female    77.638889\n",
      "                    male      72.608696\n",
      "some college        female    74.050847\n",
      "                    male      63.148148\n",
      "some high school    female    68.285714\n",
      "                    male      61.375000\n",
      "Name: writing score, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(df.groupby(['education', 'gender'])['math score'].apply(lambda x: x.mean()))\n",
    "print(df.groupby(['education', 'gender'])['reading score'].apply(lambda x: x.mean()))\n",
    "print(df.groupby(['education', 'gender'])['writing score'].apply(lambda x: x.mean()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "3e832cb427c4314582d1953a41b8c78db75e30e3"
   },
   "source": [
    "#### Задание 14. Сколько студентов успешно сдали экзамен по математике?\n",
    "\n",
    "Создайте новый столбец в таблице df под названием Math_PassStatus и запишите в него F, если студент не сдал экзамен по математике (балл за экзамен < passmark), и P иначе.\n",
    "\n",
    "Посчитайте количество студентов, сдавших и не сдавших экзамен по математике.\n",
    "\n",
    "Сделайте аналогичные шаги для экзаменов по чтению и письму."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "_uuid": "f43cddb72cc96b30df84bc0de83a4d9170904340"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Math_PassStatus\n",
      "F    135\n",
      "P    865\n",
      "Name: math score, dtype: int64\n",
      "Reading_PassStatus\n",
      "F     90\n",
      "P    910\n",
      "Name: reading score, dtype: int64\n",
      "Writing_PassStatus\n",
      "F    114\n",
      "P    886\n",
      "Name: writing score, dtype: int64\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>gender</th>\n",
       "      <th>race/ethnicity</th>\n",
       "      <th>education</th>\n",
       "      <th>lunch</th>\n",
       "      <th>test preparation</th>\n",
       "      <th>group</th>\n",
       "      <th>math score</th>\n",
       "      <th>reading score</th>\n",
       "      <th>writing score</th>\n",
       "      <th>Math_PassStatus</th>\n",
       "      <th>Reading_PassStatus</th>\n",
       "      <th>Writing_PassStatus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>bachelor's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>72</td>\n",
       "      <td>72</td>\n",
       "      <td>74</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>female</td>\n",
       "      <td>group C</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>completed</td>\n",
       "      <td>group1</td>\n",
       "      <td>69</td>\n",
       "      <td>90</td>\n",
       "      <td>88</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>master's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>90</td>\n",
       "      <td>95</td>\n",
       "      <td>93</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>male</td>\n",
       "      <td>group A</td>\n",
       "      <td>associate's degree</td>\n",
       "      <td>free/reduced</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>47</td>\n",
       "      <td>57</td>\n",
       "      <td>44</td>\n",
       "      <td>F</td>\n",
       "      <td>P</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>male</td>\n",
       "      <td>group C</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>75</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender race/ethnicity           education         lunch test preparation  \\\n",
       "0  female        group B   bachelor's degree      standard             none   \n",
       "1  female        group C        some college      standard        completed   \n",
       "2  female        group B     master's degree      standard             none   \n",
       "3    male        group A  associate's degree  free/reduced             none   \n",
       "4    male        group C        some college      standard             none   \n",
       "\n",
       "    group  math score  reading score  writing score Math_PassStatus  \\\n",
       "0  group1          72             72             74               P   \n",
       "1  group1          69             90             88               P   \n",
       "2  group1          90             95             93               P   \n",
       "3  group1          47             57             44               F   \n",
       "4  group1          76             78             75               P   \n",
       "\n",
       "  Reading_PassStatus Writing_PassStatus  \n",
       "0                  P                  P  \n",
       "1                  P                  P  \n",
       "2                  P                  P  \n",
       "3                  P                  F  \n",
       "4                  P                  P  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Math_PassStatus'] = df['math score'].apply(lambda x: (x//50 >= 1 ) and 'P' or 'F')\n",
    "print(df.groupby('Math_PassStatus')['math score'].count())\n",
    "df['Reading_PassStatus'] = df['reading score'].apply(lambda x: (x//50 >= 1) and 'P' or 'F')\n",
    "print(df.groupby('Reading_PassStatus')['reading score'].count())\n",
    "df['Writing_PassStatus'] = df['writing score'].apply(lambda x: (x//50 >= 1) and 'P' or 'F')\n",
    "print(df.groupby('Writing_PassStatus')['writing score'].count())\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "b753c90914ee65501e2ed263ea614d88a381c472"
   },
   "source": [
    "#### Задание 15. Сколько студентов успешно сдали все экзамены?\n",
    "\n",
    "Создайте столбец OverAll_PassStatus и запишите в него для каждого студента 'F', если студент не сдал хотя бы один из трех экзаменов, а иначе 'P'.\n",
    "\n",
    "Посчитайте количество студентов, которые сдали все экзамены."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "_uuid": "e98f895a022b967cab801447b616222439fe9d84"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OverAll_PassStatus\n",
      "F    188\n",
      "P    812\n",
      "Name: math score, dtype: int64\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>gender</th>\n",
       "      <th>race/ethnicity</th>\n",
       "      <th>education</th>\n",
       "      <th>lunch</th>\n",
       "      <th>test preparation</th>\n",
       "      <th>group</th>\n",
       "      <th>math score</th>\n",
       "      <th>reading score</th>\n",
       "      <th>writing score</th>\n",
       "      <th>Math_PassStatus</th>\n",
       "      <th>Reading_PassStatus</th>\n",
       "      <th>Writing_PassStatus</th>\n",
       "      <th>OverAll_PassStatus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>bachelor's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>72</td>\n",
       "      <td>72</td>\n",
       "      <td>74</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>female</td>\n",
       "      <td>group C</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>completed</td>\n",
       "      <td>group1</td>\n",
       "      <td>69</td>\n",
       "      <td>90</td>\n",
       "      <td>88</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>master's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>90</td>\n",
       "      <td>95</td>\n",
       "      <td>93</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>male</td>\n",
       "      <td>group A</td>\n",
       "      <td>associate's degree</td>\n",
       "      <td>free/reduced</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>47</td>\n",
       "      <td>57</td>\n",
       "      <td>44</td>\n",
       "      <td>F</td>\n",
       "      <td>P</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>male</td>\n",
       "      <td>group C</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>75</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender race/ethnicity           education         lunch test preparation  \\\n",
       "0  female        group B   bachelor's degree      standard             none   \n",
       "1  female        group C        some college      standard        completed   \n",
       "2  female        group B     master's degree      standard             none   \n",
       "3    male        group A  associate's degree  free/reduced             none   \n",
       "4    male        group C        some college      standard             none   \n",
       "\n",
       "    group  math score  reading score  writing score Math_PassStatus  \\\n",
       "0  group1          72             72             74               P   \n",
       "1  group1          69             90             88               P   \n",
       "2  group1          90             95             93               P   \n",
       "3  group1          47             57             44               F   \n",
       "4  group1          76             78             75               P   \n",
       "\n",
       "  Reading_PassStatus Writing_PassStatus OverAll_PassStatus  \n",
       "0                  P                  P                  P  \n",
       "1                  P                  P                  P  \n",
       "2                  P                  P                  P  \n",
       "3                  P                  F                  F  \n",
       "4                  P                  P                  P  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['OverAll_PassStatus'] = ((df['Math_PassStatus']=='P')&(df['Reading_PassStatus']=='P')&(df['Writing_PassStatus']=='P')).apply(lambda x: x and 'P'  or 'F')\n",
    "print(df.groupby('OverAll_PassStatus')['math score'].count())\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "3af9c6cb4cbfff66d4ae09509f10b8b6ce33075d"
   },
   "source": [
    "#### Задание 16. Переведем баллы в оценки\n",
    "\n",
    "### Система перевода баллов в оценки\n",
    "####    больше 90 = A\n",
    "####      80-90 = B\n",
    "####      70-80 = C\n",
    "####      60-70 = D\n",
    "####      50-60 = E\n",
    "####    меньше 50 = F (Fail)\n",
    "\n",
    "Создайте вспомогательную функцию, которая будет по среднему баллу за три экзамена выставлять оценку студенту по данным выше критериям.\n",
    "\n",
    "Создайте столбец Grade и запишите в него оценку каждого студента.\n",
    "\n",
    "Выведите количество студентов, получивших каждую из оценок."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "_uuid": "04707c5be4237020b906d2ac705a0db3d620506f"
   },
   "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>gender</th>\n",
       "      <th>race/ethnicity</th>\n",
       "      <th>education</th>\n",
       "      <th>lunch</th>\n",
       "      <th>test preparation</th>\n",
       "      <th>group</th>\n",
       "      <th>math score</th>\n",
       "      <th>reading score</th>\n",
       "      <th>writing score</th>\n",
       "      <th>Math_PassStatus</th>\n",
       "      <th>Reading_PassStatus</th>\n",
       "      <th>Writing_PassStatus</th>\n",
       "      <th>OverAll_PassStatus</th>\n",
       "      <th>Grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>bachelor's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>72</td>\n",
       "      <td>72</td>\n",
       "      <td>74</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>female</td>\n",
       "      <td>group C</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>completed</td>\n",
       "      <td>group1</td>\n",
       "      <td>69</td>\n",
       "      <td>90</td>\n",
       "      <td>88</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>master's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>90</td>\n",
       "      <td>95</td>\n",
       "      <td>93</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>male</td>\n",
       "      <td>group A</td>\n",
       "      <td>associate's degree</td>\n",
       "      <td>free/reduced</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>47</td>\n",
       "      <td>57</td>\n",
       "      <td>44</td>\n",
       "      <td>F</td>\n",
       "      <td>P</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>male</td>\n",
       "      <td>group C</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>group1</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>75</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender race/ethnicity           education         lunch test preparation  \\\n",
       "0  female        group B   bachelor's degree      standard             none   \n",
       "1  female        group C        some college      standard        completed   \n",
       "2  female        group B     master's degree      standard             none   \n",
       "3    male        group A  associate's degree  free/reduced             none   \n",
       "4    male        group C        some college      standard             none   \n",
       "\n",
       "    group  math score  reading score  writing score Math_PassStatus  \\\n",
       "0  group1          72             72             74               P   \n",
       "1  group1          69             90             88               P   \n",
       "2  group1          90             95             93               P   \n",
       "3  group1          47             57             44               F   \n",
       "4  group1          76             78             75               P   \n",
       "\n",
       "  Reading_PassStatus Writing_PassStatus OverAll_PassStatus Grade  \n",
       "0                  P                  P                  P     C  \n",
       "1                  P                  P                  P     B  \n",
       "2                  P                  P                  P     A  \n",
       "3                  P                  F                  F     F  \n",
       "4                  P                  P                  P     C  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def GetGrade(average_mark):\n",
    "    if average_mark  // 10 < 5:\n",
    "        return 'F'\n",
    "    elif average_mark // 10 < 6:\n",
    "        return 'E'\n",
    "    elif average_mark // 10 < 7:\n",
    "        return 'D'\n",
    "    elif average_mark // 10 < 8:\n",
    "        return 'C'\n",
    "    elif (average_mark // 10 < 9) or (average_mark == 90):\n",
    "        return 'B'\n",
    "    elif average_mark > 90:\n",
    "        return 'A'\n",
    "df['Grade'] = ((df['math score']+df['writing score']+df['reading score'])/3).apply(lambda x: GetGrade(x))\n",
    "df.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}