使用Python重排CSV数据时遇ValueError报错求助

原始CSV数据 #

1:100011159-T-G,CDD3-597,G,G
1:10002775-GA,CDD3-597,G,G
1:100122796-C-T,CDD3-597,T,T
1:100152282-CAAA-T,CDD3-597,C,C
1:100011159-T-G,CDD3-598,G,G
1:100152282-CAAA-T,CDD3-598,C,C  

期望输出格式 #

编写的Python代码 #

import pandas as pd


input_file = "trail_berry.csv"
output_file = "trail_output_result.csv"

# Read the CSV file without header
df = pd.read_csv(input_file, header=None)
print(df[0].str.split(',', n=2, expand=True))
# Extract SNP Name, ID, and Alleles from the data
df[['SNP_Name', 'ID', 'Alleles']] = df[0].str.split(',', n=-1, expand=True)

# Create a new DataFrame with unique SNP_Name values as columns
result_df = pd.DataFrame(columns=df['SNP_Name'].unique(), dtype=str)

# Populate the new DataFrame with ID and Alleles data
for _, row in df.iterrows():
    result_df.at[row['ID'], row['SNP_Name']] = row['Alleles']

# Reset the index
result_df.reset_index(inplace=True)
result_df.rename(columns={'index': 'ID'}, inplace=True)

# Fill NaN values with an appropriate representation (e.g., 'NULL' or '')
result_df = result_df.fillna('NULL')

# Save the result to a new CSV file
result_df.to_csv(output_file, index=False)

# Print a message indicating that the file has been saved
print("Result has been saved to {}".format(output_file))

错误信息 #

Traceback (most recent call last):
  File "berry_trail.py", line 11, in <module>
    df[['SNP_Name', 'ID', 'Alleles']] = df[0].str.split(',', n=-1, expand=True)
  File "/nas/longleaf/home/svennam/.local/lib/python3.5/site-packages/pandas/core/frame.py", line 3367, in __setitem__
    self._setitem_array(key, value)
  File "/nas/longleaf/home/svennam/.local/lib/python3.5/site-packages/pandas/core/frame.py", line 3389, in _setitem_array
    raise ValueError('Columns must be same length as key')

问题原因与解决方法 #

错误原因 #

pd.read_csv默认会按逗号分割每行数据，原始每行有4个字段（SNP名称、ID、等位基因1、等位基因2），因此读取后DataFrame会有4列，而非整行内容放在df[0]一列中。此时执行df[0].str.split(',')只会拆分第一列（SNP名称），拆分结果的列数与要赋值的['SNP_Name', 'ID', 'Alleles']三列不匹配，从而触发ValueError: Columns must be same length as key错误。

修正后的代码 #

import pandas as pd

input_file = "trail_berry.csv"
output_file = "trail_output_result.csv"

# 直接读取CSV并指定列名，自动按逗号分割字段
df = pd.read_csv(input_file, header=None, names=['SNP_Name', 'ID', 'Allele1', 'Allele2'])
# 将两个等位基因合并为一个字符串
df['Alleles'] = df['Allele1'] + df['Allele2']

# 使用pivot方法快速重塑表格，替代循环赋值
result_df = df.pivot(index='ID', columns='SNP_Name', values='Alleles').reset_index()
# 移除列索引的名称
result_df.columns.name = None

# 填充空值为空字符串（可根据需求改为'NULL'）
result_df = result_df.fillna('')

# 保存结果到CSV
result_df.to_csv(output_file, index=False)
print("Result has been saved to {}".format(output_file))

代码说明 #

1. 读取CSV时直接指定列名，避免后续手动拆分的错误；
2. 合并两个等位基因列成目标格式的字符串；
3. 使用pivot方法高效完成表格重塑，比循环iterrows更简洁高效；
4. 填充空值为期望的格式，最终保存结果。

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