基于RawData列按指定格式计算Rank、Percentile及Quintile

Hey there! Let's walk through how to calculate Rank, Percentile, and Quintile from your RawData column, along with formatting your sample data properly. Here's a clear breakdown:

First, let's organize your sample data into a clean markdown table for better readability:

Key Calculation Logic #

Let's break down how each metric is derived from the RawData column:

1. Rank

• Core behavior: Assigns a position where the highest RawData values get the lowest rank (matches your sample, where 1.20— the highest value— has a rank of 87, lower than 0.58's 897).
• How to calculate:
  • Ignore NA values when ranking.
  • Sort RawData in descending order, then assign ranks starting at 1. Your sample uses unique ranks, so it assumes no duplicate values in the full dataset; if there are ties, you can use average/min/max rank depending on your needs.

2. Rank Percentile

• Core behavior: Shows the percentage of values that are lower than the current RawData value. For example, 1.20 has a percentile of 3, meaning only 3% of values are lower than it.
• Formula: (Number of values lower than current value / Total non-NA values) * 100
  • Round the result to the nearest whole number as seen in your sample.

3. Quintile

• Core behavior: Splits sorted RawData into 5 equal groups (each representing 20% of the data). Quintile 1 is the top 20% (highest values), Quintile 5 is the bottom 20% (lowest values)— which aligns with your sample.
• Steps:
  1. Sort RawData in descending order.
  2. Divide the sorted list into 5 roughly equal parts (adjust group sizes slightly if the total count isn't divisible by 5).
  3. Assign Quintile 1 to the top 20%, Quintile 2 to the next 20%, and so on down to Quintile 5 for the bottom 20%.

Example Implementation (Python) #

If you want to automate this with code, here's a pandas snippet that replicates your sample logic:

import pandas as pd

# Load your sample data
data = {
    'RawData': [1.20, 0.58, 0.16, 1.04, None, 0.32, 0.18, 0.22, 0.89, 0.46]
}
df = pd.DataFrame(data)

# Calculate Rank (descending order, skip NAs, unique ranks for ties)
df['Rank'] = df['RawData'].rank(ascending=False, method='first').astype(int)

# Calculate Rank Percentile (rounded to whole number)
total_non_na = df['RawData'].count()
df['Rank Percentile'] = ((total_non_na - df['Rank']) / total_non_na * 100).round().astype(int)

# Calculate Quintile (descending, 5 groups)
df['Quintile'] = pd.qcut(df['RawData'], q=5, labels=[1,2,3,4,5], duplicates='drop')

# Format NA values and Rank for readability
df['Quintile'] = df['Quintile'].fillna('na')
df['Rank'] = df['Rank'].apply(lambda x: f"{x:,}" if pd.notna(x) else '-')

print(df)

This will output a DataFrame that matches your sample format. Note that exact rank numbers might shift slightly if your full dataset has ties, but the core logic stays consistent.

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