# 增強型數(shù)據(jù)預(yù)處理流程
unique_values = (
    df[COLUMN_NAME]
    .str.strip()  # 去除首尾空格
    .str.title()  # 規(guī)范大小寫格式
    .replace(r'^\s*$', pd.NA, regex=True)  # 空字符轉(zhuǎn)NA
    .dropna()  # 清除缺失值
    .drop_duplicates()  # 精準去重
    .sort_values()  # 結(jié)果排序
)

FILE_PATH = 'ai_tools_dataset.xlsx'
SHEET_NAME = 'AI Tools' 
COLUMN_NAME = '技術(shù)領(lǐng)域'  # 支持多語言字段

# 創(chuàng)建虛擬環(huán)境
python -m venv data_cleaning_env
source data_cleaning_env/bin/activate

# 安裝依賴（帶版本控制）
pip install pandas==2.1.0 openpyxl==3.1.2

try:
    df = pd.read_excel(...)
except FileNotFoundError as e:
    logging.error(f"文件路徑錯誤: {e}")
    sys.exit(1001)
except KeyError as e:
    logging.warning(f"字段缺失: {e}")
    sys.exit(1002)

# 結(jié)果文件行數(shù)驗證
wc -l output.txt

# 與原始數(shù)據(jù)對比
awk -F, '{print $2}' input.csv | sort | uniq | wc -l

# 分塊處理大數(shù)據(jù)文件
chunk_size = 100000
unique_set = set()

for chunk in pd.read_csv(FILE_PATH, 
                        chunksize=chunk_size, 
                        usecols=[COLUMN_NAME]):
    unique_set.update(chunk[COLUMN_NAME].dropna().unique())

from rapidfuzz import fuzz

def fuzzy_merge(str1, str2):
    return fuzz.token_set_ratio(str1, str2) > 85

# 可合并："AI Assistant"和"AI Assistants"

analysis_report = {
    "原始數(shù)據(jù)量": len(df),
    "缺失值數(shù)量": df[COLUMN_NAME].isna().sum(),
    "重復(fù)值比例": 1 - len(unique_values)/len(df),
    "數(shù)據(jù)類型分布": df[COLUMN_NAME].apply(type).value_counts()
}

with pd.ExcelWriter('分析報告.xlsx') as writer:
    unique_values.to_excel(writer, sheet_name='去重結(jié)果')
    pd.DataFrame(analysis_report).to_excel(writer, 
                                        sheet_name='統(tǒng)計概覽')