Superstore_analytics.ipynb

by tirthajyoti

notebooks/Superstore_analytics.ipynb

Superstore sells data analytics with Pandas

Dr. Tirthajyoti Sarkar, Fremont, CA 94536

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

Exercise 1: Load and examine a superstore sales data from an Excel file

df = pd.read_excel("Sample - Superstore.xls")
df.head(10)
Row ID Order ID Order Date Ship Date Ship Mode Customer ID Customer Name Segment Country City ... Postal Code Region Product ID Category Sub-Category Product Name Sales Quantity Discount Profit
0 1 CA-2016-152156 2016-11-08 2016-11-11 Second Class CG-12520 Claire Gute Consumer United States Henderson ... 42420 South FUR-BO-10001798 Furniture Bookcases Bush Somerset Collection Bookcase 261.9600 2 0.00 41.9136
1 2 CA-2016-152156 2016-11-08 2016-11-11 Second Class CG-12520 Claire Gute Consumer United States Henderson ... 42420 South FUR-CH-10000454 Furniture Chairs Hon Deluxe Fabric Upholstered Stacking Chairs,... 731.9400 3 0.00 219.5820
2 3 CA-2016-138688 2016-06-12 2016-06-16 Second Class DV-13045 Darrin Van Huff Corporate United States Los Angeles ... 90036 West OFF-LA-10000240 Office Supplies Labels Self-Adhesive Address Labels for Typewriters b... 14.6200 2 0.00 6.8714
3 4 US-2015-108966 2015-10-11 2015-10-18 Standard Class SO-20335 Sean O'Donnell Consumer United States Fort Lauderdale ... 33311 South FUR-TA-10000577 Furniture Tables Bretford CR4500 Series Slim Rectangular Table 957.5775 5 0.45 -383.0310
4 5 US-2015-108966 2015-10-11 2015-10-18 Standard Class SO-20335 Sean O'Donnell Consumer United States Fort Lauderdale ... 33311 South OFF-ST-10000760 Office Supplies Storage Eldon Fold 'N Roll Cart System 22.3680 2 0.20 2.5164
5 6 CA-2014-115812 2014-06-09 2014-06-14 Standard Class BH-11710 Brosina Hoffman Consumer United States Los Angeles ... 90032 West FUR-FU-10001487 Furniture Furnishings Eldon Expressions Wood and Plastic Desk Access... 48.8600 7 0.00 14.1694
6 7 CA-2014-115812 2014-06-09 2014-06-14 Standard Class BH-11710 Brosina Hoffman Consumer United States Los Angeles ... 90032 West OFF-AR-10002833 Office Supplies Art Newell 322 7.2800 4 0.00 1.9656
7 8 CA-2014-115812 2014-06-09 2014-06-14 Standard Class BH-11710 Brosina Hoffman Consumer United States Los Angeles ... 90032 West TEC-PH-10002275 Technology Phones Mitel 5320 IP Phone VoIP phone 907.1520 6 0.20 90.7152
8 9 CA-2014-115812 2014-06-09 2014-06-14 Standard Class BH-11710 Brosina Hoffman Consumer United States Los Angeles ... 90032 West OFF-BI-10003910 Office Supplies Binders DXL Angle-View Binders with Locking Rings by S... 18.5040 3 0.20 5.7825
9 10 CA-2014-115812 2014-06-09 2014-06-14 Standard Class BH-11710 Brosina Hoffman Consumer United States Los Angeles ... 90032 West OFF-AP-10002892 Office Supplies Appliances Belkin F5C206VTEL 6 Outlet Surge 114.9000 5 0.00 34.4700

10 rows × 21 columns

df.drop('Row ID',axis=1,inplace=True)
df.shape
(9994, 20)
df['Customer Name'].nunique()
793

Exercise 2: Subsetting the DataFrame

df_subset = df.loc[[i for i in range(5,10)],['Customer ID','Customer Name','City','Postal Code','Sales']]
df_subset
Customer ID Customer Name City Postal Code Sales
5 BH-11710 Brosina Hoffman Los Angeles 90032 48.860
6 BH-11710 Brosina Hoffman Los Angeles 90032 7.280
7 BH-11710 Brosina Hoffman Los Angeles 90032 907.152
8 BH-11710 Brosina Hoffman Los Angeles 90032 18.504
9 BH-11710 Brosina Hoffman Los Angeles 90032 114.900

Exercise 3: An example use case – determining statistics on sales and profit for records 100-199

df_subset = df.loc[[i for i in range(100,200)],['Sales','Profit']]
df_subset.describe()
Sales Profit
count 100.000000 100.000000
mean 262.957220 0.347574
std 858.983762 170.744869
min 1.788000 -1359.992000
25% 21.327000 1.635900
50% 66.960000 9.653600
75% 177.095000 23.458800
max 8159.952000 585.552000
df_subset.plot.box()
plt.title("Boxplot of sales and profit",fontsize=15)
plt.ylim(0,500)
plt.grid(True)
plt.show()

Exercise 4: A useful function – unique

df['State'].unique()
array(['Kentucky', 'California', 'Florida', 'North Carolina',
       'Washington', 'Texas', 'Wisconsin', 'Utah', 'Nebraska',
       'Pennsylvania', 'Illinois', 'Minnesota', 'Michigan', 'Delaware',
       'Indiana', 'New York', 'Arizona', 'Virginia', 'Tennessee',
       'Alabama', 'South Carolina', 'Oregon', 'Colorado', 'Iowa', 'Ohio',
       'Missouri', 'Oklahoma', 'New Mexico', 'Louisiana', 'Connecticut',
       'New Jersey', 'Massachusetts', 'Georgia', 'Nevada', 'Rhode Island',
       'Mississippi', 'Arkansas', 'Montana', 'New Hampshire', 'Maryland',
       'District of Columbia', 'Kansas', 'Vermont', 'Maine',
       'South Dakota', 'Idaho', 'North Dakota', 'Wyoming',
       'West Virginia'], dtype=object)
df['State'].nunique()
49
df['Country'].unique()
array(['United States'], dtype=object)
df.drop('Country',axis=1,inplace=True)

Exercise 5: Conditional Selection and Boolean Filtering

