Age字段值转换规则咨询：单值、负值及≥15数值统一处理实现

转换规则梳理 #

• 源值为3/4：保留原值输出
• 源值为任意负数：统一输出-1
• 源值≥15：统一输出15+
• 注：5~14之间的正数值无特殊限制，直接保留原值即可

常用场景实现示例 #

1. SQL实现 #

假设表名为user_info，若Age为数值类型，代码如下：

SELECT 
  CASE
    WHEN Age < 0 THEN -1
    WHEN Age IN (3,4) THEN Age
    WHEN Age >=15 THEN '15+'
    ELSE Age
  END AS converted_age
FROM user_info;

若Age为字符串类型（包含原有15+取值），先做类型转换再判断：

SELECT 
  CASE
    WHEN Age = '15+' THEN '15+'
    WHEN CAST(Age AS SIGNED) <0 THEN -1
    WHEN CAST(Age AS SIGNED) IN (3,4) THEN Age
    WHEN CAST(Age AS SIGNED) >=15 THEN '15+'
    ELSE Age
  END AS converted_age
FROM user_info;

2. Python Pandas实现 #

import pandas as pd

def convert_age(age_val):
    # 处理原有15+字符串场景
    if age_val == '15+':
        return '15+'
    # 转换为数值判断
    try:
        age_num = int(age_val)
    except:
        # 异常值可按业务需求调整处理逻辑，此处默认返回原值
        return age_val
    if age_num < 0:
        return -1
    elif age_num in (3,4):
        return age_num
    elif age_num >=15:
        return '15+'
    else:
        return age_num

# 应用到DataFrame的Age列
df['converted_age'] = df['Age'].apply(convert_age)

3. Excel实现 #

假设Age值存储在A列，B列存储转换结果，数值类型场景公式如下：

=IF(A1<0,-1,IF(OR(A1=3,A1=4),A1,IF(A1>=15,"15+",A1)))

若A列存在字符串格式的15+，调整公式为：

=IF(A1="15+","15+",IF(VALUE(A1)<0,-1,IF(OR(VALUE(A1)=3,VALUE(A1)=4),A1,IF(VALUE(A1)>=15,"15+",A1))))

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