The Merged Marksheet That Reported 71.6 Instead of 68.2
A Class 12 AI intern must produce the overall average marks for a school cluster of 4,800 students. Two files are supplied: students.csv with 5,120 rows (roll_no, name, class_section) and marks.csv with 4,800 rows (roll_no, total_marks). The office's own figure is 68.2 and it counts an 'AB' (absent) as zero.
The intern loads both files, merges them, and reports 71.6. The merged table has 380 rows where total_marks is NaN, and 44 roll numbers from students.csv never match anything in marks.csv. Inspection shows roll_no in marks.csv sometimes carries trailing spaces and lower case ('a-1042 ' versus 'A-1042'), and total_marks was read as object because 212 cells hold the string 'AB'. students.csv also contains 320 duplicated roll numbers, created when students changed sections. The intern had run df['total_marks'].mean() after the merge.
The report is due tomorrow. A teacher suggests deleting the 380 NaN rows 'so the merge looks clean'.
1. Explain, step by step, why the intern's 71.6 is wrong. Separate the join problems from the dtype problems, and say in which direction each one pushes the average.
Read the worked answer
There are three independent faults, and they push the number in different directions.
First, the join keys do not match. Trailing spaces and lower case mean 'a-1042 ' and 'A-1042' are different strings to pandas, so those rows fail to join and produce NaN in total_marks. If the merge used the default inner join, those students silently vanish from the table.
Second, students.csv has 320 duplicated roll numbers. Merging one-to-many duplicates those students' marks, so students who changed section are counted more than once and get extra weight in the mean. If those students happen to be stronger, the mean rises.
Third, total_marks is object dtype because of the 212 'AB' strings. pandas cannot average strings, so any numeric operation either fails or, after coercion, silently drops those rows. df.mean() skips NaN by default, and absent students are typically the weakest. Removing them lifts the average. That is the dominant reason 71.6 sits above the office's 68.2, which counts AB as zero and keeps all 4,800 students.
The honest conclusion: 71.6 is an average of an unknown, self-selected subset, not of the cohort.
2. Write the pandas code that produces a defensible per-student table and a corrected average. Assume pandas is imported as pd and both files are in the current directory.
Read the worked answer
students = pd.read_csv('students.csv', dtype=str)
marks = pd.read_csv('marks.csv', dtype=str)
# 1. normalise keys on both sides
for df in (students, marks):
df['roll_no'] = df['roll_no'].str.strip().str.upper()
# 2. de-duplicate students, keeping one row per roll_no
students = students.drop_duplicates(subset='roll_no', keep='first')
# 3. make marks numeric; 'AB' becomes NaN but is remembered
marks['is_absent'] = marks['total_marks'].str.strip().str.upper().eq('AB')
marks['total_marks'] = pd.to_numeric(
marks['total_marks'].str.strip().replace('AB', None), errors='coerce')
# 4. left join so no student disappears silently
merged = students.merge(marks, on='roll_no', how='left', indicator=True)
print(merged['_merge'].value_counts()) # who matched, who did not
# 5. apply the school's stated rule: AB = 0
merged['score'] = merged['total_marks'].fillna(0)
print(merged['score'].mean())
print(merged['score'].notna().sum()) # how many students counted
The reasoning is the point: normalise before joining, de-duplicate the dimension table, convert with an explicit absent flag, use a left join plus indicator so unmatched students are visible rather than lost, and apply the office's rule openly instead of letting fillna() decide quietly.
3. The teacher wants the 380 NaN rows deleted. Decide what should happen to the 44 unmatched students and the 212 'AB' entries, and state exactly what you would write in the report's methodology note.
Read the worked answer
Deleting the NaN rows is the one option that must be rejected, because it produces a number that cannot be defended and hides two different problems behind a single clean table.
The 44 unmatched students are not a data-type problem; they are a records problem. Either the student left the school, or marks.csv is incomplete, or their roll number changed. Do not invent a zero for them. Check the register, and if it is genuine missing data, exclude them and say so with the count.
The 212 'AB' entries are a policy question, not a coding question. The office's rule is AB = 0, so compute 68.2-comparable figures under that rule, and also report the mean over students with numeric marks only, with its n, so the effect of the rule is visible. Two numbers with stated definitions beat one number with a hidden assumption.
The methodology note should say: files used and date pulled, key normalisation applied, 320 duplicate student records collapsed, 212 absences scored as zero per office policy, 44 unmatched records investigated and their status, and the resulting n = X of 4,800. Anything a reader would need to reproduce 68.2 belongs there.