pandas Project: Make a Gradebook With Python & pandas

Loading the Roster File

Now you’re ready to load the data, beginning with the roster:

HERE = Path(__file__).parent
DATA_FOLDER = HERE / "data"

roster = pd.read_csv(
    DATA_FOLDER / "roster.csv",
    converters={"NetID": str.lower, "Email Address": str.lower},
    usecols=["Section", "Email Address", "NetID"],
    index_col="NetID",
)

In this code, you create two constants, HERE and DATA_FOLDER, to keep track of the location of the currently executing file as well as the folder where the data is stored. These constants use the pathlib module to make it easy to refer to different folders.

Then you read the roster file using pd.read_csv(). To help process the data later, you set an index using index_col and include only the useful columns with usecols.

To make sure you can compare strings later, you also pass the converters argument to convert columns to lowercase. This will simplify the string comparisons you’ll do later on.

You can see some of the data in the roster DataFrame below:

These are the first four rows from roster, and they match the rows from the roster table you looked at in the previous section. However, the NetID and Email Address columns have both been converted to lowercase strings because you passed str.lower to converters for those two columns. You’ve also omitted the Name and ID columns.

Loading the Homework and Exam File

Next, you can load the homework and exam grades CSV file. Remember that this file includes first and last names and the SID column in addition to all the grades. You want to ignore the columns with the submission times:

hw_exam_grades = pd.read_csv(
    DATA_FOLDER / "hw_exam_grades.csv",
    converters={"SID": str.lower},
    usecols=lambda x: "Submission" not in x,
    index_col="SID",
)

In this code, you again use the converters argument to convert the data in the SID and Email Address columns to lowercase. Although the data in these columns appear to be lowercase on first inspection, the best practice is to make sure that everything is consistent. You also need to specify SID as the index column to match the roster DataFrame.

In this CSV file, there are a number of columns containing assignment submission times that you won’t use in any further analysis. However, there are so many other columns that you want to keep that it would be tedious to list all of them explicitly.

To get around this, usecols also accepts functions that are called with one argument, the column name. If the function returns True, then the column is included. Otherwise, the column is excluded. With the lambda function you pass here, if the string "Submission" appears in the column name, then the column will be excluded.

Here’s a sample of the hw_exam_grades DataFrame to give you a sense of what the data looks like after it’s been loaded:

These are the rows for the example students in the homework and exam grades CSV file you saw in the previous section. Notice that the missing data for Traci Joyce (SID txj12345) in the Homework 1 column was read as a nan, or Not a Number, value. You’ll see how to handle this kind of data in a later section. The ellipses (...) indicate columns of data that aren’t shown in the sample here but are loaded from the real data.

Loading the Quiz Files

Last, you need to load the data from the quizzes. There are five quizzes that you need to read, and the most useful form of this data is a single DataFrame rather than five separate DataFrames. The final data format will look like this:

This DataFrame has the Email column as the index, and each quiz is in a separate column. Notice that the quizzes are out of order, but you’ll see when you calculate the final grades that the order doesn’t matter. You can use this code to load the quiz files:

quiz_grades = pd.DataFrame()
for file_path in DATA_FOLDER.glob("quiz_*_grades.csv"):
    quiz_name = " ".join(file_path.stem.title().split("_")[:2])
    quiz = pd.read_csv(
        file_path,
        converters={"Email": str.lower},
        index_col=["Email"],
        usecols=["Email", "Grade"],
    ).rename(columns={"Grade": quiz_name})
    quiz_grades = pd.concat([quiz_grades, quiz], axis=1)

In this code, you create an empty DataFrame called quiz_grades. You need the empty DataFrame for the same reason that you need to create an empty list before using list.append().

You use Path.glob() to find all the quiz CSV files and load them with pandas, making sure to convert the email addresses to lowercase. You also set the index column for each quiz to the students’ email addresses, which pd.concat() uses to align data for each student.

Notice that you pass axis=1 to pd.concat(). This causes pandas to concatenate columns rather than rows, adding each new quiz into a new column in the combined DataFrame.

Finally, you use DataFrame.rename() to change the name of the grade column from Grade to something specific to each quiz.

Merging the Roster and Homework Grades

In roster and hw_exam_grades, you have the NetID or SID column as a unique identifier for a given student. When merging or joining DataFrames in pandas, it’s most useful to have an index. You already saw how useful this was when you were loading the quiz files.

