Blog

Suppose we are working on merging individual data, including…

Suppose we are working on merging individual data, including name, ZIP code, gender, and age, with public data. Individual data is in columns A through F. In general, data collected from individuals is often subject to errors because humans self-report and record the information. Please answer the questions below using the attached data. Xlookup2_Cleaning.xlsx Round all numbers to the second decimal place. 1) There are clearly erroneous observations in the data. Use the information below to identify the erroneous observations and answer the questions. – Human age begins at 0 and generally does not exceed 125 years.- This data was collected from residents of Missouri. Missouri’s ZIP codes range from 63000 to 65999.- The genders identified in this data are Male and Female. Observations with values ​​that do not meet the above criteria are excluded from the data. Then, using the remaining observations, calculate the mean ‘Age’ [Age] and ‘Female’ ratios [Female] in percentages. 2) Use the dataset from above, with the observations with errors removed. Columns I through N are public data. Let’s assume there are no errors. Let’s say, using this, we found the RUCA1 (Column J) values corresponding to the county codes in column I and the median income (Column N) corresponding to the zip code in column K. (Let’s assume that if there are multiple zip codes and county codes, we use the first one that appears. Excel uses the first one by default when there are duplicates, so we don’t have to do anything additional for this.) Using those values, find the RUCA1 value of the county where the individual lives and the median income by zip code where the individual lives. Then, calculate ‘average income’ [averageincome] and ‘average RUCA1’ [averageRUCA1].