<jupyter_start><jupyter_text>Predictive Ordering: Preprocess Data ---This notebook contains the code to preprocess the monthly historical order data that was created in PredictiveOrdering-1Generate.ipynb. The notebook uses the following data points to compute the demand which is subsequently fed into our machine learning models:* Historical order quantity* Out of stock information* Product expiration* Remaining inventory---  Reading the Data Importing the required libraries<jupyter_code>import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
from google.colab import drive
import math<jupyter_output><empty_output><jupyter_text>Reading the csv file from Google Drive<jupyter_code>drive.mount("/content/gdrive")

df = pd.read_csv("gdrive/My Drive/Orders.csv")<jupyter_output>Mounted at /content/gdrive<jupyter_text>View the dataframe<jupyter_code>df.head()<jupyter_output><empty_output><jupyter_text>Rename "Unnamed 0" to "Period"<jupyter_code>df.rename(columns={"Unnamed: 0": "Period"}, inplace=True)<jupyter_output><empty_output><jupyter_text>Preprocessing Whenever the a product is out of stock in a certain week, it indicates that the previous order quantity was insufficient and therefore failed to satisfy future demand. We use the out of stock flag to pad the previous historical order quantity by a factor<jupyter_code>paddingFactor = 10

for i in range(1, len(df)):
  if df.loc[i, "OutOfStock1"] == True:
    df.loc[i-1, "HistoricalOrder1"] = math.ceil(df.loc[i-1, "HistoricalOrder1"] * (1 + (paddingFactor / 100)))<jupyter_output><empty_output><jupyter_text>View the dataframe<jupyter_code>df.head()<jupyter_output><empty_output><jupyter_text>Combine the Historical orders, Products Expired and Remaining Inventory using the formula below:  **Sales = Orders - (Products Expired + Remaining Inventory)**<jupyter_code>df["Sales1"] = df.HistoricalOrder1 - (df.ProductsExpired1 + df.RemainingInventory1)
df["Sales2"] = df.HistoricalOrder2 - (df.ProductsExpired2 + df.RemainingInventory2)
df["Sales3"] = df.HistoricalOrder3 - (df.ProductsExpired3 + df.RemainingInventory3)
df["Sales4"] = df.HistoricalOrder4 - (df.ProductsExpired4 + df.RemainingInventory4)
df["Sales5"] = df.HistoricalOrder5 - (df.ProductsExpired5 + df.RemainingInventory5)
df["Sales6"] = df.HistoricalOrder6 - (df.ProductsExpired6 + df.RemainingInventory6)<jupyter_output><empty_output><jupyter_text>Dropping the Historical Order, Out of Stock, Product Expired and Remaining Inventory columns as they are no longer required<jupyter_code>df.drop(columns=["HistoricalOrder1", "HistoricalOrder2", "HistoricalOrder3", "HistoricalOrder4", "HistoricalOrder5", "HistoricalOrder6"], inplace=True)
df.drop(columns=["OutOfStock1", "OutOfStock2", "OutOfStock3", "OutOfStock4", "OutOfStock5", "OutOfStock6"], inplace=True)
df.drop(columns=["ProductsExpired1", "ProductsExpired2", "ProductsExpired3", "ProductsExpired4", "ProductsExpired5", "ProductsExpired6"], inplace=True)
df.drop(columns=["RemainingInventory1", "RemainingInventory2", "RemainingInventory3", "RemainingInventory4", "RemainingInventory5", "RemainingInventory6"], inplace=True)<jupyter_output><empty_output><jupyter_text>Display the dataframe<jupyter_code>df.head()<jupyter_output><empty_output><jupyter_text>*Sales* cannot be negative, so we floor the values to 0<jupyter_code>df["Sales1"] = df["Sales1"].apply(lambda x: 0 if x < 0 else x)
df["Sales2"] = df["Sales2"].apply(lambda x: 0 if x < 0 else x)
df["Sales3"] = df["Sales3"].apply(lambda x: 0 if x < 0 else x)
df["Sales4"] = df["Sales4"].apply(lambda x: 0 if x < 0 else x)
df["Sales5"] = df["Sales5"].apply(lambda x: 0 if x < 0 else x)
df["Sales6"] = df["Sales6"].apply(lambda x: 0 if x < 0 else x)<jupyter_output><empty_output><jupyter_text>Display the dataframe<jupyter_code>df.head()<jupyter_output><empty_output><jupyter_text>Visualize the Preprocessed Data Defining a function to visualize the data<jupyter_code>shortProductNames = df.columns

def plot_series(time, series, format="-", start=0, end=None):
    plt.plot(time[start:end], series[start:end], format)
    plt.xlabel("Months")
    plt.ylabel("Order Quantities")
    plt.legend(shortProductNames, loc="upper left", bbox_to_anchor=(1.05, 0.0, 0.3, 0.2))
    plt.grid(True)

plot_series(np.arange(60, dtype="int"), df["Sales1"])<jupyter_output><empty_output><jupyter_text>Exporting the Preprocessed Data<jupyter_code>drive.mount("drive")

df.to_csv("PreprocessedOrders.csv")
!cp PreprocessedOrders.csv "drive/My Drive/"<jupyter_output>Mounted at drive
{'fraction_non_alphanumeric': 0.12613410046470458, 'fraction_numerical': 0.022350077450763443, 'mean_word_length': 7.74274661508704, 'pattern_counts': {'":': 1, '<': 57, '<?xml version=': 0, '>': 51, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '620464', 'license': 'MIT', 'n_tokens_mistral': 1596, 'n_tokens_neox': 1497, 'n_words': 358, 'path': 'PredictiveOrdering_ Preprocess Data.ipynb', 'repo_name': 'msimons0/Predictive-Ordering2', 'size': '4519'}