Remember that you passed the index_col argument to pd.read_csv() when you loaded the roster and the homework grades. Now you can merge these two DataFrames together:

final_data = pd.merge(
    roster, hw_exam_grades, left_index=True, right_index=True,
)

In this code, you use pd.merge() to combine the roster and hw_exam_grades DataFrames.

Here’s a sample of the merged DataFrame for the four example students:

Like you saw before, the ellipses indicate columns that aren’t shown in the sample here but are present in the actual DataFrame. The sample table shows that students with the same NetID or SID have been merged together, so their email addresses and Homework 1 grades match the tables you saw previously.

Merging the Quiz Grades

When you loaded the data for the quiz_grades, you used the email address as a unique identifier for each student. This is different from hw_exam_grades and roster, which used the NetID and SID, respectively.

To merge quiz_grades into final_data, you can use the index from quiz_grades and the Email Address column from final_data:

final_data = pd.merge(
    final_data, quiz_grades, left_on="Email Address", right_index=True
)

In this code, you use the left_on argument to pd.merge() to tell pandas to use the Email Address column in final_data in the merge. You also use right_index to tell pandas to use the index from quiz_grades in the merge.

Here’s a sample of the merged DataFrame showing the four example students:

Remember that ellipses mean that columns are missing in the sample table here but will be present in the merged DataFrame. You can double-check the previous tables to verify that the numbers are aligned for the correct students.

Filling in nan Values

Now all your data is merged into one DataFrame. Before you can move on to calculating the grades, you need to do one more bit of data cleaning.

You can see in the table above that Traci Joyce still has a nan value for her Homework 1 assignment. You can’t use nan values in calculations because, well, they’re not a number! You can use DataFrame.fillna() to assign a number to all the nan values in final_data:

final_data = final_data.fillna(0)

In this code, you use DataFrame.fillna() to fill all nan values in final_data with the value 0. This is an appropriate resolution because the nan value in Traci Joyce’s Homework 1 column indicates that the score is missing, meaning she probably didn’t hand in the assignment.

Here’s a sample of the modified DataFrame showing the four example students:

As you can see in this table, Traci Joyce’s Homework 1 score is now 0 instead of nan, but the grades for the other students haven’t changed.

Calculating the Exam Total Score

You’ll calculate grades for the exams first. Since each exam has a unique weight, you can calculate the total score for each exam individually. It makes the most sense to use a for loop, which you can see in this code:

n_exams = 3
for n in range(1, n_exams + 1):
    final_data[f"Exam {n} Score"] = (
        final_data[f"Exam {n}"] / final_data[f"Exam {n} - Max Points"]
    )

In this code, you set n_exams equal to 3 because you had three exams during the term. Then you loop through each exam to calculate the score by dividing the raw score by the max points for that exam.

Here’s a sample of the exam data for the four example students:

In this table, each student scored between 0.0 and 1.0 on each of the exams. At the end of your script, you’ll multiply these scores by the weight to determine the proportion of the final grade.

Calculating the Homework Scores

Next, you need to calculate the homework scores. The max points for each homework assignment varies from 50 to 100. This means that there are two ways to calculate the homework score:

1. By total score: Sum the raw scores and maximum points independently, then take the ratio.
2. By average score: Divide each raw score by its respective maximum points, then take the sum of these ratios and divide the total by the number of assignments.

The first method gives a higher score to students who performed consistently, while the second method favors students who did well on assignments that were worth more points. To help students, you’ll give them the maximum of these two scores.

Computing these scores will take a few steps:

1. Collect the columns with homework data.
2. Compute the total score.
3. Compute the average score.
4. Determine which score is larger and will be used in the final score calculation.

First, you need to collect all the columns with homework data. You can use DataFrame.filter() to do this:

homework_scores = final_data.filter(regex=r"^Homework \d\d?$", axis=1)
homework_max_points = final_data.filter(regex=r"^Homework \d\d? -", axis=1)

In this code, you use a regular expression (regex) to filter final_data. If a column name doesn’t match the regex, then the column won’t be included in the resulting DataFrame.

The other argument you pass to DataFrame.filter() is axis. Many methods of a DataFrame can operate either row-wise or column-wise, and you can switch between the two approaches using the axis argument. With the default argument axis=0, pandas would look for rows in the index that match the regex you passed. Since you want to find all the columns that match the regex instead, you pass axis=1.

