<jupyter_start><jupyter_text>Week 1 - Foundations of marketing analyticshttps://www.coursera.org/learn/foundations-marketing-analytics/home/week/1<jupyter_code>import org.apache.spark.sql.types._

val schema = StructType(
                List(
                    StructField("customer_id", StringType, false),
                    StructField("purchase_amount", DoubleType, false),
                    StructField("date_of_purchase", DateType, false)
                )
            )
val data = spark.read
                .option("sep", "\t")
                .option("mode","FAILFAST")
                .option("dateFormat","YYYY-MM-dd")
                //.option("inferSchema", true) //requires 1 pass over schema and parses date as full timestamp
                .schema(schema)
                .csv("../../data/foundation-marketing-analytics/purchases.txt")
                .toDF
data.printSchema()
data.show(5)
val desc = data.describe()
desc.show()
import org.apache.spark.sql.functions._
val enriched = data
                .withColumn("year_of_purchase", year(col("date_of_purchase")))
                .cache
enrich.show(5)<jupyter_output><empty_output><jupyter_text>**Number of purchases per year**<jupyter_code>enriched
    .groupBy(col("year_of_purchase"))
    .agg(count("*").alias("count"))
    .orderBy(col("year_of_purchase"))
    .show()<jupyter_output><empty_output><jupyter_text>**Average purchase amount per year**<jupyter_code>enriched
    .groupBy(col("year_of_purchase"))
    .agg(avg("purchase_amount").alias("avg_amount"))
    .orderBy("year_of_purchase")
    .show()<jupyter_output><empty_output><jupyter_text>**All in one**<jupyter_code>enriched
    .groupBy($"year_of_purchase")
    .agg(
        count($"year_of_purchase").alias("count"),
        sum($"purchase_amount").alias("sum_amount"),
        avg($"purchase_amount").alias("avg_amount"))
    .orderBy($"year_of_purchase")
    .show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14910025706940874, 'fraction_numerical': 0.002570694087403599, 'mean_word_length': 3.028985507246377, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '532818', 'license': 'MIT', 'n_tokens_mistral': 647, 'n_tokens_neox': 616, 'n_words': 83, 'path': 'notebooks/foundation-marketing-analytics/week-1.ipynb', 'repo_name': 'yaravind/learn-spark', 'size': '1945'}