Day 23 - 50 Days of Data Analysis .ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "31c00962",
   "metadata": {},
   "source": [
    "## 1. \n",
    "\n",
    "#### Create a pandas DataFrame from the lists above. Write a code to check if there are any missing values in the DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "83383b54",
   "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>Names</th>\n",
       "      <th>Grades</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kelly</td>\n",
       "      <td>30.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>40.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jon</td>\n",
       "      <td>30.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ken</td>\n",
       "      <td>67.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tim</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Pel</td>\n",
       "      <td>55.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Names  Grades   Age\n",
       "0  Kelly    30.0  15.0\n",
       "1    NaN    40.0   NaN\n",
       "2    Jon    30.0  18.0\n",
       "3    Ken    67.0  17.0\n",
       "4    Tim     NaN   NaN\n",
       "5    Pel    55.0  16.0"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "\n",
    "names = [\"Kelly\", np.nan, 'Jon', 'Ken', 'Tim', 'Pel']\n",
    "grades = [30, 40, 30, 67, np.nan, 55]\n",
    "age =[15, np.nan, 18, 17, np.nan, 16]\n",
    "\n",
    "df = pd.DataFrame({'Names': names,'Grades':grades,'Age': age})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "82463e6f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Names     1\n",
       "Grades    1\n",
       "Age       2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking for missing values\n",
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e57d0504",
   "metadata": {},
   "source": [
    "## 2.\n",
    "#### Your supervisor has asked that you show them how to fill the missing values, using the library scikit-learn. Write a code to fill missing values in the DataFrame above using this library. Use the most_frequent strategy for the \"Names\" column. Mean strategy for the \"Grades\" column. Median strategy for the \"Age\" column. Make a copy of the original DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "66fd26bb",
   "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>Names</th>\n",
       "      <th>Grades</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kelly</td>\n",
       "      <td>30.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Paul</td>\n",
       "      <td>40.0</td>\n",
       "      <td>16.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jon</td>\n",
       "      <td>30.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ken</td>\n",
       "      <td>67.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tim</td>\n",
       "      <td>44.4</td>\n",
       "      <td>16.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Pel</td>\n",
       "      <td>55.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Names  Grades   Age\n",
       "0  Kelly    30.0  15.0\n",
       "1   Paul    40.0  16.5\n",
       "2    Jon    30.0  18.0\n",
       "3    Ken    67.0  17.0\n",
       "4    Tim    44.4  16.5\n",
       "5    Pel    55.0  16.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.impute import SimpleImputer\n",
    "\n",
    "# Create a copy of the DataFrame\n",
    "df2 = df.copy()\n",
    "\n",
    "# create an instance of SimpleImputer with different strategies for each column\n",
    "imputer_names = SimpleImputer( strategy='constant',fill_value=\"Paul\",)\n",
    "imputer_grades = SimpleImputer(strategy='mean')\n",
    "imputer_age = SimpleImputer(strategy='median')\n",
    "\n",
    "# fit and transform the imputer on selected columns\n",
    "df2[['Names']] = imputer_names.fit_transform(df2[['Names']])\n",
    "df2[['Grades']] = imputer_grades.fit_transform(df2[['Grades']])\n",
    "df2[['Age']] = imputer_age.fit_transform(df2[['Age']])\n",
    "\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3710b67a",
   "metadata": {},
   "source": [
    "## 3.\n",
    "\n",
    "#### Using the original DataFrame (question 1) with missing values, identify and drop any columns that have more than 30% missing values. Save this to a new variable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4d483c4a",
   "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>Names</th>\n",
       "      <th>Grades</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kelly</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jon</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ken</td>\n",
       "      <td>67.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tim</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Pel</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Names  Grades\n",
       "0  Kelly    30.0\n",
       "1    NaN    40.0\n",
       "2    Jon    30.0\n",
       "3    Ken    67.0\n",
       "4    Tim     NaN\n",
       "5    Pel    55.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check percentage of missing values in each column\n",
    "missing_values = df.isnull().mean()\n",
    "\n",
    "# Select columns that have more than 50% missing values\n",
    "columns_to_drop = missing_values[missing_values > 0.30].index\n",
    "\n",
    "# Drop columns from the dataframe\n",
    "df_cleaned = df.drop(columns_to_drop, axis=1)\n",
    "\n",
    "df_cleaned"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9eb210d",
   "metadata": {},
   "source": [
    "## 4.\t\n",
    "\n",
    "#### Using pandas, check for any duplicate values in the \"Names\" columns of your DataFrame ( question 3)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7ce11df3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for duplicated values\n",
    "df_cleaned.duplicated(\"Names\").sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54f67153",
   "metadata": {},
   "source": [
    "## 5.\t\n",
    "\n",
    "#### Use the pandas groupby() method and agg() method to calculate the mean, minimum, and maximum of the \"Grades\" column, grouped by the \"Names\" column. Use the a copy of the DataFrame you saved in question 2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6ece3554",
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">Grades</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Names</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Jon</th>\n",
       "      <td>30.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kelly</th>\n",
       "      <td>30.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ken</th>\n",
       "      <td>67.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>67.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Paul</th>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pel</th>\n",
       "      <td>55.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tim</th>\n",
       "      <td>44.4</td>\n",
       "      <td>44.4</td>\n",
       "      <td>44.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Grades            \n",
       "        mean   min   max\n",
       "Names                   \n",
       "Jon     30.0  30.0  30.0\n",
       "Kelly   30.0  30.0  30.0\n",
       "Ken     67.0  67.0  67.0\n",
       "Paul    40.0  40.0  40.0\n",
       "Pel     55.0  55.0  55.0\n",
       "Tim     44.4  44.4  44.4"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate mean, min, and max of 'Grades' column grouped by 'Age' column\n",
    "sch_data_agg = df2.groupby('Names').agg({'Grades': ['mean', 'min', 'max']})\n",
    "sch_data_agg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9d845a6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}