如何高效统计学生除首次考试外的累计考试次数

Hey there! Your initial idea of flagging first attempts and summing the rest is totally on the right track—way better than manual ID counting. Let's turn that into clean, efficient code using Python's Pandas (the go-to tool for this kind of data wrangling). Since Student IDs are unique (even if names aren't), we'll anchor everything to that column.

Step-by-Step Approach #

First, we need to correctly identify the first exam date for each student. Then we have two straightforward paths to get the count you need:

1. Calculate total attempts minus 1 (since the first attempt doesn't count) – this is the simplest method.
2. Flag non-first attempts as 1, then sum those flags per ID – this matches your original thought and adds transparency.

Code Implementation #

Let's start with your sample data:

import pandas as pd

# Your dataset
data = {
    "Student ID": ["01", "01", "01", "12", "12", "43", "43", "43", "43", "06", "06"],
    "Student Name": ["Christian", "Christian", "Christian", "Laura", "Laura", "Alex", "Alex", "Alex", "Alex", "Sarah", "Sarah"],
    "Date Taken": ["2009-09-24", "2015-06-16", "2014-05-14", "2017-01-04", "2009-05-12", "2014-05-07", "2009-05-01", "2009-08-24", "2013-04-29", "2018-05-03", "2015-06-24"]
}

df = pd.DataFrame(data)

# Convert "Date Taken" to datetime format so we can sort dates correctly
df["Date Taken"] = pd.to_datetime(df["Date Taken"])

Method 1: Total Attempts Minus 1 (Fastest)

Skip extra columns and get the count directly:

# Group by Student ID, count total attempts, subtract 1 for the first attempt
non_first_counts = df.groupby("Student ID").size().sub(1).reset_index(name="Non-First Exam Count")

print(non_first_counts)

Output:

Student ID  Non-First Exam Count
0         01                     2
1         06                     1
2         12                     1
3         43                     3

Method 2: Flag Non-First Attempts Then Sum (Explicit)

If you want to keep a record of which attempts are non-first, use this:

# Sort each student's records by exam date
df_sorted = df.sort_values(["Student ID", "Date Taken"])

# Flag first attempt as 0, all others as 1
df_sorted["Is Non-First"] = df_sorted.groupby("Student ID").cumcount().apply(lambda x: 1 if x > 0 else 0)

# Sum the flags per Student ID (include name for clarity)
non_first_counts_flagged = df_sorted.groupby(["Student ID", "Student Name"])["Is Non-First"].sum().reset_index(name="Non-First Exam Count")

print(non_first_counts_flagged)

Output:

Student ID Student Name  Non-First Exam Count
0         01    Christian                     2
1         06        Sarah                     1
2         12         Laura                     1
3         43         Alex                     3

Key Notes #

• We use Student ID exclusively for grouping, so duplicate names won't cause mix-ups.
• Converting Date Taken to datetime is critical – string dates can sort incorrectly in edge cases (like "2023-12-01" vs "2023-01-01").
• Both methods give identical results – pick whichever fits your workflow (Method 1 is quicker, Method 2 is more transparent).

内容的提问来源于stack exchange，提问作者Sarah H