Now that you’ve collected the columns you need from the DataFrame, you can do the calculations with them. First, you sum the two values independently and then divide them to compute the total homework score:

sum_of_hw_scores = homework_scores.sum(axis=1)
sum_of_hw_max = homework_max_points.sum(axis=1)
final_data["Total Homework"] = sum_of_hw_scores / sum_of_hw_max

In this code, you use DataFrame.sum() and pass the axis argument. By default, .sum() will add up the values for all the rows in each column. However, you want the sum of all the columns for each row because each row represents one student. The axis=1 argument tells pandas to do just that.

Then you assign a new column in final_data called Total Homework to the ratio of the two sums.

Here’s a sample of the calculation results for the four example students:

In this table, you can see the sum of the homework scores, the sum of the max scores, and the total homework score for each student.

The other calculation method is to divide each homework score by its maximum score, add up these values, and divide the total by the number of assignments. To do this, you could use a for loop and go through each column. However, pandas allows you to be more efficient because it will match column and index labels and perform mathematical operations only on matching labels.

To make this work, you need to change the column names for homework_max_points to match the names in homework_scores. You can do this using DataFrame.set_axis():

hw_max_renamed = homework_max_points.set_axis(homework_scores.columns, axis=1)

In this code, you create a new DataFrame, hw_max_renamed, and you set the columns axis to have the same names as the columns in homework_scores. Now you can use this DataFrame for more calculations:

average_hw_scores = (homework_scores / hw_max_renamed).sum(axis=1)

In this code, you calculate the average_hw_scores by dividing each homework score by its respective maximum points. Then you add the ratios together for all the homework assignments in each row with DataFrame.sum() and the argument axis=1.

Since the maximum value on each individual assignment is 1.0, the maximum value that this sum could take would equal the total number of homework assignments. However, you need a number that’s scaled from 0 to 1 to factor into the final grade.

This means you need to divide average_hw_scores by the number of assignments, which you can do with this code:

final_data["Average Homework"] = average_hw_scores / homework_scores.shape[1]

In this code, you use DataFrame.shape to get the number of assignments from homework_scores. Like a NumPy array, DataFrame.shape returns a tuple of (n_rows, n_columns). Taking the second value from the tuple gives you the number of columns in homework_scores, which is equal to the number of assignments.

Then you assign the result of the division to a new column in final_data called Average Homework.

Here’s a sample calculation result for the four example students:

In this table, notice that the Sum of Average Homework Scores can vary from 0 to 10, but the Average Homework column varies from 0 to 1. The second column will be used to compare to Total Homework next.

Now that you have your two homework scores calculated, you can take the maximum value to be used in the final grade calculation:

final_data["Homework Score"] = final_data[
    ["Total Homework", "Average Homework"]
].max(axis=1)

In this code, you select the two columns you just created, Total Homework and Average Homework, and assign the maximum value to a new column called Homework Score. Notice that you take the maximum for each student with axis=1.

Here’s a sample of the calculated results for the four example students:

In this table, you can compare the Total Homework, Average Homework, and final Homework Score columns. You can see that the Homework Score always reflects the larger of Total Homework or Average Homework.

Calculating the Quiz Score

Next, you need to calculate the quiz score. The quizzes also have different numbers of maximum points, so you need to do the same procedure you did for the homework. The only difference is that the maximum grade on each quiz isn’t specified in the quiz data tables, so you need to create a pandas Series to hold that information:

quiz_scores = final_data.filter(regex=r"^Quiz \d$", axis=1)
quiz_max_points = pd.Series(
    {"Quiz 1": 11, "Quiz 2": 15, "Quiz 3": 17, "Quiz 4": 14, "Quiz 5": 12}
)

sum_of_quiz_scores = quiz_scores.sum(axis=1)
sum_of_quiz_max = quiz_max_points.sum()
final_data["Total Quizzes"] = sum_of_quiz_scores / sum_of_quiz_max

average_quiz_scores = (quiz_scores / quiz_max_points).sum(axis=1)
final_data["Average Quizzes"] = average_quiz_scores / quiz_scores.shape[1]

final_data["Quiz Score"] = final_data[
    ["Total Quizzes", "Average Quizzes"]
].max(axis=1)

Most of this code is quite similar to the homework code from the last section. The main difference from the homework case is that you created a pandas Series for quiz_max_points using a dictionary as input. The keys of the dictionary become index labels and the dictionary values become the Series values.

