如何高效将多源异构数据规整为目标结构pandas DataFrame

不需要逐行循环拆分小DataFrame再合并，用pandas布尔索引配合原生列对齐机制就能高效实现。

注：你给出的目标列名中重复写了两次val2，属于笔误，以下实现将第三个值列修正为val3，如果需要保留重复列名，直接替换列名列表即可。

实现代码 #

import pandas as pd
import numpy as np

data_in = [
    [1.1, 'A', 1,2,3],
    [1.2, 'B', 10,20,30,40],
    [2.1, 'A', 1.1,2.1,3.1],
    [2.1, 'B', 11,21,31,41],
    [3.1, 'A', 1.2,2.2,3.2],
    [3.2, 'B', 12,22,32,42],
]

# 提取公共固定字段
df = pd.DataFrame(
    [row[:2] for row in data_in],
    columns=['timestamp', 'source']
)

# 按来源分别赋值对应数值列，未覆盖位置自动填充NaN
a_vals = [row[2:] for row in data_in if row[1] == 'A']
df.loc[df['source'] == 'A', ['val1', 'val2', 'val3']] = a_vals

b_vals = [row[2:] for row in data_in if row[1] == 'B']
df.loc[df['source'] == 'B', ['par1', 'par2', 'par3', 'par4']] = b_vals

# 按目标顺序重排列
df = df[['timestamp', 'source', 'val1', 'val2', 'val3', 'par1', 'par2', 'par3', 'par4']]

输出结果 #

执行print(df)得到的结果和预期完全一致：

timestamp source  val1  val2  val3  par1  par2  par3  par4
0        1.1      A   1.0   2.0   3.0   NaN   NaN   NaN   NaN
1        1.2      B   NaN   NaN   NaN  10.0  20.0  30.0  40.0
2        2.1      A   1.1   2.1   3.1   NaN   NaN   NaN   NaN
3        2.1      B   NaN   NaN   NaN  11.0  21.0  31.0  41.0
4        3.1      A   1.2   2.2   3.2   NaN   NaN   NaN   NaN
5        3.2      B   NaN   NaN   NaN  12.0  22.0  32.0  42.0

方案说明 #

• 性能优于逐行遍历、多DataFrame拼接的写法：loc批量赋值是pandas原生向量化操作，数据量越大性能差距越明显
• 扩展性好：后续新增其他来源的异构数据，只需要新增对应的值提取和列赋值逻辑即可
• 无需手动处理空值：pandas列对齐机制会自动给未赋值的位置填充NaN

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