7a. Gathering and preparing metadata
Often, you may have different spreadsheets with different data points (e.g., a separate spreadsheet for each course you are collecting data from or separate spreadsheets for instructors and students). The purpose of the script described here is to output a spreadsheet that combines data from two spreadsheets based on matching information from the spreadsheets. The matching information can be different depending on your context (e.g., student names or IDs, instructor names or IDs, course, etc.).
The use case we present here is based on combining information from two spreadsheets. The two spreadsheets contain different data points but they have one shared column “Name” that has students' names, which will be used as an anchor to combine the two spreadsheets. Having a column linking the two files is an essential element, and you want to have that as well for your own use case.
Spreadsheet 1 (Registrar’s office data):
This spreadsheet may come in the form of survey data or data from the Registrar’s or an assessment office. This spreadsheet includes information about all students from a given course such as birth country, college, major, student academic level, and their proficiency test scores (e.g., TOEFL).
Spreadsheet 2 (Spreadsheet with only consented students):
While the spreadsheet from the Registrar’s office contains information about all students from a given course, you might have a separate spreadsheet with only the students who consented. This spreadsheet might also provide additional data such as instructor information or any additional information about the students (e.g., alternate names or information about whether students are heritage Spanish speakers) or the course (e.g., mode of instruction or course length, etc). In the example we provide we have information about instructors (i.e., instructors’ names in the tab names). Each tab contains information about consented students for that specific instructor.
Now that you have the necessary files and have made necessary adjustments to the spreadsheets, you are ready to run the script.