Python基础学习（二），读取文件数据，清洗数据，然后重新保存

第二阶段：进阶语法（数据采集与清洗场景）
目标：掌握列表推导、异常处理、文件读写、常用内置库。

场景：市场部需要收集竞品价格，但官网数据夹杂着空值、单位（“元/件”）和emoji符号。

具体例子：使用requests抓取HTML（模拟），用正则表达式提取数字，用try/except处理缺失值，最后清洗成纯浮点数保存。

练习：编写清洗管道，自动将“¥1,299.00”转换为1299.00。

 

业务涉及内容：

1）读取csv文件；

2）对读取的数据列表，进行特殊字符清洗；具体的特殊字符要根据文件，尽可能全面，防止缺少造成清洗不完整；

3）清洗后的数据，进行写操作，存为新文件；

4）统计清洗数据的情况；哪些有问题，具体数量等；

 

测试使用附件：https://files.cnblogs.com/files/violetlength/raw_prices.zip?t=1787886036&download=true

 

具体代码实现：myclean.py

import os
import csv
import re
import emoji

def clean_pricestr(mystr):
    """清理价格里面的特殊数据，转为数字
    主要处理：
        空值或者None
        货币符号：¥、￥、$
        千位分割符：,
        单位后缀：元/件 元
        emoji符号
        括号等特殊字符
    入参：价格字符串，
    
    返回：float或者None(清理失败时，直接返回None)
    """
    if mystr is None:
        return None
        
    if not isinstance(mystr,str):
        return None
        
    cleaned = mystr.strip()
    
    if not cleaned or cleaned == "()":
        return None
    
    cleaned = cleaned.replace('¥','')
    cleaned = cleaned.replace('￥','')
    cleaned = cleaned.replace('$','')
    cleaned = cleaned.replace('€','')
    cleaned = cleaned.replace('£','')

    cleaned = cleaned.replace('元/件','')
    cleaned = cleaned.replace('元件','')
    cleaned = cleaned.replace('元','')
    cleaned = cleaned.replace('件','')

    #处理emoji符号，需要py 3.10+版本，可以引用该第三方插件 import emoji
    cleaned = emoji.replace_emoji(cleaned, replace='')
    
    cleaned = cleaned.replace(',','')
    
    #相关的特殊字符替换后，再做一次首尾空格格式化
    cleaned = cleaned.strip()

    #再次判断格式化之后，是否有有效值，如果没有，则返回None
    if not cleaned:
        return None
    
    try:
        price = float(cleaned)
        if price < 0:  #处理负数情况
            return None
        return price
    except ValueError:
        return None

def read_csv(file):
    """读原始文件数据
    入参：csv文件
    返回：行对象列表
    """
    records = []
    try:
        with open(file,"r",encoding="gbk") as ff:
            reader = csv.DictReader(ff)
            for row in reader:
                records.append(row)
    except FileNotFoundError:
        print(f"错误：未找到文件{file}")
        return []
    except UnicodeDecodeError: #此时说明文档是UTF-8格式，再用UTF-8处理读取一下
        try:
            with open(file,"r",encoding="utf-8") as ff:
                reader = csv.DictReader(ff)
                for row in reader:
                    records.append(row)
        except Exception as e:
            print(f"读文件失败：{e}")
            return []
    return records

def clean_price_list(records):
    """清洗价格列表数据
    入参：数据列表
    返回：清洗后的列表
    """
    cleaned_records = []
    for record in records:
        product_name = record.get("产品名称","未知名称")
        original_price = record.get("价格","")
        source = record.get("来源","未知来源")
        cleaned_price = clean_pricestr(original_price)
        
        cleaned_records.append({
            "产品名称":product_name,
            "原始价格":original_price,
            "清洗后价格":cleaned_price,
            "来源":source,
            "是否有效":cleaned_price is not None
        })
        
    return cleaned_records

def save_cleaned_data(records, output_file):
    """把清洗后的数据，存到另外一个文档中
    入参：清理的数据，要存储的文档
    """
    try:
        with open(output_file,"w",encoding="utf-8", newline="") as ff:
            fieldnames=["产品名称", "原始价格", "清洗后价格", "来源", "是否有效"]
            writer=csv.DictWriter(ff,fieldnames= fieldnames)
            writer.writeheader()
            writer.writerows(records)
        print(f"数据保存地址：{output_file}")
    except Exception as e:
        print(f"保存出差：{e}")

def show_statistics(cleaned_records):
    """显示统计数据
    入参：清理的数据记录
    """
    total = len(cleaned_records)
    valid = sum(1 for r in cleaned_records if r["是否有效"])
    invalid = total - valid
    
    valid_prices = [r["清洗后价格"] for r in cleaned_records if r["是否有效"]]
    
    print(f"总数：{total}")
    print(f"合规数：{valid}")
    print(f"不合规数：{invalid}")
    
    if valid_prices:
        print(f"价格范围：{min(valid_prices):.2f} -- {max(valid_prices):.2f}")
        print(f"平均价格：{sum(valid_prices)/len(valid_prices):.2f}")

def main():
    pathdir = os.path.dirname(os.path.abspath(__file__))
    inputfile = os.path.join(pathdir,"raw_prices.csv")
    outputfile = os.path.join(pathdir,"cleaned_prices.csv")

    print("1、读原始文件数据")
    rawrecords = read_csv(inputfile)
    if not rawrecords:
        print("没有读取到数据")
        reuturn
    
    print(f"读取到{len(rawrecords)}条数据")

    print("2、清洗数据")
    cleanedrecords = clean_price_list(rawrecords)
    
    invalidcount = sum(1 for r in cleanedrecords if not r["是否有效"])
    print(f"清洗完成。{invalidcount}条数据失败")

    print("3、显示清洗结果")
    for i, record in enumerate(cleanedrecords[:5]):
        status = "✓" if record["是否有效"] else "✗"
        price_display = f"{record['清洗后价格']:.2f}" if record["是否有效"] else "无效"
        original_display = record["原始价格"].encode('ascii','replace').decode('ascii')
        print(f"{status}{record['产品名称']}：{original_display} -> {price_display}")

    if len(cleanedrecords) > 5:
        print(f"...还有{len(cleanedrecords) - 5}条")

    print("4、保存清洗结果")
    save_cleaned_data(cleanedrecords, outputfile)

    print("5、显示统计信息")
    show_statistics(cleanedrecords)

    print("\n6. 清洗规则说明：")
    print("  - 移除货币符号：YEN, DOLLAR, EURO, POUND")
    print("  - 移除千位分隔符：,")
    print("  - 移除单位后缀：YUAN/PIECE, YUAN, PIECE")
    print("  - 移除emoji符号")
    print("  - 处理空值和异常值")

if __name__ == "__main__":
    main()