如何基于df1列名与df2的name列合并DataFrame？需新增行并过滤列

步骤说明 #

1. 筛选有效列：保留df1的time列，以及列名在df2name列中的列（剔除D列）
2. 构建fruit行：为筛选后的列匹配对应fruit值，无匹配则设为空字符串
3. 插入顶部行：将fruit行添加到df1最上方，调整结构得到目标结果

代码实现 #

import pandas as pd

# 构造示例数据
df1 = pd.DataFrame(
    index=['val1', 'val2', 'val3'],
    data=[[None]*4 for _ in range(3)],
    columns=['time', 'A', 'B', 'C', 'D']
)
df1['time'] = df1.index  # 填充time列值

df2 = pd.DataFrame({
    'name': ['A', 'B', 'G', 'F'],
    'fruit': ['apple', 'banana', 'grape', 'fig']
})

# 步骤1：筛选df1的有效列
keep_cols = ['time'] + [col for col in df1.columns if col in df2['name'].tolist()]
df_filtered = df1[keep_cols].copy()

# 步骤2：构建fruit行数据
fruit_row = {'time': 'fruit'}
for col in keep_cols[1:]:
    # 匹配对应fruit值，无则为空字符串
    fruit_val = df2.loc[df2['name'] == col, 'fruit'].values[0] if col in df2['name'].tolist() else ''
    fruit_row[col] = fruit_val

# 步骤3：将fruit行插入到最上方
fruit_df = pd.DataFrame([fruit_row], index=['top'])
result = pd.concat([fruit_df, df_filtered], axis=0)

# 调整索引结构，贴近示例展示
result = result.set_index('time')
print(result)

输出结果 #

A       B C
time               
fruit  apple banana  
val1   None    None None
val2   None    None None
val3   None    None None

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