如何基于聚合数据按组计算中位数？分州和县计算中位年龄

Hey, great question! Calculating median age from grouped age-population data (grouped by State and County) requires careful handling since we don’t have individual age records—we need to work with interval data, use cumulative populations to locate the median bracket, then interpolate for a precise value. Let me walk you through two practical approaches:

This is the most straightforward method for processing structured datasets like yours.

1. Prepare Your Data #

First, load your dataset into a Pandas DataFrame. For demonstration, I’ll expand your sample to include the 85+ age group:

import pandas as pd

# Sample dataset (replace with pd.read_csv("your_data.csv") for real data)
data = pd.DataFrame({
    'State': ['AL', 'AL', 'AL', 'AL', 'AK', 'AK', 'AK', 'AK'],
    'County': ['Alachua', 'Alachua', 'Alachua', 'Alachua', 'Baker', 'Baker', 'Baker', 'Baker'],
    'Age': ['0-5', '5-10', '10-15', '85+', '0-5', '5-10', '10-15', '85+'],
    'Population': [1043, 1543, 758, 200, 543, 788, 1200, 150]
})

2. Parse Age Intervals #

Extract lower and upper bounds for each age group. For the 85+ group, we’ll assume an upper bound (e.g., 95—adjust this based on your data’s definition):

def parse_age_bounds(age_str):
    if '+' in age_str:
        lower = int(age_str.split('+')[0])
        upper = lower + 10  # Treat 85+ as 85-95; tweak if needed
    else:
        lower, upper = map(int, age_str.split('-'))
    return lower, upper

# Add bounds columns to the DataFrame
data[['Age_Lower', 'Age_Upper']] = data['Age'].apply(lambda x: pd.Series(parse_age_bounds(x)))

3. Calculate Median Age per State-County Group #

Define a function to compute median age for each group, using cumulative population to find the median bracket and linear interpolation for precision:

def compute_median_age(group):
    # Sort the group by age lower bound to ensure correct order
    sorted_group = group.sort_values('Age_Lower')
    total_pop = sorted_group['Population'].sum()
    median_threshold = total_pop / 2
    
    # Calculate cumulative population
    sorted_group['Cumulative_Pop'] = sorted_group['Population'].cumsum()
    
    # Find the first interval where cumulative population crosses the median threshold
    median_interval = sorted_group[sorted_group['Cumulative_Pop'] >= median_threshold].iloc[0]
    # Get cumulative population before this interval (0 if it's the first interval)
    prev_cumulative = sorted_group[sorted_group['Cumulative_Pop'] < median_threshold]['Cumulative_Pop'].max() if not sorted_group[sorted_group['Cumulative_Pop'] < median_threshold].empty else 0
    
    # Linear interpolation to get exact median age
    median_age = (
        median_interval['Age_Lower'] 
        + (median_threshold - prev_cumulative) / median_interval['Population'] 
        * (median_interval['Age_Upper'] - median_interval['Age_Lower'])
    )
    return median_age

# Apply the function to each State-County group
median_age_results = data.groupby(['State', 'County']).apply(compute_median_age).reset_index(name='Median_Age')
print(median_age_results)

This will output a DataFrame with the median age for each State-County pair.

If you’re working directly with a database, you can use window functions to replicate the same logic:

WITH age_bounds AS (
    SELECT 
        State,
        County,
        Population,
        -- Parse lower/upper age bounds
        CASE WHEN Age LIKE '%+' THEN CAST(SPLIT_PART(Age, '+', 1) AS INT) 
             ELSE CAST(SPLIT_PART(Age, '-', 1) AS INT) END AS Age_Lower,
        CASE WHEN Age LIKE '%+' THEN CAST(SPLIT_PART(Age, '+', 1) AS INT) + 10 
             ELSE CAST(SPLIT_PART(Age, '-', 2) AS INT) END AS Age_Upper
    FROM your_dataset_table
),
cumulative_populations AS (
    SELECT 
        *,
        SUM(Population) OVER (PARTITION BY State, County ORDER BY Age_Lower) AS Cumulative_Pop,
        SUM(Population) OVER (PARTITION BY State, County) AS Total_Pop
    FROM age_bounds
),
median_intervals AS (
    SELECT 
        State,
        County,
        Age_Lower,
        Age_Upper,
        Population,
        Cumulative_Pop,
        Total_Pop,
        LAG(Cumulative_Pop) OVER (PARTITION BY State, County ORDER BY Age_Lower) AS Prev_Cumulative
    FROM cumulative_populations
    WHERE Cumulative_Pop >= Total_Pop / 2
    QUALIFY ROW_NUMBER() OVER (PARTITION BY State, County ORDER BY Age_Lower) = 1
)
SELECT 
    State,
    County,
    Age_Lower + (Total_Pop/2 - COALESCE(Prev_Cumulative, 0)) / Population * (Age_Upper - Age_Lower) AS Median_Age
FROM median_intervals;

Key Notes #

• For the 85+ group, adjust the upper bound based on how your data defines this category (e.g., use 85 if you want to treat it as a single point, or 100 if you prefer a wider interval).
• Linear interpolation is the standard method for calculating median age from grouped data—it gives a more accurate estimate than just using the interval midpoint.

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