Label new item column dataset2 = dataset2.withcolumns([pl.lit("New Item").alias('Item…
- User
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)
- Assistant
It appears that you are using the Polars library to manipulate some datasets and join them based on certain criteria. Let's break down what your code snippets are doing:
- You create a new column
Item Typeindataset2with the value "New Item". - You then join
dataset3with the modifieddataset2and create a newItem Typecolumn, which labels Material IDs as "Low Volume" if they do not exist indataset2. - Next, you merge
dataset1with the previously merged datasets, creating a similarItem Typecolumn, which labels Material IDs as "Others" if they do not exist in the joined datasets. - You filter the final dataset for rows where
Item Typeis "New Item". - You convert the string dates in the
WeekDatecolumn to datetime objects. - You group by certain attributes, including
WeekDate, and sumOrderQuantity. - You sort by
WeekDate. - You create a new
unique_idby concatenating certain columns. - You drop the original columns from which
unique_idwas created and rename columnsWeekDateandOrderQuantitytodsandy, respectively. - You group by the new
unique_idto get the count of the grouped entries. - You count the occurrences of each group length and sort them.
- You filter the groups to keep only those with a length greater than 25.
- You join the filtered groups back to the dataset and sort by
WeekDate.
The code with some minor fixes, assuming all the initial datasets are properly defined, should look something like this:
import polars as pl # Assuming dataset1, dataset2, and dataset3 are defined previously # Label new item column dataset2 = dataset2.with_column(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[['MaterialID', 'SalesOrg', 'DistrChan', 'CL4', 'Item Type']], on=['MaterialID', 'SalesOrg', 'DistrChan', 'CL4'], how='left') merged_dataset = merged_dataset.with_column(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[['MaterialID', 'SalesOrg', 'DistrChan', 'CL4', 'Item Type']], on=['MaterialID', 'SalesOrg', 'DistrChan', 'CL4'], how='left') dataset = dataset.with_column(pl.when(pl.col('Item Type').is_null()).then(pl.lit('Others')).otherwise(pl.col('Item Type')).alias('Item Type')) # Filter for 'New Item' items dataset_newitem = dataset.filter(pl.col('Item Type') == 'New Item') # Convert 'WeekDate' to datetime format dataset_newitem = dataset_newitem.with_column( pl.col("WeekDate").str.strptime(pl.Datetime, "%Y-%m-%d") ).alias("WeekDate") # Fix: included .alias("WeekDate") to correctly apply the conversion and avoid ambiguity # 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_column( pl.concat_str(['MaterialID', 'SalesOrg', 'DistrChan', 'CL4'], separator='_').alias('unique_id') ) # Drop the original columns y_cl4 = y_cl4.drop(['MaterialID', 'SalesOrg', 'DistrChan', 'CL4']) # Rename columns 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') # Set Polars config if needed for showing more rows # pl.Config.set_tbl_rows(200) # Print counts print(counts) # Filter the lengths DataFrame for lengths greater than 25 instead of 17 as in the initial code lengths_filtered = lengths.filter(pl.col('length') > 25) # y_soldto filtered with only values greater than 25 in length y_cl4_filtered = y_cl4.join( lengths_filtered[['unique_id']], on='unique_id', how='semi' ) # Sort by 'ds' y_cl4_filtered = y_cl4_filtered.sort("ds") # Print the resulting filtered data print(y_cl4_filtered)Please ensure that the datasets
dataset1,dataset2, anddataset3are already loaded and processed as needed before you run this snippet. You'll also need to adjust the filter thresholds to match your specific requirements if the greater than 25 condition doesn't apply to your case. - You create a new column
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.