如何编程合并DataFrame并去除NaN值，实现指定格式转换？

原始DataFrame结构：

import numpy as np
import pandas as pd

data = {'col2': ['Previous Sale', 'Price', 'OR Book-Page','Qualification Description','','',np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan],
        'col3': [np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,'03/24/2015', 210000, '00000-00000', 'Sales which are qualified','','',np.nan,np.nan,np.nan,np.nan,np.nan,np.nan],
        'col4': [np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,'08/03/1995', 121000, '00000-00000', 'Sales which are qualified','','']}

dframe = pd.DataFrame(data) 

需要转换为：

data2 = {'Previous Sale': ['03/24/2015', '08/03/1995'],
        'Price': [ 210000, 121000],
        'OR Book-Page': [ '00000-00000', '00000-00000'],
        'Qualification Description': ['Sales which are qualified','Sales which are qualified'],
        'col0': ['',''],
        'col1': ['','']
        }

dframeFix = pd.DataFrame(data2)

实现步骤 #

1. 提取目标列名：从col2中取出前6个非空值作为新列名，将两个空字符串替换为col0和col1
2. 提取数据行：分别从col3和col4中提取对应位置的6个元素作为两行数据
3. 构造新DataFrame：将列名和数据行组合成规整的表格

代码实现 #

import numpy as np
import pandas as pd

# 原始数据
data = {'col2': ['Previous Sale', 'Price', 'OR Book-Page','Qualification Description','','',np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan],
        'col3': [np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,'03/24/2015', 210000, '00000-00000', 'Sales which are qualified','','',np.nan,np.nan,np.nan,np.nan,np.nan,np.nan],
        'col4': [np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,'08/03/1995', 121000, '00000-00000', 'Sales which are qualified','','']}

dframe = pd.DataFrame(data)

# 1. 提取列名，替换空字符串为col0、col1
cols = dframe['col2'].dropna().tolist()
cols[4] = 'col0'
cols[5] = 'col1'

# 2. 提取两行数据
row1 = dframe['col3'].iloc[6:12].tolist()
row2 = dframe['col4'].iloc[12:18].tolist()

# 3. 构造新DataFrame
dframeFix = pd.DataFrame([row1, row2], columns=cols)

# 验证结果
print(dframeFix)

说明 #

• 使用dropna()过滤col2中的NaN值，确保只保留有效列名
• 通过iloc按位置索引精准提取对应的数据片段，保证数据与列名一一对应
• 直接用pd.DataFrame()将行数据和列名组合成目标格式，逻辑清晰高效

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