Homework SourseNote The data file is too large and chegg would not allow me
Note: The data file is too large, and chegg would not allow me to upload it. Below are just 50 records from the total of 2000 records. Please answer the question if you already have the data file. Thanks for understanding.
In this section, you will conduct correlation and regression analyses using the table below.
Correlation: Compute a correlation matrix that includes all continuous variables. Identify all individual correlations that are significant at the 95 percent level.
Regression: Build a multiple regression model to explain the variability in the median school year. Describe the goodness of fit of your model and summarize your findings. Select at least four to seven similar independent variables from the remaining forty-nine measures and justify your selection.
Submit your response in Microsoft Excel.
| HouseholdID | City | State | MSACode | Gender | Age | Income | WealthScore | Occupation | MaritalStatus | LengthOfResidence | PresenceOfChildren | NumberOfChildren | MailResponder | MailBuyer | MailDonor | OutdoorsDimension | AthleticDimension | FitnessDimension | DomesticDimension | GoodLifeDimension | NumberOfCars | TruckOwnerCode | NewVehicleCode | StandardRetail | LowEndDepartment | MainStreetRetail | UpscaleRetail | CatalogShowroom | ComputerElectronic | Furniture | HomeOfficeSupply | HomeImprovement | MembershipWarehouse | SportingGoods | TVMailOrder | Ownacat | Ownadog | DietConcerns | VeteraninHousehold | OwnCellularphone | OwnMotorcycle | OwnRV | OwnSwimmingpool | OnlinePurchaser | WealthRating | EstimatedMedianFamilyIncome | MedianSchoolYears | ComputerOwner | Region | |
| AB100794446 | Manchester | CT | F | 60 | Under $20,000 | 141.12 | Unknown | Single | In the 3rd Year | U | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 4 | 69835 | 12 | 5 | |||||||||||||||||||||||||||||
| BL103110217 | Ankeny | IA | M | 30 | $50,000 - $74,999 | 246.38 | Skilled Trade/Machine/Laborer | Single | In the 3rd Year | U | 0 | 0 | 0 | 0 | 0 | Y | 0 | 0 | U | U | U | U | U | U | U | U | U | U | U | U | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 7 | 64938 | 14.5 | 8 | |||||||
| AL100297826 | Lake Tapps | WA | M | 62 | $75,000 - $99,999 | 375.33 | Unknown | Married | 15+ Years | U | 0 | 1 | 2 | 2 | 1 | 0 | 0 | Y | U | U | U | U | U | U | U | U | U | U | U | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 59645 | 13.5 | 6 | ||||||||
| AK103361183 | Grand Rapids | MI | M | 44 | $50,000 - $74,999 | 333.88 | Sales/Marketing | Single | 15+ Years | Y | 4 | 6 | 2 | 1 | 1 | Y | 2 | 0 | 0 | U | Y | U | U | U | U | U | U | U | U | U | U | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 5 | 62719 | 13.4 | 1 | ||||||
| AP100808185 | O Fallon | MO | F | 0 | $75,000 - $99,999 | 336.18 | Unknown | Unknown | In the 7th Year | Y | 1 | 2 | 2 | 0 | 2 | 0 | 1 | Y | U | U | U | U | U | U | U | U | U | U | U | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 8 | 72273 | 14.5 | 8 | ||||||||
| AL102281729 | Knoxville | TN | M | 30 | $40,000 - $49,999 | 323.68 | Unknown | Married | In the 6th Year | Y | 2 | 2 | 2 | 0 | Y | Y | Y | Y | 1 | 1 | 0 | U | Y | U | Y | U | Y | U | U | U | U | U | U | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 6 | 55903 | 14.6 | Y | 2 | |||
| AA100557115 | Cambridge | MN | 5120 | F | 62 | $150,000 - $174,999 | 332.89 | Unknown | Unknown | In the 10th Year | U | 0 | 2 | 2 | 0 | 1 | 1 | 0 | U | U | U | U | U | U | U | U | U | U | U | U | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 13.5 | 8 | |||||||||
| AU102418311 | Stoughton | MA | 1120 | M | 68 | $75,000 - $99,999 | 384.21 | Retired | Married | 15+ Years | U | 0 | 1 | 1 | 0 | 0 | 0 | Y | U | U | Y | Y | U | U | U | U | U | U | U | 0 | 0 | 1 | 1 | 0 | 1 | 1 | 1 | 0 | 14.2 | 5 | ||||||||||
| BS102737313 | Raymond | NH | 4160 | F | 46 | $75,000 - $99,999 | 295.39 | Unknown | Unknown | 15+ Years | Y | 2 | 1 | 1 | 0 | Y | Y | Y | 0 | 0 | 0 | 12.6 | 5 | |||||||||||||||||||||||||||
| BQ103379666 | North Mankato | MN | M | 56 | $50,000 - $74,999 | 274.67 | Upper Management/Executive | Married | 15+ Years | U | 0 | 2 | 2 | 0 | Y | 2 | 0 | 0 | U | U | U | Y | U | U | U | U | U | U | U | U | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 13.1 | 8 | |||||||||
| AX102690269 | Los Angeles | CA | 4480 | M | 36 | $30,000 - $39,999 | 363.49 | Executive/Administrator | Unknown | 15+ Years | Y | 2 | 2 | 2 | 0 | 0 | 0 | Y | Y | U | U | U | U | U | U | U | U | U | U | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 12.4 | 6 | ||||||||||
| AJ102079864 | Azle | TX | 2800 | F | 28 | $40,000 - $49,999 | 190.13 | Unknown | Unknown | In the 3rd Year | Y | 1 | 0 | 0 | 0 | 0 | 0 | 11.8 | 9 | |||||||||||||||||||||||||||||||
| BG101542955 | Easthampton | MA | 8000 | M | 0 | $30,000 - $39,999 | 217.76 | Unknown | Unknown | In the 13th Year | U | 0 | 0 | 0 | 0 | 0 | 0 | 13.2 | 5 | |||||||||||||||||||||||||||||||
| AM100685288 | Las Vegas | NV | 4120 | F | 0 | $20,000 - $29,999 | 270.39 | Unknown | Unknown | In the 11th Year | U | 0 | 0 | 0 | 0 | 0 | 0 | U | Y | U | Y | U | U | U | U | U | U | U | U | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 1 | 12 | 4 | ||||||||||
| BF101795019 | Rossville | GA | 1560 | F | 34 | Under $20,000 | 102.3 | Unknown | Unknown | In the 7th Year | U | 0 | 0 | 0 | 0 | 0 | 0 | 10.7 | 7 | |||||||||||||||||||||||||||||||
| AS101518382 | Lehigh Acres | FL | 2700 | M | 26 | $50,000 - $74,999 | 174.67 | Unknown | Unknown | In the 1st Year | U | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 11.9 | 7 | ||||||||||||||||||||||||||||||
| BT102581146 | West Columbia | SC | 1760 | M | 52 | $250,000+ | 376.97 | Executive/Administrator | Married | In the 4th Year | Y | 2 | 2 | 2 | 0 | 0 | 0 | Y | Y | U | Y | U | Y | Y | U | U | U | U | U | 0 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 12.5 | 7 | ||||||||||
| AE102386836 | Warner Robins | GA | 4680 | F | 50 | Under $20,000 | 180.26 | Unknown | Unknown | In the 2nd Year | Y | 1 | 0 | 0 | 0 | 0 | 0 | U | U | U | U | U | U | U | U | U | U | U | U | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 11.3 | 7 | ||||||||||
| BT101916705 | Akron | OH | 80 | F | 44 | $50,000 - $74,999 | 244.08 | Unknown | Unknown | In the 4th Year | Y | 1 | 2 | 2 | 0 | 0 | 1 | 0 | 12 | 1 | ||||||||||||||||||||||||||||||
| AF101535688 | Johnston | RI | 6480 | M | 48 | $30,000 - $39,999 | 357.57 | Unknown | Single | 15+ Years | Y | 1 | 2 | 2 | 0 | 0 | 0 | Y | Y | Y | Y | U | U | U | U | U | U | U | U | 1 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 12.2 | 5 | ||||||||||
| BT100905543 | Mineola | TX | M | 60 | $30,000 - $39,999 | 178.62 | Unknown | Unknown | In the 2nd Year | Y | 3 | 2 | 2 | 1 | 0 | 0 | 0 | U | U | U | U | U | U | U | U | U | U | U | U | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 0 | 1 | 11.5 | Y | 9 | |||||||||
| AR100318955 | Grants Pass | OR | F | 82 | $75,000 - $99,999 | 284.21 | Unknown | Unknown | 15+ Years | U | 0 | 2 | 2 | 0 | 2 | 0 | 0 | Y | Y | U | Y | U | U | U | U | U | U | U | U | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 11.9 | 6 | ||||||||||
| AH100794933 | Salem | OR | F | 38 | $50,000 - $74,999 | 261.51 | Unknown | Unknown | In the 12th Year | Y | 3 | 0 | 0 | 0 | 0 | 1 | 0 | U | Y | U | U | U | U | Y | U | U | U | U | U | 0 | 0 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 12.3 | 6 | ||||||||||
| AB101731499 | Somers Point | NJ | 560 | F | 66 | $20,000 - $29,999 | 234.21 | Unknown | Unknown | In the 3rd Year | U | 0 | 0 | 0 | 0 | 0 | 0 | U | U | U | U | U | U | U | U | U | U | U | U | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 0 | 12.3 | 3 | ||||||||||
| BS100678069 | Petaluma | CA | 7500 | M | 66 | $40,000 - $49,999 | 490.46 | Unknown | Unknown | 15+ Years | U | 0 | 2 | 2 | 0 | Y | Y | Y | Y | Y | 0 | 0 | Y | Y | Y | Y | U | U | Y | U | U | U | U | Y | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 14.6 | 6 | |||||
| BB100821672 | Moosup | CT | F | 46 | $75,000 - $99,999 | 310.53 | Unknown | Unknown | In the 9th Year | Y | 4 | 2 | 2 | 0 | 0 | 0 | U | Y | U | Y | U | U | Y | U | U | U | U | Y | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 12.1 | 5 | |||||||||||
| BA102835475 | Covington | GA | 520 | M | 66 | $50,000 - $74,999 | 299.34 | Sales/Marketing | Married | In the 15th Year | Y | 1 | 2 | 2 | 0 | 0 | 0 | U | Y | U | Y | U | U | Y | U | U | U | U | U | 1 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 11.5 | 7 | ||||||||||
| AK100931054 | Dillon | SC | F | 54 | $20,000 - $29,999 | 190.13 | Unknown | Unknown | In the 12th Year | Y | 1 | 0 | 0 | 0 | 0 | 0 | U | U | U | U | U | U | U | U | U | U | U | U | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 11 | 7 | |||||||||||
| AP102795843 | Flemington | MO | F | 46 | $75,000 - $99,999 | 191.12 | Executive/Administrator | Married | In the 6th Year | U | 0 | 2 | 2 | 0 | 2 | 0 | 0 | 11.5 | 8 | |||||||||||||||||||||||||||||||
| AY102599396 | Cerritos | CA | 4480 | M | 64 | $125,000 - $149,999 | 490.46 | Executive/Administrator | Married | 15+ Years | U | 0 | 2 | 2 | 1 | Y | Y | 0 | 0 | Y | Y | Y | Y | U | U | Y | U | U | U | U | U | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 15.3 | 6 | ||||||||
| BH102034931 | Columbia | SC | 1760 | F | 52 | $40,000 - $49,999 | 203.29 | Unknown | Married | In the 3rd Year | U | 0 | 0 | 0 | 0 | 0 | 0 | 15.2 | 7 | |||||||||||||||||||||||||||||||
| BT102671792 | Walhalla | SC | M | 68 | Under $20,000 | 151.32 | Executive/Administrator | Single | In the 14th Year | U | 0 | 1 | 1 | 0 | 0 | 0 | Y | U | U | U | U | U | U | U | U | U | U | U | 0 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 1 | 11.5 | 7 | |||||||||||
| AP102934858 | Eden | NY | 1280 | M | 38 | $75,000 - $99,999 | 285.86 | Executive/Administrator | Married | In the 2nd Year | Y | 2 | 1 | 1 | 0 | 1 | 1 | 0 | Y | U | U | U | U | U | U | Y | U | U | U | U | 0 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 13.5 | 3 | |||||||||
| AR102810155 | Corvallis | MT | F | 64 | $75,000 - $99,999 | 274.67 | Unknown | Unknown | In the 7th Year | U | 0 | 2 | 2 | 0 | 0 | 1 | 1 | U | U | U | U | U | U | U | U | U | U | U | U | 0 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 13.1 | 4 | ||||||||||
| AG102694587 | Salina | KS | F | 76 | $20,000 - $29,999 | 186.84 | Upper Management/Executive | Married | 15+ Years | U | 0 | 2 | 2 | 0 | 0 | 0 | Y | Y | U | Y | U | Y | U | U | U | U | U | U | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 13.3 | 8 | |||||||||||
| AA100276439 | Crestview | FL | 2750 | F | 58 | $75,000 - $99,999 | 229.28 | Unknown | Unknown | In the 2nd Year | U | 0 | 1 | 1 | 0 | 1 | 1 | 1 | Y | Y | U | Y | U | U | U | U | U | U | U | U | 1 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 0 | 12 | 7 | |||||||||
| AZ104193154 | Baltimore | MD | 720 | M | 38 | $30,000 - $39,999 | 369.74 | Unknown | Unknown | 15+ Years | U | 1 | 2 | 2 | 1 | Y | U | U | Y | U | U | U | U | U | U | U | U | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 7 | |||||||||||||
| AL101833829 | Chicago Hts | IL | 1600 | F | 0 | $20,000 - $29,999 | 127.96 | Unknown | Single | In the 4th Year | U | 0 | 2 | 2 | 0 | 0 | 0 | 11.4 | 1 | |||||||||||||||||||||||||||||||
| AX100815576 | Covington | VA | F | 38 | $75,000 - $99,999 | 247.7 | Unknown | Unknown | In the 6th Year | Y | 2 | 1 | 1 | 0 | 0 | 0 | 11.9 | 7 | ||||||||||||||||||||||||||||||||
| BT100779542 | Holly Lake Ra | TX | M | 70 | Under $20,000 | 136.84 | Unknown | Married | In the 7th Year | U | 0 | 1 | 1 | 0 | 1 | 0 | 0 | 12.8 | 9 | |||||||||||||||||||||||||||||||
| AT103418941 | Crp Christi | TX | 1880 | M | 88 | $125,000 - $149,999 | 325.99 | Upper Management/Executive | Married | 15+ Years | U | 0 | 2 | 2 | 0 | Y | 2 | 0 | 1 | Y | U | Y | Y | U | U | Y | U | U | U | U | U | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 14.1 | Y | 9 | |||||||
| AF102360978 | Queens Vlg | NY | 5600 | M | 58 | $30,000 - $39,999 | 349.34 | Unknown | Married | In the 4th Year | U | 0 | 2 | 2 | 0 | 1 | 0 | 0 | U | U | U | U | U | U | U | U | U | U | U | U | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 12.3 | 3 | |||||||||
| AH105381822 | Dallas | OR | 7080 | F | 0 | $40,000 - $49,999 | 206.25 | Homemaker | Unknown | In the 2nd Year | U | 0 | 0 | 0 | 1 | U | U | U | U | U | U | U | U | U | U | U | U | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 6 | |||||||||||||
| AT103049290 | Lititz | PA | 4000 | F | 52 | $40,000 - $49,999 | 200.66 | Executive/Administrator | Unknown | In the 11th Year | U | 0 | 0 | 0 | 0 | 0 | 0 | 10.8 | 3 | |||||||||||||||||||||||||||||||
| BM102055805 | Lanett | AL | M | 34 | $20,000 - $29,999 | 178.95 | Unknown | Unknown | 15+ Years | U | 0 | 2 | 2 | 0 | 2 | 1 | 1 | Y | U | U | U | U | U | U | U | U | U | U | U | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 11.4 | 2 | ||||||||||
| AN103221326 | Mishawaka | IN | 7800 | M | 48 | $75,000 - $99,999 | 332.24 | Sales/Marketing | Married | In the 14th Year | Y | 1 | 2 | 2 | 0 | Y | Y | 0 | 0 | 13.7 | 1 | |||||||||||||||||||||||||||||
| BK102944161 | Westfield | NY | 3610 | M | 46 | $50,000 - $74,999 | 283.55 | Upper Management/Executive | Unknown | In the 12th Year | Y | 2 | 1 | 1 | 0 | 1 | 1 | 0 | U | U | U | U | U | U | U | U | U | U | U | U | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 11.9 | 3 | |||||||||
| BE102692940 | Walnut Creek | CA | 5775 | M | 46 | $40,000 - $49,999 | 376.64 | Skilled Trade/Machine/Laborer | Single | In the 2nd Year | Y | 1 | 0 | 0 | 0 | 0 | 0 | 15.3 | 6 | |||||||||||||||||||||||||||||||
| AM107675543 | Mount Airy | NC | M | 46 | $30,000 - $39,999 | 144.08 | Homemaker | Unknown | In the 2nd Year | Y | 0 | 2 | 2 | 1 | U | U | U | U | U | U | U | U | U | U | U | U | 0 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | Y | 7 |
Solution
A variable is said to be continuous in (a,b) if it takes all the possible values between a and b including the decimal values. If it takes only integer value then it is said to be discrete variable.
So in the given problem the continuous variables are - Median Score and Wealth Score.
The correlation between these two variables is - 0.719237
At 95% level of significance, the critical value of the correlation for 2000 data points is approximately 0.05.
As the calculated correlation is much larger than the critical value, hence it is significant.
------------------------------------------------------------------------------------------------------------------------
Regression -
The dependent variable is the \"Median Score\" and we are asked to choose any random 4-7 independent variables from the remaining 49 variables for building the regression model.
I am choosing the variables - Age, Wealth Score, NumberOfChildren, MailResponder, MailBuyer, MailDonor, TruckOwnerCode, NewVehicleCode, VeteraninHousehold, OwnCellularphone, OwnMotorcycle, OwnRV, OwnSwimmingpool, OnlinePurchaser & Region.
So there are 15 independent variables. Now a regression model can be built using the random data ppoints from the complete data set. As there are so many data missing from the table, so we randomly choose 1382 rows for which we have the data of all the variables.
The output is as shown below -
The R-sqaure value is 0.560678 for this regression. So, the model includes approximately 56% of the total variation in the data.
Generally we do not consider a model good if the R-Square value is less than 0.8 in case of multiple linear regression. So, the model is not so good to predict a future response. However, it is statistically significant model as suggested by the F test.
| SUMMARY OUTPUT | ||||||||
| Regression Statistics | ||||||||
| Multiple R | 0.748784 | |||||||
| R Square | 0.560678 | |||||||
| Adjusted R Square | 0.555854 | |||||||
| Standard Error | 0.956185 | |||||||
| Observations | 1382 | |||||||
| ANOVA | ||||||||
| df | SS | MS | F | Significance F | ||||
| Regression | 15 | 1593.914 | 106.2609 | 116.2225 | 3.6E-231 | |||
| Residual | 1366 | 1248.919 | 0.914289 | |||||
| Total | 1381 | 2842.833 | ||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
| Intercept | 9.482744 | 0.151299 | 62.67553 | 0 | 9.18594 | 9.779547 | 9.18594 | 9.779547 |
| Age | -0.0032 | 0.002119 | -1.51239 | 0.130666 | -0.00736 | 0.000952 | -0.00736 | 0.000952 |
| WealthScore | 0.012154 | 0.000315 | 38.53238 | 1.8E-220 | 0.011535 | 0.012772 | 0.011535 | 0.012772 |
| NumberOfChildren | -0.10758 | 0.026334 | -4.08508 | 4.66E-05 | -0.15923 | -0.05592 | -0.15923 | -0.05592 |
| MailResponder | -0.09561 | 0.13096 | -0.73011 | 0.46545 | -0.35252 | 0.161289 | -0.35252 | 0.161289 |
| MailBuyer | 0.115098 | 0.129953 | 0.885691 | 0.37594 | -0.13983 | 0.370027 | -0.13983 | 0.370027 |
| MailDonor | -0.02382 | 0.079637 | -0.2991 | 0.764912 | -0.18004 | 0.132404 | -0.18004 | 0.132404 |
| TruckOwnerCode | 0.002577 | 0.066166 | 0.038945 | 0.96894 | -0.12722 | 0.132374 | -0.12722 | 0.132374 |
| NewVehicleCode | 0.091007 | 0.071051 | 1.280871 | 0.200456 | -0.04837 | 0.230389 | -0.04837 | 0.230389 |
| VeteraninHousehold | -0.03736 | 0.070603 | -0.52912 | 0.596805 | -0.17586 | 0.101145 | -0.17586 | 0.101145 |
| OwnCellularphone | 0.111527 | 0.055318 | 2.016089 | 0.043986 | 0.003009 | 0.220045 | 0.003009 | 0.220045 |
| OwnMotorcycle | -0.09508 | 0.098686 | -0.96348 | 0.335479 | -0.28867 | 0.098511 | -0.28867 | 0.098511 |
| OwnRV | -0.11803 | 0.080901 | -1.45899 | 0.144799 | -0.27674 | 0.04067 | -0.27674 | 0.04067 |
| OwnSwimmingpool | -0.17367 | 0.07139 | -2.43275 | 0.015112 | -0.31372 | -0.03363 | -0.31372 | -0.03363 |
| OnlinePurchaser | 0.011567 | 0.055741 | 0.207523 | 0.835633 | -0.09778 | 0.120914 | -0.09778 | 0.120914 |
| Region | 0.023278 | 0.009926 | 2.345121 | 0.019163 | 0.003806 | 0.04275 | 0.003806 | 0.04275 |