df_subset = df.loc[[i for i in range (10)],['Ship Mode','State','Sales']]
df_subset
Ship Mode State Sales
0 Second Class Kentucky 261.9600
1 Second Class Kentucky 731.9400
2 Second Class California 14.6200
3 Standard Class Florida 957.5775
4 Standard Class Florida 22.3680
5 Standard Class California 48.8600
6 Standard Class California 7.2800
7 Standard Class California 907.1520
8 Standard Class California 18.5040
9 Standard Class California 114.9000
df_subset[df_subset['Sales']>100]
Ship Mode State Sales
0 Second Class Kentucky 261.9600
1 Second Class Kentucky 731.9400
3 Standard Class Florida 957.5775
7 Standard Class California 907.1520
9 Standard Class California 114.9000
df_subset[(df_subset['State']!='California') & (df_subset['Sales']>100)]
Ship Mode State Sales
0 Second Class Kentucky 261.9600
1 Second Class Kentucky 731.9400
3 Standard Class Florida 957.5775

Exercise 6: Setting and re-setting index

matrix_data = np.matrix('22,66,140;42,70,148;30,62,125;35,68,160;25,62,152')
row_labels = ['A','B','C','D','E']
column_headings = ['Age', 'Height', 'Weight']

df1 = pd.DataFrame(data=matrix_data, index=row_labels, columns=column_headings)
print("\nThe DataFrame\n",'-'*25, sep='')
print(df1)
print("\nAfter resetting index\n",'-'*35, sep='')
print(df1.reset_index())
print("\nAfter resetting index with 'drop' option TRUE\n",'-'*45, sep='')
print(df1.reset_index(drop=True))
print("\nAdding a new column 'Profession'\n",'-'*45, sep='')
df1['Profession'] = "Student Teacher Engineer Doctor Nurse".split()
print(df1)
print("\nSetting 'Profession' column as index\n",'-'*45, sep='')
print (df1.set_index('Profession'))
The DataFrame
-------------------------
   Age  Height  Weight
A   22      66     140
B   42      70     148
C   30      62     125
D   35      68     160
E   25      62     152

After resetting index
-----------------------------------
  index  Age  Height  Weight
0     A   22      66     140
1     B   42      70     148
2     C   30      62     125
3     D   35      68     160
4     E   25      62     152

After resetting index with 'drop' option TRUE
---------------------------------------------
   Age  Height  Weight
0   22      66     140
1   42      70     148
2   30      62     125
3   35      68     160
4   25      62     152

Adding a new column 'Profession'
---------------------------------------------
   Age  Height  Weight Profession
A   22      66     140    Student
B   42      70     148    Teacher
C   30      62     125   Engineer
D   35      68     160     Doctor
E   25      62     152      Nurse

Setting 'Profession' column as index
---------------------------------------------
            Age  Height  Weight
Profession                     
Student      22      66     140
Teacher      42      70     148
Engineer     30      62     125
Doctor       35      68     160
Nurse        25      62     152

Exercise 7: GroupBy method

df_subset = df.loc[[i for i in range (10)],['Ship Mode','State','Sales']]
df_subset
Ship Mode State Sales
0 Second Class Kentucky 261.9600
1 Second Class Kentucky 731.9400
2 Second Class California 14.6200
3 Standard Class Florida 957.5775
4 Standard Class Florida 22.3680
5 Standard Class California 48.8600
6 Standard Class California 7.2800
7 Standard Class California 907.1520
8 Standard Class California 18.5040
9 Standard Class California 114.9000
byState = df_subset.groupby('State')
byState
<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7f2b430f19e8>
print("\nGrouping by 'State' column and listing mean sales\n",'-'*50, sep='')
print(byState.mean())
Grouping by 'State' column and listing mean sales
--------------------------------------------------
                 Sales
State                 
California  185.219333
Florida     489.972750
Kentucky    496.950000
print("\nGrouping by 'State' column and listing total sum of sales\n",'-'*50, sep='')
print(byState.sum())
Grouping by 'State' column and listing total sum of sales
--------------------------------------------------
                Sales
State                
California  1111.3160
Florida      979.9455
Kentucky     993.9000
print(pd.DataFrame(df_subset.groupby('State').describe().loc['California']).transpose())
           Sales                                                              
           count        mean         std   min     25%     50%    75%      max
