Missing_Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Missing Data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "# May take a little while on a local computer\n",
    "spark = SparkSession.builder.appName(\"missingdata\").getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = spark.read.csv(\"ContainsNull.csv\",header=True,inferSchema=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice how the data remains as a null."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Drop the missing data\n",
    "\n",
    "One can use the .na functions for missing data. The drop command has the following parameters:\n",
    "\n",
    "    df.na.drop(how='any', thresh=None, subset=None)\n",
    "    \n",
    "    * param how: 'any' or 'all'.\n",
    "    \n",
    "        If 'any', drop a row if it contains any nulls.\n",
    "        If 'all', drop a row only if all its values are null.\n",
    "    \n",
    "    * param thresh: int, default None\n",
    "    \n",
    "        If specified, drop rows that have less than `thresh` non-null values.\n",
    "        This overwrites the `how` parameter.\n",
    "        \n",
    "    * param subset: \n",
    "        optional list of column names to consider."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+-----+\n",
      "|  Id| Name|Sales|\n",
      "+----+-----+-----+\n",
      "|emp4|Cindy|456.0|\n",
      "+----+-----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Drop any row that contains missing data\n",
    "df.na.drop().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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": [
    "# Has to have at least 2 NON-null values\n",
    "df.na.drop(thresh=2).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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": [
    "df.na.drop(subset=[\"Sales\"]).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+-----+\n",
      "|  Id| Name|Sales|\n",
      "+----+-----+-----+\n",
      "|emp4|Cindy|456.0|\n",
      "+----+-----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.na.drop(how='any').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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.na.drop(how='all').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fill the missing values\n",
    "\n",
    "We can also fill the missing values with new values. If you have multiple nulls across multiple data types, Spark is actually smart enough to match up the data types. For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+---------+-----+\n",
      "|  Id|     Name|Sales|\n",
      "+----+---------+-----+\n",
      "|emp1|     John| null|\n",
      "|emp2|NEW VALUE| null|\n",
      "|emp3|NEW VALUE|345.0|\n",
      "|emp4|    Cindy|456.0|\n",
      "+----+---------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.na.fill('NEW VALUE').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+-----+\n",
      "|  Id| Name|Sales|\n",
      "+----+-----+-----+\n",
      "|emp1| John|  0.0|\n",
      "|emp2| null|  0.0|\n",
      "|emp3| null|345.0|\n",
      "|emp4|Cindy|456.0|\n",
      "+----+-----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.na.fill(0).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Usually you should specify what columns you want to fill with the subset parameter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-------+-----+\n",
      "|  Id|   Name|Sales|\n",
      "+----+-------+-----+\n",
      "|emp1|   John| null|\n",
      "|emp2|No Name| null|\n",
      "|emp3|No Name|345.0|\n",
      "|emp4|  Cindy|456.0|\n",
      "+----+-------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.na.fill('No Name',subset=['Name']).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A very common practice is to fill values with the mean value for the column, for example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "400.5"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyspark.sql.functions import mean\n",
    "mean_val = df.select(mean(df['Sales'])).collect()\n",
    "\n",
    "# Weird nested formatting of Row object!\n",
    "mean_val[0][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "mean_sales = mean_val[0][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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,[\"Sales\"]).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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": [
    "# One (very ugly) one-liner\n",
    "df.na.fill(df.select(mean(df['Sales'])).collect()[0][0],['Sales']).show()"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}