如何使用Pandas将同Nominal的多行数据合并为单行？

原始数据格式：

+--------+------------+-----------+-----------+-------------+--------------+
| Nominal| IP_Address | MaxTxPower| gsmSecName| CellName    | fingerprint  |
+--------+------------+-----------+-----------+-------------+--------------+
| DU0949 | 172.18.26.1| 15000     | DU0949A   |             |              |
+--------+------------+-----------+-----------+-------------+--------------+
| DU0949 |            | 40000     |           | DU0949U09A0 | DU0949_BB2   |
+--------+------------+-----------+-----------+-------------+--------------+
| LO0004 | 172.18.26.2| 15000     | LO0004D   |             |              |
+--------+------------+-----------+-----------+-------------+--------------+
| LO0004 |            | 40000     |           | LO0004U09A3 | LO0004_BB2   |
----------------------------------------------------------------------------

需要转换为：

+--------+------------+-----------+-----------+------------+--------------+-----------+
| Nominal| IP_Address | MaxTxPower| gsmSecName| CellName   | fingerprint  | MaxTxPower|
+--------+------------+-----------+-----------+------------+--------------+-----------+
| DU0949 | 172.18.26.1| 15000     | DU0949A   | DU0949U09A0| DU0949_BB2   | 40000     |
+--------+------------+-----------+-----------+------------+--------------+-----------+
| LO0004 | 172.18.26.2| 15000     | LO0004D   | LO0004U09A3| LO0004_BB2   | 40000     |
+--------+------------+-----------+-----------+------------+--------------+-----------+

用户尝试的错误代码：

import pandas as pd
data = {
    'Nominal': ['DU0949', 'DU0949', 'LO0004', 'LO0004'],
    '2G_IP_Address': ['172.18.26.1', '', '172.18.26.2', ''],
    'MaxTxPower': [15000, 40000, 15000, 40000],
    'gsmSecName': ['DU0949A', '', 'LO0004D', ''],
    'CellName': ['', 'DU0949U09A0', '', 'LO0004U09A3'],
    'fingerprint': ['', 'DU0949_BB2', '', 'LO0004_BB2']
}

df = pd.DataFrame(data)
df = df[(df['IP_Address'] != '') & (df['gsmSecName'] != '')]
df = df.reset_index(drop=True)
df.columns = ['Nominal', 'IP_Address', 'MaxTxPower_1', 'gsmSectorName', 'CellName_1', 'fingerprint_1']
df = df[['Nominal', 'IP_Address', 'MaxTxPower_1', 'gsmSectorName', 'CellName_1', 'fingerprint_1']]
df = df.rename(columns={'MaxTxPower_1': 'MaxTxPower', 'CellName_1': 'CellName', 'fingerprint_1':'fingerprint'})
print(df)

正确实现代码 #

核心思路：按Nominal分组合并同组非空数据，拆分两组MaxTxPower到对应列

import pandas as pd

# 修正列名后的原始数据
data = {
    'Nominal': ['DU0949', 'DU0949', 'LO0004', 'LO0004'],
    'IP_Address': ['172.18.26.1', '', '172.18.26.2', ''],
    'MaxTxPower': [15000, 40000, 15000, 40000],
    'gsmSecName': ['DU0949A', '', 'LO0004D', ''],
    'CellName': ['', 'DU0949U09A0', '', 'LO0004U09A3'],
    'fingerprint': ['', 'DU0949_BB2', '', 'LO0004_BB2']
}

df = pd.DataFrame(data)

# 按Nominal分组，提取每组非空字段值
grouped = df.groupby('Nominal').agg(lambda x: x[x != ''].values[0] if any(x != '') else '')

# 拆分每组的两个MaxTxPower值
max_tx_list = df.groupby('Nominal')['MaxTxPower'].apply(list).tolist()
grouped['MaxTxPower'] = [item[0] for item in max_tx_list]
grouped['MaxTxPower_final'] = [item[1] for item in max_tx_list]

# 调整列顺序并匹配目标格式列名
final_df = grouped[['IP_Address', 'MaxTxPower', 'gsmSecName', 'CellName', 'fingerprint', 'MaxTxPower_final']].reset_index()
final_df.columns = ['Nominal', 'IP_Address', 'MaxTxPower', 'gsmSecName', 'CellName', 'fingerprint', 'MaxTxPower']

# 打印结果
print(final_df.to_string(index=False))

代码说明 #

1. 修正了原始数据列名错误（将2G_IP_Address改为目标格式的IP_Address）
2. 通过groupby+agg提取每组内非空字段值，解决同Nominal两行数据的合并需求
3. 单独拆分每组的两个MaxTxPower值，分别对应目标中的前后两个MaxTxPower列
4. 调整列顺序并匹配目标格式的列名，最终输出符合要求的结果

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