请求实现多列数值型年龄列分箱为类别并汇总计数

下面提供两种常用工具的实现方法，满足将18-90+的单年度年龄数据分箱为指定组别并累计计数的需求：

Pandas（Python）实现 #

假设你的数据存储在一个DataFrame中，包含age（年龄，可能为数字或含90+的字符串）和count（对应年龄的汇总数）两列。

步骤1：预处理年龄列（若包含90+字符串） #

先将非数字格式的年龄转为整数，方便后续分箱：

import pandas as pd

# 示例数据
data = {
    'age': ['18', '19', '29', '30', '49', '50', '64', '65', '79', '80', '90+'],
    'count': [100, 120, 90, 150, 200, 180, 160, 140, 110, 80, 50]
}
df = pd.DataFrame(data)

# 转换年龄为整数：替换'90+'为90，再转类型
df['age_num'] = df['age'].str.replace('+', '', regex=False).astype(int)

步骤2：定义分箱规则并分组求和 #

设定分箱区间和对应组名，用pd.cut完成分组后汇总计数：

# 定义分箱边界（左闭右开，17确保18被包含，inf覆盖80及以上所有年龄）
bins = [17, 29, 49, 64, 79, float('inf')]
# 指定目标组名
labels = [
    'young_adults_18_29',
    'younger_working_age_30_49',
    'older_working_age_50_64',
    'retirement_age_65_79',
    'older_adults_80+'
]

# 生成年龄组列
df['age_group'] = pd.cut(df['age_num'], bins=bins, labels=labels, right=False)

# 按年龄组汇总计数
result = df.groupby('age_group')['count'].sum().reset_index()
print(result)

输出结果会直接展示每个年龄组的累计总数。

SQL实现 #

如果数据存储在数据库中，可通过CASE WHEN语句分组求和：

基础版（年龄为数字类型） #

SELECT
  CASE
    WHEN age BETWEEN 18 AND 29 THEN 'young_adults_18_29'
    WHEN age BETWEEN 30 AND 49 THEN 'younger_working_age_30_49'
    WHEN age BETWEEN 50 AND 64 THEN 'older_working_age_50_64'
    WHEN age BETWEEN 65 AND 79 THEN 'retirement_age_65_79'
    WHEN age >= 80 THEN 'older_adults_80+'
  END AS age_group,
  SUM(count) AS total_count
FROM your_table
GROUP BY age_group
ORDER BY age_group;

适配含90+的字符串年龄 #

若年龄列是带+的字符串，先转换为数字再分组（以MySQL为例）：

SELECT
  CASE
    WHEN age_num BETWEEN 18 AND 29 THEN 'young_adults_18_29'
    WHEN age_num BETWEEN 30 AND 49 THEN 'younger_working_age_30_49'
    WHEN age_num BETWEEN 50 AND 64 THEN 'older_working_age_50_64'
    WHEN age_num BETWEEN 65 AND 79 THEN 'retirement_age_65_79'
    WHEN age_num >= 80 THEN 'older_adults_80+'
  END AS age_group,
  SUM(count) AS total_count
FROM (
  SELECT
    CAST(REPLACE(age, '+', '') AS UNSIGNED) AS age_num,
    count
  FROM your_table
) AS temp
GROUP BY age_group
ORDER BY age_group;

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