California   6.0  185.219333  355.889307  7.28  15.591  33.682  98.39  907.152
df_subset.groupby('Ship Mode').describe().loc[['Second Class','Standard Class']]
Sales
count mean std min 25% 50% 75% max
Ship Mode
Second Class 3.0 336.173333 364.373037 14.62 138.290 261.96 496.950 731.9400
Standard Class 7.0 296.663071 435.947552 7.28 20.436 48.86 511.026 957.5775
pd.DataFrame(byState.describe().loc['California'])
California
Sales count 6.000000
mean 185.219333
std 355.889307
min 7.280000
25% 15.591000
50% 33.682000
75% 98.390000
max 907.152000
byStateCity=df.groupby(['State','City'])
byStateCity.describe()['Sales']
count mean std min 25% 50% 75% max
State City
Alabama Auburn 6.0 294.471667 361.914543 3.760 8.8050 182.0300 456.40750 900.080
Decatur 13.0 259.601538 385.660903 14.940 23.9200 44.9500 239.92000 1215.920
Florence 5.0 399.470000 796.488863 4.980 7.2700 12.4800 152.76000 1819.860
Hoover 4.0 131.462500 230.646923 7.160 13.3925 20.7250 138.79500 477.240
Huntsville 10.0 248.437000 419.576667 3.620 26.8700 81.9200 171.80750 1319.960
Mobile 11.0 496.635455 914.087425 8.960 46.8600 70.9800 505.96500 3040.000
Montgomery 10.0 372.273000 475.397645 10.160 21.7075 187.2150 499.05500 1394.950
Tuscaloosa 2.0 87.850000 76.523096 33.740 60.7950 87.8500 114.90500 141.960
Arizona Avondale 6.0 157.801333 288.247527 14.576 18.1480 35.5960 88.67800 742.336
Bullhead City 2.0 11.144000 4.559425 7.920 9.5320 11.1440 12.75600 14.368
Chandler 7.0 153.821000 305.283748 8.544 9.1200 49.7920 78.89750 842.376
Gilbert 15.0 278.158800 346.945589 5.904 36.1240 82.3680 375.80700 1113.024
Glendale 23.0 126.863696 225.003236 2.368 14.8760 42.9760 109.13200 933.536
Mesa 28.0 144.205000 155.275947 4.368 31.7640 81.6515 202.90250 552.000
Peoria 16.0 83.834500 88.768365 4.536 14.8920 68.1540 92.42600 280.792
Phoenix 63.0 174.607254 322.324198 1.408 12.8145 46.8720 193.96400 1879.960
Scottsdale 12.0 122.192250 103.500825 4.401 30.1230 110.3040 186.89850 307.776
Sierra Vista 3.0 25.357333 9.543341 14.368 22.2560 30.1440 30.85200 31.560
Tempe 13.0 82.330923 119.755669 3.366 8.3760 12.7680 79.40000 318.400
Tucson 32.0 197.281750 242.004135 4.272 31.3200 95.9890 243.54000 1023.936
Yuma 4.0 210.216250 270.654379 10.496 36.2660 115.1920 289.14225 599.985
Arkansas Conway 1.0 301.960000 NaN 301.960 301.9600 301.9600 301.96000 301.960
Fayetteville 14.0 267.343571 482.533092 6.240 19.5525 75.0850 297.83750 1793.980
Hot Springs 4.0 61.457500 65.669769 25.920 28.9725 29.9950 62.48000 159.920
Jonesboro 11.0 265.029091 366.221274 6.630 23.1500 59.9800 439.16000 1067.940
Little Rock 24.0 148.347917 206.445952 11.160 19.0700 60.9900 180.69000 881.930
Pine Bluff 2.0 106.455000 132.221897 12.960 59.7075 106.4550 153.20250 199.950
Rogers 1.0 40.410000 NaN 40.410 40.4100 40.4100 40.41000 40.410
Springdale 1.0 4.300000 NaN 4.300 4.3000 4.3000 4.30000 4.300
Texarkana 2.0 327.120000 393.462497 48.900 188.0100 327.1200 466.23000 605.340
... ... ... ... ... ... ... ... ... ...
Washington Bellingham 3.0 1263.413333 1327.859461 25.120 562.3100 1099.5000 1882.56000 2665.620
Covington 4.0 103.420000 93.849114 29.900 42.6500 73.4500 134.22000 236.880
Des Moines 7.0 493.491143 665.676105 18.540 86.7940 215.9760 623.18200 1799.970
Edmonds 14.0 180.263714 344.045236 7.380 21.1450 54.0420 155.47250 1298.550
Everett 1.0 3.856000 NaN 3.856 3.8560 3.8560 3.85600 3.856
Kent 8.0 168.906750 257.793525 19.440 38.4920 60.9880 168.21250 786.480
Longview 3.0 39.736667 31.681749 18.240 21.5450 24.8500 50.48500 76.120
Marysville 2.0 51.090000 59.778807 8.820 29.9550 51.0900 72.22500 93.360
Olympia 5.0 204.089600 236.364267 14.030 45.6800 155.2500 201.56800 603.920
Pasco 6.0 366.852000 356.325643 5.880 97.5540 352.3200 464.61000 975.920
Redmond 3.0 18.410000 5.473783 12.320 16.1550 19.9900 21.45500 22.920
Renton 3.0 414.210667 544.196636 51.840 101.3200 150.8000 595.39600 1039.992
Seattle 428.0 279.300799 823.990115 1.344 20.6905 65.6400 201.21000 13999.960
Spokane 7.0 289.702571 300.735758 23.840 103.6200 149.7300 404.75400 837.600
Vancouver 5.0 137.367200 157.470820 9.640 14.8000 44.0200 302.37600 316.000
West Virginia Wheeling 4.0 302.456000 313.508627 6.240 63.3600 265.1200 504.21600 673.344
Wisconsin Appleton 2.0 835.655000 1151.304190 21.560 428.6075 835.6550 1242.70250 1649.750
Eau Claire 6.0 274.275000 237.093940 32.560 117.6700 217.7700 364.37000 680.010
Franklin 9.0 530.472222 715.606149 3.600 106.0500 171.5500 392.94000 1951.840
Green Bay 4.0 131.830000 224.726888 16.740 18.4050 20.8400 134.26500 468.900
Kenosha 9.0 434.081111 301.753460 14.980 139.9500 399.9500 699.98000 860.930
La Crosse 5.0 166.082000 200.481230 3.040 56.8200 68.6400 201.96000 499.950
Madison 10.0 534.679000 875.980874 1.810 32.3000 119.5850 614.39000 2807.840
Milwaukee 45.0 253.560444 376.458437 5.820 29.3400 92.5200 272.40000 1526.560
Sheboygan 4.0 19.935000 15.126715 1.980 11.7225 20.0850 28.29750 37.590
Superior 9.0 144.414444 213.394065 5.560 17.1200 47.4000 125.99000 629.100
Waukesha 1.0 54.500000 NaN 54.500 54.5000 54.5000 54.50000 54.500
Wausau 4.0 79.370000 111.450605 12.390 20.0325 29.6050 88.94250 245.880
West Allis 2.0 125.240000 165.067007 8.520 66.8800 125.2400 183.60000 241.960
Wyoming Cheyenne 1.0 1603.136000 NaN 1603.136 1603.1360 1603.1360 1603.13600 1603.136

604 rows × 8 columns

Exercise 8: Missing values in Pandas

df_missing=pd.read_excel("Sample - Superstore.xls",sheet_name="Missing")
df_missing
Customer Product Sales Quantity Discount Profit
0 Brosina Hoffman NaN 1706.184 9.0 0.2 85.3092
1 Brosina Hoffman Phones 911.424 4.0 0.2 68.3568
2 Zuschuss Donatelli Art 8.560 2.0 0.0 2.4824
3 Zuschuss Donatelli Phones NaN 3.0 0.2 16.0110
4 Zuschuss Donatelli Binders 22.720 4.0 0.2 7.3840
5 Eric Hoffmann Binders 11.648 NaN 0.2 4.2224
6 Eric Hoffmann Accessories 90.570 3.0 0.0 11.7741
7 Ruben Ausman NaN 77.880 2.0 0.0 NaN
8 NaN Accessories 13.980 2.0 0.0 6.1512
9 Kunst Miller Binders 25.824 6.0 0.2 9.3612
10 Kunst Miller Paper 146.730 3.0 0.0 68.9631
df_missing.isnull()
Customer Product Sales Quantity Discount Profit
0 False True False False False False
1 False False False False False False
2 False False False False False False
3 False False True False False False
4 False False False False False False
5 False False False True False False
6 False False False False False False
7 False True False False False True
8 True False False False False False
9 False False False False False False
10 False False False False False False
for c in df_missing.columns:
    miss = df_missing[c].isnull().sum()
    if miss>0:
        print("{} has {} missing value(s)".format(c,miss))
    else:
        print("{} has NO missing value!".format(c))
