基于Pandas实现单列数据排序/均值计算及研讨会评分排名需求

Hey there, let's walk through these two Pandas tasks step by step—they're super common in data wrangling, so I'll use concrete examples to make it easy to follow.

First up, let's cover basic sorting and mean calculations for single columns. I'll use a sample DataFrame to demonstrate both numeric and text data scenarios.

Sample Data Setup #

import pandas as pd

# Create a test DataFrame with numeric and text columns
data = {
    'test_scores': [82, 95, 78, 95, 88],
    'student_names': ['Zoe', 'alex', 'Mia', 'Ben', 'chloe']
}
df = pd.DataFrame(data)

Numeric Column Operations #

• Sorting: Use sort_values() to order the column. You can toggle ascending/descending with the ascending parameter.

  # Sort scores from lowest to highest
  sorted_asc = df.sort_values(by='test_scores')
  # Sort scores from highest to lowest
  sorted_desc = df.sort_values(by='test_scores', ascending=False)
  

• Mean Calculation: Grab the column directly and call mean()—simple as that.

  average_score = df['test_scores'].mean()
  print(f"Average test score: {average_score:.2f}")
  

Text Column Sorting #

Text sorting defaults to case-sensitive alphabetical order, but you can adjust for case insensitivity if needed:

# Default case-sensitive sort (uppercase letters come first)
sorted_names_case_sensitive = df.sort_values(by='student_names')
# Case-insensitive sort (treats 'Alex' and 'alex' the same)
sorted_names_case_insensitive = df.sort_values(by='student_names', key=lambda x: x.str.lower())

Let's tackle the student workshop review data task. The goal is to calculate average responses per student, sort by that average, and output a ranking table with college, level, and formatted rank (like "1st", "2nd").

Sample Data Setup #

First, let's simulate the review data you described (I'll include the level and college columns since they're needed for the final output):

review_data = {
    'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
    'question': ['Q1', 'Q2', 'Q1', 'Q3', 'Q2'],
    'response': [4.5, 3.8, 4.2, 4.7, 4.0],
    'level': ['graduate', 'undergraduate', 'graduate', 'undergraduate', 'graduate'],
    'college': ['science', 'education', 'science', 'engineering', 'education']
}
review_df = pd.DataFrame(review_data)

Step 1: Calculate Average Responses per Student #

We'll group by name to get average responses, and keep each student's level and college (assuming each student only belongs to one level/college):

# Group by name, compute average response, and retain level/college
student_avg = review_df.groupby('name').agg(
    avg_response=('response', 'mean'),
    level=('level', 'first'),
    college=('college', 'first')
).reset_index()

Step 2: Sort and Generate Rankings #

Next, sort by average response (descending) and add a rank column. We'll also format the rank to use suffixes like "st", "nd", "rd":

# Sort by average response (highest first)
sorted_students = student_avg.sort_values(by='avg_response', ascending=False).reset_index(drop=True)

# Add numeric rank (starts at 1)
sorted_students['rank'] = sorted_students.index + 1

# Function to add rank suffixes
def format_rank(rank):
    if rank % 10 == 1 and rank != 11:
        return f"{rank}st"
    elif rank % 10 == 2 and rank != 12:
        return f"{rank}nd"
    elif rank % 10 == 3 and rank != 13:
        return f"{rank}rd"
    else:
        return f"{rank}th"

# Apply formatting to rank
sorted_students['rank_formatted'] = sorted_students['rank'].apply(format_rank)

Step 3: Output the Final Ranking Table #

Finally, rearrange the columns to match your desired output and print:

# Create the final ranking table
ranking_table = sorted_students[['college', 'level', 'rank_formatted', 'name', 'avg_response']]
print(ranking_table.to_string(index=False))

Example Output #

college          level rank_formatted     name  avg_response
engineering undergraduate           1st    David           4.7
     science      graduate           2nd    Alice           4.5
     science      graduate           3rd  Charlie           4.2
    education      graduate           4th      Eve           4.0
    education undergraduate           5th      Bob           3.8

内容的提问来源于stack exchange，提问作者Matt Wood