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