Customer has 1 missing value(s)
Product has 2 missing value(s)
Sales has 1 missing value(s)
Quantity has 1 missing value(s)
Discount has NO missing value!
Profit has 1 missing value(s)

Exercise 9: Filling missing values with fillna()

df_missing.fillna('FILL')
Customer Product Sales Quantity Discount Profit
0 Brosina Hoffman FILL 1706.18 9 0.2 85.3092
1 Brosina Hoffman Phones 911.424 4 0.2 68.3568
2 Zuschuss Donatelli Art 8.56 2 0.0 2.4824
3 Zuschuss Donatelli Phones FILL 3 0.2 16.011
4 Zuschuss Donatelli Binders 22.72 4 0.2 7.384
5 Eric Hoffmann Binders 11.648 FILL 0.2 4.2224
6 Eric Hoffmann Accessories 90.57 3 0.0 11.7741
7 Ruben Ausman FILL 77.88 2 0.0 FILL
8 FILL Accessories 13.98 2 0.0 6.1512
9 Kunst Miller Binders 25.824 6 0.2 9.3612
10 Kunst Miller Paper 146.73 3 0.0 68.9631
df_missing[['Customer','Product']].fillna('FILL')
Customer Product
0 Brosina Hoffman FILL
1 Brosina Hoffman Phones
2 Zuschuss Donatelli Art
3 Zuschuss Donatelli Phones
4 Zuschuss Donatelli Binders
5 Eric Hoffmann Binders
6 Eric Hoffmann Accessories
7 Ruben Ausman FILL
8 FILL Accessories
9 Kunst Miller Binders
10 Kunst Miller Paper
df_missing['Sales'].fillna(method='ffill')
0     1706.184
1      911.424
2        8.560
3        8.560
4       22.720
5       11.648
6       90.570
7       77.880
8       13.980
9       25.824
10     146.730
Name: Sales, dtype: float64
df_missing['Sales'].fillna(method='bfill')
0     1706.184
1      911.424
2        8.560
3       22.720
4       22.720
5       11.648
6       90.570
7       77.880
8       13.980
9       25.824
10     146.730
Name: Sales, dtype: float64
df_missing['Sales'].fillna(df_missing.mean()['Sales'])
0     1706.184
1      911.424
2        8.560
3      301.552
4       22.720
5       11.648
6       90.570
7       77.880
8       13.980
9       25.824
10     146.730
Name: Sales, dtype: float64

Exercise 10: Dropping missing values with dropna()

df_missing.dropna(axis=0)
Customer Product Sales Quantity Discount Profit
1 Brosina Hoffman Phones 911.424 4.0 0.2 68.3568
2 Zuschuss Donatelli Art 8.560 2.0 0.0 2.4824
4 Zuschuss Donatelli Binders 22.720 4.0 0.2 7.3840
6 Eric Hoffmann Accessories 90.570 3.0 0.0 11.7741
9 Kunst Miller Binders 25.824 6.0 0.2 9.3612
10 Kunst Miller Paper 146.730 3.0 0.0 68.9631
df_missing.dropna(axis=1)
Discount
0 0.2
1 0.2
2 0.0
3 0.2
4 0.2
5 0.2
6 0.0
7 0.0
8 0.0
9 0.2
10 0.0
df_missing.dropna(axis=1,thresh=10)
Customer Sales Quantity Discount Profit
0 Brosina Hoffman 1706.184 9.0 0.2 85.3092
1 Brosina Hoffman 911.424 4.0 0.2 68.3568
2 Zuschuss Donatelli 8.560 2.0 0.0 2.4824
3 Zuschuss Donatelli NaN 3.0 0.2 16.0110
4 Zuschuss Donatelli 22.720 4.0 0.2 7.3840
5 Eric Hoffmann 11.648 NaN 0.2 4.2224
6 Eric Hoffmann 90.570 3.0 0.0 11.7741
7 Ruben Ausman 77.880 2.0 0.0 NaN
8 NaN 13.980 2.0 0.0 6.1512
9 Kunst Miller 25.824 6.0 0.2 9.3612
10 Kunst Miller 146.730 3.0 0.0 68.9631

Exercise 11: Outlier detection using simple statistical test

df_sample = df[['Customer Name','State','Sales','Profit']].sample(n=50).copy()
# Assign a wrong (negative value) in few places
df_sample['Sales'].iloc[5]=-1000.0
df_sample['Sales'].iloc[15]=-500.0
/srv/conda/envs/saturn/lib/python3.7/site-packages/pandas/core/indexing.py:190: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  self._setitem_with_indexer(indexer, value)
df_sample.plot.box()
plt.title("Boxplot of sales and profit", fontsize=15)
plt.xticks(fontsize=15)
plt.yticks(fontsize=15)
plt.grid(True)

Exercise 12: Concatenation

