Freight Optimization¶
Notes: https://notes.pipal.in/2018/vmware-ml2/
1 - Introduction | Freight Optimization
In [2]:
import numpy as np
import pandas as pd
Datasets¶
In [3]:
url = "https://notes.pipal.in/2018/vmware-ml2/10-Secondary-Freight-Data.csv"
matrix_url = "https://notes.pipal.in/2018/vmware-ml2/13-location-distance-matrix.csv"
In [4]:
freight = pd.read_csv(url)
In [5]:
freight.head()
Out[5]:
In [6]:
freight.dtypes
Out[6]:
In [8]:
freight.columns
Out[8]:
In [90]:
matrix = pd.read_csv(matrix_url)
In [91]:
matrix.head()
Out[91]:
Refine the Data¶
Basic Cleaning:
- fix column names
- drop unused/redundant columns
Basic Cleaning¶
In [9]:
columns = {
'Sales Order No. New': 'orderno',
'Date': 'date',
'Supplying DC Location': 'source',
'Customer Code New': 'custcode',
'Customer Town': 'dest',
'Qty (in cases)': 'qty',
'Freight/Cartage': 'cartage',
'Remarks': 'remarks',
'Amount': 'amount'
}
In [12]:
freight.rename(columns=columns, inplace=True)
In [13]:
freight.head()
Out[13]:
In [15]:
freight.drop(["orderno", "custcode"], axis=1, inplace=True)
In [16]:
freight.head()
Out[16]:
In [18]:
freight.amount.dtype
Out[18]:
In [23]:
freight.amount.unique()[:20]
Out[23]:
In [25]:
freight.dtypes
Out[25]:
In [26]:
# remove commas in the numbers
freight.amount.head()
Out[26]:
In [33]:
freight.amount.head().str.replace(",", "")
Out[33]:
In [35]:
freight.amount = freight.amount.str.replace(",", "")
In [36]:
freight.amount.head()
Out[36]:
Problem: Replace commas from cartage column.
In [38]:
freight.cartage = freight.cartage.str.replace(",", "")
In [42]:
# fix column types
freight.amount = pd.to_numeric(freight.amount)
freight.cartage = pd.to_numeric(freight.cartage)
freight.date = pd.to_datetime(freight.date)
In [43]:
freight.head()
Out[43]:
In [44]:
# dtypes
freight.dtypes
Out[44]:
Q: Is the remarks column required?
Lets see.
In [45]:
freight.remarks.unique()
Out[45]:
In [46]:
freight[freight.remarks=='Charges - Per case'].head()
Out[46]:
In [47]:
freight[freight.remarks=='Charges - Per Consignment'].head()
Out[47]:
Fix Missing Values¶
In [49]:
len(freight)
Out[49]:
In [50]:
freight.isnull().head()
Out[50]:
In [51]:
freight.isnull().sum()
Out[51]:
In [52]:
freight[freight.source.isnull()]
Out[52]:
In [53]:
# remove NaN values
freight.dropna(inplace=True)
Standadize text fields¶
In [58]:
shortunits = {
"Charges - Per case": "case",
"Charges - Per Consignment": "consignment"
}
freight.remarks = freight.remarks.replace(shortunits)
In [59]:
freight.remarks.value_counts()
Out[59]:
In [61]:
freight.dest.value_counts()
Out[61]:
In [72]:
def fix_names(c):
c = c.str.strip()
c = c.str.title()
return c
In [73]:
freight.source = fix_names(freight.source)
freight.dest = fix_names(freight.dest)
In [74]:
freight.head()
Out[74]:
Q: Which source has the maximum quantity of transfer?
In [77]:
freight.groupby('source').sum().head()
Out[77]:
In [81]:
freight.groupby('source').sum().qty.sort_values(ascending=False).head()
Out[81]:
In [82]:
(freight.groupby('source')
.sum()
.qty
.sort_values(ascending=False)
.head()
)
Out[82]:
Q: What is the source/destination pair that has the maximum quantity of transfer?
In [84]:
(freight.groupby(['source', 'dest'])
.sum()
.qty
.sort_values(ascending=False)
.head()
)
Out[84]:
Q: Show data for Bangalore -> Bangalore.
In [86]:
freight[(freight.source == 'Bangalore') & (freight.dest == 'Bangalore')].head()
Out[86]:
In [88]:
freight['computed_amount'] = freight.qty * freight.cartage
In [89]:
freight.head()
Out[89]:
Problem: Refine the location-distance-matrix data and merge it with the freight dataframe.
Hint: pd.merge?
In [94]:
matrix = pd.read_csv(matrix_url)
In [95]:
matrix.head()
Out[95]:
In [97]:
# fix column names
columns = {
"Source": "source",
"S. Longitude": "slon",
"S. Latitude": "slat",
"Destination": "dest",
"D. Longitude": "dlon",
"D. Latitude": "dlat",
"Lane": "lane",
"Distance": "distance"
}
matrix.rename(columns=columns, inplace=True)
In [98]:
matrix.head()
Out[98]:
In [99]:
# missing values
matrix.isnull().sum()
Out[99]:
In [101]:
# standadize names
matrix.source = fix_names(matrix.source)
matrix.dest = fix_names(matrix.dest)
matrix.head()
Out[101]:
In [111]:
(matrix.source + "--" + matrix.dest).nunique(), len(matrix)
Out[111]:
In [113]:
# drop the duplicates
matrix.drop_duplicates(['source', 'dest'], inplace=True)
In [116]:
len(matrix)
Out[116]:
In [114]:
df = pd.merge(freight, matrix, how='left', on=['source', 'dest'])
In [115]:
len(freight), len(matrix), len(df)
Out[115]:
In [117]:
df.head()
Out[117]:
In [121]:
df.isnull().sum()
Out[121]:
In [122]:
len(df)
Out[122]:
In [133]:
df[df.isnull().sum(axis=1)>0].head()
Out[133]:
Q: What is the total amount where distance is missing?
In [134]:
df[df.isnull().sum(axis=1)>0].amount.sum()
Out[134]:
In [137]:
df[df.isnull().sum(axis=1)>0].amount.sum() / df.amount.sum()
Out[137]:
In [138]:
freight.source.nunique(), matrix.source.nunique(), df.source.nunique()
Out[138]:
In [139]:
freight.source.unique()
Out[139]:
In [140]:
matrix.source.unique()
Out[140]:
In [143]:
set(freight.source.unique()) - set(matrix.source.unique())
Out[143]:
In [144]:
set(freight.dest.unique()) - set(matrix.dest.unique())
Out[144]:
In [149]:
cities = {
'Ahemadabad': 'Ahmedabad',
'Bbsr': 'Bhubaneshwar',
'Gauwhati': 'Guwahati',
'Zirakhpur': 'Zirakpur'
}
# freight.replace({"source": cities}).head()
freight.replace({"source": cities}, inplace=True)
In [150]:
freight.head()
Out[150]:
In [151]:
df = pd.merge(freight, matrix, how='left', on=['source', 'dest'])
In [152]:
df.isnull().sum()
Out[152]:
In [154]:
df[df.isnull().sum(axis=1) > 0].head()
Out[154]:
In [155]:
df[df.isnull().sum(axis=1) > 0].amount.sum() / df.amount.sum()
Out[155]:
In [166]:
data = df.dropna()
In [207]:
data = data[data.remarks == "case"].copy()
Explore¶
In [180]:
#!pip install altair
In [161]:
import altair as alt
import matplotlib.pyplot as plt
%matplotlib inline
In [184]:
plt.style.use("ggplot")
In [185]:
data.distance.hist(bins=40)
Out[185]:
In [186]:
data.cartage.hist(bins=40);
In [183]:
data[data.cartage > 100]
Out[183]:
In [199]:
data.plot(kind="scatter", x="distance", y="cartage", alpha=0.05);
In [200]:
data.plot(kind="scatter", x="distance", y="cartage", alpha=0.05, logy=True);
In [213]:
data.loc[data.distance==0, "distance"] = 5
In [214]:
data.plot(kind="scatter", x="distance", y="cartage", alpha=0.05, logy=True, logx=True);
In [216]:
data.plot(kind="scatter",
x="distance",
y="cartage",
c="amount",
alpha=0.05,
logy=True,
logx=True,
cmap='viridis');
In [219]:
data.to_csv("data.csv")
Model¶
In [220]:
data.head()
Out[220]:
Do we need to take date column in modeling?
Let us see if date has any effect on the cartage.
In [226]:
data[(data.source=='Bangalore') & (data.dest == 'Hubli')].describe()
Out[226]:
In [231]:
data.groupby(['source', 'dest']).std().cartage.value_counts()
Out[231]:
In [233]:
data.groupby(['source', 'dest']).std().cartage.hist();
In [247]:
# roll up
agg_func = {
"cartage": ["mean"],
"distance": ["mean"],
"qty": ["sum"],
"amount": ["sum", "count"]
}
newdata = (data
.groupby(["source", "dest", "slat", "slon", "dlat", "dlon"])
.agg(agg_func)
.reset_index())
newdata.columns = newdata.columns.droplevel(1)
newdata.columns = list(newdata.columns)[:-1] + ['count']
newdata.head()
Out[247]:
In [252]:
newdata.plot(kind='scatter', x='distance', y='cartage', alpha=0.3);
In [254]:
newdata.plot(kind='scatter', x='distance', y='cartage', alpha=0.3, logy=True, logx=True);
In [257]:
newdata['distance_log'] = np.log10(newdata.distance)
newdata['cartage_log'] = np.log10(newdata.cartage)
X = newdata[['distance_log']]
y = newdata.cartage_log
In [258]:
X.head()
Out[258]:
In [259]:
y.head()
Out[259]:
In [260]:
from sklearn.linear_model import LinearRegression
In [262]:
model = LinearRegression()
model.fit(X, y)
Out[262]:
In [263]:
model.intercept_, model.coef_
Out[263]:
In [295]:
def show_predicitons(X, y, y_pred):
plt.scatter(X, y)
plt.plot(X, y_pred, color='b')
In [297]:
y_pred = model.predict(X)
show_predicitons(X, y, y_pred)
In [300]:
from sklearn.model_selection import cross_val_score
In [322]:
model = LinearRegression()
score = cross_val_score(model,
X, y,
scoring='neg_mean_squared_error',
cv=5,
n_jobs=-1,)
np.mean(score)
Out[322]:
In [311]:
score
Out[311]:
Lets build another model with 2 features.
In [318]:
X2 = newdata[['distance_log', 'count']]
In [321]:
model = LinearRegression()
score = cross_val_score(model,
X2, y,
scoring='neg_mean_squared_error',
cv=5,
n_jobs=-1,)
np.mean(score)
Out[321]:
In [325]:
newdata['cartage_per_distance'] = newdata['cartage'] / newdata['distance']
newdata['cartage_per_distance_log'] = np.log10(newdata['cartage_per_distance'])
In [324]:
newdata.plot(kind='scatter',
x='distance',
y='cartage_per_distance',
logx=True,
logy=True);
In [329]:
model = LinearRegression()
In [330]:
X = newdata[['distance_log']]
y = newdata['cartage_per_distance_log']
In [331]:
score = cross_val_score(model,
X, y,
scoring='neg_mean_squared_error',
cv=5,
n_jobs=-1,)
np.mean(score)
Out[331]:
In [332]:
model.fit(X, y)
y_pred = model.predict(X)
show_predicitons(X, y, y_pred)
Tree-based models¶
In [333]:
from sklearn.tree import DecisionTreeRegressor
In [368]:
tree = DecisionTreeRegressor(max_depth=2)
In [369]:
X = newdata[['distance_log']]
y = newdata['cartage_per_distance_log']
In [370]:
tree.fit(X, y)
Out[370]:
In [371]:
def show_predicitons(X, y, y_pred):
plt.scatter(X, y, color='r')
plt.scatter(X, y_pred, color='b')
In [372]:
y_pred = tree.predict(X)
show_predicitons(X, y, y_pred)
In [373]:
#!pip install modelvis
In [374]:
import modelvis
In [375]:
modelvis.render_tree(tree, feature_names=['distance_log'])
Out[375]:
In [377]:
model.predict([[1.4]])
Out[377]:
Grid Search¶
In [378]:
from sklearn.model_selection import GridSearchCV
In [380]:
tree = DecisionTreeRegressor()
param_grid = {
"max_depth": [2, 3, 4, 5, 6, 7, 8, 9, 10]
}
grid = GridSearchCV(tree,
param_grid=param_grid,
cv=10,
scoring="neg_mean_squared_error")
grid.fit(X, y)
grid.best_estimator_
Out[380]:
In [396]:
tree = grid.best_estimator_
df2 = newdata.sort_values(by='distance_log')
X = df2[['distance_log']]
y = df2.cartage_per_distance_log
def show_predicitons2(model, X, y):
X = X.as_matrix()
X1 = np.arange(X[0].min(), X[0].max(), 1000)
y1 = model.predict(X1)
def show_predicitons(X, y, y_pred):
plt.scatter(X, y, color='r')
plt.plot(X, y_pred, color='b')
y_pred = tree.predict(X)
show_predicitons(X, y, y_pred)
Cars dataset¶
Download the cars dataset and build a model to predict the price of a car.
features: brand, kmpl, bhp, type
target: price