<jupyter_start><jupyter_text>Data preparation<jupyter_code># Check the first five rows
sales.head()
# View datatype of each column
sales.info()
# Convert some of the columns to their appropriate data type
float_columns = ['Weight_Kg', 'Low_Price', 'High_Price', 'Average_Price', 
                 'Sales_Total', 'Total_Kg_Sold', 'Total_Qty_Sold', 'Stock_On_Hand']


# Convert the columns to numeric
for col in float_columns:
    # sales[col] = sales[col].astype(float)
    sales[col] = pd.to_numeric(sales[col])
sales.info()
sales.head()
# Check for the number of days in the database
print(f"{sales['Date'].nunique()} days recorded in the database")
# Remove days whereby total sales equal 0 because it registers average_price as zero.
filtered_sales = sales[sales['Sales_Total'] != 0]
print(f"{filtered_sales['Date'].nunique()} days recorded in the database")<jupyter_output>147 days recorded in the database<jupyter_text>Therefore no days were lost due to filtering by 'Sales_Total'<jupyter_code>filtered_sales.head()
# Check PINEAPPLE commodity to observe daily sales
sales[(sales['Commodities'] == 'PINEAPPLE QUEEN VICTORIA') & (sales['Container'] == 'LM080') & (sales['Province'] == 'NATAL')].sort_values('Date').head(20)<jupyter_output><empty_output><jupyter_text>Based on the above table, it can be seen that multiple sales are taking place on the same day, which means that the data has to be consolidated to one day.<jupyter_code># Consolidation of repeated sales in a single day of the same product to one day
df = filtered_sales.groupby(['Province', 'Container', 'Size_Grade', 'Weight_Kg', 'Commodities', 'Date']
    )[['Low_Price', 'High_Price', 'Sales_Total', 'Total_Qty_Sold', 'Total_Kg_Sold', 'Stock_On_Hand']].agg(
        {
            'Low_Price':min,
            'High_Price':max,
            'Sales_Total':sum,
            'Total_Qty_Sold':sum,
            'Total_Kg_Sold':sum,
            'Stock_On_Hand':sum
        }
)
df
df.reset_index(inplace=True)
df.head()
df['avg_price_per_kg'] = round(df['Sales_Total'] / df['Total_Kg_Sold'], 2)
df['low_price_per_kg'] = round(df['Low_Price'] / df['Weight_Kg'], 2)
df['high_price_per_kg'] = round(df['High_Price'] / df['Weight_Kg'], 2)
df.head()
# Check which product is regularly sold based on the days in the data
day_count = sales['Date'].nunique()
df.groupby(['Province', 'Container', 'Size_Grade', 'Weight_Kg', 'Commodities'])['Commodities'].value_counts().apply(lambda x: x / day_count).sort_values(ascending=False)<jupyter_output><empty_output><jupyter_text>Based on the results above, **APPLE GOLDEN DELICIOUS** has been sold for every day that is recorded in the database. For this notebook, this product will be used as a template for developing a forecasting model.<jupyter_code>filtered_df = df[
    (df['Commodities'] == 'APPLE GOLDEN DELICIOUS') & 
    (df['Weight_Kg'] == 12.0) &
    (df['Size_Grade'] == '1S') &
    (df['Container'] == 'EC120') &
    (df['Province'] == 'CAPE')
]
filtered_df.head()
apples = filtered_df[[
    'Province', 'Date', 'Low_Price', 'High_Price', 'Sales_Total', 'Total_Qty_Sold',
    'Total_Kg_Sold', 'Stock_On_Hand', 'avg_price_per_kg', 'low_price_per_kg', 'high_price_per_kg'
]]
# Check for multicollinearity
sns.heatmap(apples.corr(), annot=True, cbar=False);<jupyter_output><empty_output><jupyter_text>Excluding the average prices per kg, there's high correlation between **Sales_Total, Total_Qty_Sold** and **Total_Kg_Sold**. For determining inventory levels, only **Total_Qty_Sold** will remain.<jupyter_code>apples = apples[['Date', 'Total_Qty_Sold', 'Stock_On_Hand', 
                 'avg_price_per_kg', 'low_price_per_kg', 'high_price_per_kg']]
apples.head()
apples['weekday'] = filtered_df['Date'].apply(lambda x: x.day_name())
#apples.head()
apples.sort_values('Date', inplace=True)
#apples.head()
apples['month'] = apples['Date'].apply(lambda x: x.month_name())
#apples.head()
def season(month):
    """
    Returns the season of which the month falls in
    
    Parameters
    -----------
    month: str
        The month of the year as a full month name
    
    Returns
    --------
    str:
        The season of the year
        
    Examples
    ---------
    >>> season('October')
    'spring'
    """
    
    # Seasons
    summer = ['December', 'January', 'February']
    autumn = ['March', 'April', 'May']
    winter = ['June', 'July', 'August']
    spring = ['September', 'October', 'November']
    
    if month in summer:
        return 'summer'
    elif month in autumn:
        return 'autumn'
    elif month in winter:
        return 'winter'
    else:
        return 'spring'
apples['season'] = apples['month'].apply(season)
apples.head()
apples['weekday'].value_counts()
apples['season'].value_counts()
plt.figure(figsize=(16,5))
sns.swarmplot(x='season', y='avg_price_per_kg', data=apples)
plt.title("Price variations of Apples Golden Delicious");
apples['week_of_year'] = apples['Date'].apply(lambda x: x.week)
#apples.head()
apples['is_month_end'] = apples['Date'].apply(lambda x: 1 if x.day in range(25,32) else 0)
#apples.head()
apples['is_month_end'].value_counts()
apples['range'] = apples['high_price_per_kg'] - apples['low_price_per_kg']
apples.head()
df = apples[['avg_price_per_kg', 'weekday', 'month', 'season', 'is_month_end', 'range']]
df['range'] = df['range'].shift(periods=-1)
df.tail()
# Drop the last row since column values were shifted up by 1 period
df.dropna(inplace=True)
df = pd.get_dummies(df, drop_first=True)
df.head()
X = df.iloc[:,1:]
y = df['avg_price_per_kg']
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, shuffle=False, random_state=42)
lr = LinearRegression()
lr.fit(X_train, y_train)
predict = lr.predict(X_test)
predict_df = pd.DataFrame({'actual':y_test.values, 'predict':predict}, index=range(len(y_test)))
ax = predict_df.plot(figsize=(12,6))
ax.autoscale(axis='x', tight=True)
ax.set(ylabel='avg price per kg')
plt.title("average price per kg of apples");<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15411127523387494, 'fraction_numerical': 0.006400787789266372, 'mean_word_length': 4.980372914622179, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 23, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '369554', 'license': 'MIT', 'n_tokens_mistral': 2187, 'n_tokens_neox': 2079, 'n_words': 507, 'path': 'Modelling/.ipynb_checkpoints/regression-checkpoint.ipynb', 'repo_name': 'scmosoeu/fresh_produce', 'size': '6093'}