df_1 = df[['Customer Name','State','Sales','Profit']].sample(n=4)
df_2 = df[['Customer Name','State','Sales','Profit']].sample(n=4)
df_3 = df[['Customer Name','State','Sales','Profit']].sample(n=4)
df_1
Customer Name State Sales Profit
6025 Darrin Martin Ohio 10.368 3.7584
8666 Carl Jackson Pennsylvania 16.520 1.6520
1517 Tom Boeckenhauer Washington 83.840 27.2480
1392 Paul Prost Georgia 342.370 160.9139
df_2
Customer Name State Sales Profit
4034 Jim Mitchum Ohio 18.693 -14.3313
9091 Dorothy Wardle Ohio 15.552 5.4432
4681 Richard Eichhorn New York 29.610 13.3245
2360 Christine Phan New York 34.860 16.3842
df_3
Customer Name State Sales Profit
5960 Marina Lichtenstein California 21.120 6.5472
4948 Bill Donatelli California 117.488 41.1208
2068 Zuschuss Carroll California 199.960 69.9860
7176 Heather Kirkland Texas 32.232 2.4174
df_cat1 = pd.concat([df_1,df_2,df_3], axis=0)
df_cat1
Customer Name State Sales Profit
6025 Darrin Martin Ohio 10.368 3.7584
8666 Carl Jackson Pennsylvania 16.520 1.6520
1517 Tom Boeckenhauer Washington 83.840 27.2480
1392 Paul Prost Georgia 342.370 160.9139
4034 Jim Mitchum Ohio 18.693 -14.3313
9091 Dorothy Wardle Ohio 15.552 5.4432
4681 Richard Eichhorn New York 29.610 13.3245
2360 Christine Phan New York 34.860 16.3842
5960 Marina Lichtenstein California 21.120 6.5472
4948 Bill Donatelli California 117.488 41.1208
2068 Zuschuss Carroll California 199.960 69.9860
7176 Heather Kirkland Texas 32.232 2.4174
df_cat2 = pd.concat([df_1,df_2,df_3], axis=1)
df_cat2
Customer Name State Sales Profit Customer Name State Sales Profit Customer Name State Sales Profit
1392 Paul Prost Georgia 342.370 160.9139 NaN NaN NaN NaN NaN NaN NaN NaN
1517 Tom Boeckenhauer Washington 83.840 27.2480 NaN NaN NaN NaN NaN NaN NaN NaN
2068 NaN NaN NaN NaN NaN NaN NaN NaN Zuschuss Carroll California 199.960 69.9860
2360 NaN NaN NaN NaN Christine Phan New York 34.860 16.3842 NaN NaN NaN NaN
4034 NaN NaN NaN NaN Jim Mitchum Ohio 18.693 -14.3313 NaN NaN NaN NaN
4681 NaN NaN NaN NaN Richard Eichhorn New York 29.610 13.3245 NaN NaN NaN NaN
4948 NaN NaN NaN NaN NaN NaN NaN NaN Bill Donatelli California 117.488 41.1208
5960 NaN NaN NaN NaN NaN NaN NaN NaN Marina Lichtenstein California 21.120 6.5472
6025 Darrin Martin Ohio 10.368 3.7584 NaN NaN NaN NaN NaN NaN NaN NaN
7176 NaN NaN NaN NaN NaN NaN NaN NaN Heather Kirkland Texas 32.232 2.4174
8666 Carl Jackson Pennsylvania 16.520 1.6520 NaN NaN NaN NaN NaN NaN NaN NaN
9091 NaN NaN NaN NaN Dorothy Wardle Ohio 15.552 5.4432 NaN NaN NaN NaN

Exercise 13: Merging by a common key

df_1=df[['Customer Name','Ship Date','Ship Mode']][0:4]
df_1
Customer Name Ship Date Ship Mode
0 Claire Gute 2016-11-11 Second Class
1 Claire Gute 2016-11-11 Second Class
2 Darrin Van Huff 2016-06-16 Second Class
3 Sean O'Donnell 2015-10-18 Standard Class
df_2=df[['Customer Name','Product Name','Quantity']][0:4]
df_2
Customer Name Product Name Quantity
0 Claire Gute Bush Somerset Collection Bookcase 2
1 Claire Gute Hon Deluxe Fabric Upholstered Stacking Chairs,... 3
2 Darrin Van Huff Self-Adhesive Address Labels for Typewriters b... 2
3 Sean O'Donnell Bretford CR4500 Series Slim Rectangular Table 5
pd.merge(df_1,df_2,on='Customer Name',how='inner')
Customer Name Ship Date Ship Mode Product Name Quantity
0 Claire Gute 2016-11-11 Second Class Bush Somerset Collection Bookcase 2
1 Claire Gute 2016-11-11 Second Class Hon Deluxe Fabric Upholstered Stacking Chairs,... 3
2 Claire Gute 2016-11-11 Second Class Bush Somerset Collection Bookcase 2
3 Claire Gute 2016-11-11 Second Class Hon Deluxe Fabric Upholstered Stacking Chairs,... 3
4 Darrin Van Huff 2016-06-16 Second Class Self-Adhesive Address Labels for Typewriters b... 2
5 Sean O'Donnell 2015-10-18 Standard Class Bretford CR4500 Series Slim Rectangular Table 5
pd.merge(df_1,df_2,on='Customer Name',how='inner').drop_duplicates()
Customer Name Ship Date Ship Mode Product Name Quantity
0 Claire Gute 2016-11-11 Second Class Bush Somerset Collection Bookcase 2
1 Claire Gute 2016-11-11 Second Class Hon Deluxe Fabric Upholstered Stacking Chairs,... 3
4 Darrin Van Huff 2016-06-16 Second Class Self-Adhesive Address Labels for Typewriters b... 2
5 Sean O'Donnell 2015-10-18 Standard Class Bretford CR4500 Series Slim Rectangular Table 5
df_3=df[['Customer Name','Product Name','Quantity']][2:6]
df_3
Customer Name Product Name Quantity
2 Darrin Van Huff Self-Adhesive Address Labels for Typewriters b... 2
3 Sean O'Donnell Bretford CR4500 Series Slim Rectangular Table 5
4 Sean O'Donnell Eldon Fold 'N Roll Cart System 2
5 Brosina Hoffman Eldon Expressions Wood and Plastic Desk Access... 7
pd.merge(df_1,df_3,on='Customer Name',how='inner').drop_duplicates()
Customer Name Ship Date Ship Mode Product Name Quantity
0 Darrin Van Huff 2016-06-16 Second Class Self-Adhesive Address Labels for Typewriters b... 2
1 Sean O'Donnell 2015-10-18 Standard Class Bretford CR4500 Series Slim Rectangular Table 5
2 Sean O'Donnell 2015-10-18 Standard Class Eldon Fold 'N Roll Cart System 2
pd.merge(df_1,df_3,on='Customer Name',how='outer').drop_duplicates()
Customer Name Ship Date Ship Mode Product Name Quantity
0 Claire Gute 2016-11-11 Second Class NaN NaN
2 Darrin Van Huff 2016-06-16 Second Class Self-Adhesive Address Labels for Typewriters b... 2.0
3 Sean O'Donnell 2015-10-18 Standard Class Bretford CR4500 Series Slim Rectangular Table 5.0
4 Sean O'Donnell 2015-10-18 Standard Class Eldon Fold 'N Roll Cart System 2.0
5 Brosina Hoffman NaT NaN Eldon Expressions Wood and Plastic Desk Access... 7.0

Exercise 14: Join method

