View the step-by-step solution to:

Do you smoke cigars? -H Mean Standard Error Median Mode Standard Deviation Sample Variance Kurtosis Skewness Range Minimum Maximum Sum Count Do you

Lab 11: Clean Your Data



In this lab you will clean your group project data, and create descriptive statistics, conduct data analysis, and create charts or graphs illustrating your results. This is an individual lab assignment, not a group assignment.



1. Open the data you will use for your group project in Excel. Examine the codebook for your data. Label each column with a name that describes the data provided in the column. Delete columns that will not be used for your analysis. Keep any variables you think you may possibly use, since it is much easier to drop them later than to add them back later.



2. Calculate descriptive statistics for your data. Look at each variable’s output, and notice where the maximum and minimum values are unreasonable. Use this and your codebook to recognize which variables need cleaning…in the next step.



3. Look at the data and see if any of the numeric columns have non-numeric data (below the top label row). Sort the data to get all non-numeric values at the top or bottom of the columns. Delete rows with non-numeric data, or change the non-numeric values to corresponding numbers, if they exist.



4. Look to see if there are any rows without data or with missing observations. Use the codebook to determine values that represent N/A (not applicable) or Does Not Know. Sort the data by each suspect variable, being sure to sort the whole data set every time, so each row still corresponds to the correct observation. Delete rows with missing information IF the variables they correspond to are variables you plan to use in your regression.



5. Transform the raw data into variables you can analyze. Create new columns with titles that reflect the data contained in them. Create dummy variables for all categorical variables using IF statements. The form for an if statement is =IF(A1 = 2, value if true, value if false), so if A1 = 2, then set the cell equal to ‘value if true’, otherwise set the cell equal to ‘value if false’. So if you want to change this cell to 1 if A1= 2, and set it equal to 0 otherwise, your if statement should be =IF(A1=2,1,0).



6. Copy and paste all the data you want to use for your analysis to a new worksheet. When you paste the data, choose Paste Special, Values, so that the formulas do not depend on the original data.



7. In your new worksheet, arrange your data so that all the X variables are lined up next to one another, and all the possible Y variables are aligned in the same section of your Excel spreadsheet. You can do this by inserting columns and copying and pasting columns to the correct place. You can delete source columns now, if you don’t need them.



8. Create Descriptive Statistics for all of your variables. In the Data tab, use Data Analysis—Descriptive Statistics, and highlight all the numeric rows to create your Input range.



9. Examine the descriptive statistics to make sure they make sense. Are there any obvious recording errors, or outliers. For example, is average age 20,000? If so, go back to your original data and see what is causing this problem.



10. Create a graph or chart that illustrates an interesting result from your analysis.



11. Perform one regression that you believe makes sense. Be sure to think about what your dependent variable should be, and what regressors you want to include.



