<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 03-exploratory-analysis for final project
I am working on the Kaggle Grupo Bimbo competition dataset for this project. 
Link to Grupo Bimbo Kaggle competition
Step1:  Part 1. Identify the Problem
Problem
Step2:  Part 3. Parse, Mine, and Refine the data
Perform exploratory data analysis and verify the quality of the data.
Check columns and counts to drop any non-generic or near-empty columns
Step3:  Check for missing values and drop or impute
Step4:  Wrangle the data to address any issues from above checks
Step5:  Perform exploratory data analysis
Step6:  Check and convert all data types to numerical
Step7:  Part 4. Build a Model
Create a cross validation split, select and build a model, evaluate the model, and refine the model
Create cross validation sets
Step8:  Build a model
Step9:  Evaluate the model
Step10:  Part 5
Step11:  Load Kaggle test data, make predictions using model, and generate submission file
Step12:  Kaggle score 
<ASSISTANT_TASK:>
Python Code:
import numpy as np
import pandas as pd
from sklearn import cross_validation
from sklearn import metrics
from sklearn import linear_model
import seaborn as sns
import matplotlib.pyplot as plt
sns.set(style="whitegrid", font_scale=1)
%matplotlib inline
Explanation: 03-exploratory-analysis for final project
I am working on the Kaggle Grupo Bimbo competition dataset for this project. 
Link to Grupo Bimbo Kaggle competition: Kaggle-GrupoBimbo
End of explanation
# Load train data
# Given size of training data, I chose to use only 10% for speed reasons
# QUESTION - how can i randomize with python? i used sql to create the random sample below.
df_train = pd.read_csv("train_random10percent.csv")
# Check head
df_train.head()
# Load test data
df_test = pd.read_csv("test.csv")
# Check head. I noticed that I will have to drop certain columns so that test and train sets have the same features. 
df_test.head()
#given that i cannot use a significant amount of variables in train data, i created additoinal features using the mean
#i grouped on product id since i will ultimately be predicting demand for each product
df_train_mean = df_train.groupby('Producto_ID').mean().add_suffix('_mean').reset_index()
df_train_mean.head()
#from above, adding 2 additional features, the average sales units and the average demand
df_train2 = df_train.merge(df_train_mean[['Producto_ID','Venta_uni_hoy_mean', 'Demanda_uni_equil_mean']],how='inner',on='Producto_ID')
df_train2.sample(5)
# Adding features to the test set in order to match train set
df_test2 = df_test.merge(df_train_mean[['Producto_ID','Venta_uni_hoy_mean', 'Demanda_uni_equil_mean']],how='left',on='Producto_ID')
df_test2.head()
Explanation: Part 1. Identify the Problem
Problem: Given various sales/client/product data, we want to predict demand for each product at each store on a weekly basis. Per the train dataset, the average demand for a product at a store per week is 7.2 units. However, this does not factor in cases in which store managers under-predict demand for a product which we can see when returns=0 for that week. There are 74,180,464 records in the train data, of which 71,636,003 records have returns=0 or approx 96%. This generally means that managers probably often under predict product demand (unless that are exact on the money, which seems unlikely). 
Goals: The goal is to predict demand for each product at each store on a weekly basis while avoiding under-predicting demand.
Hypothesis: As stated previously, the average product demand at a store per week is 7.2 units per the train data. However, given the likelihood of managers underpredicint product demand, I hypothesize a good model should return a number higher than 7.2 units to more accurately predict demand.
Part 2. Acquire the Data
Kaggle has provided five files for this dataset:
train.csv: Use for building a model (contains target variable "Demanda_uni_equil")
test.csv: Use for submission file (fill in for target variable "Demanda_uni_equil")
cliente_tabla.csv: Contains client names (can be joined with train/test on Cliente_ID)
producto_tabla.csv: Contains product names (can be join with train/test on Producto_ID)
town_state.csv: Contains town and state (can be join with train/test on Agencia_ID)
Notes: I will further split train.csv to generate my own cross validation set. However, I will use all of train.csv to train my final model since Kaggle has already supplied a test dataset. Additionally, I am only using a random 10% of the train data given to me for EDA and model development. Using the entire train dataset proved to be too time consuming for the quick iternations needed for initial modeling building and EDA efforts. I plan to use 100% of the train dataset once I build a model I'm comfortable with. I may have to explore using EC2 for this effort.
End of explanation
# Check columns
print "train dataset columns:"
print df_train2.columns.values
print 
print "test dataset columns:"
print df_test2.columns.values
# Check counts
print "train dataset counts:"
print df_train2.count()
print
print "test dataset counts:"
print df_test2.count()
Explanation: Part 3. Parse, Mine, and Refine the data
Perform exploratory data analysis and verify the quality of the data.
Check columns and counts to drop any non-generic or near-empty columns
End of explanation
# Check counts for missing values in each column
print "train dataset missing values:"
print df_train2.isnull().sum()
print
print "test dataset missing values:"
print df_test2.isnull().sum()
Explanation: Check for missing values and drop or impute
End of explanation
# Drop columns not included in test dataset
df_train2 = df_train2.drop(['Venta_uni_hoy', 'Venta_hoy', 'Dev_uni_proxima', 'Dev_proxima'], axis=1)
# Check data
df_train2.head()
# Drop blank values in test set and replace with mean
# Replace missing values for venta_uni_hoy_mean using mean
df_test2.loc[(df_test2['Venta_uni_hoy_mean'].isnull()), 'Venta_uni_hoy_mean'] = df_test2['Venta_uni_hoy_mean'].dropna().mean()
# Replace missing values for demand using mean
df_test2.loc[(df_test2['Demanda_uni_equil_mean'].isnull()), 'Demanda_uni_equil_mean'] = df_test2['Demanda_uni_equil_mean'].dropna().mean()
print "test dataset missing values:"
print df_test2.isnull().sum()
Explanation: Wrangle the data to address any issues from above checks
End of explanation
# Get summary statistics for data
df_train2.describe()
#RE RUN THIS LAST
# Get pair plot for data
sns.pairplot(df_train2)
#show demand by weeks
timing = pd.read_csv('train_random10percent.csv', usecols=['Semana','Demanda_uni_equil'])
print(timing['Semana'].value_counts())
plt.hist(timing['Semana'].tolist(), bins=7, color='blue')
plt.show()
#QUESTION - is this a time series problem since we are predicting demand for weeks 10 and 11? and beyond?
#Show box plot of demand by week
sns.factorplot(
    x='Semana',
    y='Demanda_uni_equil',
    data=df_train2,
    kind='box')
