import pandas as pd
import numpy as np
print(pd.__version__)

rng = np.random.default_rng(seed=111)

first_names = ['Aiko', 'Ben', 'Camila', 'Dev', 'Ella', 'Finn', 'Greta', 'Hassan', 'Ivy', 'Jamal', 'Kira', 'Leo', 'Mira', 'Noah', 'Priya', 'Quinn', 'Rosa', 'Sam', 'Tara', 'Umar']
students = pd.DataFrame({
    'student_id': range(1, len(first_names) + 1),
    'name': first_names,
    'attendance_pct': np.clip(rng.normal(90, 8, size=len(first_names)), 55, 100).round(1)
})
students.head()

subjects = ['Math', 'Science', 'English', 'History']
score_rows = []

for _, student in students.iterrows():
    base_ability = rng.normal(75, 10)
    for subject in subjects:
        attendance_bonus = (student['attendance_pct'] - 90) * 0.3
        score = base_ability + attendance_bonus + rng.normal(0, 8)
        score = float(np.clip(score, 30, 100).round(1))
        score_rows.append([student['student_id'], subject, score])

scores = pd.DataFrame(score_rows, columns=['student_id', 'subject', 'score'])
print(scores.shape)

students.to_csv('students.csv', index=False)
scores.to_csv('student_scores.csv', index=False)
students = pd.read_csv('students.csv')
scores = pd.read_csv('student_scores.csv')
scores.head()

print('students:', students.shape)
print('scores:', scores.shape)
scores['subject'].value_counts()

scores['score'].describe().round(1)

gradebook = scores.pivot_table(values='score', index='student_id', columns='subject')
gradebook = gradebook.merge(students[['student_id', 'name']], on='student_id').set_index('name')
gradebook.round(1).head()

student_avg = scores.groupby('student_id')['score'].mean().round(1)
student_avg = student_avg.reset_index().merge(students[['student_id', 'name']], on='student_id')
student_avg = student_avg.sort_values('score', ascending=False)
student_avg.head()

def letter_grade(score):
    if score >= 90:
        return 'A'
    elif score >= 80:
        return 'B'
    elif score >= 70:
        return 'C'
    elif score >= 60:
        return 'D'
    else:
        return 'F'

student_avg['letter_grade'] = student_avg['score'].apply(letter_grade)
student_avg[['name', 'score', 'letter_grade']].head()

student_avg['letter_grade'].value_counts().reindex(['A', 'B', 'C', 'D', 'F'], fill_value=0)

scores['passed'] = scores['score'] >= 60
pass_rate_by_subject = scores.groupby('subject')['passed'].mean().mul(100).round(1).sort_values(ascending=False)
pass_rate_by_subject

subject_summary = scores.groupby('subject').agg(
    avg_score=('score', 'mean'),
    pass_rate_pct=('passed', lambda s: round(s.mean() * 100, 1)),
    lowest_score=('score', 'min')
).round(1).sort_values('avg_score', ascending=False)
subject_summary

performance = students.merge(student_avg[['student_id', 'score']], on='student_id')
performance = performance.rename(columns={'score': 'avg_score'})
correlation = performance[['attendance_pct', 'avg_score']].corr().iloc[0, 1]
print(f'Correlation between attendance and average score: {correlation:.2f}')

import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt

fig, ax = plt.subplots(figsize=(8, 5))
subject_summary['avg_score'].plot(kind='bar', ax=ax, color='seagreen', ylim=(0, 100))
ax.set_title('Average Score by Subject')
ax.set_ylabel('Average Score')
plt.xticks(rotation=0)
plt.tight_layout()
plt.savefig('avg_score_by_subject.png', dpi=150)
plt.close(fig)
print('Saved avg_score_by_subject.png')

fig, ax = plt.subplots(figsize=(8, 5))
ax.scatter(performance['attendance_pct'], performance['avg_score'], alpha=0.7, color='indigo')
ax.set_title('Attendance vs. Average Score')
ax.set_xlabel('Attendance (%)')
ax.set_ylabel('Average Score')
plt.tight_layout()
plt.savefig('attendance_vs_score.png', dpi=150)
plt.close(fig)
print('Saved attendance_vs_score.png')