优化Python中Pandas处理大型CSV文件的性能

import os
import pandas as pd

# 假设文件路径和目标列表已定义
# desktop = os.path.join(os.path.join(os.environ["USERPROFILE"]), "Desktop")
# dados = pd.read_csv(desktop + '\test-1000-rows.csv')

# 模拟一个DataFrame和目标项列表
data = {'column_a': ['apple_pie', 'banana_split', 'cherry_tart', 'grape_juice', 'apple_cider'],
        'column_b': ['orange_soda', 'lemonade', 'pineapple_juice', 'mango_shake', 'berry_smoothie'],
        'column_c': [10, 20, 30, 40, 50]}
dados = pd.DataFrame(data)

target_items = ['apple', 'juice', 'berry'] # 假设这是要检查的列表
result_list = []

# 低效的行迭代操作
for i, row in dados.iterrows():
    # 遍历目标项列表
    for item in target_items:
        # 检查 column_a 或 column_b 是否包含该项
        if item in str(row['column_a']) or item in str(row['column_b']):
            result_list.append(row['column_c'])
            break # 找到一个匹配项后跳出内层循环

print(f"通过iterrows获取的结果: {result_list}")

import pandas as pd
import re

# 模拟数据 (与上面相同)
data = {'column_a': ['apple_pie', 'banana_split', 'cherry_tart', 'grape_juice', 'apple_cider'],
        'column_b': ['orange_soda', 'lemonade', 'pineapple_juice', 'mango_shake', 'berry_smoothie'],
        'column_c': [10, 20, 30, 40, 50]}
dados = pd.DataFrame(data)

target_items = ['apple', 'juice', 'berry']

# 1. 构建正则表达式模式
# 使用 re.escape 确保目标项中的特殊字符被正确转义
pattern = '|'.join([re.escape(item) for item in target_items])

# 2. 应用向量化操作进行条件检查
# 检查 'column_a' 是否包含任一目标项
condition_a = dados['column_a'].str.contains(pattern, na=False, regex=True)
# 检查 'column_b' 是否包含任一目标项
condition_b = dados['column_b'].str.contains(pattern, na=False, regex=True)

# 组合两个条件 (逻辑或操作)
final_condition = condition_a | condition_b

# 3. 使用布尔索引筛选并获取 'column_c' 的值
result_list_vectorized = dados.loc[final_condition, 'column_c'].tolist()

print(f"通过向量化操作获取的结果: {result_list_vectorized}")

import pandas as pd
import re

file_path = 'your_large_file.csv' # 替换为你的大型CSV文件路径
chunk_size = 100000 # 每次读取10万行数据，可根据内存情况调整

target_items = ['apple', 'juice', 'berry']
pattern = '|'.join([re.escape(item) for item in target_items])

all_results = [] # 用于存储所有数据块处理后的结果

# 使用 chunksize 分块读取CSV文件
for chunk in pd.read_csv(file_path, chunksize=chunk_size):
    # 对每个数据块应用之前定义的向量化操作
    condition_a = chunk['column_a'].str.contains(pattern, na=False, regex=True)
    condition_b = chunk['column_b'].str.contains(pattern, na=False, regex=True)
    final_condition = condition_a | condition_b

    chunk_results = chunk.loc[final_condition, 'column_c'].tolist()
    all_results.extend(chunk_results) # 将当前数据块的结果添加到总结果列表中

print(f"通过分块处理获取的总结果数量: {len(all_results)}")
# print(all_results) # 如果结果列表不大，可以打印查看