df_1=df[['Customer Name','Ship Date','Ship Mode']][0:4]
df_1.set_index(['Customer Name'],inplace=True)
df_1
Ship Date Ship Mode
Customer Name
Claire Gute 2016-11-11 Second Class
Claire Gute 2016-11-11 Second Class
Darrin Van Huff 2016-06-16 Second Class
Sean O'Donnell 2015-10-18 Standard Class
df_2=df[['Customer Name','Product Name','Quantity']][2:6]
df_2.set_index(['Customer Name'],inplace=True)
df_2
Product Name Quantity
Customer Name
Darrin Van Huff Self-Adhesive Address Labels for Typewriters b... 2
Sean O'Donnell Bretford CR4500 Series Slim Rectangular Table 5
Sean O'Donnell Eldon Fold 'N Roll Cart System 2
Brosina Hoffman Eldon Expressions Wood and Plastic Desk Access... 7
df_1.join(df_2,how='left').drop_duplicates()
Ship Date Ship Mode Product Name Quantity
Customer Name
Claire Gute 2016-11-11 Second Class NaN NaN
Darrin Van Huff 2016-06-16 Second Class Self-Adhesive Address Labels for Typewriters b... 2.0
Sean O'Donnell 2015-10-18 Standard Class Bretford CR4500 Series Slim Rectangular Table 5.0
Sean O'Donnell 2015-10-18 Standard Class Eldon Fold 'N Roll Cart System 2.0
df_1.join(df_2,how='right').drop_duplicates()
Ship Date Ship Mode Product Name Quantity
Customer Name
Brosina Hoffman NaT NaN Eldon Expressions Wood and Plastic Desk Access... 7
Darrin Van Huff 2016-06-16 Second Class Self-Adhesive Address Labels for Typewriters b... 2
Sean O'Donnell 2015-10-18 Standard Class Bretford CR4500 Series Slim Rectangular Table 5
Sean O'Donnell 2015-10-18 Standard Class Eldon Fold 'N Roll Cart System 2
df_1.join(df_2,how='inner').drop_duplicates()
Ship Date Ship Mode Product Name Quantity
Customer Name
Darrin Van Huff 2016-06-16 Second Class Self-Adhesive Address Labels for Typewriters b... 2
Sean O'Donnell 2015-10-18 Standard Class Bretford CR4500 Series Slim Rectangular Table 5
Sean O'Donnell 2015-10-18 Standard Class Eldon Fold 'N Roll Cart System 2
df_1.join(df_2,how='outer').drop_duplicates()
Ship Date Ship Mode Product Name Quantity
Customer Name
Brosina Hoffman NaT NaN Eldon Expressions Wood and Plastic Desk Access... 7.0
Claire Gute 2016-11-11 Second Class NaN NaN
Darrin Van Huff 2016-06-16 Second Class Self-Adhesive Address Labels for Typewriters b... 2.0
Sean O'Donnell 2015-10-18 Standard Class Bretford CR4500 Series Slim Rectangular Table 5.0
Sean O'Donnell 2015-10-18 Standard Class Eldon Fold 'N Roll Cart System 2.0

Miscelleneous useful methods

Exercise 15: Randomized sampling - sample method

