def get_concept_list(df: pyspark.sql.dataframe.DataFrame) -> list: # Query and create new dataframe concept_list = [ row.Concept_NEW for row in df.select("Concept_NEW").distinct().collect() ] return concept_list
def _simple_entropy(df: pyspark.sql.dataframe.DataFrame, column_name: str) -> float: count = df.count() testdf = df.select(column_name).groupby(column_name).agg((F.count(column_name) / count).alias("p")) result = testdf.groupby().agg(-F.sum(F.col("p") * F.log2("p"))).collect()[0][0] if not result: return 0.0 return result
def merge_dataset( df: pyspark.sql.dataframe.DataFrame ) -> pyspark.sql.dataframe.DataFrame: # Generate sale information for each product in each month month_df = df.select('MatID', "SKU", year("Date").alias('year'), month("Date").alias('month'), 'GrossSales', 'NetSales', 'COGS', 'QtySold', 'Price', 'SellMargin', 'FrontMargin', 'SubCategory', 'Vendor').groupBy("month", 'MatID', "SubCategory", 'Vendor') ## get the average net-sales of each product month_avg_NetSale = month_df.avg("NetSales").withColumnRenamed( "avg(NetSales)", "totalMonthlyNetSale") ## get the average gross-sales of each product month_avg_GrossSale = month_df.avg("GrossSales").withColumnRenamed( "avg(GrossSales)", "totalMonthlyGrossSale") month_avg_COGS = month_df.avg("COGS").withColumnRenamed( "avg(COGS)", "avgCOGS") month_avg_QtySold = month_df.avg("QtySold").withColumnRenamed( "avg(QtySold)", "totalMonthlyQtySold") month_avg_Price = month_df.avg("Price").withColumnRenamed( "avg(Price)", "Price") month_avg_SM = month_df.avg("SellMargin").withColumnRenamed( "avg(SellMargin)", "SellMargin") month_avg_FM = month_df.avg("FrontMargin").withColumnRenamed( "avg(FrontMargin)", "avgFrontMargin") month_merge = month_avg_NetSale.join( month_avg_GrossSale, on=["MatID", 'month', 'SubCategory', 'Vendor'], how="inner") month_merge = month_merge.join( month_avg_COGS, on=["MatID", 'month', 'SubCategory', 'Vendor'], how="inner") month_merge = month_merge.join( month_avg_QtySold, on=["MatID", 'month', 'SubCategory', 'Vendor'], how="inner") month_merge = month_merge.join( month_avg_Price, on=["MatID", 'month', 'SubCategory', 'Vendor'], how="inner") month_merge = month_merge.join( month_avg_SM, on=["MatID", 'month', 'SubCategory', 'Vendor'], how="inner") month_merge = month_merge.join( month_avg_FM, on=["MatID", 'month', 'SubCategory', 'Vendor'], how="inner") return month_merge
def find_and_analysis_fullMonth_SKU( df_atLeastOneMonth: pyspark.sql.dataframe.DataFrame, split_month: int, spark) -> pyspark.sql.dataframe.DataFrame: """ Find SKU, which "soldQty < capacity" in every month """ full_month_items = select_full_month_item( df_atLeastOneMonth.toPandas(), month_list=[split_month, split_month + 1, split_month + 2]) # three month data since split_month full_month_SKU_info = get_full_month_SKU_info( full_month_items, df_atLeastOneMonth.select(selected_column_fullMonth), spark).dropDuplicates() return full_month_SKU_info
def clean_dist_df( dist_df: pyspark.sql.dataframe.DataFrame ) -> pyspark.sql.dataframe.DataFrame: # filter data dist_df = dist_df.select("Name", "Facings", "Capacity", 'Days Supply', 'Classification', 'Mat ID', '# POGs') ### Rename column dist_df = dist_df.withColumnRenamed("Name", "SKU") dist_df = dist_df.withColumnRenamed("Days Supply", "DaysSupply") dist_df = dist_df.withColumnRenamed("Mat ID", "MatID") dist_df = dist_df.withColumnRenamed("# POGs", "POGS") # Conver columns to `FloatType()` dist_df = dist_df.withColumn("Facings", dist_df.Facings.cast('float')) dist_df = dist_df.withColumn("Capacity", dist_df.Capacity.cast('float')) dist_df = dist_df.withColumn("DaysSupply", dist_df.DaysSupply.cast('float')) dist_df = dist_df.withColumn("MatID", dist_df.MatID.cast('integer')) dist_df = dist_df.withColumn("POGS", dist_df.POGS.cast('integer')) return dist_df
def clean_dataset( df: pyspark.sql.dataframe.DataFrame ) -> pyspark.sql.dataframe.DataFrame: ## Select the target features df = df.select('Index', 'Date Detail', 'Company', 'Business Unit', 'Concept_NEW', 'Product Category', 'Company and Cost Centre', 'SKU', 'POS Net Sales', 'Rank Total') ## Reanme columns df = df.withColumnRenamed("POS Net Sales", "NetSales") df = df.withColumnRenamed("Date Detail", "Date") df = df.withColumnRenamed("Product Category", "Category") df = df.withColumnRenamed("Company and Cost Centre", "Store") df = df.withColumnRenamed("Business Unit", "BusinessUnit") df = df.withColumnRenamed("Rank Total", "rank") ## Column type cast columns = ['NetSales', 'rank'] df = convertColumn(df, columns, FloatType()) # Replace none to 0 df = df.na.fill(0) return df
def estimate_segments( df: pyspark.sql.dataframe.DataFrame, target_field: str = None, max_segments: int = 30, include_columns: List[str] = [], unique_perc_bounds: Tuple[float, float] = [None, 0.8], null_perc_bounds: Tuple[float, float] = [None, 0.2], ) -> Optional[Union[List[Dict], List[str]]]: """ Estimates the most important features and values on which to segment data profiling using entropy-based methods. If no target column provided, maximum entropy column is substituted. :param df: the dataframe of data to profile :param target_field: target field (optional) :param max_segments: upper threshold for total combinations of segments, default 30 :param include_columns: additional non-string columns to consider in automatic segmentation. Warning: high cardinality columns will degrade performance. :param unique_perc_bounds: tuple of form [lower, upper] with bounds on the percentage of unique values (|unique| / |X|). Upper bound exclusive. :param null_perc_bounds: tuple of form [lower, upper] with bounds on the percentage of null values. Upper bound exclusive. :return: a list of segmentation feature names """ current_split_columns = [] segments = [] segments_used = 1 max_entropy_column = (float("-inf"), None) if not unique_perc_bounds[0]: unique_perc_bounds[0] = float("-inf") if not unique_perc_bounds[1]: unique_perc_bounds[1] = float("inf") if not null_perc_bounds[0]: null_perc_bounds[0] = float("-inf") if not null_perc_bounds[1]: null_perc_bounds[1] = float("inf") valid_column_names = set() count = df.count() print("Limiting to categorical (string) data columns...") valid_column_names = {col for col in df.columns if (df.select(col).dtypes[0][1] == "string" or col in include_columns)} print("Gathering cardinality information...") n_uniques = {col: df.agg(F.approx_count_distinct(col)).collect()[0][0] for col in valid_column_names} print("Gathering missing value information...") n_nulls = {col: df.filter(df[col].isNull()).count() for col in valid_column_names} print("Finding valid columns for autosegmentation...") for col in valid_column_names.copy(): null_perc = 0.0 if count == 0 else n_nulls[col] / count unique_perc = 0.0 if count == 0 else n_uniques[col] / count if ( col in segments or n_uniques[col] <= 1 or null_perc < null_perc_bounds[0] or null_perc >= null_perc_bounds[1] or unique_perc < unique_perc_bounds[0] or unique_perc >= unique_perc_bounds[1] ): valid_column_names.remove(col) if not valid_column_names: return [] if not target_field: print("Finding alternative target field since none were specified...") for col in valid_column_names: col_entropy = _simple_entropy(df, col) if n_uniques[col] > 1: col_entropy /= math.log(n_uniques[col]) if col_entropy > max_entropy_column[0]: max_entropy_column = (col_entropy, col) target_field = max_entropy_column[1] print(f"Using {target_field} column as target field.") assert target_field in df.columns valid_column_names.add(target_field) valid_column_names = list(valid_column_names) countdf = df.select(valid_column_names).groupby(valid_column_names).count().cache() print("Calculating segments...") while segments_used < max_segments: valid_column_names = {col for col in valid_column_names if (col not in segments and n_uniques[col] * segments_used <= (max_segments - segments_used))} _, segment_column_name = _find_best_split( countdf, current_split_columns, list(valid_column_names), target_column_name=target_field, normalization=n_uniques ) if not segment_column_name: break segments.append(segment_column_name) current_split_columns.append(segment_column_name) segments_used *= n_uniques[segment_column_name] return segments
def get_week_list(df: pyspark.sql.dataframe.DataFrame) -> list: # Query and create new dataframe week_list = [row.Date for row in df.select("Date").distinct().collect()] week_list.sort() # sort date by increasing order return week_list
def Data_clean_and_merge( df: pyspark.sql.dataframe.DataFrame, Subcat_info: pyspark.sql.dataframe.DataFrame, Vendor_info: pyspark.sql.dataframe.DataFrame, store_name: str, begin_date="2019-04-01", end_date="2019-10-01") -> pyspark.sql.dataframe.DataFrame: # select useful columns Subcat_info = Subcat_info.select('SKU', 'SubCategory') Vendor_info = Vendor_info.select('SKU', 'Vendor') # clean data entry: remove ID split_col = split(Subcat_info['SKU'], '-') Subcat_info = Subcat_info.withColumn('MatID', split_col.getItem(0)) Subcat_info = Subcat_info.withColumn('MatID', regexp_replace( col("MatID"), "[ZNDF]", "")) # remove letters from matID split_col2 = split(Vendor_info['SKU'], '-') Vendor_info = Vendor_info.withColumn('MatID', split_col2.getItem(0)) Vendor_info = Vendor_info.withColumn('MatID', regexp_replace( col("MatID"), "[ZNDF]", "")) # remove letters from matID split_col = split(Subcat_info['SubCategory'], '-') split_col2 = split(Vendor_info['Vendor'], '-') Subcat_info = Subcat_info.withColumn('SubCategory', split_col.getItem(1)) Vendor_info = Vendor_info.withColumn('Vendor', split_col2.getItem(1)) # filter data df = df.select("Date", "Store", 'item', 'POS Gross Sales', 'POS Net Sales', 'POS Total Discount', 'POS Qty Sold', 'POS COGS (INV)') # Check only one store df = df.filter(df.Store == store_name) # Remove comma from integer (e.g. 1,333 to 1333) udf = UserDefinedFunction(lambda x: re.sub(',', '', x), StringType()) #num_columns = ['TotalDiscount', 'QtySold', 'GrossSales', 'NetSales', 'COGS'] df = df.select(*[udf(column).alias(column) for column in df.columns]) # filter data, and keep only half years # Convert Date column to timestamp df = df.withColumn("Date", to_timestamp(df.Date, "yyyyMM")) df = df.filter(df.Date >= begin_date) df = df.filter(df.Date < end_date) # April - Sep # separate Item name to SKU and ID split_col = split(df['item'], '-') df = df.withColumn('MatID', split_col.getItem(0)) df = df.withColumn('MatID', regexp_replace(col("MatID"), "[ZNDF]", "")) # remove letters from matID df = df.withColumn('SKU', split_col.getItem(1)) ### Rename column df = df.withColumnRenamed("Sales Type", "SalesType") df = df.withColumnRenamed("POS Gross Sales", "GrossSales") df = df.withColumnRenamed("POS Net Sales", "NetSales") df = df.withColumnRenamed("POS Total Discount", "TotalDiscount") df = df.withColumnRenamed("POS Qty Sold", "QtySold") df = df.withColumnRenamed("POS COGS (INV)", "COGS") # Assign all column names to `columns` columns = ['TotalDiscount', 'QtySold', 'GrossSales', 'NetSales', 'COGS'] # Conver the `df` columns to `FloatType()` df = convertColumn(df, columns, FloatType()) # drop unnecessary items columns_to_drop = ['item'] df = df.drop(*columns_to_drop) # Convert Date column to timestamp df = df.withColumn("Date", to_timestamp(df.Date, "yyyyMM")) # Create the new columns df = df.withColumn("Price", df.GrossSales / df.QtySold) df = df.withColumn("FrontMargin", (df.GrossSales + df.COGS)) df = df.withColumn("SellMargin", (df.NetSales + df.COGS)) # add subcategory column df = df.join(Subcat_info.select("MatID", 'SubCategory'), on=["MatID"], how="left") df = df.join(Vendor_info.select("MatID", 'Vendor'), on=["MatID"], how="left") return df