Do you smoke cigars? -H Do you smoke cigars on the avg.? -H Age when First smoked -H Ever smoked cigars? -H Do you usual y smoke cigars-H #children in FU Parents who smoked b4 Age 17 Chronic Condition Employment Status Working now Only temp looking for work/unemploy Retired Permanently disabled Salary Amt Spanish Descent Head Religious Nationality Chronic Condition W smoke cigars w Cigars per day w Age when first smoked-W Ever smoked Cigars-w Race of Head White Black American Indian Asian Mean 4.0290581162 Mean 2.2005260521 Mean 3.3950400802 Mean 2.8292835671 Mean 3.0908066132 Mean 0.7075400802 Mean 2.9149549098 Mean 4.3547094188Mean 1.6753507014 Mean 0.7686623 Mean 0.00501002 Mean 0.056989 Mean 0.120991984 Mean 0.0483467 Mean 9908.2989867235 Mean 0.0541082164 Mean 3.3068637275 Mean 4.371743487 Mean 2.2799348697 Mean 2.3049849699 Mean 0.3346693387 Mean 0.7675350701 Mean 1.7591433 Mean 1.4058116 Mean 0.627505 Mean 0.3527054 Mean 0.0062625 Mean 0.0135271 Standard Error 0.0191940825 Standard Error 0.0579332994 Standard Error 0.0763706249 Standard Error 0.0256282106 Standard Error 0.0842955317 Standard Error 0.0107358212 Standard Error 0.0203718189 Standard Error 0.0164672869 Standard Error 0.0143301838 Standard Error 0.0047196 Standard Error 0.0007902164 Standard Error 0.0025946 Standard Error 0.0036499898 Standard Error 0.00240071 Standard Error 260.2158284974 Standard Error 0.0029868935 Standard Error 0.0467651968 Standard Error 0.0588091154 Standard Error 0.0270109234 Standard Error 0.0270929955 Standard Error 0.0182756019 Standard Error 0.0379126503 Standard Error 0.025633 Standard Error 0.0064762 Standard Error 0.0054111 Standard Error 0.0053478 Standard Error 0.0008829 Standard Er 0.0012929 Median 5 Median 0 Median 0 Median 5 Median 0 Median 0 Median 2 Median 5 Median 1 Median 1 Median 0 Median 0 Median 0 Median 0 Median 0 Median 0 Median 3 Median 3 Median 1 Median 1 Median 0 Median 0 Median 0Median 1 Median 1 Median 0 Median 0 Median 0 Mode 5 Mode 0 Mode 0 Mode 5 Mode 0 Mode 0 Mode 5 Mode 5 Mode 1 Mode 1 Mode 0 Mode 0 Mode 0 Mode 0 Mode 0 Mode 0 Mode 0 Mode 0 Mode 0 Mode 0 Mode 0 Mode 0 Mode 0Mode 1 Mode 1 Mode 0 Mode 0 Mode 0 Standard Deviation 1.7150532954 Standard Deviation 5.1765275188 Standard Deviation 6.8239621316 Standard Deviation 2.2899634374 Standard Deviation 7.5320781713 Standard Deviation 0.9592803165 Standard Deviation 1.8202878549 Standard Deviation 1.4714053018Standard Deviation 1.2804482286 Standard Deviation 0.4217142 Standard Deviation 0.0706083861 Standard Deviation 0.2318361 Standard Deviation 0.3261383851 Standard Deviation 0.2145112 Standard Deviation 23251.125193549 Standard Deviation 0.2668885892 Standard Deviation 4.178621465 Standard Deviation 5.2547845134 Standard Deviation 2.4135132938 Standard Deviation 2.420846703 Standard Deviation 1.632984092 Standard Deviation 3.3876178267 Standard Deviation 2.2903879 Standard Deviation 0.578669 Standard Deviation 0.4834995 Standard Deviation 0.4778419 Standard Deviation 0.0788929 Standard De0.1155238 Sample Variance 2.9414078062 Sample Variance 26.7964371528 Sample Variance 46.5664591741 Sample Variance 5.2439325444 Sample Variance 56.732201579 Sample Variance 0.9202187257 Sample Variance 3.3134478747 Sample Variance 2.1650335621 Sample Variance 1.639547666 Sample Variance 0.1778428 Sample Variance 0.0049855442 Sample Variance 0.053748 Sample Variance 0.1063662462 Sample Variance 0.0460151 Sample Variance 540614822.766091 Sample Variance 0.071229519 Sample Variance 17.4608773478 Sample Variance 27.6127602819 Sample Variance 5.8250464194 Sample Variance 5.8604987596 Sample Variance 2.6666370446 Sample Variance 11.4759545397 Sample Variance 5.2458766 Sample Variance 0.3348579 Sample Variance 0.2337718 Sample Variance 0.2283329 Sample Variance 0.0062241 Sample Var 0.0133457 Kurtosis -0.5593452713 Kurtosis 4.8758956293 Kurtosis 0.7363643192 Kurtosis -1.7916237041 Kurtosis 7.8164954281 Kurtosis -0.1856441916 Kurtosis -1.8286964773 Kurtosis 1.3927348676 Kurtosis 0.6386393745 Kurtosis -0.375837 Kurtosis 194.7277139923 Kurtosis 12.616337 Kurtosis 3.405540168 Kurtosis 15.745351 Kurtosis 4.779499658 Kurtosis 30.7207335943 Kurtosis 1.9354000764 Kurtosis -0.1663985538 Kurtosis -1.9143692327 Kurtosis -1.9266359901 Kurtosis 25.9863456802 Kurtosis 16.5022727272 Kurtosis -1.485603 Kurtosis 3.3727751 Kurtosis -1.722114 Kurtosis -1.620144 Kurtosis 154.78397 Kurtosis 68.983583 Skewness -1.200331166 Skewness 2.436428566 Skewness 1.5948164236 Skewness -0.0826543412 Skewness 2.797774365 Skewness 1.0495136707 Skewness 0.1966178037 Skewness -1.8418431119 Skewness 1.5212638689 Skewness -1.274463 Skewness 14.0242267012 Skewness 3.8227185 Skewness 2.3247983008 Skewness 4.2120549 Skewness 2.3790087568 Skewness 5.394310861 Skewness 1.5678888592 Skewness 1.1241832086 Skewness 0.2167651885 Skewness 0.1935621474 Skewness 5.1570554706 Skewness 4.2643055128 Skewness 0.6759054 Skewness 1.5156974 Skewness -0.527558 Skewness 0.6166537 Skewness 12.519792 Skewness 8.42415 Range 4 Range 20 Range 23 Range 9 Range 40 Range 3 Range 4 Range 4 Range 4 Range 1 Range 1 Range 1 Range 1 Range 1 Range 115000 Range 2 Range 18 Range 15 Range 5 Range 5 Range 10 Range 18 Range 5 Range 3 Range 1 Range 1 Range 1 Range 1 Minimum 1 Minimum 0 Minimum 0 Minimum 0 Minimum 0 Minimum 0 Minimum 1 Minimum 1 Minimum 1 Minimum 0 Minimum 0 Minimum 0 Minimum 0 Minimum 0 Minimum 0 Minimum 0 Minimum 0 Minimum 0 Minimum 0 Minimum 0 Minimum 0 Minimum 0 Minimum 0Minimum 1 Minimum 0 Minimum 0 Minimum 0 Minimum 0 Maximum 5 Maximum 20 Maximum 23 Maximum 9 Maximum 40 Maximum 3 Maximum 5 Maximum 5 Maximum 5 Maximum 1 Maximum 1 Maximum 1 Maximum 1 Maximum 1 Maximum 115000 Maximum 2 Maximum 18 Maximum 15 Maximum 5 Maximum 5 Maximum 10 Maximum 18 Maximum 5 Maximum 4 Maximum 1 Maximum 1 Maximum 1 Maximum 1 Sum 32168 Sum 17569 Sum 27106 Sum 22589 Sum 24677 Sum 5649 Sum 23273 Sum 34768Sum 13376 Sum 6137 Sum 40 Sum 455 Sum 966 Sum 386 Sum 79107859.11 Sum 432 Sum 26402 Sum 34904 Sum 18203 Sum 18403 Sum 2672 Sum 6128 Sum 14045 Sum 11224 Sum 5010 Sum 2816 Sum 50 Sum 108 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984 Count 7984
Background image of page 1
Background image of page 2
Show entire document

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question