<jupyter_start><jupyter_text>Spark DataFrame - Data CleaningThere are three options when dealing with missing data: 1. Changing the data to null2. Drop the data point (or entire row)3. Fill it in with a different valueThese points are dependent on your requirements. Objective: Let's explore our options when it comes to cleaning a basic dataset.<jupyter_code># Must be included at the beginning of each new notebook. Remember to change the app name.
import findspark
findspark.init('/home/ubuntu/spark-2.1.1-bin-hadoop2.7')
import pyspark
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName('missing').getOrCreate()
# Importing data which has a header. Schema is automatically configured.
df = spark.read.csv('Datasets/contains_null.csv', header=True, inferSchema=True)

# Let's see the data. You'll notice nulls.
df.show()
# Using this syntax, we can drop any row with missing data. Three rows are dropped.
df.na.drop().show()
# Requires a certain amount of non-null values. Row two was dropped, as there's only one non-null value.
df.na.drop(thresh=2).show()
# Drops a row if all values are missing. Zero rows are dropped. 
df.na.drop(how="all").show()
# Drops a row if a value from a particular row is missing. Two rows are dropped.
df.na.drop(subset="Sales").show()
# Instead of dropping the row, this fills null string types with FILL VALUE. 
df.na.fill("FILL VALUE").show()

# Spark will only apply numbers to number data types, and strings to string data types.
df.na.fill(0).show()

# However, it's good practice to specify the row you want to fill using subset. 
df.na.fill('FILL NAME', subset=['Name']).show()
# Also, it's good practice to use your sales average to fill missing data. 
from pyspark.sql.functions import mean

# Let's collect the average. You'll notice that the collection returns the average in an interesting format.
mean_sales = df.select(mean(df['Sales'])).collect()
mean_sales
# If we use the index, we should be able to access the actual value.
mean_sales[0]

# Looks like we need to go one level deeper. Perfect! Let's assign that value to a variable.
mean_sales[0][0]
mean_sales_val = mean_sales[0][0]
# And finally, fill the missing values with the mean.
df.na.fill(mean_sales_val, subset=['Sales']).show()<jupyter_output>+----+-----+-----+
|  Id| Name|Sales|
+----+-----+-----+
|emp1| John|400.5|
|emp2| null|400.5|
|emp3| null|345.0|
|emp4|Cindy|456.0|
+----+-----+-----+
{'fraction_non_alphanumeric': 0.12381735911147676, 'fraction_numerical': 0.014397367338543809, 'mean_word_length': 4.28695652173913, 'pattern_counts': {'":': 0, '<': 4, '<?xml version=': 0, '>': 4, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '129287', 'license': 'MIT', 'n_tokens_mistral': 787, 'n_tokens_neox': 742, 'n_words': 303, 'path': 'Spark DataFrame - Data Cleaning.ipynb', 'repo_name': 'nhu002/722-test', 'size': '2431'}