Handle Missing Values.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark = SparkSession.builder.appName('Missing Data').getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read.csv('ContainsNull.csv', inferSchema=True, header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+-----+\n",
      "|  Id| Name|Sales|\n",
      "+----+-----+-----+\n",
      "|emp1| John| null|\n",
      "|emp2| null| null|\n",
      "|emp3| null|345.0|\n",
      "|emp4|Cindy|456.0|\n",
      "+----+-----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- Id: string (nullable = true)\n",
      " |-- Name: string (nullable = true)\n",
      " |-- Sales: double (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We will handle missing data in this notebook. There are three ways to handle missing data:\n",
    "# 1. Drop the row\n",
    "# 2. Keep the data\n",
    "# 3. Replace null values with someother values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+-----+\n",
      "|  Id| Name|Sales|\n",
      "+----+-----+-----+\n",
      "|emp4|Cindy|456.0|\n",
      "+----+-----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# DROPPING MISSING VALUES\n",
    "df.na.drop().show() # so if we delete na values it will delete all rows which contain atleast one null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+-----+\n",
      "|  Id| Name|Sales|\n",
      "+----+-----+-----+\n",
      "|emp1| John| null|\n",
      "|emp2| null| null|\n",
      "|emp3| null|345.0|\n",
      "|emp4|Cindy|456.0|\n",
      "+----+-----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show() # you can still see the column as drop is not a inplace function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+-----+\n",
      "|  Id| Name|Sales|\n",
      "+----+-----+-----+\n",
      "|emp1| John| null|\n",
      "|emp3| null|345.0|\n",
      "|emp4|Cindy|456.0|\n",
      "+----+-----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# We can also define a threshold value\n",
    "df.na.drop(thresh=2).show() # rows with 2 or more null values will be deleted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+-----+\n",
      "|  Id| Name|Sales|\n",
      "+----+-----+-----+\n",
      "|emp4|Cindy|456.0|\n",
      "+----+-----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# There is one more parameter \"how\". default value is any. This means if row has any null value\n",
    "# then row willbe deleted.\n",
    "df.na.drop(how='any').show() # in this case you dont have to mention how."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+-----+\n",
      "|  Id| Name|Sales|\n",
      "+----+-----+-----+\n",
      "|emp1| John| null|\n",
      "|emp2| null| null|\n",
      "|emp3| null|345.0|\n",
      "|emp4|Cindy|456.0|\n",
      "+----+-----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# You can also assign 'all' value to how param.\n",
    "df.na.drop(how='all').show() # Delete only if all column values are null in row\n",
    "# So here every column has atleast Id so no rows was deleted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+-----+\n",
      "|  Id| Name|Sales|\n",
      "+----+-----+-----+\n",
      "|emp3| null|345.0|\n",
      "|emp4|Cindy|456.0|\n",
      "+----+-----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# you can also check missing values for a particular colum\n",
    "df.na.drop(subset=['Sales']).show() # so here we can see Name is null but it doesnt matter\n",
    "#  it will only delete if Sales value is null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fill missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+--------------+-----+\n",
      "|  Id|          Name|Sales|\n",
      "+----+--------------+-----+\n",
      "|emp1|          John| null|\n",
      "|emp2|MISSING_VALUES| null|\n",
      "|emp3|MISSING_VALUES|345.0|\n",
      "|emp4|         Cindy|456.0|\n",
      "+----+--------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.na.fill('MISSING_VALUES', subset=['Name']).show() # it will fill MISSING_VALUES text to Name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# lets replace null values in Sales to mean value\n",
    "mean_data = df.agg({'Sales': 'mean'}).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_sales = mean_data[0][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "400.5"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean_sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+-----+\n",
      "|  Id| Name|Sales|\n",
      "+----+-----+-----+\n",
      "|emp1| John|400.5|\n",
      "|emp2| null|400.5|\n",
      "|emp3| null|345.0|\n",
      "|emp4|Cindy|456.0|\n",
      "+----+-----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.na.fill(mean_sales, subset=['Sales']).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}