<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 TxDOT Project
Step1:  Load in the Data
Step2:  Formatting & Identifying the Data
Step3:  Exploring the Data
We see that Bids are Log Normally Distributed
Step4:  Because the TxDot Estimates are so highly correlated with the winning bid, we can use it as a baseline for the model predictions
My idea was to classify the TxDot Estimates according to their scoring method of estimating within 10% of the winning bid and then having 3 separate regressive models for the 3 classes (within 10%, more than 10%, less than 10%). 
TxDot's goal for their estimates is to have 55% of their estimates within 10% of the winning bid.
TxDot considers bids over or under the estimate by more than 10% as bad
We can then use this score to judge our model predictions
Step5:  We now need to build and train a model that can classify a bid as over or under the TxDOT estimate
Here We can see how this classification might look
Step6:  1) Splitting the Data into Training and Testing Sets
Step7:  ### This will split the training and testing sets into a more useful format for loading into models
Step8:  Classifying using Decision Tree
Step9:  Model given Wining Bid is within 10% of TxDot Estimate
Step10:  Predicting
Step11:  Model given Wining Bid is More Than 10% of TxDot Estimate
Step12:  Predicting
Step13:  Model given Wining Bid is Less Than 10% of TxDot Estimate
Step14:  Predicting
Step15:  Our Model Prediction of Winning Bid will be a Weighted average of Bid predictions with weights being the probability of being classified as such class found from Logistic Regression
Step16:  df_test['Hyp_More'] = 1
df_test['Hyp_Less'] = 1
df_test['Hyp_Within'] = 1
Step17:  E[Bid] = P(within10)xE[within10] + P(above10)xE[above10] + P(below10)xE[below10]
Step18:  Bid Model Comparison by Graph
Red is Bad  (beyond the 10% threshhold)
Green is Good   (within 10% of actual)
TxDOT Estimates
Step19:  Model Predictions
<ASSISTANT_TASK:>
Python Code:
tree1 = tree.DecisionTreeClassifier( criterion ='entropy',random_state = 0) 
fittedtree=tree1.fit( X_train, y_train) 
#print metrics.confusion_matrix(y_train, fittedtree.predict(X_train))
#print metrics.accuracy_score(y_train, fittedtree.predict(X_train))
cross_validation.cross_val_score(tree1, X_train, y_train, cv = 10).mean()
#tree.export_graphviz(fittedtree,  out_file =' tree.dot',  feature_names =['Sepal Length', 'Sepal Width', 'Petal Length', 'Petal Width'])
import os
import math
import numpy as np
import pandas as pd
%matplotlib inline
import matplotlib.pyplot as plt
import csv
import seaborn as sns
import statsmodels.api as sm
import statsmodels.formula.api as smf
from sklearn import feature_selection, linear_model
Explanation: TxDOT Project
End of explanation
df = pd.read_csv('C:/Users/Collin/Documents/Python_Projects/TxDOT/data/Bid_Data.csv')
len(df)
Explanation: Load in the Data
End of explanation
df = df[df['Rank'] == 1]
df = df[df['Type'] == 'Construction']
df = df.drop(' From', 1)
df = df.drop('To', 1)
df = df.drop('Contract Description', 1)
df = df.drop('Contractor', 1)
df = df.drop('Contract Category', 1)
df = df.drop('LNG MON', 1)
df = df.drop('MONTH', 1)
df['Award Amount'] = df['Award Amount'].str.lstrip('$')
df['Engineers Estimate'] = df['Engineers Estimate'].str.lstrip('$')
df['Award Amount'] = df['Award Amount'].str.replace(',','').astype(float)
df['Engineers Estimate'] = df['Engineers Estimate'].str.replace(',','').astype(float)
#Renaming Variables
df['EngEst'] = df['Engineers Estimate']
df['NBidders'] = df['Number of Bidders']
df['Date'] = pd.to_datetime(df['Letting Date'])
df.set_index('Date' , inplace=True)
df['Year'] = df.index.year
df['Month'] = df.index.month
df['WinBid'] = df['Award Amount']
# Creating New Varialbes
df['Diff'] = df['EngEst'] - df['WinBid']
df['lnWinBid'] = np.log(df['WinBid'])
df['lnEngEst'] = np.log(df['EngEst'])
df['DiffLn'] = df['lnWinBid'] - df['lnEngEst']
df['Within10Percent'] = 1 
df['PercentOff'] = df['Diff'] / df['EngEst']
df['MoreOrLessThan10'] = 0
df['LessThan10'] = 0
df['MoreThan10'] = 0
df.loc[(df.PercentOff > .10) , 'Within10Percent'] = 0
df.loc[(df.PercentOff < -.10) , 'Within10Percent'] = 0
df.loc[(df.PercentOff > .10) , 'MoreOrLessThan10'] = 1
df.loc[(df.PercentOff < -.10) , 'MoreOrLessThan10'] = 2
df.loc[(df.PercentOff > .10) , 'MoreThan10'] = 1
df.loc[(df.PercentOff < -.10) , 'LessThan10'] = 1
print len(df)
Explanation: Formatting & Identifying the Data
End of explanation
sns.jointplot(x="EngEst", y="WinBid", data=df, kind="reg"); sns.jointplot(x="lnEngEst", y="lnWinBid", data=df, kind="reg");
Explanation: Exploring the Data
We see that Bids are Log Normally Distributed
End of explanation
#Using ALL the Data
Percent = float(df.Within10Percent.sum()) / len(df)
print  (Percent)*100 , '% of All the TxDOT estimates were within 10% of actual bid'
Percent_April_2016 = float(df[(df.Year == 2016) & (df.Month == 4)].Within10Percent.sum()) / len(df_test)
print  (Percent_April_2016)*100 , '% of the April 2016 TxDOT estimates were within 10% of actual bid'
Explanation: Because the TxDot Estimates are so highly correlated with the winning bid, we can use it as a baseline for the model predictions
My idea was to classify the TxDot Estimates according to their scoring method of estimating within 10% of the winning bid and then having 3 separate regressive models for the 3 classes (within 10%, more than 10%, less than 10%). 
TxDot's goal for their estimates is to have 55% of their estimates within 10% of the winning bid.
TxDot considers bids over or under the estimate by more than 10% as bad
We can then use this score to judge our model predictions
End of explanation
cmap = {'0': 'g', '1': 'r', '2': 'b' }
df['cMoreOrLessThan10'] = df.MoreOrLessThan10.apply(lambda x: cmap[str(x)])
print df.plot('EngEst', 'WinBid', kind='scatter', c=df.cMoreOrLessThan10)
cmap = {'0': 'g', '1': 'r', '2': 'b' }
df['cMoreOrLessThan10'] = df.MoreOrLessThan10.apply(lambda x: cmap[str(x)])
print df.plot('lnEngEst', 'lnWinBid', kind='scatter', c=df.cMoreOrLessThan10)
Explanation: We now need to build and train a model that can classify a bid as over or under the TxDOT estimate
Here We can see how this classification might look
End of explanation
df_test = df[(df.Year == 2016) & (df.Month == 4)]
print len(df_test) , 'projects in April 2016'
df_train = df[(df.Year != 2016) | (df.Month != 4)]
print len(df_train) ,'projects from Jan 2010 to April 2016'
#df_train[['Year','Month']].tail()
#df_test.columns
Explanation: 1) Splitting the Data into Training and Testing Sets
End of explanation
#names_x = ['Length','Year','Month','lnEngEst','Time', 'Highway', 'District', 'County' ]
names_x = ['Length','Year','Month','lnEngEst','Time', 'NBidders']
names_y = ['MoreOrLessThan10']
df_Train_x = df_train[ names_x ]
df_Train_y = df_train[ names_y ]
df_Test_x = df_test[ names_x ]
df_Test_y = df_test[ names_y ]
df_Train_x.head()
test_X.head()
Explanation: ### This will split the training and testing sets into a more useful format for loading into models
End of explanation
from sklearn import tree
clf = tree.DecisionTreeClassifier()
clf = clf.fit(df_Train_x, df_Train_y)
clf.predict(df_Test_x)
clf.score(df_Test_x, df_Test_y)
Explanation: Classifying using Decision Tree
End of explanation
#subsets the training data to just those who were within 10% of the TxDot Estimate
df_train_within = df[df.Within10Percent == 1]
model_3 = smf.ols(formula = 'lnWinBid ~ lnEngEst+Year+Month+Year*Month+NBidders+NBidders*Year+Time', data = df_train_within).fit()
model_3.summary()
Explanation: Model given Wining Bid is within 10% of TxDot Estimate
End of explanation
df_test.loc[:,'Lnprediction_within'] = model_3.predict(test_X)
df_test.Lnprediction_within.head()
Explanation: Predicting
End of explanation
#subsets the training data to just those who were more than 10% of the TxDot Estimate
df_train_more = df[df.MoreThan10 == 1]
model_4 = smf.ols(formula = 'lnWinBid ~ lnEngEst+Year+Month+Year*Month+NBidders+NBidders*Year+Time', data = df_train_more).fit()
model_4.summary()
Explanation: Model given Wining Bid is More Than 10% of TxDot Estimate
End of explanation
df_test.loc[:,'Lnprediction_more'] = model_4.predict(test_X)
Explanation: Predicting
End of explanation
#subsets the training data to just those who were less than 10% of the TxDot Estimate
df_train_less = df[df.LessThan10 == 1]
model_5 = smf.ols(formula = 'lnWinBid ~ lnEngEst+Year+Month+Year*Month+NBidders+NBidders*Year+Time', data = df_train_less).fit()
model_5.summary()
Explanation: Model given Wining Bid is Less Than 10% of TxDot Estimate
End of explanation
df_test.loc[:,'Lnprediction_less'] = model_5.predict(test_X)
Explanation: Predicting
End of explanation
#df_test.columns
Explanation: Our Model Prediction of Winning Bid will be a Weighted average of Bid predictions with weights being the probability of being classified as such class found from Logistic Regression
End of explanation
df_test[['p_more', 'p_less', 'p_within', 'Lnprediction_within', 'Lnprediction_more', 'Lnprediction_less']].head()
Explanation: df_test['Hyp_More'] = 1
df_test['Hyp_Less'] = 1
df_test['Hyp_Within'] = 1
End of explanation
df_test.loc[:,'lnpred'] = df_test.p_within*df_test.Lnprediction_within + df_test.p_more*df_test.Lnprediction_more + df_test.p_less*df_test.Lnprediction_less  
df_test.loc[:,'BidPrediction'] = np.exp(df_test.loc[:,'lnpred'])
df_test.loc[:,'PredDiff'] = df_test.loc[:,'BidPrediction'] - df_test.loc[:,'WinBid']
df_test.loc[:,'PredPercentOff'] = df_test.loc[:,'PredDiff'] / df_test.loc[:,'BidPrediction']
df_test.loc[:,'PredWithin10Percent'] = 1
df_test.loc[(df_test.PredPercentOff > .10) , 'PredWithin10Percent'] = 0
df_test.loc[(df_test.PredPercentOff < -.10) , 'PredWithin10Percent'] = 0
ModelPercent = float(df_test.PredWithin10Percent.sum()) / len(df_test)
PercentIncrease = (ModelPercent)*100 - (Percent_April_2016)*100
NumberCorrectIncrease = (PercentIncrease/100)*len(df_test)
print  (Percent_April_2016)*100 , '% of the TxDOT estimates were within 10% of actual bid'
print  (ModelPercent)*100 , '% of the Model predictions were within 10% of actual bid'
print 
print 'this is a increase of :', PercentIncrease, '%'
print 'or', NumberCorrectIncrease, 'more estimates within the 10% threshhold'
print 'In April 2016 TxDOT under estimated bids by: ' , df_test.Diff.sum()
print
print 'In April 2016 the Model under estimated bids by: ' ,df_test.PredDiff.sum()
print
print 'In April 2016 the model was ' , df_test.Diff.sum() - df_test.PredDiff.sum() , 'closer to the winning bids than TxDOT' 
print
print 'The model predicted a sum of' ,df_test.BidPrediction.sum() ,'for all the projects in April 2016'
print
print 'TxDOT predicted a sum of' ,df_test.EngEst.sum() ,'for all the projects in April 2016'
df_test[['Diff','PredDiff']].std()
df_test[['Diff','PredDiff']].describe()
Explanation: E[Bid] = P(within10)xE[within10] + P(above10)xE[above10] + P(below10)xE[below10]
End of explanation
cmap = {'0': 'r', '1': 'g' }
df_test.loc[:,'cWithin10Percent'] = df_test.Within10Percent.apply(lambda x: cmap[str(x)])
print df_test.plot('lnEngEst', 'lnWinBid', kind='scatter', c=df_test.cWithin10Percent)
Explanation: Bid Model Comparison by Graph
Red is Bad  (beyond the 10% threshhold)
Green is Good   (within 10% of actual)
TxDOT Estimates
End of explanation
predcmap = {'0': 'r', '1': 'g' }
df_test.loc[:,'cPredWithin10Percent'] = df_test.PredWithin10Percent.apply(lambda x: predcmap[str(x)])
print df_test.plot('lnpred', 'lnWinBid', kind='scatter', c=df_test.cPredWithin10Percent)
Explanation: Model Predictions
End of explanation
<END_TASK>