College Applications. Bickley Academy is a competitive...

College Applications. Bickley Academy is a competitive private college preparatory high school. Bickley's administration is now considering applications from the three local junior high schools: Montgomery, Cleeghorn, and Benson. Bickley's admissions department has compiled a file named bickley that contains the first name, last name, email address, and grade point average (GPA) for applicants from these schools. Bickley's Director of Admissions, Joan Bisdubic, would like to look at the relationship between these students' GPAs and the schools they are attending. The school each student is attending can be identified by the domain name in the student's email address. To ensure each applicant receives full and proper consideration, Joan Bisdubic would also like to ensure that the file has the GPA for every applicant. LO 4, 5 a. Create a new set of data from the data in the file bickley that includes the applicant's first name, last name, email username (the portion of the email address that precedes the @ symbol), email domain (the portion of the email address that follows the @ symbol), and GPA. Save your file under the name bickleyapp.xlsx. b. Using the file bickleyapp that you created in part (a), determine if any records have missing values for the GPA field. Provide a list of the names of the applicants for whom the value of the field GPA is missing.

College Applications. Bickley Academy is a competitive private college preparatory high school. Bickley's administration is now considering applications from the three local junior high schools: Montgomery, Cleeghorn, and Benson. Bickley's admissions department has compiled a file named bickley that contains the first name, last name, email address, and grade point average (GPA) for applicants from these schools.
Bickley's Director of Admissions, Joan Bisdubic, would like to look at the relationship between these students' GPAs and the schools they are attending. The school each student is attending can be identified by the domain name in the student's email address. To ensure each applicant receives full and proper consideration, Joan Bisdubic would also like to ensure that the file has the GPA for every applicant. LO 4, 5
a. Create a new set of data from the data in the file bickley that includes the applicant's first name, last name, email username (the portion of the email address that precedes the @ symbol), email domain (the portion of the email address that follows the @ symbol), and GPA. Save your file under the name bickleyapp.xlsx.
b. Using the file bickleyapp that you created in part (a), determine if any records have missing values for the GPA field. Provide a list of the names of the applicants for whom the value of the field GPA is missing.

Key Concepts

Data Extraction and Transformation

This concept involves manipulating datasets to isolate, reformat, and derive new information from existing variables. In the context of the question, it entails taking the original file and creating a new dataset that includes additional fields derived from the email address, like the username and domain. This is a common task in data wrangling where data from raw sources is restructured to make it more useful for analysis.

Email Address Parsing

Email address parsing is the process of splitting an email string into its constituent components, typically the username and the domain. This is important when the domain is used as a proxy for another attribute, such as the applicant's affiliation with a particular school, allowing for insights based on the grouping by email domain.

Missing Data Identification

Identifying missing data is a fundamental aspect of data cleaning and validation, ensuring that the dataset is complete and reliable before further analysis. This involves checking for fields, such as GPA in this case, that may have null or undefined entries, and then listing the records that need attention to maintain the integrity of the analysis.

Spreadsheet Data Management

Spreadsheet data management refers to the systematic organization and handling of data within spreadsheet software, including the manipulation, storage, and retrieval of data. Creating, editing, and analyzing files, like the bickleyapp.xlsx file, often involves these skills to ensure data is formatted correctly and is ready for further statistical analysis or reporting.

Please give Ace some feedback