df.sample(n=5)
Order ID Order Date Ship Date Ship Mode Customer ID Customer Name Segment City State Postal Code Region Product ID Category Sub-Category Product Name Sales Quantity Discount Profit
8531 CA-2016-156748 2016-11-30 2016-12-06 Standard Class BS-11755 Bruce Stewart Consumer Detroit Michigan 48227 Central OFF-PA-10002713 Office Supplies Paper Adams Phone Message Book, 200 Message Capacity... 13.760 2 0.0 6.3296
8210 CA-2017-128769 2017-09-07 2017-09-12 Standard Class DM-12955 Dario Medina Corporate Nashville Tennessee 37211 South OFF-AP-10001271 Office Supplies Appliances Eureka The Boss Cordless Rechargeable Stick Vac 81.568 2 0.2 7.1372
4255 CA-2017-163160 2017-10-13 2017-10-16 First Class TS-21610 Troy Staebel Consumer Freeport Illinois 61032 Central OFF-BI-10000778 Office Supplies Binders GBC VeloBinder Electric Binding Machine 96.784 4 0.8 -145.1760
4796 US-2016-148901 2016-05-14 2016-05-19 Standard Class MK-17905 Michael Kennedy Corporate Jacksonville Florida 32216 South OFF-BI-10001718 Office Supplies Binders GBC DocuBind P50 Personal Binding Machine 57.582 3 0.7 -44.1462
3242 CA-2017-114524 2017-03-31 2017-04-05 Second Class EG-13900 Emily Grady Consumer Chicago Illinois 60623 Central OFF-BI-10002799 Office Supplies Binders SlimView Poly Binder, 3/8" 13.468 13 0.8 -22.8956
df.sample(frac=0.001)
Order ID Order Date Ship Date Ship Mode Customer ID Customer Name Segment City State Postal Code Region Product ID Category Sub-Category Product Name Sales Quantity Discount Profit
398 CA-2016-108987 2016-09-08 2016-09-10 Second Class AG-10675 Anna Gayman Consumer Houston Texas 77036 Central OFF-ST-10001580 Office Supplies Storage Super Decoflex Portable Personal File 35.952 3 0.2 3.5952
1984 CA-2014-164721 2014-11-25 2014-11-27 Second Class LW-16825 Laurel Workman Corporate Brentwood California 94513 West FUR-FU-10001940 Furniture Furnishings Staple-based wall hangings 23.880 3 0.0 10.5072
8838 CA-2014-153808 2014-04-06 2014-04-10 Second Class FH-14275 Frank Hawley Corporate Los Angeles California 90004 West OFF-AR-10001725 Office Supplies Art Boston Home & Office Model 2000 Electric Penci... 70.950 3 0.0 18.4470
2512 CA-2017-155089 2017-12-02 2017-12-06 Standard Class DB-12910 Daniel Byrd Home Office Tampa Florida 33614 South OFF-BI-10002429 Office Supplies Binders Premier Elliptical Ring Binder, Black 45.660 5 0.7 -33.4840
4277 US-2016-107440 2016-04-16 2016-04-20 Standard Class BS-11365 Bill Shonely Corporate Lakewood New Jersey 8701 East TEC-MA-10001047 Technology Machines 3D Systems Cube Printer, 2nd Generation, Magenta 9099.930 7 0.0 2365.9818
6113 CA-2016-124590 2016-11-12 2016-11-16 Standard Class SP-20920 Susan Pistek Consumer Parma Ohio 44134 East FUR-CH-10000454 Furniture Chairs Hon Deluxe Fabric Upholstered Stacking Chairs,... 1537.074 9 0.3 0.0000
9358 CA-2016-168046 2016-01-25 2016-01-28 Second Class JD-15895 Jonathan Doherty Corporate New York City New York 10024 East FUR-TA-10001095 Furniture Tables Chromcraft Round Conference Tables 313.722 3 0.4 -99.3453
5125 CA-2014-160766 2014-09-14 2014-09-14 Same Day DM-13015 Darrin Martin Consumer New York City New York 10009 East OFF-EN-10001099 Office Supplies Envelopes Staple envelope 68.460 7 0.0 31.4916
2187 CA-2017-143063 2017-08-10 2017-08-15 Standard Class IL-15100 Ivan Liston Consumer Columbus Indiana 47201 Central OFF-EN-10003134 Office Supplies Envelopes Staple envelope 70.080 6 0.0 35.0400
8079 US-2016-164945 2016-11-22 2016-11-27 Standard Class CA-12055 Cathy Armstrong Home Office New York City New York 10009 East OFF-BI-10001524 Office Supplies Binders GBC Premium Transparent Covers with Diagonal L... 134.272 8 0.2 46.9952
df.sample(frac=0.001,replace=True)
Order ID Order Date Ship Date Ship Mode Customer ID Customer Name Segment City State Postal Code Region Product ID Category Sub-Category Product Name Sales Quantity Discount Profit
340 CA-2014-122336 2014-04-13 2014-04-17 Second Class JD-15895 Jonathan Doherty Corporate Philadelphia Pennsylvania 19140 East OFF-AR-10000122 Office Supplies Art Newell 314 17.856 4 0.2 1.1160
5616 CA-2016-158001 2016-08-23 2016-08-27 Standard Class JP-15460 Jennifer Patt Corporate New York City New York 10035 East OFF-AR-10001954 Office Supplies Art Newell 331 29.340 6 0.0 7.9218
3342 CA-2016-145982 2016-08-27 2016-09-01 Second Class TB-21055 Ted Butterfield Consumer Quincy Massachusetts 2169 East FUR-TA-10001307 Furniture Tables SAFCO PlanMaster Heigh-Adjustable Drafting Tab... 244.615 1 0.3 20.9670
4256 CA-2017-163160 2017-10-13 2017-10-16 First Class TS-21610 Troy Staebel Consumer Freeport Illinois 61032 Central FUR-FU-10001424 Furniture Furnishings Dax Clear Box Frame 10.476 3 0.6 -6.8094
1494 CA-2014-123925 2014-12-17 2014-12-19 Second Class RF-19840 Roy Französisch Consumer Columbus Georgia 31907 South OFF-AR-10002952 Office Supplies Art Stanley Contemporary Battery Pencil Sharpeners 40.050 3 0.0 11.2140
3297 US-2015-110569 2015-05-23 2015-05-30 Standard Class EB-13870 Emily Burns Consumer Phoenix Arizona 85023 West OFF-AP-10004708 Office Supplies Appliances Fellowes Superior 10 Outlet Split Surge Protector 121.792 4 0.2 13.7016
7474 CA-2014-167199 2014-01-06 2014-01-10 Standard Class ME-17320 Maria Etezadi Home Office Henderson Kentucky 42420 South FUR-CH-10004063 Furniture Chairs Global Deluxe High-Back Manager's Chair 2573.820 9 0.0 746.4078
2328 CA-2017-138422 2017-09-23 2017-09-26 First Class KN-16705 Kristina Nunn Home Office Fort Collins Colorado 80525 West OFF-EN-10004147 Office Supplies Envelopes Wausau Papers Astrobrights Colored Envelopes 14.352 3 0.2 5.2026
8491 CA-2017-106824 2017-07-07 2017-07-11 Standard Class AT-10735 Annie Thurman Consumer Los Angeles California 90049 West OFF-FA-10001135 Office Supplies Fasteners Brites Rubber Bands, 1 1/2 oz. Box 5.940 3 0.0 0.1188
6938 CA-2016-125094 2016-11-05 2016-11-09 Standard Class NP-18700 Nora Preis Consumer Seattle Washington 98105 West TEC-AC-10004420 Technology Accessories Cherry 142-key Programmable Keyboard 479.720 4 0.0 52.7692

Exercise 16: Pandas value_count method to return unique records

df['Customer Name'].value_counts()[:10]
William Brown          37
Matt Abelman           34
Paul Prost             34
John Lee               34
Seth Vernon            32
Jonathan Doherty       32
Edward Hooks           32
Chloris Kastensmidt    32
Emily Phan             31
Arthur Prichep         31
Name: Customer Name, dtype: int64

Exercise 17: Pivot table functionality - pivot_table

df_sample=df.sample(n=100)
df_sample.pivot_table(values=['Sales','Quantity','Profit'],index=['Region','State'],aggfunc='mean')
Profit Quantity Sales
Region State
Central Illinois -1.730133 2.333333 8.238667
Indiana 51.996600 2.000000 274.650000
Kansas 9.167400 6.000000 27.780000
Michigan 66.508800 4.000000 138.560000
Minnesota 39.126267 2.666667 141.286667
Missouri 83.281000 5.000000 4164.050000
Oklahoma 62.906400 1.000000 262.110000
Texas -31.245014 3.785714 145.750429
Wisconsin 116.428650 6.000000 411.870000
East Connecticut 10.048350 3.000000 35.415000
Maryland 21.403200 6.000000 43.680000
Massachusetts 83.250800 2.000000 361.960000
New York -20.267600 2.166667 88.709333
Ohio -11.456557 4.857143 79.252857
Pennsylvania -109.189660 2.200000 173.691600
Rhode Island 20.124800 2.000000 52.960000
South Alabama 117.010400 4.000000 900.080000
Arkansas 2.999000 1.000000 29.990000
Florida 2.069150 3.000000 159.902000
Georgia 329.963025 2.750000 729.285000
Kentucky 41.293800 3.000000 152.940000
North Carolina 8.292600 6.000000 24.840000
Tennessee 3.339000 3.000000 8.904000
West Arizona -17.760600 3.000000 130.680000
California 10.052450 3.590909 108.638091
Nevada 70.097700 3.000000 170.970000
Oregon -1.004850 4.500000 23.962500
Utah 76.677900 6.000000 512.340000
Washington 23.869900 3.000000 433.018333

