<jupyter_start><jupyter_text>Importing Libraries<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

from datetime import datetime

import seaborn as sns
sns.set()

from sklearn.metrics import r2_score, median_absolute_error, mean_absolute_error
from sklearn.metrics import median_absolute_error, mean_squared_error, mean_squared_log_error

from scipy.optimize import minimize
import statsmodels.tsa.api as smt
import statsmodels.api as sm

from tqdm import tqdm_notebook

from itertools import product

def mean_absolute_percentage_error(y_true, y_pred):
    return np.mean(np.abs((y_true - y_pred) / y_true)) * 100

import warnings
warnings.filterwarnings('ignore')

%matplotlib inline<jupyter_output><empty_output><jupyter_text>Loading The dataset intern data sheet. <NAME><jupyter_code>df = pd.read_excel('Data Sheet for Interns.xlsx')
df.head()
#Giving the column name
headers = df.iloc[0]
new_df  = pd.DataFrame(df.values[1:], columns=headers)
new_df.head()<jupyter_output><empty_output><jupyter_text>Checking the NaN value in Dataset.<jupyter_code>print("No of Nan values in our dataframe : ", sum(new_df.isnull().any()))
new_df.isnull().sum()
#Checking the NaN values in entire dataset.
df.isnull().sum().sum()
print(df.shape)
#Droping the empty row
new_df.dropna(how = 'all', inplace=True)
new_df.head()
print("No of Nan values in our dataframe : ", sum(new_df.isnull().any()))
pip install missingno<jupyter_output>Requirement already satisfied: missingno in c:\programdata\anaconda3\lib\site-packages (0.5.0)
Requirement already satisfied: numpy in c:\programdata\anaconda3\lib\site-packages (from missingno) (1.20.1)
Requirement already satisfied: seaborn in c:\programdata\anaconda3\lib\site-packages (from missingno) (0.11.1)
Requirement already satisfied: matplotlib in c:\programdata\anaconda3\lib\site-packages (from missingno) (3.3.4)
Requirement already satisfied: scipy in c:\programdata\anaconda3\lib\site-packages (from missingno) (1.6.2)
Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.3 in c:\programdata\anaconda3\lib\site-packages (from matplotlib->missingno) (2.4.7)
Requirement already satisfied: python-dateutil>=2.1 in c:\programdata\anaconda3\lib\site-packages (from matplotlib->missingno) (2.8.1)
Requirement already satisfied: cycler>=0.10 in c:\programdata\anaconda3\lib\site-packages (from matplotlib->missingno) (0.10.0)
Requirement already satisfied: kiwisolver>=1.0[...]<jupyter_text>Missingno library offers a very nice way to visualize the distribution of NaN values.Missingno is a Python library and compatible with Pandas.<jupyter_code>import missingno as msno
# Visualize missing values as a matrix
msno.matrix(new_df)<jupyter_output><empty_output><jupyter_text>counsellor name, fees total and fees received has same NaN trends.<jupyter_code># VisualizING the number of missing, values as a bar chart
msno.bar(new_df)
new_df.shape[1]
new_df.dropna(thresh=new_df.shape[1]-2, axis=0, inplace=True)
print(new_df.shape)
msno.matrix(new_df)
msno.heatmap(new_df)<jupyter_output><empty_output><jupyter_text>A value near -1 means if one variable appears then the other variable is very likely to be missing.A value near 0 means there is no dependence between the occurrence of missing values of two variables.A value near 1 means if one variable appears then the other variable is very likely to be present.<jupyter_code>print(new_df.shape)
print(new_df.columns.values)
#Removing Duplicates
dup_bool = new_df.duplicated(['Month', 'Counsellor Name', 'Course Name', 'Fees Total', 'Fees Received', 'Fees Pending'])
dups = sum(dup_bool) # by considering all columns.
print("Number of duplicate rows in dataset = {}".format(dups))
new_df.drop_duplicates(keep=False,inplace=True)
#Removing Duplicates
dup_bool = new_df.duplicated(['Month', 'Counsellor Name', 'Course Name', 'Fees Total', 'Fees Received', 'Fees Pending'])
dups = sum(dup_bool) # by considering all columns.
print("Number of duplicate rows in dataset = {}".format(dups))
print(new_df.shape)
#Checking the NaN values in entire dataset.
new_df.isnull().sum().sum()
new_df.describe(include="all")
msno.matrix(new_df)
#Checking for the NaN values.
print(new_df.info())
#Deleting the rows having atleast one NaN value.  
new_df.dropna(inplace=True)
msno.matrix(new_df)
#Checking for the NaN values.
print(new_df.info())
for i in new_df.columns:
    print(new_df[i].unique())
    print("\n")
