如何将关联列转为行结构？基于Pandas重构船舶AIS数据

原始数据 #

现有一份包含datetime、own_mmsi、target_mmsi、own_lat、own_lon、target_lat、target_lon字段的船舶数据，生成代码如下：

import pandas as pd

data = {
    'datetime': ['2019-01-01 08:03:00', '2019-01-01 08:04:00', '2019-01-01 08:04:00'],
    'own_mmsi': [236385000, 236385000, 244013009],
    'target_mmsi': [244013009, 244013009, 236385000],
    'own_lat': [59.330711, 59.330400, 59.327970],
    'own_lon': [10.515833, 10.514336, 10.515833],
    'target_lat': [59.327478, 59.327970, 59.330400],
    'target_lon': [10.515475, 10.515833, 10.514336],
}
df = pd.DataFrame(data)

需求说明 #

需要将own_mmsi与target_mmsi合并到mmsi列，own_lat/own_lon和target_lat/target_lon合并到lat/lon列，最终每个datetime下的每个mmsi对应一行数据，预期输出前3行如下：

尝试的错误代码 #

曾使用melt和pivot_table处理，但未得到预期结果：

melted_df = pd.melt(
    df,
    id_vars=['datetime', 'own_mmsi'],
    value_vars=['target_mmsi', 'target_lat', 'target_lon'],
    var_name='attribute',
    value_name='value'
)

reshaped_df = melted_df.pivot_table(
    index=['datetime', 'own_mmsi'],
    columns='attribute',
    values='value',
    aggfunc='first'
).reset_index()
   
reshaped_df.columns.name = None  # Remove the name of the columns index
reshaped_df = reshaped_df.rename(columns={'target_mmsi': 'own_target_mmsi', 'target_lat': 'own_target_lat', 'target_lon': 'own_target_lon'})

print(reshaped_df)

正确解决方案 #

方法一：拆分拼接子DataFrame #

思路是分别提取"own"和"target"对应的数据，统一列名后拼接，逻辑直观易懂：

# 提取own相关数据并重命名列
own_df = df[['datetime', 'own_mmsi', 'own_lat', 'own_lon']].rename(
    columns={'own_mmsi': 'mmsi', 'own_lat': 'lat', 'own_lon': 'lon'}
)

# 提取target相关数据并重命名列
target_df = df[['datetime', 'target_mmsi', 'target_lat', 'target_lon']].rename(
    columns={'target_mmsi': 'mmsi', 'target_lat': 'lat', 'target_lon': 'lon'}
)

# 合并两个DataFrame并重置索引
result_df = pd.concat([own_df, target_df], ignore_index=True)

# 按datetime排序，让结果更规整
result_df = result_df.sort_values(by='datetime').reset_index(drop=True)

# 查看前3行结果
print(result_df.head(3))

执行后输出的前3行与预期完全一致：

datetime       mmsi       lat        lon
0 2019-01-01 08:03:00  236385000  59.330711  10.515833
1 2019-01-01 08:03:00  244013009  59.327478  10.515475
2 2019-01-01 08:04:00  236385000  59.330400  10.514336

方法二：使用pd.wide_to_long #

适合字段命名规则统一的场景，通过识别列名前缀实现快速重构：

# 给列名添加统一后缀，适配wide_to_long的识别规则
df.columns = [col.replace('_mmsi', '_mmsi_id').replace('_lat', '_lat_val').replace('_lon', '_lon_val') for col in df.columns]

# 调用wide_to_long转换数据
result_df = pd.wide_to_long(
    df,
    stubnames=['_mmsi_id', '_lat_val', '_lon_val'],
    i='datetime',
    j='type',
    sep='_',
    suffix='.*'
).reset_index()

# 重命名列并筛选需要的字段
result_df = result_df.rename(
    columns={'_mmsi_id': 'mmsi', '_lat_val': 'lat', '_lon_val': 'lon'}
)[['datetime', 'mmsi', 'lat', 'lon']]

# 排序并重置索引
result_df = result_df.sort_values(by='datetime').reset_index(drop=True)

此方法输出结果与方法一完全相同，代码更简洁。

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