Since the index labels in quiz_max_points have the same names as quiz_scores, you don’t need to use DataFrame.set_axis() for the quizzes. pandas also broadcasts the shape of a Series so that it matches the DataFrame.

Here’s a sample of the result of this calculation for the quizzes:

In this table, the Quiz Score is always the larger of Total Quizzes or Average Quizzes, as expected.

Calculating the Letter Grade

Now you’ve completed all the required calculations for the final grade. You have scores for the exams, homework, and quizzes that are all scaled between 0 and 1. Next, you need to multiply each score by its weighting to determine the final grade. Then you can map that value onto a scale for letter grades, A through F.

Similar to the maximum quiz scores, you’ll use a pandas Series to store the weightings. That way, you can multiply by the correct columns from final_data automatically. Create your weightings with this code:

weightings = pd.Series(
    {
        "Exam 1 Score": 0.05,
        "Exam 2 Score": 0.1,
        "Exam 3 Score": 0.15,
        "Quiz Score": 0.30,
        "Homework Score": 0.4,
    }
)

In this code, you give a weighting to each component of the class. As you saw earlier, Exam 1 is worth 5 percent, Exam 2 is worth 10 percent, Exam 3 is worth 15 percent, quizzes are worth 30 percent, and Homework is worth 40 percent of the overall grade.

Next, you can combine these percentages with the scores you calculated previously to determine the final score:

final_data["Final Score"] = (final_data[weightings.index] * weightings).sum(
    axis=1
)
final_data["Ceiling Score"] = np.ceil(final_data["Final Score"] * 100)

In this code, you select the columns of final_data that have the same names as the index in weightings. You need to do this because some of the other columns in final_data have type str, so pandas will raise a TypeError if you try to multiply weightings by all of final_data.

Next, you take the sum of these columns for each student with DataFrame.sum(axis=1) and you assign the result of this to a new column called Final Score. The value in this column for each student is a floating-point number between 0 and 1.

Finally, being the really nice teacher that you are, you’re going to round each student’s grade up. You multiply each student’s Final Score by 100 to put it on a scale from 0 to 100, then you use numpy.ceil() to round each score to the next highest integer. You assign this value to a new column called Ceiling Score.

Here’s a sample calculation result for these columns for the four example students:

The last thing to do is to map each student’s ceiling score onto a letter grade. At your school, you might use these letter grades:

• A: Score of 90 or higher
• B: Score between 80 and 90
• C: Score between 70 and 80
• D: Score between 60 and 70
• F: Score below 60

Since each letter grade has to map to a range of scores, you can’t easily use just a dictionary for the mapping. Fortunately, pandas has Series.map(), which allows you to apply an arbitrary function to the values in a Series. You could do something similar if you used a different grading scale than letter grades.

You can write an appropriate function this way:

grades = {
    90: "A",
    80: "B",
    70: "C",
    60: "D",
    0: "F",
}

def grade_mapping(value):
    for key, letter in grades.items():
        if value >= key:
            return letter

In this code, you create a dictionary that stores the mapping between the lower limit of each letter grade and the letter. Then you define grade_mapping(), which takes as an argument the value of a row from the ceiling score Series. You loop over the items in grades, comparing value to the key from the dictionary. If value is greater than key, then the student falls in that bracket and you return the appropriate letter grade.

With grade_mapping() defined, you can use Series.map() to find the letter grades:

letter_grades = final_data["Ceiling Score"].map(grade_mapping)
final_data["Final Grade"] = pd.Categorical(
    letter_grades, categories=grades.values(), ordered=True
)

In this code, you create a new Series called letter_grades by mapping grade_mapping() onto the Ceiling Score column from final_data. Since there are five choices for a letter grade, it makes sense for this to be a categorical data type. Once you’ve mapped the scores to letters, you can create a categorical column with the pandas Categorical class.

To create the categorical column, you pass the letter grades as well as two keyword arguments:

1. categories is passed the values from grades. The values in grades are the possible letter grades in the class.
2. ordered is passed True to tell pandas that the categories are ordered. This will help later if you want to sort this column.

The categorical column that you create is assigned to a new column in final_data called Final Grade.

Here are the final grades for the four example students:

Among the four example students, one person got a B and three people got Cs, matching their ceiling scores and the letter grade mapping you created.