import re
import numpy as np

target_numeric_col = '需要转数字的文本列' # 示例：'获赞'
target_text_col = '需要提取中文的列' # 示例：'收货人'

# 1. 清洗包含前缀符号的数值字段
prefix_patterns = ['.', 'I ', '■ ', '一 ', '_', '. ']
def clean_numeric_with_prefix(value):
    val_str = str(value).strip()
    if val_str in ['None', 'nan', '', 'nan']:
        return np.nan
    for prefix in prefix_patterns:
        if val_str.startswith(prefix):
            val_str = val_str[len(prefix):].strip()
            break
    if val_str == '':
        return np.nan
    try:
        return int(val_str)
    except ValueError:
        return np.nan

# 2. 清洗文本字段，仅保留 Unicode 范围内的中文字符（\u4e00-\u9fff）
def clean_chinese_name(name):
    if pd.isna(name):
        return name
    s = str(name)
    chinese_chars = re.findall(r'[\u4e00-\u9fff]', s)
    cleaned = ''.join(chinese_chars)
    return cleaned if cleaned else ''

if target_numeric_col in df.columns:
    df[f'{target_numeric_col}_清洗后'] = df[target_numeric_col].apply(clean_numeric_with_prefix)
    
if target_text_col in df.columns:
    df[f'{target_text_col}_清洗后'] = df[target_text_col].apply(clean_chinese_name)

output_path = '/mnt/data/标准化清洗结果.xlsx'

# 保存清洗结果
df.to_excel(output_path, index=False, engine='openpyxl')
print(f'清洗结果已保存到: {output_path}')

# 生成可下载链接
print(f'[下载清洗结果表](sandbox:{output_path})')

# 内存清理
if 'df' in locals():
    del df
    gc.collect()