<jupyter_start><jupyter_text>2.4 Train Data Cleaning DescriptionBasic data visualization and data formatting for train.csv Notebook Steps1. Connect Spark1. Input Data1. Examine Data1. Data Cleaning1. Output Data  1. Connect Spark<jupyter_code>%load_ext sparkmagic.magics
%manage_spark<jupyter_output><empty_output><jupyter_text>2. Input Data<jupyter_code>%%spark
df = spark.read.csv("s3://jolfr-capstone3/raw/train", header=True)<jupyter_output>UsageError: Cell magic `%%spark` not found.<jupyter_text>3. Examine Data  show()<jupyter_code>%%spark
df.show()<jupyter_output><empty_output><jupyter_text>count()<jupyter_code>%%spark
df.count()<jupyter_output><empty_output><jupyter_text>describe()<jupyter_code>%%spark
df.describe().show()<jupyter_output><empty_output><jupyter_text>printSchema()<jupyter_code>%%spark
df.printSchema()<jupyter_output><empty_output><jupyter_text>columns<jupyter_code>%%spark
df.columns<jupyter_output><empty_output><jupyter_text>head(5)<jupyter_code>%%spark
df.head(5)<jupyter_output><empty_output><jupyter_text>Null per Column<jupyter_code>%%spark
from pyspark.sql.functions import isnan, when, count, col

df.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in df.columns]).show()<jupyter_output><empty_output><jupyter_text>Value Counts<jupyter_code>%%spark
df.groupBy('is_churn').count().orderBy('count').show()<jupyter_output><empty_output><jupyter_text>4. Data Cleaning<jupyter_code>%%spark
from pyspark.sql import types
from pyspark.sql.functions import col, to_date<jupyter_output><empty_output><jupyter_text>msnoThe msno column corresponds to user ids for the dataset, so the column is renamed from msno to user_id.<jupyter_code>%%spark
df = df.withColumnRenamed("msno","user_id")<jupyter_output><empty_output><jupyter_text>is_churnThe is_churn column is cast from string to boolean.<jupyter_code>%%spark
df = df.withColumn("is_churn",col("is_churn").cast(types.BooleanType()))<jupyter_output><empty_output><jupyter_text>5. Data Output  Final Check<jupyter_code>%%spark
df.printSchema()
%%spark
df.show()<jupyter_output><empty_output><jupyter_text>Output to File<jupyter_code>%%spark

df.write.format("com.databricks.spark.csv").option("header", "true").mode('overwrite').save('s3://jolfr-capstone3/interim/train')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14497170222028732, 'fraction_numerical': 0.00783630822812364, 'mean_word_length': 11.094736842105263, 'pattern_counts': {'":': 0, '<': 60, '<?xml version=': 0, '>': 60, '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': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '481548', 'license': 'MIT', 'n_tokens_mistral': 895, 'n_tokens_neox': 861, 'n_words': 118, 'path': 'notebooks/2-data_cleaning/2.4-train_cleaning.ipynb', 'repo_name': 'jolfr/capstone-03', 'size': '2297'}