ASSINGMENT 1 – BUSI650 Business Analytics
Weightage: 10% of the final grade
Submission deadline: Check Moodle for details.
Submission Instructions: Please submit an Excel file that includes your work (functions, computations,
formulas, etc) to answer all the questions in this assignment. Remember to specify the question number
and part name (1. a, 1. b, …). Save your file as FirstName-LastName-Assignmnet1.xlsx
Catherine is a marketing manager at Organic Food Superstore. She would like to use the company’s
marketing dollars to market a new line of Asian- inspired meals to college-educated millennials. She
has acquired a representative sample. She has spends days to form a small dataset, you can see in
what shape her collected data is from the attached Excel file.
a) Find the % missing values for each and every feature? (variable / columns)
b) Based on your answer to a), which feature has the most and least amount of missing values
(ignore the first column)?
c) What portion of the data (200 subjects in total) is complete and can be used for analysis
immediately without further processing? (We want the actual number not an estimate, use a
method that could have been applied even if there were 200,00 subjects in this dataset)
d) How many customers have an income greater than 53K but made a total orders of less than 9?
a) In your opinion what is the best strategy to deal with the missing values in the “ZipCode” column?
Be specific!
b) In your opinion what is the best strategy to deal with the missing values in the “Income” column?
Be specific!
c) Apply the strategy you mentioned in b) on “Income” column
d) Compare the average value of “Income” column before and after your applied strategy.
Note: For all parts of this assignment, use formula, functions and cell referencing (no absolute
numbers), otherwise some marks will be deducted.
Good Luck
>Customers
001 / /86
5 00 7
0
0
7
1
3 1
3
1 6
Yes 5 00
7
3
12 Male Black Yes 2 9 122 TV Male White Yes 4 2
13 Very Dissatisfied SM 6
Female 5 0
0
7 Very Dissatisfied 7
Male White Yes 2 1
9 SM 17
Female Hispanic /85
Yes 3 153 2 Very Satisfied Web 1
Male Hispanic Yes 5 000
9
23 122 Very Satisfied 7
Female Yes 3 000
3 238 Very Satisfied Web Male Yes 0
4 Neutral TV Male White Yes 1 8
17 0
Very Dissatisfied Web Female Black Yes 3 15 Very Satisfied Referral 02
Male Hispanic Yes 2 00
7 262 TV 28
Male Hispanic / Yes 5 000
6 13 68 Neutral Referral Female White Yes 3 0
4 300 Very Dissatisfied TV 06
Male White No 5 0
129 100 1 Very Dissatisfied Web Male Black No 2 6 Very Dissatisfied TV 7
Male White Yes 1 3
9
10 35 Very Satisfied Referral 4 Male Yes 2 6
20 56 Neutral Web Male Black Yes 5 00
3 80 Very Dissatisfied Web Female Black No 5 15 Very Satisfied Female White No 4 90060 153 2 Very Satisfied TV 886
Male Yes 2 7 Referral 494
Male White Yes 4 307 6 35 Neutral Web 08
Male Black Yes 5 90024 6 Neutral Web Male Hispanic No 1 90033 697 0
22 Very Satisfied Referral Female Hispanic Yes 2 00
631 17 354 Somewhat Satisfied TV Male American Indian Yes 5 60000 63 1 Very Satisfied TV Male White Yes 1 10 6 Somewhat Satisfied Web Male Hispanic Yes 00 7
26 Somewhat Satisfied SM Male White Yes 5 00
50 50 1 Somewhat Satisfied Web 04
Female White Yes 3 90011 73000 539 5 12 Very Satisfied Web 42
Male Black Yes 2 536 12 140 Referral Male Black Yes 3 90011 55000 3 Somewhat Satisfied TV 099
Male Yes 2 90038 7 96 Somewhat Dissatisfied SM 7
Male Black Yes 5 398 8 Somewhat Satisfied Male White Yes 4 60000 84 3 349 Neutral SM 8
Female White Yes 1 814 14 58 Neutral TV Male Hispanic Yes 55000 12 17 Somewhat Satisfied SM Male White Yes 3 13 108 Somewhat Dissatisfied Web 34
Male Pacific Islander Yes 5 3 Somewhat Satisfied TV Male White Yes 1 00
1
21 Very Satisfied SM 88
Male Hispanic Yes 90060 449 11 Somewhat Satisfied Web Male Hispanic Yes 4 4
31 95 Somewhat Satisfied Referral Male White Yes 2 68000 25 92 Somewhat Satisfied TV Male Hispanic Yes 4 3 281 Somewhat Satisfied TV Female Black No 5 90033 300 4 66 Neutral SM Male White Yes 3 90028 7
16 Neutral SM Male White Yes 3 69000 3 11 Somewhat Satisfied Male White No 4 55000 28 328 Somewhat Dissatisfied TV 4
Female Black Yes 5 90045 97000 16 59 Somewhat Satisfied Web Male Hispanic Yes 5 90014 00
9 Very Satisfied Referral Male Hispanic Yes 4 96000 9 240 Very Satisfied Web Male Black Yes 2 90050 000
2
19 9 Somewhat Satisfied TV 5
Male Black Yes 2 90033 1000 8 360 Somewhat Satisfied Referral Female Black Yes 1 26 100 Somewhat Satisfied SM Female Black Yes 4 911 850 10 47 Somewhat Satisfied SM Female Asian Yes 5 417 9 76 Somewhat Satisfied Web Female Hispanic Yes 2 56000 22 Somewhat Satisfied Referral Female Black Yes 4 90062 870 19 40 Very Satisfied Referral 473
Male White Yes 4 42 78 2 11 Somewhat Satisfied Referral Male White Yes 2 95 113 2 Somewhat Satisfied TV Male Asian Yes 3 90023 56000 10 352 Neutral Web Female Hispanic Yes 2 90050 44000 500 5 Somewhat Satisfied TV Male Hispanic No 3 90014 1 52 Somewhat Satisfied Web 9
Female Hispanic No 4 90067 42000 476 4 26 Very Satisfied Referral 186
Male Hispanic Yes 2 94000 7 262 Neutral TV Male Black Yes 1 90010 000
17 Very Satisfied SM Male White Yes 2 90066 97000 4
1020 24 Somewhat Satisfied TV Male Hispanic Yes 4 90008 538 8 320 Somewhat Satisfied Web Female Black Yes 4 94000 7 Somewhat Dissatisfied TV Male White Yes 3 516 8 330 Very Satisfied Web Male Asian Yes 4 90021 6 18 Somewhat Satisfied TV Female White Yes 5 90010 19 Very Satisfied Web Male Asian Yes 1 97000 12 49 Somewhat Dissatisfied Referral Male White No 3 90001 00
124 1 Very Satisfied TV Female Black Yes 5 90038 00
125 183 1 26 Neutral Referral Male White Yes 1 7 Somewhat Satisfied TV Male Hispanic Yes 3 90013 56000 65 87 1 341 Somewhat Satisfied Web Male White Yes 4 60000 14 195 Very Satisfied TV Female White Yes 4 90030 47000 5
16 76 Neutral TV 1
Female Black Yes 5 90051 63000 105 188 4 183 Somewhat Satisfied Web 4
Male Black Yes 3 90034 94000 15 Very Satisfied Web 35
Male Asian Yes 4 90028 73000 107 148 4 214 Male Black No 4 90045 46000 19 Very Satisfied Web Female White Yes 1 5 200 Somewhat Dissatisfied Web 1
Female White Yes 3 00
860 582 12 218 Somewhat Satisfied Referral Female Asian Yes 1 90042 50000 27 Somewhat Satisfied SM Male Yes 4 587 5 180 Very Satisfied Referral 0
Male Yes 5 90033 1028 13 29 Somewhat Satisfied Web Male White Yes 4 68000 87 82 2 Somewhat Dissatisfied SM 7
Male Black Yes 5 547 900 15 Somewhat Satisfied SM Female White Yes 2 90023 47000 5 Somewhat Satisfied SM Male No 4 63 60 1 Very Satisfied Web Female Hispanic Yes 5 90052 83000 16 95 Neutral TV Male White Yes 5 90014 000
363 4 89 Somewhat Satisfied Web Male Black No 5 90060 771 20 267 Somewhat Satisfied TV Male Hispanic Yes 1 97000 26 313 Somewhat Dissatisfied TV Male Asian No 2 90030 56000 12 12 Somewhat Satisfied TV 3
Male Asian Yes 1 870 18 Somewhat Dissatisfied Web Male White Yes 3 90063 50000 11 130 Somewhat Satisfied SM Male Hispanic Yes 3 90028 16 132 Somewhat Satisfied Web Male White No 5 109000 652 10 Neutral SM Female White Yes 5 65000 12 22 Somewhat Dissatisfied Web Female Black Yes 2 8 Very Satisfied TV Female Black Yes 3 90029 42000 11 38 Very Satisfied TV Male White Yes 2 90055 69000 12 67 Somewhat Satisfied TV 518
Female White Yes 5 90025 62000 19 Somewhat Satisfied Referral 23
Female Hispanic Yes 4 90032 8 Somewhat Satisfied Referral Male White Yes 1 90067 428 2 345 Very Satisfied SM Male Black Yes 2 90007 6 205 Somewhat Satisfied Web Male Black No 3 66000 706 4 69 Neutral TV Male White Yes 4 16 16 Somewhat Satisfied Referral White Yes 2 72000 571 728 13 241 Somewhat Satisfied TV Female White Yes 2 90007 10 239 Somewhat Satisfied SM 20
Black Yes 3 578 568 10 353 Neutral SM Male American Indian Yes 1 90004 822 9 Neutral TV Male Black Yes 5 50000 181 210 3 Neutral SM Male Black No 2 90021 54000 284 439 9 196 Somewhat Satisfied Web Male White No 3 90054 553 17 Very Satisfied Referral Male Black Yes 3 90015 745 15 Somewhat Dissatisfied SM Female Black Yes 2 90062 84000 532 11 108 Somewhat Satisfied TV Male Asian Yes 4 90047 58000 542 15 149 Somewhat Satisfied SM Female Black Yes 3 90034 63000 9 145 Somewhat Satisfied Web Female Black Yes 3 90043 43000 447 626 13 296 Somewhat Satisfied SM Female White Yes 3 90031 97000 860 7 115 Neutral TV Male White No 5 90047 70000 2 117 Somewhat Satisfied Web 50
Female White Yes 4 90035 48000 26 342 Very Satisfied TV Female Black Yes 5 97000 660 5 76 Somewhat Satisfied TV Female White No 5 90029 369 267 6 308 Somewhat Satisfied TV Male Black Yes 5 90020 70000 13 76 Somewhat Satisfied Web Male White Yes 3 90027 407 9 30 Very Satisfied SM Male American Indian Yes 5 90043 692 12 Very Satisfied SM Male White Yes 4 90057 281 221 4 354 Very Satisfied Web Female White Yes 5 90032 151 2 234 Somewhat Satisfied Web Male Hispanic Yes 1 90063 28 145 Somewhat Satisfied Web Male Asian Yes 2 90010 109000 178 127 1 Very Satisfied Web Male White No 3 90059 38000 794 555 6 14 Somewhat Satisfied Referral Female Hispanic Yes 4 90029 449 6 296 Somewhat Satisfied Web 23
Female White Yes 3 90059 73000 451 10 180 Somewhat Satisfied SM Male White Yes 1 69000 499 9 Somewhat Satisfied Referral Male Black Yes 4 90063 91000 842 7 103 Somewhat Satisfied TV Male Hispanic Yes 3 90032 65000 7 250 Neutral SM Male Asian Yes 3 90031 80000 300 4 139 Neutral Referral Female Black 8/12/99 Yes 1 960 12 296 Somewhat Satisfied SM Male Hispanic Yes 1 90029 47000 517 10 345 Neutral TV Male Hispanic No 3 90055 51000 22 Somewhat Satisfied Web Female White Yes 3 78000 13 280 Somewhat Dissatisfied Web Female Black No 5 90015 67000 610 10 59 Neutral Web Female White Yes 3 90010 61000 22 Somewhat Dissatisfied TV Male White Yes 2 90047 136000 333 563 14 15 Somewhat Satisfied Web Male White Yes 4 90050 77000 275 288 2 Somewhat Satisfied SM Female Black Yes 2 90030 41000 979 9 Very Satisfied Web Male White Yes 1 90065 87000 157 4 129 Somewhat Satisfied Referral Male White No 1 90032 71000 659 11 121 Very Satisfied Referral Female White Yes 2 90014 653 556 5 100 Somewhat Satisfied Referral Female Pacific Islander Yes 2 90020 62000 9 Neutral TV Female White Yes 2 90034 572 698 10 345 Somewhat Satisfied TV Male Black Yes 4 90054 68000 620 11 97 Somewhat Dissatisfied Web Male Hispanic Yes 4 54000 920 23 Somewhat Satisfied Web Female White Yes 1 90007 44000 700 20 173 Somewhat Satisfied Web Female White No 3 90054 27 352 Very Satisfied TV Male Black Yes 1 90052 74000 214 220 2 134 Somewhat Satisfied Web Female Yes 5 90064 41000 18 44 Very Satisfied Web Male Yes 4 90043 95000 7 176 Very Satisfied Web Male Yes 1 90001 963 13 248 Somewhat Satisfied SM Male Black Yes 90013 99000 610 520 7 68 Neutral SM Male White Yes 5 90001 86000 312 226 2 100 Somewhat Satisfied TV Female Black No 4 43000 93 69 1 Somewhat Satisfied TV Female White No 1 90032 42000 183 4 60 Somewhat Satisfied TV Female Black Yes 4 90012 70000 127 104 3 137 Neutral Referral Male White Yes 5 90023 28 220 Somewhat Satisfied Web Female Hispanic 3 90013 986 15 Somewhat Satisfied TV Male White No 4 90039 71000 213 2 42 Very Satisfied Referral Male White No 2 90049 67000 708 549 12 133 Neutral Web Female Hispanic Yes 3 90013 00
9 213 TV Female Black Yes 1 90010 2 248 Somewhat Satisfied TV 8
Male Hispanic Yes 4 90050 86000 75 68 1 176 Somewhat Satisfied Referral Female Black Yes 5 90012 61000 1144 28 95 Somewhat Satisfied TV Male Black Yes 2 90047 60000 514 636 10 265 Neutral Referral Female Black Yes 1 90030 54000 266 5 281 Very Satisfied Referral Female White Yes 4 87000 841 7 181 Somewhat Satisfied TV Female White Yes 2 57000 28 Very Satisfied Referral Male White Yes 3 90061 75000 2 211 Somewhat Satisfied Referral Male Hispanic Yes 4 90050 100000 486 437 5 223 Somewhat Satisfied Referral Male Hispanic Yes 5 90063 813 24 140 Neutral Web Male White Yes 3 90036 406 409 3 294 Somewhat Satisfied SM Female White No 1 15 280 Very Satisfied Web Male Black Yes 4 90044 82000 11 159 Very Satisfied TV Male Black Yes 4 90050 247 6 148 Neutral Web Male White Yes 2 90030 58000 319 2 128 Somewhat Satisfied SM Male Hispanic Yes 5 90001 97000 70 89 2 205 Somewhat Satisfied TV Female Black Yes 5 90019 94000 1206 18 181 Somewhat Satisfied Web Male Hispanic Yes 1 90024 49000 760 11 136 Neutral SM Female Black Yes 2 90067 322 7 Web Male Hispanic Yes 1 90005 82000 25 302 Somewhat Satisfied Web Male Asian Yes 1 90010 49000 397 10 200 Somewhat Satisfied Web Female American Indian Yes 1 50000 625 514 10 233 Somewhat Dissatisfied Referral Female White Yes 1 90055 52000 13 Somewhat Dissatisfied Referral Male White Yes 5 102000 1080 10 113 Somewhat Satisfied SM
2
CustID
Sex
Race
BirthDate
College
HouseholdSize
ZipCode
Income
Spending
20
1
7
Spending201
8
NumOfOrders
DaysSinceLast
Satisfaction
Channel
1
5
3
6
Female
Black
12
16
Yes
9
4
5
30
28
24
10
Very Dissatisfied
SM
15
13
Male
White
5/9/
93
900
26
9
40
1
22
84
262
TV
153
21
60
5/22/
66
900
27
64000
5
23
Very Satisfied
1
532
307
9/16/64
900
29
60000
516
58
129
1532
35
Hispanic
No
900
17
5
70
555
845
97
Web
153
2
38
8/27/57
90028
63
4
52
1
50
Neutral
15
330
5/
14
90063
84000
229
1
44
1533
56
2/10/81
90050
76
107
Referral
1533
69
10/8/74
42
300
2
47
1533766
9/16/71
90057
7
100
9
25
708
171
1533791
10/27/99
90060
97000
82
1028
11
1533917
3/3/93
90045
7
68
915
19
15
345
12/26/
89
90056
1
140
938
6
65
15
3
49
1/
18
59
90033
96
31
796
1535063
12/17/82
90024
4
200
341
313
15
354
2/4/84
90051
5
500
80
1535
449
9/21/58
90062
56000
435
384
180
1535
92
1/12/79
90003
128
87
1
536
75
Pacific Islander
5/17/58
73000
127
1
108
1536
814
7/25/62
90036
860
4
78
401
1537373
1/20/72
90035
44000
615
748
2
95
1537
553
7/7/61
360
1
538
American Indian
9/15/96
90014
94000
590
524
177
Somewhat Satisfied
1
539
11/17/73
90038
54000
451
15
398
8/2/54
82000
389
486
1
67
1539941
6/22/94
42000
115
267
1539995
10/12/56
90067
960
802
1540076
1/24/78
90012
113
178
1540
183
90066
1018
800
1540217
10/22/69
90011
760
546
103
271
1540539
5/28/60
90049
1000
240
15
406
4/22/70
428
15
417
3/15/60
90052
106000
775
Somewhat Dissatisfied
1541931
1/4/56
561
587
302
1
542
Asian
7/6/84
86000
234
314
154
214
3/27/89
90030
51000
573
218
1542
352
4/3/78
90048
132
154
281
12/10/60
969
1543502
4/6/82
90007
461
563
1543
582
4/23/73
90047
101000
1166
1064
15
437
3/6/90
90042
69000
390
442
265
1543
850
12/7/53
90008
660
1851
124
315
15
439
6/19/63
83000
813
263
1544003
5/8/84
90010
68000
1357
121
1544391
8/20/97
90005
1314
1027
1544417
3/14/80
90040
46000
299
369
1544461
8/3/72
47000
328
1544588
9/1/95
78000
562
105
224
1544628
9/15/83
90021
215
173
1544649
11/19/62
90055
1081
1116
154
476
12/16/95
1272
911
1545054
5/11/84
1020
356
626
320
1545
822
10/17/64
90023
613
1
196
1546050
3/3/65
148
117
892
154
610
7/13/60
123000
799
1546138
3/8/81
1170
1
125
1546179
9/22/74
90034
50000
1546224
9/25/80
90043
99000
228
1546623
5/24/80
90029
723
1038
188
1546
870
5/24/63
71000
1127
1
547
11/26/92
90027
65000
1547
620
11/5/62
90001
63000
241
1547
771
3/10/96
679
790
1547864
6/15/87
397
333
1548038
10/13/96
72000
182
163
154
841
5/27/54
453
1
549
3/18/57
967
764
1549
239
7/8/90
151
667
688
296
1549430
3/26/98
133
176
1549535
10/5/61
87000
438
1549
652
12/3/57
90025
572
767
227
1549708
90032
388
1550
134
1/7/97
91000
571
423
1550268
62000
986
862
273
1550307
11/26/54
90013
887
1
195
1550486
4/28/55
1
220
130
205
1550596
6/20/87
920
1550597
5/4/63
90020
618
489
308
1550751
1/15/53
1550
842
11/3/84
90053
403
684
1550969
4/8/78
104
774
155
139
6/10/87
155
145
8/5/99
675
580
342
15
517
9/27/76
1551796
11/14/61
1730
1206
159
1552
447
7/1/96
312
602
155
284
9/14/76
90068
520
1553405
1/9/64
1566
1193
251
1553430
11/10/52
90044
77000
707
155
353
9/15/00
109000
1113
1553559
8/26/97
90065
149
155
363
8/27/58
90002
158
1553660
8/2/88
698
745
160
1553
692
4/23/95
90019
70000
288
1553737
8/20/79
1
210
867
1553843
11/13/80
136
289
1553952
43000
1
250
1554342
11/4/69
1495
1126
1554589
9/20/95
1489
1
226
155
514
10/10/75
90059
950
211
1555
266
4/4/99
324
482
1555356
8/4/87
89000
899
1004
1555414
3/9/56
90031
1046
351
1555438
1/21/66
90016
637
728
1555539
6/10/81
90061
41000
913
1182
1555
659
8/18/94
1456
1101
1555
979
1/22/56
636
706
1
556
6/25/96
794
724
305
15
568
4/26/83
58000
686
1123
254
1557
181
2/19/69
105000
327
1557273
12/2/66
61000
275
431
1557386
9/23/75
564
1557
407
12/26/76
90015
48000
1398
1163
1557679
9/28/84
90004
1557697
2/28/61
1112
897
15
578
9/13/66
90039
80000
1559734
6/26/68
75000
603
252
1560021
6/28/97
90054
309
1560
248
3/8/77
1560301
3/23/85
93000
907
164
1560457
12/7/89
789
318
1560632
11/15/52
467
1560809
8/26/83
673
1560830
2/4/74
463
383
1561447
11/27/74
1562348
12/2/63
877
1562398
11/7/87
280
233
15
625
9/19/77
1514
1106
1562867
7/2/71
739
1563068
10/24/93
38000
1563186
3/22/64
871
1563296
8/12/99
95000
474
1563548
10/2/88
1
67000
221
1563690
5/11/64
100000
1563805
5/21/79
194
1563935
3/14/74
74000
1062
1075
1563995
12/10/58
319
1564191
11/17/95
1564329
1/10/82
102000
522
15
653
7/28/66
421
1565
499
11/14/83
90018
424
279
1565587
5/27/85
657
1565651
2/1/52
1646
1110
1565786
2/23/60
415
1566013
90022
761
1566051
9/25/95
274
1566110
7/3/66
683
1092
358
1566529
3/3/92
1164
1105
1567124
12/9/56
1128
1567193
10/15/64
1
157
962
168
1567195
7/13/58
1567250
3/13/77
174
1567389
12/9/00
1344
213
1567469
2/18/60
247
1567597
9/5/81
882
1567998
5/27/88
1568380
8/19/56
1096
963
231
1568821
6/27/74
52000
1569187
2/12/56
758
1569198
6/3/54
90064
835
331
1569840
8/25/89
823
1570150
3/8/63
53000
947
1155
1571158
11/22/79
1571419
8/17/64
1285
1
137
1571455
3/5/60
585
1144
1571457
2/19/96
1099
1571887
8/11/76
1571944
11/20/67
1572715
4/25/97
90026
223
1572877
7/28/99
245
1573196
11/4/00
1573273
9/9/75
110000
1497
1157
1573758
6/7/00
57000
531
1573908
9/10/90
293
1574015
1574042
10/19/74
1080
593
1044
1574047
2/4/72
49000
298
202
157
409
11/24/79
1574151
1/28/80
778
1574379
9/1/79
1574687
10/12/68
338
1574764
2/19/80
90041
689
1574841
11/6/81
90037
1212
1122
1575
294
8/25/63
175
172
1575448
6/19/98
1575538
11/2/96
129000
949
1575993
11/10/86
92000
1576211
8/14/85
90017
31000
1336
1244
1576752
7/18/68
1
322
1241
1576984
2/20/66
152
1577095
12/4/97
471
1577691
3/27/76
1577940
2/27/73
634
1578327
8/24/67
1015
1578472
7/4/62
76000
385
1578525
12/12/63
1521
1579349
12/19/80
1579389
5/21/00
90009
1579857
1/26/91
325
586
219
1579979
7/5/99
926
Order your essay today and save 25% with the discount code: GREEN
