Python读取多CSV文件合并异常：行列合并同一单元格解决方案

问题描述 #

我需要读取文件夹中的多个CSV文件并合并为一个文件，但运行代码后，原本应分离的行和列都被合并到了同一个单元格中，求解决方法。

我的代码 #

import csv
from pathlib import Path
import pandas as pd

csv_folder = Path('D:/Myfolder')

def read_csv_with_retry(file_path, encodings):
 for encoding in encodings:
    try:
        return pd.read_csv(file_path, encoding=encoding, 
low_memory=False, error_bad_lines=False)
    except UnicodeDecodeError as e:
        print(f"UnicodeDecodeError: {e}")
    except Exception as e:
        print(f"Error reading {file_path} with encoding {encoding}: {e}")
raise Exception(f"Unable to read {file_path} with any of the specified encodings")

encodings_to_try = ['utf-8', 'cp949', 'latin1']

for file in csv_folder.glob('*.csv'):
 try:
    df = read_csv_with_retry(file, encodings_to_try)
 except Exception as e:
    print(f"Error: {e}")
    continue

# Drop rows with missing values
df = df.dropna()

# Convert the entire DataFrame to string to handle mixed types
df = df.astype(str)

# Print some information about the data
print(f"Original file: {file}")
print(f"Number of rows before dropna: {len(df)}")
print("Sample of the data:")
print(df.head())

new_file_name = file.parent.joinpath(f"{file.stem}-edited.csv")

try:
    # Save the DataFrame with explicit encoding and line termination
    df.to_csv(new_file_name, index=None, encoding='utf-16', line_terminator='\n', quoting=csv.QUOTE_NONNUMERIC)
except Exception as e:
    print(f"Error saving {new_file_name}: {e}")

print("Processing complete.")

运行结果 #

所有数据被挤在同一列的单元格中，没有按照原有列结构正确分割。

预期结果 #

数据按CSV文件原有的列、行结构正常分离展示，最终合并为一个完整的CSV文件。

解决步骤 #

1. 指定正确的分隔符 #

CSV行列混乱大多是因为pandas默认用逗号分隔，但你的文件可能使用了制表符（\t）、分号（;）或其他分隔符。修改read_csv的sep参数：

• 已知分隔符时直接指定，比如制表符：

  return pd.read_csv(file_path, encoding=encoding, sep='\t', low_memory=False, error_bad_lines=False)
  

• 未知分隔符时，让pandas自动检测：

  return pd.read_csv(file_path, encoding=encoding, sep=None, engine='python', low_memory=False, error_bad_lines=False)
  

2. 确认文件编码准确性 #

用文本编辑器（如Notepad++）打开CSV文件，查看实际编码，将其加入encodings_to_try的优先位置，避免因编码不匹配导致解析错乱。

3. 修复多文件合并逻辑 #

你的代码目前仅逐个处理文件并单独保存，未实现合并。修改为批量合并的完整代码：

import csv
from pathlib import Path
import pandas as pd

csv_folder = Path('D:/Myfolder')

def read_csv_with_retry(file_path, encodings):
 for encoding in encodings:
    try:
        # 加入自动检测分隔符逻辑
        return pd.read_csv(file_path, encoding=encoding, sep=None, engine='python', low_memory=False, error_bad_lines=False)
    except UnicodeDecodeError as e:
        print(f"UnicodeDecodeError: {e}")
    except Exception as e:
        print(f"Error reading {file_path} with encoding {encoding}: {e}")
raise Exception(f"Unable to read {file_path} with any of the specified encodings")

encodings_to_try = ['utf-8', 'cp949', 'latin1']
# 初始化空DataFrame用于合并所有数据
merged_df = pd.DataFrame()

for file in csv_folder.glob('*.csv'):
 try:
    df = read_csv_with_retry(file, encodings_to_try)
 except Exception as e:
    print(f"Error: {e}")
    continue
 # 将当前文件数据追加到合并DataFrame
 merged_df = pd.concat([merged_df, df], ignore_index=True)

# 处理合并后的数据
merged_df = merged_df.dropna()
merged_df = merged_df.astype(str)

# 打印合并后的数据信息
print(f"Total rows after merge and dropna: {len(merged_df)}")
print("Sample of merged data:")
print(merged_df.head())

# 保存合并后的文件
merged_file_name = csv_folder.joinpath("merged_all.csv")
try:
    # 使用utf-8-sig编码，方便Excel识别
    merged_df.to_csv(merged_file_name, index=None, encoding='utf-8-sig', line_terminator='\n', quoting=csv.QUOTE_NONNUMERIC)
except Exception as e:
    print(f"Error saving {merged_file_name}: {e}")

print("Processing complete.")

4. 调整保存编码参数 #

原代码使用utf-16编码，部分软件（如Excel）打开时可能出现异常，换成utf-8-sig编码可让Excel直接正确识别分隔符和内容。

内容的提问来源于stack exchange，提问作者Yeeun Kim