data_clean_lesson_2.ipynb

{
 "cells": [
  {
   "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>1</th>\n",
       "      <th>Mickéy Mousé</th>\n",
       "      <th>56</th>\n",
       "      <th>70kgs</th>\n",
       "      <th>72</th>\n",
       "      <th>69</th>\n",
       "      <th>71</th>\n",
       "      <th>-</th>\n",
       "      <th>-.1</th>\n",
       "      <th>-.2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.0</td>\n",
       "      <td>Donald Duck</td>\n",
       "      <td>34.0</td>\n",
       "      <td>154.89lbs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>85</td>\n",
       "      <td>84</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.0</td>\n",
       "      <td>Mini Mouse</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>65</td>\n",
       "      <td>69</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.0</td>\n",
       "      <td>Scrooge McDuck</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>78</td>\n",
       "      <td>79</td>\n",
       "      <td>72</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5.0</td>\n",
       "      <td>Pink Panther</td>\n",
       "      <td>54.0</td>\n",
       "      <td>198.658lbs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>69</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6.0</td>\n",
       "      <td>Huey McDuck</td>\n",
       "      <td>52.0</td>\n",
       "      <td>189lbs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>68</td>\n",
       "      <td>75</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     1    Mickéy Mousé    56       70kgs  72  69  71   -  -.1 -.2\n",
       "0  2.0     Donald Duck  34.0   154.89lbs   -   -   -  85   84  76\n",
       "1  3.0      Mini Mouse  16.0         NaN   -   -   -  65   69  72\n",
       "2  4.0  Scrooge McDuck   NaN       78kgs  78  79  72   -    -   -\n",
       "3  5.0    Pink Panther  54.0  198.658lbs   -   -   -  69  NaN  75\n",
       "4  6.0     Huey McDuck  52.0      189lbs   -   -   -  68   75  72"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_csv('DataSource/patient_heart_rate.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Clean Objective:\n",
    "# Missing headers in the csv file\n",
    "# Multiple variables are stored in one column\n",
    "# Column data contains inconsistent unit values\n",
    "# Missing values\n",
    "# An empty row in the data\n",
    "# Duplicate records in the data\n",
    "# Non-ASCII characters\n",
    "# Column headers are values and not variable names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>Id</th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Weight</th>\n",
       "      <th>m0006</th>\n",
       "      <th>m0612</th>\n",
       "      <th>m1218</th>\n",
       "      <th>f0006</th>\n",
       "      <th>f0612</th>\n",
       "      <th>f1218</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Mickéy Mousé</td>\n",
       "      <td>56.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>72</td>\n",
       "      <td>69</td>\n",
       "      <td>71</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>Donald Duck</td>\n",
       "      <td>34.0</td>\n",
       "      <td>154.89lbs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>85</td>\n",
       "      <td>84</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>Mini Mouse</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>65</td>\n",
       "      <td>69</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>Scrooge McDuck</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>78</td>\n",
       "      <td>79</td>\n",
       "      <td>72</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>Pink Panther</td>\n",
       "      <td>54.0</td>\n",
       "      <td>198.658lbs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>69</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id            Name   Age      Weight m0006 m0612 m1218 f0006 f0612 f1218\n",
       "0  1.0    Mickéy Mousé  56.0       70kgs    72    69    71     -     -     -\n",
       "1  2.0     Donald Duck  34.0   154.89lbs     -     -     -    85    84    76\n",
       "2  3.0      Mini Mouse  16.0         NaN     -     -     -    65    69    72\n",
       "3  4.0  Scrooge McDuck   NaN       78kgs    78    79    72     -     -     -\n",
       "4  5.0    Pink Panther  54.0  198.658lbs     -     -     -    69   NaN    75"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "column_names= [\"Id\", \"Name\", \"Age\", \"Weight\",'m0006','m0612','m1218','f0006','f0612','f1218']\n",
    "\n",
    "df = pd.read_csv(\"DataSource/patient_heart_rate.csv\", names = column_names)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>Id</th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Weight</th>\n",
       "      <th>m0006</th>\n",
       "      <th>m0612</th>\n",
       "      <th>m1218</th>\n",
       "      <th>f0006</th>\n",
       "      <th>f0612</th>\n",
       "      <th>f1218</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Mickéy Mousé</td>\n",
       "      <td>56.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>72</td>\n",
       "      <td>69</td>\n",
       "      <td>71</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>Donald Duck</td>\n",
       "      <td>34.0</td>\n",
       "      <td>154.89lbs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>85</td>\n",
       "      <td>84</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>Mini Mouse</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>65</td>\n",
       "      <td>69</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>Scrooge McDuck</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>78</td>\n",
       "      <td>79</td>\n",
       "      <td>72</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>Pink Panther</td>\n",
       "      <td>54.0</td>\n",
       "      <td>198.658lbs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>69</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6.0</td>\n",
       "      <td>Huey McDuck</td>\n",
       "      <td>52.0</td>\n",
       "      <td>189lbs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>68</td>\n",
       "      <td>75</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7.0</td>\n",
       "      <td>Dewey McDuck</td>\n",
       "      <td>19.0</td>\n",
       "      <td>56kgs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>71</td>\n",
       "      <td>78</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8.0</td>\n",
       "      <td>Scööpy Doo</td>\n",
       "      <td>32.0</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>78</td>\n",
       "      <td>76</td>\n",
       "      <td>75</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9.0</td>\n",
       "      <td>Huey McDuck</td>\n",
       "      <td>52.0</td>\n",
       "      <td>189lbs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>68</td>\n",
       "      <td>75</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10.0</td>\n",
       "      <td>Louie McDuck</td>\n",
       "      <td>12.0</td>\n",
       "      <td>45kgs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>92</td>\n",
       "      <td>95</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Id            Name   Age      Weight m0006 m0612 m1218 f0006 f0612 f1218\n",
       "0    1.0    Mickéy Mousé  56.0       70kgs    72    69    71     -     -     -\n",
       "1    2.0     Donald Duck  34.0   154.89lbs     -     -     -    85    84    76\n",
       "2    3.0      Mini Mouse  16.0         NaN     -     -     -    65    69    72\n",
       "3    4.0  Scrooge McDuck   NaN       78kgs    78    79    72     -     -     -\n",
       "4    5.0    Pink Panther  54.0  198.658lbs     -     -     -    69   NaN    75\n",
       "5    6.0     Huey McDuck  52.0      189lbs     -     -     -    68    75    72\n",
       "6    7.0    Dewey McDuck  19.0       56kgs     -     -     -    71    78    75\n",
       "7    8.0      Scööpy Doo  32.0       78kgs    78    76    75     -     -     -\n",
       "8    NaN             NaN   NaN         NaN   NaN   NaN   NaN   NaN   NaN   NaN\n",
       "9    9.0     Huey McDuck  52.0      189lbs     -     -     -    68    75    72\n",
       "10  10.0    Louie McDuck  12.0       45kgs     -     -     -    92    95    87"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Solved: Missing headers in the csv file \n",
    "df.head(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      Id   Age      Weight m0006 m0612 m1218 f0006 f0612 f1218 Firstname  \\\n",
      "0    1.0  56.0       70kgs    72    69    71     -     -     -    Mickéy   \n",
      "1    2.0  34.0   154.89lbs     -     -     -    85    84    76    Donald   \n",
      "2    3.0  16.0         NaN     -     -     -    65    69    72      Mini   \n",
      "3    4.0   NaN       78kgs    78    79    72     -     -     -   Scrooge   \n",
      "4    5.0  54.0  198.658lbs     -     -     -    69   NaN    75      Pink   \n",
      "5    6.0  52.0      189lbs     -     -     -    68    75    72      Huey   \n",
      "6    7.0  19.0       56kgs     -     -     -    71    78    75     Dewey   \n",
      "7    8.0  32.0       78kgs    78    76    75     -     -     -    Scööpy   \n",
      "8    NaN   NaN         NaN   NaN   NaN   NaN   NaN   NaN   NaN       NaN   \n",
      "9    9.0  52.0      189lbs     -     -     -    68    75    72      Huey   \n",
      "10  10.0  12.0       45kgs     -     -     -    92    95    87     Louie   \n",
      "\n",
      "   Lastname  \n",
      "0     Mousé  \n",
      "1      Duck  \n",
      "2     Mouse  \n",
      "3    McDuck  \n",
      "4   Panther  \n",
      "5    McDuck  \n",
      "6    McDuck  \n",
      "7       Doo  \n",
      "8       NaN  \n",
      "9    McDuck  \n",
      "10   McDuck  \n"
     ]
    }
   ],
   "source": [
    "# The Name column contains two variables Lastname and Firstname\n",
    "# To respect the tidy principles, we want to isolate each variable in a single column.\n",
    "\n",
    "df[['Firstname','Lastname']] = df['Name'].str.split(expand=True)\n",
    "df = df.drop('Name', axis=1)\n",
    "print (df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>Id</th>\n",
       "      <th>Age</th>\n",
       "      <th>Weight</th>\n",
       "      <th>m0006</th>\n",
       "      <th>m0612</th>\n",
       "      <th>m1218</th>\n",
       "      <th>f0006</th>\n",
       "      <th>f0612</th>\n",
       "      <th>f1218</th>\n",
       "      <th>Firstname</th>\n",
       "      <th>Lastname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>72</td>\n",
       "      <td>69</td>\n",
       "      <td>71</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>Mickéy</td>\n",
       "      <td>Mousé</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>85</td>\n",
       "      <td>84</td>\n",
       "      <td>76</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>65</td>\n",
       "      <td>69</td>\n",
       "      <td>72</td>\n",
       "      <td>Mini</td>\n",
       "      <td>Mouse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>78</td>\n",
       "      <td>79</td>\n",
       "      <td>72</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>Scrooge</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>90kgs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>69</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "      <td>Pink</td>\n",
       "      <td>Panther</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>85kgs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>68</td>\n",
       "      <td>75</td>\n",
       "      <td>72</td>\n",
       "      <td>Huey</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>56kgs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>71</td>\n",
       "      <td>78</td>\n",
       "      <td>75</td>\n",
       "      <td>Dewey</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>78</td>\n",
       "      <td>76</td>\n",
       "      <td>75</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>Scööpy</td>\n",
       "      <td>Doo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>85kgs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>68</td>\n",
       "      <td>75</td>\n",
       "      <td>72</td>\n",
       "      <td>Huey</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>45kgs</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>92</td>\n",
       "      <td>95</td>\n",
       "      <td>87</td>\n",
       "      <td>Louie</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Id   Age Weight m0006 m0612 m1218 f0006 f0612 f1218 Firstname Lastname\n",
       "0    1.0  56.0  70kgs    72    69    71     -     -     -    Mickéy    Mousé\n",
       "1    2.0  34.0  70kgs     -     -     -    85    84    76    Donald     Duck\n",
       "2    3.0  16.0    NaN     -     -     -    65    69    72      Mini    Mouse\n",
       "3    4.0   NaN  78kgs    78    79    72     -     -     -   Scrooge   McDuck\n",
       "4    5.0  54.0  90kgs     -     -     -    69   NaN    75      Pink  Panther\n",
       "5    6.0  52.0  85kgs     -     -     -    68    75    72      Huey   McDuck\n",
       "6    7.0  19.0  56kgs     -     -     -    71    78    75     Dewey   McDuck\n",
       "7    8.0  32.0  78kgs    78    76    75     -     -     -    Scööpy      Doo\n",
       "8    NaN   NaN    NaN   NaN   NaN   NaN   NaN   NaN   NaN       NaN      NaN\n",
       "9    9.0  52.0  85kgs     -     -     -    68    75    72      Huey   McDuck\n",
       "10  10.0  12.0  45kgs     -     -     -    92    95    87     Louie   McDuck"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Solved: Multiple variables are stored in one column\n",
    "# Problem:\n",
    "# The way the measurement unit was written for Weight column is not consistent.\n",
    "# The second and seventh row contains data for inconsistent unit values (kgs,lbs)\n",
    "\n",
    "weight = df['Weight']\n",
    " \n",
    "for i in range (0 ,len(weight)):    \n",
    "    x= str(weight[i])\n",
    "    #Incase lbs is part of observation remove it\n",
    "    if \"lbs\" in x[-3:]:\n",
    "        #Remove the lbs from the value\n",
    "        x = x[:-3:]\n",
    "        #Convert string to float\n",
    "        float_x = float(x)\n",
    "        #Covert to kgs and store as int\n",
    "        y =int(float_x/2.2)\n",
    "        #Convert back to string\n",
    "        y = str(y)+\"kgs\"\n",
    "        weight[i]= y\n",
    "\n",
    "df.head(100)\n",
    "\n",
    "# Solved: Column data contains inconsistent unit values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>Id</th>\n",
       "      <th>Age</th>\n",
       "      <th>Weight</th>\n",
       "      <th>m0006</th>\n",
       "      <th>m0612</th>\n",
       "      <th>m1218</th>\n",
       "      <th>f0006</th>\n",
       "      <th>f0612</th>\n",
       "      <th>f1218</th>\n",
       "      <th>Firstname</th>\n",
       "      <th>Lastname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>72</td>\n",
       "      <td>69</td>\n",
       "      <td>71</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Mickéy</td>\n",
       "      <td>Mousé</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>85</td>\n",
       "      <td>84</td>\n",
       "      <td>76</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65</td>\n",
       "      <td>69</td>\n",
       "      <td>72</td>\n",
       "      <td>Mini</td>\n",
       "      <td>Mouse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>78</td>\n",
       "      <td>79</td>\n",
       "      <td>72</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Scrooge</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>90kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>69</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "      <td>Pink</td>\n",
       "      <td>Panther</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>85kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68</td>\n",
       "      <td>75</td>\n",
       "      <td>72</td>\n",
       "      <td>Huey</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>56kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71</td>\n",
       "      <td>78</td>\n",
       "      <td>75</td>\n",
       "      <td>Dewey</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>78</td>\n",
       "      <td>76</td>\n",
       "      <td>75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Scööpy</td>\n",
       "      <td>Doo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>85kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68</td>\n",
       "      <td>75</td>\n",
       "      <td>72</td>\n",
       "      <td>Huey</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>45kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92</td>\n",
       "      <td>95</td>\n",
       "      <td>87</td>\n",
       "      <td>Louie</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Id   Age Weight m0006 m0612 m1218 f0006 f0612 f1218 Firstname Lastname\n",
       "0    1.0  56.0  70kgs    72    69    71   NaN   NaN   NaN    Mickéy    Mousé\n",
       "1    2.0  34.0  70kgs   NaN   NaN   NaN    85    84    76    Donald     Duck\n",
       "2    3.0  16.0    NaN   NaN   NaN   NaN    65    69    72      Mini    Mouse\n",
       "3    4.0   NaN  78kgs    78    79    72   NaN   NaN   NaN   Scrooge   McDuck\n",
       "4    5.0  54.0  90kgs   NaN   NaN   NaN    69   NaN    75      Pink  Panther\n",
       "5    6.0  52.0  85kgs   NaN   NaN   NaN    68    75    72      Huey   McDuck\n",
       "6    7.0  19.0  56kgs   NaN   NaN   NaN    71    78    75     Dewey   McDuck\n",
       "7    8.0  32.0  78kgs    78    76    75   NaN   NaN   NaN    Scööpy      Doo\n",
       "8    NaN   NaN    NaN   NaN   NaN   NaN   NaN   NaN   NaN       NaN      NaN\n",
       "9    9.0  52.0  85kgs   NaN   NaN   NaN    68    75    72      Huey   McDuck\n",
       "10  10.0  12.0  45kgs   NaN   NaN   NaN    92    95    87     Louie   McDuck"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Problem: \n",
    "# There are few missing values in the Age, Weight and Heart Rate values in the dataset\n",
    "\n",
    "df.replace('-', np.nan, inplace=True)\n",
    "df.head(100)\n",
    "\n",
    "# Solved: Empty Missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>Id</th>\n",
       "      <th>Age</th>\n",
       "      <th>Weight</th>\n",
       "      <th>m0006</th>\n",
       "      <th>m0612</th>\n",
       "      <th>m1218</th>\n",
       "      <th>f0006</th>\n",
       "      <th>f0612</th>\n",
       "      <th>f1218</th>\n",
       "      <th>Firstname</th>\n",
       "      <th>Lastname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>72</td>\n",
       "      <td>69</td>\n",
       "      <td>71</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Mickéy</td>\n",
       "      <td>Mousé</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>85</td>\n",
       "      <td>84</td>\n",
       "      <td>76</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65</td>\n",
       "      <td>69</td>\n",
       "      <td>72</td>\n",
       "      <td>Mini</td>\n",
       "      <td>Mouse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>78</td>\n",
       "      <td>79</td>\n",
       "      <td>72</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Scrooge</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>90kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>69</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "      <td>Pink</td>\n",
       "      <td>Panther</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>85kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68</td>\n",
       "      <td>75</td>\n",
       "      <td>72</td>\n",
       "      <td>Huey</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>56kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71</td>\n",
       "      <td>78</td>\n",
       "      <td>75</td>\n",
       "      <td>Dewey</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>78</td>\n",
       "      <td>76</td>\n",
       "      <td>75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Scööpy</td>\n",
       "      <td>Doo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>85kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68</td>\n",
       "      <td>75</td>\n",
       "      <td>72</td>\n",
       "      <td>Huey</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>45kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92</td>\n",
       "      <td>95</td>\n",
       "      <td>87</td>\n",
       "      <td>Louie</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Id   Age Weight m0006 m0612 m1218 f0006 f0612 f1218 Firstname Lastname\n",
       "0    1.0  56.0  70kgs    72    69    71   NaN   NaN   NaN    Mickéy    Mousé\n",
       "1    2.0  34.0  70kgs   NaN   NaN   NaN    85    84    76    Donald     Duck\n",
       "2    3.0  16.0    NaN   NaN   NaN   NaN    65    69    72      Mini    Mouse\n",
       "3    4.0   NaN  78kgs    78    79    72   NaN   NaN   NaN   Scrooge   McDuck\n",
       "4    5.0  54.0  90kgs   NaN   NaN   NaN    69   NaN    75      Pink  Panther\n",
       "5    6.0  52.0  85kgs   NaN   NaN   NaN    68    75    72      Huey   McDuck\n",
       "6    7.0  19.0  56kgs   NaN   NaN   NaN    71    78    75     Dewey   McDuck\n",
       "7    8.0  32.0  78kgs    78    76    75   NaN   NaN   NaN    Scööpy      Doo\n",
       "9    9.0  52.0  85kgs   NaN   NaN   NaN    68    75    72      Huey   McDuck\n",
       "10  10.0  12.0  45kgs   NaN   NaN   NaN    92    95    87     Louie   McDuck"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Problem: If you observe in the dataset, you can see that there is a Empty row\n",
    "\n",
    "df.dropna(how=\"all\", inplace=True)\n",
    "df.head(100)\n",
    "\n",
    "# Solved: An empty row in the data"
   ]
  },
  {
   "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>Id</th>\n",
       "      <th>Age</th>\n",
       "      <th>Weight</th>\n",
       "      <th>m0006</th>\n",
       "      <th>m0612</th>\n",
       "      <th>m1218</th>\n",
       "      <th>f0006</th>\n",
       "      <th>f0612</th>\n",
       "      <th>f1218</th>\n",
       "      <th>Firstname</th>\n",
       "      <th>Lastname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>72</td>\n",
       "      <td>69</td>\n",
       "      <td>71</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Mickéy</td>\n",
       "      <td>Mousé</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>85</td>\n",
       "      <td>84</td>\n",
       "      <td>76</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65</td>\n",
       "      <td>69</td>\n",
       "      <td>72</td>\n",
       "      <td>Mini</td>\n",
       "      <td>Mouse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>78</td>\n",
       "      <td>79</td>\n",
       "      <td>72</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Scrooge</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>90kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>69</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "      <td>Pink</td>\n",
       "      <td>Panther</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>85kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68</td>\n",
       "      <td>75</td>\n",
       "      <td>72</td>\n",
       "      <td>Huey</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>56kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71</td>\n",
       "      <td>78</td>\n",
       "      <td>75</td>\n",
       "      <td>Dewey</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>78</td>\n",
       "      <td>76</td>\n",
       "      <td>75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Scööpy</td>\n",
       "      <td>Doo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>45kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92</td>\n",
       "      <td>95</td>\n",
       "      <td>87</td>\n",
       "      <td>Louie</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Id   Age Weight m0006 m0612 m1218 f0006 f0612 f1218 Firstname Lastname\n",
       "0    1.0  56.0  70kgs    72    69    71   NaN   NaN   NaN    Mickéy    Mousé\n",
       "1    2.0  34.0  70kgs   NaN   NaN   NaN    85    84    76    Donald     Duck\n",
       "2    3.0  16.0    NaN   NaN   NaN   NaN    65    69    72      Mini    Mouse\n",
       "3    4.0   NaN  78kgs    78    79    72   NaN   NaN   NaN   Scrooge   McDuck\n",
       "4    5.0  54.0  90kgs   NaN   NaN   NaN    69   NaN    75      Pink  Panther\n",
       "5    6.0  52.0  85kgs   NaN   NaN   NaN    68    75    72      Huey   McDuck\n",
       "6    7.0  19.0  56kgs   NaN   NaN   NaN    71    78    75     Dewey   McDuck\n",
       "7    8.0  32.0  78kgs    78    76    75   NaN   NaN   NaN    Scööpy      Doo\n",
       "10  10.0  12.0  45kgs   NaN   NaN   NaN    92    95    87     Louie   McDuck"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Problem\n",
    "# Sometimes, in the dataset there may be some duplicate records. \n",
    "# In our dataset there is a record which is repeated in 6th and 9th rows\n",
    "\n",
    "df = df.drop_duplicates(subset=['Firstname','Lastname'])\n",
    "df.head(10)\n",
    "\n",
    "# Solved: Duplicate records in the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>Id</th>\n",
       "      <th>Age</th>\n",
       "      <th>Weight</th>\n",
       "      <th>m0006</th>\n",
       "      <th>m0612</th>\n",
       "      <th>m1218</th>\n",
       "      <th>f0006</th>\n",
       "      <th>f0612</th>\n",
       "      <th>f1218</th>\n",
       "      <th>Firstname</th>\n",
       "      <th>Lastname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>72</td>\n",
       "      <td>69</td>\n",
       "      <td>71</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Micky</td>\n",
       "      <td>Mous</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>85</td>\n",
       "      <td>84</td>\n",
       "      <td>76</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65</td>\n",
       "      <td>69</td>\n",
       "      <td>72</td>\n",
       "      <td>Mini</td>\n",
       "      <td>Mouse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>78</td>\n",
       "      <td>79</td>\n",
       "      <td>72</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Scrooge</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>90kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>69</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "      <td>Pink</td>\n",
       "      <td>Panther</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>85kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68</td>\n",
       "      <td>75</td>\n",
       "      <td>72</td>\n",
       "      <td>Huey</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>56kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71</td>\n",
       "      <td>78</td>\n",
       "      <td>75</td>\n",
       "      <td>Dewey</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>78</td>\n",
       "      <td>76</td>\n",
       "      <td>75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Scpy</td>\n",
       "      <td>Doo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>45kgs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92</td>\n",
       "      <td>95</td>\n",
       "      <td>87</td>\n",
       "      <td>Louie</td>\n",
       "      <td>McDuck</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Id   Age Weight m0006 m0612 m1218 f0006 f0612 f1218 Firstname Lastname\n",
       "0    1.0  56.0  70kgs    72    69    71   NaN   NaN   NaN     Micky     Mous\n",
       "1    2.0  34.0  70kgs   NaN   NaN   NaN    85    84    76    Donald     Duck\n",
       "2    3.0  16.0    NaN   NaN   NaN   NaN    65    69    72      Mini    Mouse\n",
       "3    4.0   NaN  78kgs    78    79    72   NaN   NaN   NaN   Scrooge   McDuck\n",
       "4    5.0  54.0  90kgs   NaN   NaN   NaN    69   NaN    75      Pink  Panther\n",
       "5    6.0  52.0  85kgs   NaN   NaN   NaN    68    75    72      Huey   McDuck\n",
       "6    7.0  19.0  56kgs   NaN   NaN   NaN    71    78    75     Dewey   McDuck\n",
       "7    8.0  32.0  78kgs    78    76    75   NaN   NaN   NaN      Scpy      Doo\n",
       "10  10.0  12.0  45kgs   NaN   NaN   NaN    92    95    87     Louie   McDuck"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Problem\n",
    "# There are few non-ASCII characters in the Firstname and Lastname columns in the data\n",
    "\n",
    "df.Firstname.replace({r'[^\\x00-\\x7F]+':''}, regex=True, inplace=True)\n",
    "df.Lastname.replace({r'[^\\x00-\\x7F]+':''}, regex=True, inplace=True)\n",
    "\n",
    "df.head(10)\n",
    "\n",
    "# Solved: Non-ASCII characters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>Id</th>\n",
       "      <th>Age</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Firstname</th>\n",
       "      <th>Lastname</th>\n",
       "      <th>PulseRate</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>Micky</td>\n",
       "      <td>Mous</td>\n",
       "      <td>72</td>\n",
       "      <td>m</td>\n",
       "      <td>00-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>Micky</td>\n",
       "      <td>Mous</td>\n",
       "      <td>69</td>\n",
       "      <td>m</td>\n",
       "      <td>06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>Micky</td>\n",
       "      <td>Mous</td>\n",
       "      <td>71</td>\n",
       "      <td>m</td>\n",
       "      <td>12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "      <td>85</td>\n",
       "      <td>f</td>\n",
       "      <td>00-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "      <td>84</td>\n",
       "      <td>f</td>\n",
       "      <td>06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>70kgs</td>\n",
       "      <td>Donald</td>\n",
       "      <td>Duck</td>\n",
       "      <td>76</td>\n",
       "      <td>f</td>\n",
       "      <td>12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>5.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>90kgs</td>\n",
       "      <td>Pink</td>\n",
       "      <td>Panther</td>\n",
       "      <td>69</td>\n",
       "      <td>f</td>\n",
       "      <td>00-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>5.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>90kgs</td>\n",
       "      <td>Pink</td>\n",
       "      <td>Panther</td>\n",
       "      <td>75</td>\n",
       "      <td>f</td>\n",
       "      <td>12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>6.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>85kgs</td>\n",
       "      <td>Huey</td>\n",
       "      <td>McDuck</td>\n",
       "      <td>68</td>\n",
       "      <td>f</td>\n",
       "      <td>00-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>6.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>85kgs</td>\n",
       "      <td>Huey</td>\n",
       "      <td>McDuck</td>\n",
       "      <td>75</td>\n",
       "      <td>f</td>\n",
       "      <td>06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>6.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>85kgs</td>\n",
       "      <td>Huey</td>\n",
       "      <td>McDuck</td>\n",
       "      <td>72</td>\n",
       "      <td>f</td>\n",
       "      <td>12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>7.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>56kgs</td>\n",
       "      <td>Dewey</td>\n",
       "      <td>McDuck</td>\n",
       "      <td>71</td>\n",
       "      <td>f</td>\n",
       "      <td>00-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>7.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>56kgs</td>\n",
       "      <td>Dewey</td>\n",
       "      <td>McDuck</td>\n",
       "      <td>78</td>\n",
       "      <td>f</td>\n",
       "      <td>06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>7.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>56kgs</td>\n",
       "      <td>Dewey</td>\n",
       "      <td>McDuck</td>\n",
       "      <td>75</td>\n",
       "      <td>f</td>\n",
       "      <td>12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>Scpy</td>\n",
       "      <td>Doo</td>\n",
       "      <td>78</td>\n",
       "      <td>m</td>\n",
       "      <td>00-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>8.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>Scpy</td>\n",
       "      <td>Doo</td>\n",
       "      <td>76</td>\n",
       "      <td>m</td>\n",
       "      <td>06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>8.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>78kgs</td>\n",
       "      <td>Scpy</td>\n",
       "      <td>Doo</td>\n",
       "      <td>75</td>\n",
       "      <td>m</td>\n",
       "      <td>12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>45kgs</td>\n",
       "      <td>Louie</td>\n",
       "      <td>McDuck</td>\n",
       "      <td>92</td>\n",
       "      <td>f</td>\n",
       "      <td>00-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>45kgs</td>\n",
       "      <td>Louie</td>\n",
       "      <td>McDuck</td>\n",
       "      <td>95</td>\n",
       "      <td>f</td>\n",
       "      <td>06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>45kgs</td>\n",
       "      <td>Louie</td>\n",
       "      <td>McDuck</td>\n",
       "      <td>87</td>\n",
       "      <td>f</td>\n",
       "      <td>12-18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Id   Age Weight Firstname Lastname PulseRate Sex   Time\n",
       "0    1.0  56.0  70kgs     Micky     Mous        72   m  00-06\n",
       "9    1.0  56.0  70kgs     Micky     Mous        69   m  06-12\n",
       "18   1.0  56.0  70kgs     Micky     Mous        71   m  12-18\n",
       "28   2.0  34.0  70kgs    Donald     Duck        85   f  00-06\n",
       "37   2.0  34.0  70kgs    Donald     Duck        84   f  06-12\n",
       "46   2.0  34.0  70kgs    Donald     Duck        76   f  12-18\n",
       "31   5.0  54.0  90kgs      Pink  Panther        69   f  00-06\n",
       "49   5.0  54.0  90kgs      Pink  Panther        75   f  12-18\n",
       "32   6.0  52.0  85kgs      Huey   McDuck        68   f  00-06\n",
       "41   6.0  52.0  85kgs      Huey   McDuck        75   f  06-12\n",
       "50   6.0  52.0  85kgs      Huey   McDuck        72   f  12-18\n",
       "33   7.0  19.0  56kgs     Dewey   McDuck        71   f  00-06\n",
       "42   7.0  19.0  56kgs     Dewey   McDuck        78   f  06-12\n",
       "51   7.0  19.0  56kgs     Dewey   McDuck        75   f  12-18\n",
       "7    8.0  32.0  78kgs      Scpy      Doo        78   m  00-06\n",
       "16   8.0  32.0  78kgs      Scpy      Doo        76   m  06-12\n",
       "25   8.0  32.0  78kgs      Scpy      Doo        75   m  12-18\n",
       "35  10.0  12.0  45kgs     Louie   McDuck        92   f  00-06\n",
       "44  10.0  12.0  45kgs     Louie   McDuck        95   f  06-12\n",
       "53  10.0  12.0  45kgs     Louie   McDuck        87   f  12-18"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Problem:\n",
    "# Column headers are values and not variable names\n",
    "# The columns headers are composed of the values \n",
    "# for Sex and Hour range variables for each individual row. \n",
    "# Corresponding to each ‘m’ column for males, there is also an ‘f’ \n",
    "# column for females, f0006, f0612 and so on\n",
    "\n",
    "#Melt the Sex + time range columns in single column\n",
    "df = pd.melt(df,id_vars=['Id','Age','Weight','Firstname','Lastname'], value_name=\"PulseRate\",var_name=\"sex_and_time\").sort_values(['Id','Age','Weight','Firstname','Lastname'])\n",
    " \n",
    "# Extract Sex, Hour lower bound and Hour upper bound group\n",
    "tmp_df = df[\"sex_and_time\"].str.extract(\"(\\D)(\\d+)(\\d{2})\",expand=True)\n",
    " \n",
    "# Name columns\n",
    "tmp_df.columns = [\"Sex\", \"hours_lower\", \"hours_upper\"]\n",
    " \n",
    "# Create Time column based on \"hours_lower\" and \"hours_upper\" columns\n",
    "tmp_df[\"Time\"] = tmp_df[\"hours_lower\"] + \"-\" + tmp_df[\"hours_upper\"]\n",
    " \n",
    "# Merge \n",
    "df = pd.concat([df, tmp_df], axis=1)\n",
    " \n",
    "# Drop unnecessary columns and rows\n",
    "df = df.drop(['sex_and_time','hours_lower','hours_upper'], axis=1)\n",
    "df = df.dropna()\n",
    "df.to_csv('DataSource/patient_heart_rate_outputcleanup.csv',index=False)\n",
    "df.head(30)"
   ]
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}