new_df["Fees Total"].replace({"CARRY FORW": 0, "Fees Total": 0, "Carry forwarded":0}, inplace=True)
new_df["Fees Received"].replace({"CARRY FORW": 0, "Fees Total": 0, "Carry forwarded":0}, inplace=True)
new_df["Fees Pending"].replace({"Fees Pending": 0, "Fees Total": 0, "Carry forwarded":0}, inplace=True)
new_df["Fees Received"].replace({"Fees Received": 0, "Fees Total": 0, "Carry forwarded":0}, inplace=True)
new_df["Fees Pending"].replace({"CARRY FORW": 0, "Fees Pending": 0, "Fees Total": 0, "Carry forwarded": 0}, inplace=True)
new_df["Fees Pending"].replace({"0": 0}, inplace=True)
for i in new_df.columns:
    print(new_df[i].unique())
    print("\n")
print(new_df.info())
new_df = new_df[new_df.Month != 'megh']
#Saving the Cleaned Datasheet-1.

new_df.to_excel('Datasheet-3.xlsx')
'''
new_df["Fees Total"] = pd.to_numeric(new_df["Fees Total"])
new_df["Fees Received"] = pd.to_numeric(new_df["Fees Received"])
new_df["Fees Pending"] = pd.to_numeric(new_df["Fees Pending"])
'''
x1=new_df.groupby('Month')['Fees Total'].sum()
print(x1)
t1 = np.array(x1.index)
t2 = np.array(x1)
plt.figure(figsize=(16,6))
plt.plot(t1, t2)
plt.show()
x2=new_df.groupby('Counsellor Name')['Fees Total'].sum()
print(x2)
import seaborn as sns

plt.figure(figsize=(16,6))

sns.barplot(x=x2.index, y=x2)
x3=new_df.groupby('Month')['Fees Received'].sum()
print(x3)
plt.figure(figsize=(16,6))

sns.barplot(x=x3.index, y=x3)
t1 = np.array(x3.index)
t2 = np.array(x3)
plt.figure(figsize=(16,6))
plt.plot(t1, t2)
plt.show()
x4=new_df.groupby('Month')['Fees Pending'].sum()
print(x4)
plt.figure(figsize=(16,6))

sns.barplot(x=x4.index, y=x4)
t1 = np.array(x4.index)
t2 = np.array(x4)
plt.figure(figsize=(16,6))
plt.plot(t1, t2)
plt.show()
#groupby/agg to aggregate the groups. 
#For each group, apply set to find the unique strings, and ''.join to concatenate the strings:
new_df.groupby('Counsellor Name').agg(lambda x: ''.join(set(x)))<jupyter_output><empty_output><jupyter_text>Forecasting Techniques.  Areas where time series forecasting is used – * Forecasting Sales* Call Volume in a Call Center* Solar activity* Ocean tides* Stock market behaviour, and many others.<jupyter_code>new_df.head()
new_df.rename(columns={"Counsellor Name": "Counsellor_Name", "Course Name": "Course_Name", "Fees Total": "Fees_Total", "Fees Received":"Fees_Received", "Fees Pending": "Fees_Pending"})
new_df.info()
new_df['Datetime'] = pd.to_datetime(new_df['Month'])
new_df.head()
print(new_df.shape)
import datetime
new_df['month'] = new_df['Datetime'].dt.month
new_df['year'] = new_df['Datetime'].dt.year

new_df = new_df.drop(['Month'], axis = 1)
#Saving the Cleaned Datasheet-1.

new_df.to_excel('Datasheet-2.xlsx')
new_df = new_df.set_index('Datetime')
new_df.head()
##Creating train and test set 

train=new_df[:800] 
test=new_df[800:]
x6=train.groupby('Datetime')['Fees Total'].sum()
x7=test.groupby('Datetime')['Fees Total'].sum()
plt.figure(figsize=(16,6))
plt.plot(x6.index, x6)
plt.plot(x7.index, x7)

plt.title('Datetiem VS Fees Total')
plt.xlabel('Datetime')
plt.ylabel('Fees Total')
plt.grid()
plt.show()

plt.figure(figsize=(16,6))
plt.scatter(x6.index, x6)
plt.scatter(x7.index, x7)

plt.title('Datetiem VS Fees Total')
plt.xlabel('Datetime')
plt.ylabel('Fees Total')
plt.grid()

plt.show()

plt.figure(figsize=(15, 8))
plt.plot(train['Fees Total'])
plt.plot(test['Fees Total'])

plt.title('Datetime Vs Fees Total')
plt.ylabel('Fees Total (INR)')
plt.xlabel('Datetime')
plt.grid()
plt.show()<jupyter_output><empty_output><jupyter_text>Methos for forecasting. 1. Naive Approach.In the naive approach, we simply take the last month value and estimate the same value for the next month. Such forecasting technique which assumes that the next expected point is equal to the last observed point is called Naive Method.![image.png](attachment:image.png)<jupyter_code>dd= np.asarray(train['Fees Total'])
y_hat = test.copy()
y_hat['naive'] = dd[len(dd)-1]
plt.figure(figsize=(12,8))
plt.plot(train.index, train['Fees Total'], label='Train')
plt.plot(test.index,test['Fees Total'], label='Test')
plt.plot(y_hat.index,y_hat['naive'], label='Naive Forecast')
plt.legend(loc='best')

