1: Included with this assignment is an Excel spreadsheet that contains data with two dimension values.The purpose of this assignment is to demonstrate steps performed in a K-Means Cluster analysis.Review the “k-MEANS CLUSTERING ALGORITHM” section in Chapter 4 of the Sharda et. al. textbook for additional background.Use Excel to perform the following data analysis.Plot the data on a scatter plot.Determine the ideal number of clusters.Choose random center points (centroids) for each cluster. (Note: Each student will select a different random set of centroids.)Using a standard distance formula measure the distance from each data point to each center point.Assign each data point to an initial cluster region based on closeness.For each cluster calculate new center points.Repeat steps 4 through 6.You will use Excel to help with calculations, but only standard functions should be used (i.e. don’t use a plug-in to perform the analysis for you.) You need to show your work doing this analysis the long way. If you were to repeat steps 4 through 6, what will likely happen with the cluster centroids? The rubric for this assignment can be viewed when clicking on the assignment link.Here is a link to an example spreadsheet using a smaller data set. It contains two tabs. The first tab is the raw data. The second tab contains the analysis that was performed. Make sure that you use a different starting center points from the example.2: Included with this assignment is an Excel spreadsheet containing customer receipts.The purpose of this assignment is to demonstrate steps performed in an Apriori analysis (i.e. Market Basket analysis).Review the “APRIORI ALGORITHM” section of Chapter 4 of the Sharda et. al. textbook for additional background.Use Excel to perform this analysis.List the SKU which was purchased the most.List the two SKUs that were purchased most frequently together.List the three SKUs that were purchased most frequently together.List the four SKUs that were purchased most frequently together.Make note of any pattern that you noticed while performing the analysis. As a retail business owner, how would you use the results from this analysis? The rubric for this assignment can be viewed when clicking on the assignment link..
cluster_analysis_example__1_wk4.xlsx
week_4_apriori_data_question2.xlsx
week_4_cluster_data__1_qst1.xlsx
Unformatted Attachment Preview
Data Point #
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
X
11.4
38.6
42.5
16.4
41.4
45.8
10.1
16
2.7
4.2
33.8
2.4
18.6
34.3
39.9
28
21.3
24.1
Y
22
34.1
0.2
27.8
29.2
9.2
42.7
17.1
3.3
37.2
33.9
9.5
48.9
46.1
26.9
32.5
25.2
39.3
60
50
40
30
20
10
0
0
5
Chart Title
5
10
15
20
25
30
35
40
45
50
Data Point #
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
X
11.4
38.6
42.5
16.4
41.4
45.8
10.1
16
2.7
4.2
33.8
2.4
18.6
34.3
39.9
28
21.3
24.1
Y
22
34.1
0.2
27.8
29.2
9.2
42.7
17.1
3.3
37.2
33.9
9.5
48.9
46.1
26.9
32.5
25.2
39.3
First round (Center Points are Guessed)
Find Distances to Center Points
Highlight shows nearest points to centers
Center 1
Center 2
10
30
25
30
X
Y
X
Y
8.1
15.8
28.9
14.2
44.1
34.6
6.8
8.9
31.4
16.4
41.4
29.4
12.7
19.6
14.2
15.7
27.7
34.8
9.2
22.0
24.1
9.6
21.9
30.5
20.8
20.0
29.1
18.6
30.1
15.2
18.2
3.9
12.3
6.1
16.9
9.3
Center 3
40
X
Y
29.7
4.3
29.9
23.7
1.6
21.6
32.5
27.2
45.9
36.5
7.3
42.8
28.6
17.1
3.1
12.3
19.3
18.4
After Round One (Assign Groups to Points)
Group 1
Group 2
Group 3
X
X
30
Y
11.4
16.4
X
Y
38.6
42.5
34.1
0.2
41.4
45.8
29.2
9.2
33.8
33.9
34.3
39.9
46.1
26.9
276.3
179.6
27.8
10.1
16
2.7
4.2
42.7
17.1
3.3
37.2
2.4
9.5
18.6
Averages
Y
22
63.2
159.6
28
21.3
24.1
92
9.028571
22.8
23
48.9
32.5
25.2
39.3
145.9
36.475 39.47143 25.65714
Find Distances to Center Points
New Center Point based on Averages
Center 1
Center 2
9
22.8
23
X
Y
X
Y
2.529822
18.56906
31.68359
15.78354
40.41052
41.20607
8.930845
10.92016
33.02605
19.7952
39.23264
35.56867
19.93038
14.31258
9.027181
20.62426
20.49244
38.91439
15.17893
18.81303
27.17076
11.10856
14.84756
33.96115
27.80953
13.15751
34.39448
14.82734
31.17082
19.43631
21.33284
6.403124
12.53196
11.42716
22.36649
3.008322
Center 3
36.5
39.5
X
Y
28.34255
8.448077
25.67586
23.19526
3.982462
17.66182
33.96115
25.02419
43.08132
37.126
9.986491
40.48271
31.22579
21.05232
1.264911
13.36001
18.20687
20.54556
After Round Two
Group 1
25.7
X
11.4
16.4
16
2.7
4.2
2.4
53.1
8.85
After Round Two
Group 2
Y
X
Group 3
Y
X
Y
22
38.6
42.5
34.1
0.2
41.4
45.8
29.2
9.2
33.8
33.9
39.9
28
26.9
32.5
270
166
27.8
10.1
42.7
17.1
3.3
37.2
9.5
18.6
34.3
116.9
28
21.3
24.1
136.4
48.9
46.1
32.5
25.2
39.3
234.7
19.48333 22.73333 39.11667 38.57143 23.71429
Center 1
8.9
X
Y
3.535534
33.09456
38.74855
11.1866
33.91666
38.31057
23.23101
7.494665
17.34589
18.31338
28.76404
11.92686
30.95884
36.77934
31.87099
23.10433
13.64734
24.96157
Center 2
19.5
22.7
X
20.49634
16.66763
43.64917
12.93754
21.15892
37.78386
13.1042
22.99761
41.0078
18.59731
12.25765
35.8922
10.62309
13.54843
21.08744
8.464632
13.97033
1.414214
Y
Center 3
39.1
38.6
X
Y
27.25307
10.4
23.82142
22.57543
6.17171
16.18919
34.25274
23.544
41.29128
36.95416
11.27298
38.88547
32.17204
22.80899
3.453983
13.77679
17.36491
21.29812
After Round Three
Group 1
Group 2
Group 3
23.7
X
Y
11.4
X
Y
X
22
38.6
42.5
16.4
27.8
41.4
45.8
10.1
16
2.7
4.2
17.1
3.3
37.2
2.4
9.5
42.7
33.8
18.6
34.3
48.9
46.1
39.9
21.3
28
32.5
39.3
209.5
25.2
74.4
142.1
24.1
115.1
10.62857
20.3
23.02
242
41.9 40.33333
Center 1
10.6
X
Y
Y
34.1
0.2
29.2
9.2
33.9
26.9
133.5
22.25
Center 2
20.3
23
X
Y
Center 3
41.9
40.3
X
22.3
TRAN#
SKU#1
486
487
488
489
2576
3173
3771
4674
4675
4676
4677
6351
6356
6357
6360
6365
6372
6377
6379
6380
6382
6383
6387
6390
6391
6392
6393
6401
6403
6409
6412
6419
6422
6430
6432
6434
6435
6438
6442
6448
6450
6452
6453
6456
6457
6459
SKU#2
0
1
1
1
0
1
0
1
1
0
1
0
0
0
0
0
1
0
0
0
1
1
0
0
0
0
0
0
0
0
0
1
0
1
0
0
0
0
0
1
0
0
0
1
0
1
SKU#3
1
0
0
1
0
1
0
1
0
1
1
1
0
0
0
0
0
0
0
0
1
1
1
0
1
0
0
0
1
0
1
1
0
0
0
0
0
0
0
1
0
0
0
0
1
0
SKU#4
1
1
1
1
0
0
1
1
0
0
0
0
0
0
0
0
1
0
0
0
1
0
0
0
0
0
0
0
0
0
0
1
0
0
1
0
0
0
0
0
0
0
0
0
0
0
SKU#5
1
1
1
0
1
0
1
1
0
1
0
0
0
0
0
1
0
1
1
1
0
0
0
1
1
1
1
0
0
0
0
0
0
0
0
1
0
0
1
0
1
1
1
1
0
1
SKU#6
1
1
1
0
1
1
0
0
1
1
1
0
1
0
0
0
1
0
0
0
1
0
0
0
1
1
0
0
0
0
0
1
1
0
1
1
0
0
0
1
0
0
0
0
0
0
SKU#7
1
0
0
1
1
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
1
0
0
0
0
0
0
1
0
0
0
1
0
1
0
0
0
0
0
1
0
0
0
0
0
0
1
1
1
0
0
1
1
0
1
0
1
0
1
0
0
0
1
0
0
0
1
0
1
0
0
0
0
0
0
0
0
1
1
1
0
1
0
0
0
1
0
0
0
0
0
0
6461
6464
6466
6468
6470
6475
6482
6484
6491
6496
6497
6498
6505
6507
6509
6514
6516
6518
6522
6528
6530
6534
6535
6536
6540
6547
6549
6550
6553
6558
6568
6570
6571
6572
6577
6578
6586
6591
6594
6595
6596
6599
6603
6608
6610
6612
6613
0
1
0
0
0
1
0
0
0
1
0
0
1
0
0
0
1
1
0
1
0
0
0
0
1
1
0
0
0
0
1
0
0
0
1
0
1
1
0
0
0
1
0
1
0
0
0
0
0
0
0
0
0
0
0
0
1
0
0
1
0
0
1
0
0
0
0
0
1
0
0
1
1
0
0
0
0
1
0
0
0
0
0
1
0
0
0
0
0
0
1
1
0
0
0
0
0
1
0
0
1
0
0
1
0
0
1
0
0
0
0
0
0
0
0
0
0
0
0
1
0
0
0
0
1
0
0
0
1
0
1
0
1
0
0
0
0
0
0
0
0
0
0
0
1
1
1
0
1
0
0
0
1
0
1
1
0
0
0
0
0
1
1
0
1
1
0
1
1
1
1
0
1
0
0
0
1
0
0
0
0
0
1
0
0
1
1
1
1
1
1
0
0
0
0
1
0
1
1
0
1
1
0
0
0
1
0
0
0
0
0
0
0
0
0
1
0
0
1
0
1
0
0
0
1
0
1
1
0
0
0
0
1
0
0
0
0
0
1
0
1
0
1
0
1
0
0
1
0
0
0
1
0
0
1
0
0
0
0
0
1
0
0
0
0
1
0
0
0
0
0
1
0
0
0
0
0
0
0
0
0
0
0
1
0
0
0
0
0
0
0
1
0
0
0
0
0
1
1
1
0
1
0
0
0
0
0
1
0
1
0
0
0
0
1
0
1
0
1
0
0
1
0
0
1
0
0
0
0
6614
6615
6618
6620
6626
6631
6632
0
0
0
0
1
0
0
0
0
0
0
1
0
1
0
0
0
1
1
0
0
1
1
1
1
0
1
0
0
0
0
0
0
1
0
0
0
0
0
1
0
0
0
0
0
0
0
1
0
SKU#8
SKU#9
1
0
0
1
0
0
1
1
1
1
0
0
1
0
0
0
0
0
0
0
1
0
0
0
1
1
0
0
0
0
0
1
1
1
0
1
0
0
0
0
0
0
0
0
0
0
SKU#10
0
1
0
1
1
0
0
1
1
0
1
1
0
0
0
0
0
0
0
0
1
0
1
0
1
0
0
0
0
0
0
1
0
1
0
1
0
0
0
1
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
1
1
1
0
0
0
0
0
1
1
1
0
0
0
0
0
1
1
0
0
0
1
1
0
1
1
0
0
0
0
0
0
0
0
0
1
0
0
0
0
0
0
0
1
0
0
1
1
0
1
0
1
0
0
0
0
0
0
0
0
1
1
1
0
1
0
0
0
0
0
1
0
0
1
0
0
1
0
1
0
0
0
0
0
0
0
0
0
0
0
1
0
0
1
1
0
0
1
1
0
0
0
0
0
0
0
0
0
0
0
0
1
0
0
0
1
0
1
0
0
1
0
1
0
0
0
0
0
1
0
1
0
0
0
0
0
1
1
1
1
1
0
0
0
1
1
1
0
0
0
1
0
0
0
1
0
0
0
1
0
0
1
0
0
0
0
1
1
1
0
0
0
1
0
0
0
0
0
0
1
0
0
0
0
0
0
0
0
0
0
0
0
0
1
0
0
Data Point #
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
X
11,4
38,6
42,5
16,4
41,4
45,8
10,1
16
2,7
4,2
33,8
2,4
18,6
34,3
39,9
28
21,3
24,1
30,1
7,2
21,6
30,5
2,1
16,7
15,3
26,5
25,8
36,3
27,4
4,5
0,3
32,7
0,7
6,9
9,8
20,1
9
13,6
46,2
6,1
Y
22
34,1
0,2
27,8
29,2
9,2
42,7
17,1
3,3
37,2
33,9
9,5
48,9
46,1
26,9
32,5
25,2
39,3
46,4
26,2
39
9
19,2
32,4
21,7
1,6
25,5
20,7
9,4
0,3
36,7
46,4
15,8
8,4
18,7
4,8
12,4
30,3
26
12,7
…
Purchase answer to see full
attachment
Recent Comments