The dataset contains data on used cars for sale during the late summer of 2004 in The Netherlands. It has 1436 records containing details on 38 attributes, including Price, Age, Kilometers, HP, and other specications.

Before building models using various data analytics techniques, we need to verify that the data are in reasonable condition. Exploring data techniques are utilized to help us understanding the global landscape of the collected data, detecting unusual values, cleaning and manipulating data, visual discovery and hypothesis generation.

Methods for exploring data include looking at various data aggregations and summarizes, both numerically and graphically. This includes looking at each variable separately as well as looking at relationships between variables. The purpose is to discover patterns and exceptions. For numerical variables, histograms and box plots can be used to learn about the distributions of their value, to detect outliers, and to find other information that is relevant to the analysis task. Similarly, for categorical variables, we can use bar charts. Scatter plots of pairs of numerical variables can be used to learn about possible relationships, the type of relationship, and to detect outliers. Also, to scatter matrix plot, we can use correlation tables to identify the possible relationship between the variables.

1. Which variables are numerical? Which are binary? Which are ordinal? Which are nominal?
2. Create a table with the average, median, min, max, standard deviation, count blank(the number of records with missing values) for each of the quantitative variables. This can be done through 1) Excels functions or 2)Excels Data Data Analysis Descriptive Statistics menu and then use an excel function for counting missing values.
3. Use XLMiner to plot a histogram for each of the quantitative variables. Based on the histogram and summary statistics, answer the following questions:
   1. Which variable has the largest variability?
   2. Which variables seem skewed?
   3. Are there any values that seem extreme (outliers)?
4. Compute the correlation table for the quantitative variable (use Excels Data Data Analysis Correlation menu). In addition, use XLMiner to generate a scatter plot matrix for these variables.
   • Which pair of variables is most strongly correlated?
   • How can we reduce the number of variables based on these correlations?
   • How would the correlations change if we normalized the data first?
5. Explore the relationship between price and the numerical variables by using scatter plot matrix. What does this plot show us? Present your insights within 100 words
6. Explore the relationship between price and the categorical variables by using box plots and Pivot Charts. Which of the pair among variables seem to be correlated? What do these plots and charts show us? Present your insights within 100 words
7. Describe how you would handle records with missing values.
8. Describe how you would handle records with outliers.
9. Describe how you would convert Fuel Type and Color to binary variables. Conrm this using XLMiners utility to transform categorical data into dummies.
10. Conduct a principal components analysis on the cleaned data and comment on the results. Should the data be normalized? Discuss what characterizes the components you consider key. Use the principal components utility in XLMiner.
11. Present your insights in a word document. Your insights should not exceed 100 words for each above question.
12. Modify the worksheets titles and names appropriately to reflect the contents.

Important submission instructions

Save your Word file and XLMiner solution. Use the link Homework 1 to upload these files. Due by 11.59 P.M. Feb. 15, 2021.

