<jupyter_start><jupyter_text>Sparkify Project WorkspaceThis workspace contains a tiny subset (128MB) of the full dataset available (12GB). Feel free to use this workspace to build your project, or to explore a smaller subset with Spark before deploying your cluster on the cloud. Instructions for setting up your Spark cluster is included in the last lesson of the Extracurricular Spark Course content.You can follow the steps below to guide your data analysis and model building portion of this project.<jupyter_code># import libraries
import seaborn as sns
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from pyspark.sql import SparkSession
from pyspark.ml.feature import RegexTokenizer, VectorAssembler, Normalizer, StandardScaler
from pyspark.sql.functions import isnan, when, count, col
from pyspark.sql.functions import udf
from pyspark.sql.types import IntegerType
%%timeit
# create a Spark session
spark = SparkSession \
    .builder \
    .appName("Data Wrangling") \
    .getOrCreate()

# to check spark WebUI: http://localhost:4040/jobs/ or call directly 'spark' to get a link
spark<jupyter_output><empty_output><jupyter_text>Load and Clean DatasetIn this workspace, the mini-dataset file is `mini_sparkify_event_data.json`. Load and clean the dataset, checking for invalid or missing data - for example, records without userids or sessionids.<jupyter_code>pwd
path = '/home/freemo/Projects/largeData/mini_sparkify_event_data.json'
df = spark.read.json(file)
df.take(1)
print((df.count(), len(df.columns)))
mising_values.show()
df.show(5)
spark.conf.set('spark.sql.repl.eagerEval.enabled', True)
df
df.show(1, vertical=True)
df.printSchema()
df.select([count(when(col(c).isNull(), c)).alias(c) for c in df.columns]).show()
df.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in df.columns]).show()
# missing values in userID
df.select([count(when(isnan('userID'),True))]).show()
# missing values in sessionID
df.select([count(when(isnan('sessionID'),True))]).show()
df.select([count(when(isnan('userID') | col('userID').isNull() , True))]).show()
df.select([count(when(isnan('sessionID') | col('sessionID').isNull() , True))]).show()
df.distinct().show()<jupyter_output>+---------------+----------+---------+------+-------------+--------+---------+-----+--------------------+------+-----------+-------------+---------+--------------------+------+-------------+--------------------+------+
|         artist|      auth|firstName|gender|itemInSession|lastName|   length|level|            location|method|       page| registration|sessionId|                song|status|           ts|           userAgent|userId|
+---------------+----------+---------+------+-------------+--------+---------+-----+--------------------+------+-----------+-------------+---------+--------------------+------+-------------+--------------------+------+
|The Futureheads| Logged In|  Ainsley|     F|           80|  Farley|120.81587| free|McAllen-Edinburg-...|   PUT|   NextSong|1538304455000|      187|               Robot|   200|1538373133000|"Mozilla/5.0 (Win...|    78|
|           null| Logged In|  Ainsley|     F|           81|  Farley|     null| free|McAllen-Edinburg-...|   PUT|Thumbs Down|[...]
{'fraction_non_alphanumeric': 0.24140068174775334, 'fraction_numerical': 0.018593120545398203, 'mean_word_length': 2.5163398692810457, 'pattern_counts': {'":': 0, '<': 8, '<?xml version=': 0, '>': 8, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '3383', 'license': 'MIT', 'n_tokens_mistral': 989, 'n_tokens_neox': 925, 'n_words': 254, 'path': '.ipynb_checkpoints/Sparkify-checkpoint.ipynb', 'repo_name': 'rabadzhiyski/projectSpark', 'size': '3227'}