Databricks hands on create contiguous rows for missing data

Create contiguous rows for missing data

As a data engineer creating ETL pipelines I often have to transform my data to create contiguous data.For instance in this toy example I have data for cornflakes sales for 4 months in 2003 January,February,March and September.I will then have to augment to my data and create rows for the rest of the month for every year. In this article I will show you how to using a community databricks notebook

Consider Corn flake sales data with missing months.

My aim is to generate rows for the missing months per year.To generate contiguous data for all months we first find the months that are missing .We will then create rows for each of those missing months and set the value of the sales to the average per year.

In code speak

df_1=productDF.groupby(["Product","Year"]).agg(F.collect_set("Month"),F.mean("Sales")) df_1=df_1.withColumnRenamed("collect_set(Month)","Months_Sold") df_1=df_1.withColumnRenamed("avg(Sales)","Average_per_year") df_1=df_1.withColumn("Year_Months",F.array([F.lit(i) for i in range(1,13)])) df_1=df_1.withColumn("Left_Months",F.array_except(F.col("Year_Months"),F.col("Months_Sold"))) df_1=(df_1.withColumn("Month",F.explode(F.col("Left_Months")))) df_1=df_1.withColumn("Sales",F.col("Average_per_year")) df_2=df_1.select("Month","Year","Product","Sales") df_all=productDF.union(df_2).orderBy([F.col("Year"),F.col("Month")]) 

Git hub source :Contiguous data