# Label new item column
dataset2 = dataset2.with_columns([pl.lit("New Item").alias('Item Type')])

# Merge dataset2 and dataset3 and label which materialID as low volume or new item
merged_dataset = dataset3.join(dataset2.select(['MaterialID', 'SalesOrg', 'DistrChan', 'CL4', 'Item Type']), on=['MaterialID', 'SalesOrg', 'DistrChan', 'CL4'], how='left')
merged_dataset = merged_dataset.with_columns([pl.when(pl.col('Item Type').is_null()).then(pl.lit('Low Volume')).otherwise(pl.col('Item Type')).alias('Item Type')])

# Merge merged_dataset with dataset1
dataset = dataset1.join(merged_dataset.select(['MaterialID', 'SalesOrg', 'DistrChan', 'CL4', 'Item Type']), on=['MaterialID', 'SalesOrg', 'DistrChan', 'CL4'], how='left')
dataset = dataset.with_columns([pl.when(pl.col('Item Type').is_null()).then(pl.lit('Others')).otherwise(pl.col('Item Type')).alias('Item Type')])
 dataset.head() MaterialID	SalesOrg	DistrChan	SoldTo	DC	WeekDate	OrderQuantity	DeliveryQuantity	ParentProductCode	PL2	PL3	PL4	PL5	CL4	Item Type
i64	str	i64	i64	str	str	f64	f64	i64	str	str	str	str	str	str
12421256	"US01"	8	5932810	"5583"	"2022-03-28"	1.0	1.0	12421256	"US6"	"US66F"	"US66F6F30"	"US66F6F306F30F…	"6067437"	"Others"
12421256	"US01"	8	5946860	"5583"	"2022-03-14"	3.0	3.0	12421256	"US6"	"US66F"	"US66F6F30"	"US66F6F306F30F…	"6067437"	"Others"
12421256	"US01"	8	5952162	"5583"	"2022-03-14"	1.0	1.0	12421256	"US6"	"US66F"	"US66F6F30"	"US66F6F306F30F…	"6067437"	"Others"
12421161	"US01"	8	5958951	"5583"	"2022-03-07"	1.0	1.0	12421161	"US6"	"US66J"	"US66J6J54"	"US66J6J546J54L…	"6067465"	"Others"
12531761	"US06"	10	6088109	"5843"	"2022-03-07"	60.0	60.0	12242722	"USI"	"USII6"	"USII6I689"	"USII6I689I689E…	"6635338"	"Others" # Filter for 'Low Volume' items
dataset_newitem = dataset.filter(pl.col('Item Type') == 'New Item') # Convert 'WeekDate' to datetime format
dataset_newitem = dataset_newitem.with_columns(
    pl.col("WeekDate").str.strptime(pl.Datetime, "%Y-%m-%d")
)

# Group by ‘MaterialID’, ‘SalesOrg’, ‘DistrChan’, 'CL4' and 'WeekDate', then sum 'OrderQuantity'
y_cl4 = dataset_newitem.groupby(['MaterialID', 'SalesOrg', 'DistrChan', 'CL4', 'WeekDate']).agg(
    pl.sum("OrderQuantity").alias("OrderQuantity")
)

# Sort by 'WeekDate'
y_cl4 = y_cl4.sort("WeekDate") # Concatenate ‘MaterialID’, ‘SalesOrg’, ‘DistrChan’, ‘CL4’ to a new column ‘unique_id’
y_cl4 = y_cl4.with_columns(
    pl.concat_str([pl.col('MaterialID'), pl.col('SalesOrg'), pl.col('DistrChan'), pl.col('CL4')], separator='_').alias('unique_id')
)

# Drop the original columns 
y_cl4 = y_cl4.drop(['MaterialID', 'SalesOrg', 'DistrChan', 'CL4'])

y_cl4 = y_cl4.rename({'WeekDate': 'ds', 'OrderQuantity': 'y'}) # Group by ‘unique_id’ and calculate the length of each group
lengths = y_cl4.groupby('unique_id').agg(pl.count().alias('length'))

# Count the occurrences of each length
counts = lengths.groupby('length').agg(pl.count().alias('count'))
counts = counts.sort('length')
 
pl.Config.set_tbl_rows(200)  


print(counts) # Filter the lengths DataFrame for lengths greater than 17
lengths_filtered = lengths.filter(pl.col('length') > 25)

# y_soldto filtered with only values greater than 17 in length
y_cl4_filtered = y_cl4.join(
    lengths_filtered.select(pl.col('unique_id')), 
    on='unique_id', 
    how='semi'  
)

# Sort by 'WeekDate'
y_cl4_filtered = y_cl4_filtered.sort("ds")

print(y_cl4_filtered)