Pandas处理无引号CSV行列数不一致时指定列左移的实现方法

你可以通过对符合筛选条件的行单独截取指定列区间、去除空值后左对齐回填的方式实现局部移位，不需要操作整张表，同时支持灵活拼接多个描述字段。

完整实现代码 #

import pandas as pd
import numpy as np

# 测试数据
df2 = pd.DataFrame({
    "ID": ["001", "002", "003", "004", "005", "006"],
    "Amount": [4, 1, 2, 2, 4, 5],
    "Descrption 1": ["Hi", "Good", "Biz", "S", "Okay", "Good2"],
    "Entered Date": ["Jill", "2021-01-02", "Money", "O", "Sarah", '2021-03-04'],
    "Description 2": ['2021-01-01', "Good", "Inc", "S", "2021-04-13", "Good2"],
    "Tax": ['Hi', .4, '2021-03-02', "2021-03-25", "Okay", .4],
    "Unnamed: 1": ["Jill", np.nan, "Biz", 'S', "Sarah", np.nan],
    "Unnamed: 2": [.4, np.nan, "Money", 'O', .7, np.nan],
    "Unnamed: 3": [np.nan, np.nan, "Inc", 'S', np.nan, np.nan],
    "Unnamed: 4": [np.nan, np.nan, .3, .2, np.nan, np.nan],
})

# 1. 自定义配置参数
filter_cond = df2['Unnamed: 1'].isna() & df2['Unnamed: 3'].notna()
# 固定列截止索引：该索引之前的ID、Amount列不会被改动
fix_col_end_idx = 1
# 需要拼接的描述字段列索引，需多字段拼接时直接往列表加索引即可
merge_desc_cols = [2, 3]
# 移位后有效列总数，根据最终需求调整
valid_col_count = 4

# 2. 逐行处理符合条件的错位数据
for idx, row in df2[filter_cond].iterrows():
    # 拼接指定描述字段
    merged_desc = ','.join([str(row[i]) for i in merge_desc_cols])
    # 提取描述字段后所有非空值，自动左移
    rest_values = [v for v in row[merge_desc_cols[-1]+1:] if pd.notna(v)]
    # 拼接新行内容，不足补空值对齐列数
    new_row_vals = list(row[:fix_col_end_idx+1]) + [merged_desc] + rest_values
    new_row_vals += [np.nan] * (len(df2.columns) - len(new_row_vals))
    # 回填原表
    df2.loc[idx] = new_row_vals

# 3. 删除末尾多余空列得到最终结果
df_final = df2.iloc[:, :fix_col_end_idx + valid_col_count + 1]
print(df_final)

功能适配说明 #

• 局部移位特性：仅处理指定列区间，前置固定列数据完全不受影响
• 多描述字段扩展：如果后续需要将2个及以上逗号拆分的内容并入描述字段，仅需修改merge_desc_cols的列索引列表即可，移位逻辑自动适配
• 筛选规则可自定义：可根据实际数据错位规律调整filter_cond的判断逻辑，适配不同错位场景

效果参考 #

原始数据展示：

处理后目标效果：

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