plt.title('Datetime Vs Fees Total:Naive Forecast ')
plt.ylabel('Fees Total (INR)')
plt.xlabel('Datetime')

plt.show()
#RMSE(Root Mean Squared)

from sklearn.metrics import mean_squared_error
from math import sqrt
rms = sqrt(mean_squared_error(test['Fees Total'], y_hat.naive))
print(rms)<jupyter_output>9503.401432881083<jupyter_text>2. Simple AverageMany a times we are provided with a dataset, which though varies by a small margin throughout it’s time period, but the average at each time period remains constant. In such a case we can forecast the price of the next month/day somewhere similar to the average of all the past month/day.Such forecasting technique which forecasts the expected value equal to the average of all previously observed points is called Simple Average technique.![image.png](attachment:image.png)We take all the values previously known, calculate the average and take it as the next value.<jupyter_code>y_hat_avg = test.copy()
y_hat_avg['avg_forecast'] = train['Fees Total'].mean()
plt.figure(figsize=(12,8))
plt.plot(train['Fees Total'], label='Train')
plt.plot(test['Fees Total'], label='Test')
plt.plot(y_hat_avg['avg_forecast'], label='Average Forecast')
plt.legend(loc='best')

plt.title('Datetime Vs Fees Total:Simple Average')
plt.ylabel('Fees Total (INR)')
plt.xlabel('Datetime')
plt.show()
#rms
rms = sqrt(mean_squared_error(test['Fees Total'], y_hat_avg.avg_forecast))
print(rms)<jupyter_output>10144.768438060037<jupyter_text>3. Moving AverageMany a times we are provided with a dataset, in which the prices/sales of the object increased/decreased sharply some time periods ago. In order to use the  previous Average method, we have to use the mean of all the previous data, but using all the previous data doesn’t sound right.Using the prices of the initial period would highly affect the forecast for the next period. Therefore as an improvement over simple average, we will take the average of the prices for last few time periods only. Obviously the thinking here is that only the recent values matter. Such forecasting technique which uses window of time period for calculating the average is called Moving Average technique. Calculation of the moving average involves what is sometimes called a “sliding window” of size n.Using a simple moving average model, we forecast the next value(s) in a time series based on the average of a fixedfinite number ‘p’ of the previous values. Thus, for all i > p![image.png](attachment:image.png)A moving average can actually be quite effective, especially if we pick the right p for the series.<jupyter_code>y_hat_avg = test.copy()
y_hat_avg['moving_avg_forecast'] = train['Fees Total'].rolling(60).mean().iloc[-1]
plt.figure(figsize=(16,8))
plt.plot(train['Fees Total'], label='Train')
plt.plot(test['Fees Total'], label='Test')
plt.plot(y_hat_avg['moving_avg_forecast'], label='Moving Average Forecast')
plt.legend(loc='best')
plt.title('Datetime Vs Fees Total:Simple Average')
plt.ylabel('Fees Total (INR)')
plt.xlabel('Datetime')
plt.show()
#RMSE
rms = sqrt(mean_squared_error(test['Fees Total'], y_hat_avg.moving_avg_forecast))
print(rms)
#Moving Average on complete dataset

def plot_moving_average(series, window, plot_intervals=False, scale=1.96):

    rolling_mean = series.rolling(window=window).mean()
    
    plt.figure(figsize=(17,8))
    plt.title('Moving average\n window size = {}'.format(window))
    plt.plot(rolling_mean, 'g', label='Rolling mean trend')
    
    #Plot confidence intervals for smoothed values
    if plot_intervals:
        mae = mean_absolute_error(series[window:], rolling_mean[window:])
        deviation = np.std(series[window:] - rolling_mean[window:])
        lower_bound = rolling_mean - (mae + scale * deviation)
        upper_bound = rolling_mean + (mae + scale * deviation)
        plt.plot(upper_bound, 'r--', label='Upper bound / Lower bound')
        plt.plot(lower_bound, 'r--')
            
    plt.plot(series[window:], label='Actual values')
    plt.legend(loc='best')
    plt.grid(True)
#Smooth by the previous 1 month (30 days)
plot_moving_average(new_df['Fees Total'], 30)
#plt.title('Datetime Vs Fees Total:Simple Average')
plt.ylabel('Fees Total (INR)')
plt.xlabel('Datetime')
plt.show()
#Smooth by previous quarter (90 days)
plot_moving_average(new_df['Fees Total'], 90, plot_intervals=True)
plt.ylabel('Fees Total (INR)')
plt.xlabel('Datetime')
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1368315918869084, 'fraction_numerical': 0.017593730792870313, 'mean_word_length': 6.215631929046563, 'pattern_counts': {'":': 22, '<': 39, '<?xml version=': 0, '>': 47, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '69719', 'license': 'MIT', 'n_tokens_mistral': 4751, 'n_tokens_neox': 4500, 'n_words': 1207, 'path': 'Day1.ipynb', 'repo_name': 'Saurav-Raghaw/Sales-Forecasting', 'size': '13022'}