V home AutoSave OFF Insert Toyota Corolla (9) Analytic Solver Data Mining Tell me Draw Page Layout Formulas Data Review View Share Comments Arial v 10 v E pv ab Wrap Text General Normal BV Bad AutoSum X Cut Copy v Format --- WE 4 Fill v Paste A BIU Neutral v Good Merge & Center Format $78-98 $ % ) Insert Delete Conditional Format Formatting as Table Sort & Filter Clear v Find Select Ideas R19 X fx 1105 D E I J L 0 U Y Z AA 1 1 0 0 0 4 4 4 4 ld 1 0 O Automatic 6 7 7 11 7 8 10 1 6 11 6 Mig Year 4 10 10 9 7 3 6 1 6 3 3 10 4 4 4 4. 4 4 4 4 4 4 4 4 4 2 11 6 10 8 11 8 2 2 1 5 4 4 4 7 6 3 9 9 4 4 4 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 A B 33 TOYOTA COI 15950 34 TOYOTA CO 14950 35 TOYOTA CA 15500 36 TOYOTA Coi 15750 37 TOYOTA Cor 15950 38 TOYOTA Ct 14950 39 TOYOTA Cot 15750 40 TOYOTA CO 14750 + TOYOTA Ca 13950 2 TOYOTA Cot 16750 43 TOYOTA CO! 13950 44 TOYOTA COI 16950 5 TOYOTA Cot 16950 46 TOYOTA CO 19000 47 TOYOTA COI 17950 48 TOYOTA COI 15800 49 TOYOTA CO 17950 50 TOYOTA C 21950 51 TOYOTA Co 17950 52 TOYOTA CO 15750 53 TOYOTA CO: 20500 54 TOYOTA CI 21950 55 TOYOTA CO 15500 56 TOYOTA CO 13250 57 TOYOT CI 15250 58 TOYOTA Cot 15250 59 TOYOTA CO 18950 60 TOYOTA CO 15999 61 TOYOTA Ct 14950 62 TOYOTA CO 16500 63 TOYOTA Cor 18750 64 TOYOTA CO 17950 65 TOYOTA Co. 17950 B TOYOTA COI 16950 67 TOYOTA CO 18950 68 TOYOTA CO! 14950 69 TOYOTA Cor 22250 70 TOYOTA CO 15950 71 TOYOTA CO 15950 2 TOYOTA Col 12995 73 TOYOTA CO 18950 74 TOYOTA Ca 15750 75 TOYOTA CO 19950 76 TOYOTA COI 16950 77 TOYOTA COI 18750 78 TOYOTA Cor 18450 79 TOYOTA Cot 16895 80 TOYOTA CO: 14900 1 TOYOTA I. 18950 27 26 22 26 25 23 32 27 22 27 22 27 22 23 27 22 22 31 22 30 26 27 25 32 28 26 23 30 22 27 31 30 27 26 28 22 22 25 28 32 28 23 28 23 31 27 29 30 25 1 5 7 10 F G H 2002 29510 Petrol 2002 32692 Petrol 2002 41000 Petrol 2002 43000 Patrol 2002 25000 Patrol 2002 10000 Petrol 2002 25329 Petrol 2002 27500 Petrol 2002 49059 Petrol 2002 44068 Petrol 2002 46961 Petrol 2002 110404 Diesel 2002 100250 Diesel 2002 34000 Diesel 2002 79375 Diesel 2002 75048 Petrol 2002 72215 Diesel 2002 64982 Petrol 2002 62636 Diesel 2002 57086 Patrol 2002 56000 Petrol 2002 49866 Petrol 2002 49163 Petrol 2002 45725 Petrol 43210 Patrol 2002 43000 Petrol 2002 39704 Petrol 2002 38950 Patrol 2002 37400 Petrol 2002 37177 Petrol 2002 36544 Patrol 2002 33511 Petrol 2002 32809 Petrol 2002 32181 Patrol 2002 30993 Petrol 2002 30400 Petrol 2002 30000 Diesel 2002 29719 Petrol 2002 29206 Petrol 2002 29198 Petrol 2002 28817 Patrol 2002 28227 Petrol 2002 28000 Patrol 2002 28000 Petrol 2002 25266 Petrol 2002 23489 Petrol 2002 22575 Petrol 2002 22000 Petrol 2002 20019 Petrol K 1 Black 1 Grey 1 Grey O Grey o 0 Grey 1 Blue 1 Green O Blue O Blue 1 Red Red O Blue O Red O Red 0 Red 1 Grey 1 Blue 1 Grey 1 Grey 1 Grey 1 Grey 1 Blue 1 Green O Green 1 Black o Grey O Grey 1 Blue 1 Red 1 Black 0 Red 1 Blue 1 Blue 1 Black 1 Blue 1 Black 1 Grey 1 Grey 1 Black 1 Grey 1 Grey 1 Grey 1 Grey O Black 1 Grey 1 Grey O Silver 1 Grey 1 Grey 1 Blue 97 97 97 97 97 97 97 97 97 97 97 90 90 90 90 97 90 192 90 97 110 192 110 110 97 97 110 110 97 110 110 110 97 110 110 97 110 97 97 97 110 110 97 110 110 110 110 110 97 110 M 01400 0 1400 01400 0 1400 0 1400 01400 0 1400 01400 0 1400 0 1400 0 1400 0 3000 0 2000 o 2000 0 2000 01400 0 2000 0 1800 0 2000 01400 1600 0 1800 11600 1600 01400 01400 1 1600 0 1600 01400 0 1600 0 1600 0 1600 0 1400 0 1600 0 1600 01400 0 2000 01400 0 1400 01400 5598 0 1400 0 1600 0 1600 0 1600 0 1600 0 1600 01400 116000 IND 3 3 3 3 3 3 3 3 3 3 3 3 3 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 P Q R S 5 85 1100 1 5 85 1100 0 5 85 1100 0 5 85 1100 1 Model Price Age 08 04 Ng Month 1 TOYOTA Corol 13500 23 2 TOYOTA Corol 13750 23 3 TOYOTA Corol 13950 24 4 TOYOTA Coroll 14950 26 5 TOYOTA Corol 13750 30 6 TOYOTA Corol 12950 32 7 TOYOTA Corol 16900 27 8 TOYOTA Corol 18600 30 9 TOYOTA Corol 21500 27 10 TOYOTA Corol 12950 23 11 TOYOTA Corot 20950 25 12 TOYOTA Corol 19950 22 13 TOYOTA Corol 19600 25 14 TOYOTA Corol 21500 31 15 TOYOTA Corol 22500 32 16 TOYOTA Corol 22000 28 17 TOYOTA Corol 22750 30 18 TOYOTA Corol 17950 24 19 TOYOTA Corol 16750 24 20 TOYOTA Corot 16950 30 21 TOYOTA Corol 15950 30 22 TOYOTA Corol 16950 29 23 TOYOTA Corol 15950 28 24 TOYOTA Corol 16950 28 25 TOYOTA Corol 16250 29 26 TOYOTA Corol 15950 25 27 TOYOTA Corol 17495 27 28 TOYOTA Corol 15750 29 29 TOYOTA Corol 16950 28 30 TOYOTA Coroll 17950 30 31 TOYOTA Corol 12950 29 32 TOYOTA Corol 15750 22 33 TOYOTA Coro 15950 27 34 TOYOTA Corol 14950 26 35 TOYOTA Corol 15500 22 36 TOYOTA Coroll 15750 26 37 TOYOTA Corol 15950 25 38 TOYOTA Corol 14950 23 39 TOYOTA Corot 15750 32 40 TOYOTA Corol 14750 27 41 TOYOTA Corol 13950 22 42 TOYOTA Corol 16750 27 43 TOYOTA Corol 13950 22 44 TOYOTA Coro 16950 27 V W 3 1 1 1 1 3 1 1 3 1 1 3 1 1 1 KM Fuel Typ HP 2002 46986 Diesel 2002 72937 Diesel 2002 41711 Diesel 2002 48000 Diesel 2002 38500 Diesel 2002 61000 Diesel 2002 94612 Diesel 2002 75889 Diesel 2002 19700 Petrol 2002 71138 Diesel 2002 31461 Petrol 2002 43610 Petrol 2002 32189 Petrol 2002 23000 Petrol 2002 34131 Petrol 2002 18739 Petrol 2002 34000 Petrol 2002 21716 Petrol 2002 25563 Petrol 2002 64359 Petrol 2002 67660 Petrol 2002 43905 Petrol 2002 56349 Petrol 2002 32220 Petrol 2002 25813 Petrol 2002 28450 Petrol 2002 34545 Petrol 2002 41415 Petrol 2002 44142 Petrol 2002 11090 Petrol 2002 9750 Petrol 2002 35199 Petrol 2002 29510 Petrol 2002 32692 Petrol 2002 41000 Petrol 2002 43000 Petrol 2002 25000 Petrol 2002 10000 Petrol 2002 25329 Petrol 2002 27500 Petrol 2002 49059 Petrol 2002 44068 Petrol 2002 46961 Petrol 2002 110404 Diesel 2002 1 1 1 1 1 1 1 1 Met Color 90 1 Blue 90 1 Silver 90 1 Blue 90 O Black 90 O Black 90 O White 90 1 Grey 90 1 Grey 192 O Red 69 O Blue 192 O Silver 192 O Red 192 O Red 192 1 Black 192 1 Grey 1 192 O Grey 192 1 Grey 110 1 Blue 110 O Grey 110 1 Grey 110 1 Blue 110 O Grey 110 1 Black 110 1 Black 110 1 Grey 110 1 Blue 110 1 Blue 110 1 Black 110 O Grey 110 1 Blue 97 1 Silver 97 1 Blue 97 1 Black 97 1 Grey 97 1 Grey 97 O Grey 97 O Grey 97 1 Blue 97 1 Green 97 O Blue 97 O Blue 97 1 Red 97 O Blue 90 O Red 4 4 4 3 4 4 6 2 3 6 7 5 5 11 11 8 5 1 5 10 5 5 10 2 6 3 3 4 5 5 5 5 4 8 6 4 5 3 4 4 11 6 7 4 4 4 4 4 4 4 4 4 4 11 4 4 4 7 8 10 1 6 6 11 6 6 11 6 4 4 4 3 8 V home AutoSave OFF Insert Toyota Corolla (9) Analytic Solver Data Mining Tell me Draw Page Layout Formulas Data Review View Share Comments Arial v 10 v E pv ab Wrap Text General Normal BV Bad AutoSum X Cut Copy v Format --- WE 4 Fill v Paste A BIU Neutral v Good Merge & Center Format $78-98 $ % ) Insert Delete Conditional Format Formatting as Table Sort & Filter Clear v Find Select Ideas R19 X fx 1105 D E I J L 0 U Y Z AA 1 1 0 0 0 4 4 4 4 ld 1 0 O Automatic 6 7 7 11 7 8 10 1 6 11 6 Mig Year 4 10 10 9 7 3 6 1 6 3 3 10 4 4 4 4. 4 4 4 4 4 4 4 4 4 2 11 6 10 8 11 8 2 2 1 5 4 4 4 7 6 3 9 9 4 4 4 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 A B 33 TOYOTA COI 15950 34 TOYOTA CO 14950 35 TOYOTA CA 15500 36 TOYOTA Coi 15750 37 TOYOTA Cor 15950 38 TOYOTA Ct 14950 39 TOYOTA Cot 15750 40 TOYOTA CO 14750 + TOYOTA Ca 13950 2 TOYOTA Cot 16750 43 TOYOTA CO! 13950 44 TOYOTA COI 16950 5 TOYOTA Cot 16950 46 TOYOTA CO 19000 47 TOYOTA COI 17950 48 TOYOTA COI 15800 49 TOYOTA CO 17950 50 TOYOTA C 21950 51 TOYOTA Co 17950 52 TOYOTA CO 15750 53 TOYOTA CO: 20500 54 TOYOTA CI 21950 55 TOYOTA CO 15500 56 TOYOTA CO 13250 57 TOYOT CI 15250 58 TOYOTA Cot 15250 59 TOYOTA CO 18950 60 TOYOTA CO 15999 61 TOYOTA Ct 14950 62 TOYOTA CO 16500 63 TOYOTA Cor 18750 64 TOYOTA CO 17950 65 TOYOTA Co. 17950 B TOYOTA COI 16950 67 TOYOTA CO 18950 68 TOYOTA CO! 14950 69 TOYOTA Cor 22250 70 TOYOTA CO 15950 71 TOYOTA CO 15950 2 TOYOTA Col 12995 73 TOYOTA CO 18950 74 TOYOTA Ca 15750 75 TOYOTA CO 19950 76 TOYOTA COI 16950 77 TOYOTA COI 18750 78 TOYOTA Cor 18450 79 TOYOTA Cot 16895 80 TOYOTA CO: 14900 1 TOYOTA I. 18950 27 26 22 26 25 23 32 27 22 27 22 27 22 23 27 22 22 31 22 30 26 27 25 32 28 26 23 30 22 27 31 30 27 26 28 22 22 25 28 32 28 23 28 23 31 27 29 30 25 1 5 7 10 F G H 2002 29510 Petrol 2002 32692 Petrol 2002 41000 Petrol 2002 43000 Patrol 2002 25000 Patrol 2002 10000 Petrol 2002 25329 Petrol 2002 27500 Petrol 2002 49059 Petrol 2002 44068 Petrol 2002 46961 Petrol 2002 110404 Diesel 2002 100250 Diesel 2002 34000 Diesel 2002 79375 Diesel 2002 75048 Petrol 2002 72215 Diesel 2002 64982 Petrol 2002 62636 Diesel 2002 57086 Patrol 2002 56000 Petrol 2002 49866 Petrol 2002 49163 Petrol 2002 45725 Petrol 43210 Patrol 2002 43000 Petrol 2002 39704 Petrol 2002 38950 Patrol 2002 37400 Petrol 2002 37177 Petrol 2002 36544 Patrol 2002 33511 Petrol 2002 32809 Petrol 2002 32181 Patrol 2002 30993 Petrol 2002 30400 Petrol 2002 30000 Diesel 2002 29719 Petrol 2002 29206 Petrol 2002 29198 Petrol 2002 28817 Patrol 2002 28227 Petrol 2002 28000 Patrol 2002 28000 Petrol 2002 25266 Petrol 2002 23489 Petrol 2002 22575 Petrol 2002 22000 Petrol 2002 20019 Petrol K 1 Black 1 Grey 1 Grey O Grey o 0 Grey 1 Blue 1 Green O Blue O Blue 1 Red Red O Blue O Red O Red 0 Red 1 Grey 1 Blue 1 Grey 1 Grey 1 Grey 1 Grey 1 Blue 1 Green O Green 1 Black o Grey O Grey 1 Blue 1 Red 1 Black 0 Red 1 Blue 1 Blue 1 Black 1 Blue 1 Black 1 Grey 1 Grey 1 Black 1 Grey 1 Grey 1 Grey 1 Grey O Black 1 Grey 1 Grey O Silver 1 Grey 1 Grey 1 Blue 97 97 97 97 97 97 97 97 97 97 97 90 90 90 90 97 90 192 90 97 110 192 110 110 97 97 110 110 97 110 110 110 97 110 110 97 110 97 97 97 110 110 97 110 110 110 110 110 97 110 M 01400 0 1400 01400 0 1400 0 1400 01400 0 1400 01400 0 1400 0 1400 0 1400 0 3000 0 2000 o 2000 0 2000 01400 0 2000 0 1800 0 2000 01400 1600 0 1800 11600 1600 01400 01400 1 1600 0 1600 01400 0 1600 0 1600 0 1600 0 1400 0 1600 0 1600 01400 0 2000 01400 0 1400 01400 5598 0 1400 0 1600 0 1600 0 1600 0 1600 0 1600 01400 116000 IND 3 3 3 3 3 3 3 3 3 3 3 3 3 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 P Q R S 5 85 1100 1 5 85 1100 0 5 85 1100 0 5 85 1100 1 Model Price Age 08 04 Ng Month 1 TOYOTA Corol 13500 23 2 TOYOTA Corol 13750 23 3 TOYOTA Corol 13950 24 4 TOYOTA Coroll 14950 26 5 TOYOTA Corol 13750 30 6 TOYOTA Corol 12950 32 7 TOYOTA Corol 16900 27 8 TOYOTA Corol 18600 30 9 TOYOTA Corol 21500 27 10 TOYOTA Corol 12950 23 11 TOYOTA Corot 20950 25 12 TOYOTA Corol 19950 22 13 TOYOTA Corol 19600 25 14 TOYOTA Corol 21500 31 15 TOYOTA Corol 22500 32 16 TOYOTA Corol 22000 28 17 TOYOTA Corol 22750 30 18 TOYOTA Corol 17950 24 19 TOYOTA Corol 16750 24 20 TOYOTA Corot 16950 30 21 TOYOTA Corol 15950 30 22 TOYOTA Corol 16950 29 23 TOYOTA Corol 15950 28 24 TOYOTA Corol 16950 28 25 TOYOTA Corol 16250 29 26 TOYOTA Corol 15950 25 27 TOYOTA Corol 17495 27 28 TOYOTA Corol 15750 29 29 TOYOTA Corol 16950 28 30 TOYOTA Coroll 17950 30 31 TOYOTA Corol 12950 29 32 TOYOTA Corol 15750 22 33 TOYOTA Coro 15950 27 34 TOYOTA Corol 14950 26 35 TOYOTA Corol 15500 22 36 TOYOTA Coroll 15750 26 37 TOYOTA Corol 15950 25 38 TOYOTA Corol 14950 23 39 TOYOTA Corot 15750 32 40 TOYOTA Corol 14750 27 41 TOYOTA Corol 13950 22 42 TOYOTA Corol 16750 27 43 TOYOTA Corol 13950 22 44 TOYOTA Coro 16950 27 V W 3 1 1 1 1 3 1 1 3 1 1 3 1 1 1 KM Fuel Typ HP 2002 46986 Diesel 2002 72937 Diesel 2002 41711 Diesel 2002 48000 Diesel 2002 38500 Diesel 2002 61000 Diesel 2002 94612 Diesel 2002 75889 Diesel 2002 19700 Petrol 2002 71138 Diesel 2002 31461 Petrol 2002 43610 Petrol 2002 32189 Petrol 2002 23000 Petrol 2002 34131 Petrol 2002 18739 Petrol 2002 34000 Petrol 2002 21716 Petrol 2002 25563 Petrol 2002 64359 Petrol 2002 67660 Petrol 2002 43905 Petrol 2002 56349 Petrol 2002 32220 Petrol 2002 25813 Petrol 2002 28450 Petrol 2002 34545 Petrol 2002 41415 Petrol 2002 44142 Petrol 2002 11090 Petrol 2002 9750 Petrol 2002 35199 Petrol 2002 29510 Petrol 2002 32692 Petrol 2002 41000 Petrol 2002 43000 Petrol 2002 25000 Petrol 2002 10000 Petrol 2002 25329 Petrol 2002 27500 Petrol 2002 49059 Petrol 2002 44068 Petrol 2002 46961 Petrol 2002 110404 Diesel 2002 1 1 1 1 1 1 1 1 Met Color 90 1 Blue 90 1 Silver 90 1 Blue 90 O Black 90 O Black 90 O White 90 1 Grey 90 1 Grey 192 O Red 69 O Blue 192 O Silver 192 O Red 192 O Red 192 1 Black 192 1 Grey 1 192 O Grey 192 1 Grey 110 1 Blue 110 O Grey 110 1 Grey 110 1 Blue 110 O Grey 110 1 Black 110 1 Black 110 1 Grey 110 1 Blue 110 1 Blue 110 1 Black 110 O Grey 110 1 Blue 97 1 Silver 97 1 Blue 97 1 Black 97 1 Grey 97 1 Grey 97 O Grey 97 O Grey 97 1 Blue 97 1 Green 97 O Blue 97 O Blue 97 1 Red 97 O Blue 90 O Red 4 4 4 3 4 4 6 2 3 6 7 5 5 11 11 8 5 1 5 10 5 5 10 2 6 3 3 4 5 5 5 5 4 8 6 4 5 3 4 4 11 6 7 4 4 4 4 4 4 4 4 4 4 11 4 4 4 7 8 10 1 6 6 11 6 6 11 6 4 4 4 3 8