Explanation: Perform exploratory data analysis
End of explanation
# Check data types
df_train.dtypes
#these are all numerical but are not continuous values and therefore don't have relative significant to one another, except for week
#however, creating dummy variables for all these is too memory intensive. as such, might have to explore using a random forest model
#in addition to the linear regression model
Explanation: Check and convert all data types to numerical
End of explanation
#create cross validation sets
#set target variable name
target = 'Demanda_uni_equil'
#set X and y
X = df_train2.drop([target], axis=1)
y = df_train2[target]
# create separate training and test sets with 60/40 train/test split
X_train, X_test, y_train, y_test = cross_validation.train_test_split(X, y, test_size= .4, random_state=0) 
Explanation: Part 4. Build a Model
Create a cross validation split, select and build a model, evaluate the model, and refine the model
Create cross validation sets
End of explanation
#create linear regression object
lm = linear_model.LinearRegression()
#train the model using the training data
lm.fit(X_train,y_train)
Explanation: Build a model
End of explanation
# Check R^2 on test set
print "R^2: %0.3f" % lm.score(X_test,y_test)
# Check MSE on test set
#http://scikit-learn.org/stable/modules/classes.html#module-sklearn.metrics
print "MSE: %0.3f" % metrics.mean_squared_error(y_test, lm.predict(X_test))
#QUESTION - should i check this on train set?
print "MSE: %0.3f" % metrics.mean_squared_error(y_train, lm.predict(X_train))
Explanation: Evaluate the model
End of explanation
# Set target variable name
target = 'Demanda_uni_equil'
# Set X_train and y_train
X_train = df_train2.drop([target], axis=1)
y_train = df_train2[target]
# Build tuned model
#create linear regression object
lm = linear_model.LinearRegression()
#train the model using the training data
lm.fit(X_train,y_train)
# Score tuned model
print "R^2: %0.3f" % lm.score(X_train, y_train)
print "MSE: %0.3f" % metrics.mean_squared_error(y_train, lm.predict(X_train))
Explanation: Part 5: Present the Results
Generate summary of findings and kaggle submission file.
NOTE: For the purposes of generating summary narratives and kaggle submission, we can train the model on the entire training data provided in train.csv.
Load Kaggle training data and use entire data to train tuned model
End of explanation
#create data frame for submission
df_sub = df_test2[['id']]
df_test2 = df_test2.drop('id', axis=1)
#predict using tuned model
df_sub['Demanda_uni_equil'] = lm.predict(df_test2)
df_sub.describe()
d = df_sub['Demanda_uni_equil']
d[d<0] = 0
df_sub.describe()
# Write submission file
df_sub.to_csv("mysubmission3.csv", index=False)
Explanation: Load Kaggle test data, make predictions using model, and generate submission file
End of explanation
#notes
#want to try to use a classifier like random forest or logistic regression
Explanation: Kaggle score : 0.75682
End of explanation
<END_TASK>