Asennetaan pandas ja openpyxl:

pip install pandas openpyxl

Alla olevassa esimerkissä opiskelijoiden nimet ja ryhmät ovat Excel-tiedossossa students.xlsx sarakkeissa Student name ja Group name. Ohjelma luo tiedoston assessment_sheet.xlsx, jossa on sarakkeet opiskelijoiden tietoja sekä tehtävien pisteytystä varten. Tehtävien nimet ja niiden maksimipisteet on annettu ohjelmassa sanakirjana assessment_criteria.

import pandas as pd

# Load the student names and group names from the provided Excel sheet
students_df = pd.read_excel('students.xlsx', engine='openpyxl')

# Define the assessment criteria and their maximum points
assessment_criteria = {
    'Task 1': 10,
    'Task 2': 15,
    'Task 3': 20,
    'Task 4': 25,
    'Individual Task 1': 5,
    'Individual Task 2': 10
}

# Create a new DataFrame for the assessment sheet
assessment_df = pd.DataFrame(columns=['Student Name', 'Group Name'] + list(assessment_criteria.keys()) + ['Total Points'])

# Populate the assessment DataFrame with student names and group names
rows = []
for index, row in students_df.iterrows():
    student_name = row['Student Name']
    group_name = row['Group Name']
    rows.append({'Student Name': student_name, 'Group Name': group_name})

assessment_df = pd.concat([assessment_df, pd.DataFrame(rows)], ignore_index=True)

# Save the assessment DataFrame to a new Excel file
assessment_df.to_excel('assessment_sheet.xlsx', index=False, engine='openpyxl')

print("The assessment sheet has been created and saved as 'assessment_sheet.xlsx'.")