Exercise 18: Sorting by particular column

df_sample=df[['Customer Name','State','Sales','Quantity']].sample(n=15)
df_sample
Customer Name State Sales Quantity
3668 Tim Brockman Oregon 103.920 10
7086 Adam Hart Pennsylvania 190.896 2
6528 Bradley Talbott New York 216.400 4
2022 Rick Huthwaite Rhode Island 49.530 3
8172 Gary Hansen California 2.880 1
4140 Ralph Arnett Pennsylvania 79.120 5
2480 Frank Olsen Texas 153.584 2
4502 Michael Stewart Texas 21.312 6
2433 Jonathan Howell Texas 54.368 4
7863 Toby Gnade Arizona 272.736 3
2244 Barry Franz California 9.020 2
5590 Doug Bickford California 352.380 2
7945 Guy Armstrong Texas 40.980 5
8219 Russell D'Ascenzo Texas 4.928 2
2393 Christine Sundaresam Massachusetts 39.680 2
df_sample.sort_values(by='Sales')
Customer Name State Sales Quantity
8172 Gary Hansen California 2.880 1
8219 Russell D'Ascenzo Texas 4.928 2
2244 Barry Franz California 9.020 2
4502 Michael Stewart Texas 21.312 6
2393 Christine Sundaresam Massachusetts 39.680 2
7945 Guy Armstrong Texas 40.980 5
2022 Rick Huthwaite Rhode Island 49.530 3
2433 Jonathan Howell Texas 54.368 4
4140 Ralph Arnett Pennsylvania 79.120 5
3668 Tim Brockman Oregon 103.920 10
2480 Frank Olsen Texas 153.584 2
7086 Adam Hart Pennsylvania 190.896 2
6528 Bradley Talbott New York 216.400 4
7863 Toby Gnade Arizona 272.736 3
5590 Doug Bickford California 352.380 2
df_sample.sort_values(by=['State','Sales'])
Customer Name State Sales Quantity
7863 Toby Gnade Arizona 272.736 3
8172 Gary Hansen California 2.880 1
2244 Barry Franz California 9.020 2
5590 Doug Bickford California 352.380 2
2393 Christine Sundaresam Massachusetts 39.680 2
6528 Bradley Talbott New York 216.400 4
3668 Tim Brockman Oregon 103.920 10
4140 Ralph Arnett Pennsylvania 79.120 5
7086 Adam Hart Pennsylvania 190.896 2
2022 Rick Huthwaite Rhode Island 49.530 3
8219 Russell D'Ascenzo Texas 4.928 2
4502 Michael Stewart Texas 21.312 6
7945 Guy Armstrong Texas 40.980 5
2433 Jonathan Howell Texas 54.368 4
2480 Frank Olsen Texas 153.584 2

Exercise 19: Flexibility for user-defined function with apply method

def categorize_sales(price):
    if price < 50:
        return "Low"
    elif price < 200:
        return "Medium"
    else:
        return "High"
df_sample=df[['Customer Name','State','Sales']].sample(n=100)
df_sample.head(10)
Customer Name State Sales
4207 Anne McFarland Rhode Island 17.670
1214 Stefania Perrino Pennsylvania 4.503
1612 Marina Lichtenstein California 83.984
4361 Lauren Leatherbury Louisiana 29.160
5859 MaryBeth Skach Arkansas 301.960
9748 Brosina Hoffman Illinois 797.944
7668 Matt Abelman Massachusetts 40.080
2226 Laura Armstrong Texas 2.896
3417 Tony Chapman Colorado 43.560
9627 Bobby Trafton Michigan 14.670
df_sample['Sales Price Category']=df_sample['Sales'].apply(categorize_sales)
df_sample.head(10)
Customer Name State Sales Sales Price Category
4207 Anne McFarland Rhode Island 17.670 Low
1214 Stefania Perrino Pennsylvania 4.503 Low
1612 Marina Lichtenstein California 83.984 Medium
4361 Lauren Leatherbury Louisiana 29.160 Low
5859 MaryBeth Skach Arkansas 301.960 High
9748 Brosina Hoffman Illinois 797.944 High
7668 Matt Abelman Massachusetts 40.080 Low
2226 Laura Armstrong Texas 2.896 Low
3417 Tony Chapman Colorado 43.560 Low
9627 Bobby Trafton Michigan 14.670 Low
df_sample['Customer Name Length']=df_sample['Customer Name'].apply(len)
df_sample.head(10)
Customer Name State Sales Sales Price Category Customer Name Length
4207 Anne McFarland Rhode Island 17.670 Low 14
1214 Stefania Perrino Pennsylvania 4.503 Low 16
1612 Marina Lichtenstein California 83.984 Medium 19
4361 Lauren Leatherbury Louisiana 29.160 Low 18
5859 MaryBeth Skach Arkansas 301.960 High 14
9748 Brosina Hoffman Illinois 797.944 High 15
7668 Matt Abelman Massachusetts 40.080 Low 12
2226 Laura Armstrong Texas 2.896 Low 15
3417 Tony Chapman Colorado 43.560 Low 12
9627 Bobby Trafton Michigan 14.670 Low 13
df_sample['Discounted Price']=df_sample['Sales'].apply(lambda x:0.85*x if x>200 else x)
df_sample.head(10)
Customer Name State Sales Sales Price Category Customer Name Length Discounted Price
4207 Anne McFarland Rhode Island 17.670 Low 14 17.6700
1214 Stefania Perrino Pennsylvania 4.503 Low 16 4.5030
1612 Marina Lichtenstein California 83.984 Medium 19 83.9840
4361 Lauren Leatherbury Louisiana 29.160 Low 18 29.1600
5859 MaryBeth Skach Arkansas 301.960 High 14 256.6660
9748 Brosina Hoffman Illinois 797.944 High 15 678.2524
7668 Matt Abelman Massachusetts 40.080 Low 12 40.0800
2226 Laura Armstrong Texas 2.896 Low 15 2.8960
3417 Tony Chapman Colorado 43.560 Low 12 43.5600
9627 Bobby Trafton Michigan 14.670 Low 13 14.6700