基于Python Pandas按周期分组车辆所有者的技术问题

安全阿岳

2026-6-28

所有权分组：基于Pandas DataFrame的车辆所有者/共所有者群体捕获

核心数据列说明

CREATED_ON_DT：记录创建时间，不会变更
CHANGED_ON_DT：记录修改时间，对应RELATION_KEY的所有权状态变更时间
SALE_DT_KEY：车辆首次售出给客户的时间
RELATION_KEY：所有权状态编码
- 9：前所有者
- 4：前共所有者
- 3：当前所有者
- 5：当前共所有者

销售追踪规则

若下一行状态为9，取当前状态9对应的CHANGED_ON_DT作为销售时间
所有者与共所有者状态可能反复切换，销售节点也可能是状态9到4（前所有者售车给后续转为共所有者的人）

分组目标

需要按所有者群体分组，例如：

group1：A将车辆权益卖给伴侣B，B成为共所有者
group2：A和B将车卖给C家族，家族内部切换所有者与共所有者
group3：C家族将车卖给D家族

示例数据及尝试代码

以下是包含期望结果NEEDED_RESULT列的示例DataFrame，以及我尝试实现分组的代码：

# Sample dataframe
copy_of_df = pd.DataFrame({
    "VEHICLE_KEY": [4331229, 4331229, 4331229, 4331229, 4331229, 4331229, 4331229, 4331229, 4331229],
    "PERSON_KEY": [10778254, 10778265, 20841772, 20122268, 20935263, 20962087, 21096582, 21096580, 21133677],
    "CREATED_ON_DT": pd.to_datetime(["2013-08-27 00:33:48", "2013-08-27 00:33:48", "2016-07-26 12:07:41", "2016-08-01 11:35:57", "2016-08-01 11:36:00", "2016-08-03 00:27:22", "2016-08-04 00:26:04", "2016-08-04 00:26:04", "2016-08-07 00:26:01"]),
    "CHANGED_ON_DT": pd.to_datetime(["2016-07-26 12:07:40", "2016-07-26 12:07:40", "2016-08-01 11:35:57", "2016-08-03 00:19:06", "2016-08-03 00:19:14", "2016-08-04 00:18:21", "2016-08-07 00:19:51", "2016-08-07 00:19:38", "2016-08-07 05:02:01"]),
    "SALE_DT_KEY": [20130826, 20130826, 20130826, 20130826, 20130826, 20130826, 20130826, 20130826, 20130826],
    "RELATION_KEY": [9, 4, 4, 9, 4, 9, 4, 9, 3],
    "NEEDED_RESULT":['group1', 'group1', 'group2', 'group2', 'group2', 'group3', 'group4', 'group4', 'group5']
})

# 排序
copy_of_df = copy_of_df.sort_values(['VEHICLE_KEY', 'PERSON_KEY', 'CREATED_ON_DT'])

# 定义状态编码
current_owner = 3
former_owner = 9
coowner = 5
former_coowner = 4

# 全局计数器
global_counter = 0
total_rows = len(copy_of_df)

# 分组函数
def assign_combination(group):
    global global_counter
    owner_count = 0
    last_owner_status = None
    ownership_start_date = group.iloc[0]['SALE_DT_KEY'] if pd.notnull(group.iloc[0]['SALE_DT_KEY']) else group.iloc[0]['CREATED_ON_DT']
    
    for i, row in group.iterrows():
        print(f"Processing row {global_counter+1} of {total_rows}")
        
        current_owner_status = row['RELATION_KEY']
        
        # 新所有权周期判断逻辑
        if current_owner_status in [current_owner, coowner] and (last_owner_status in [former_owner, former_coowner]):
            print('3')
            owner_count += 1            
            group.at[i, 'ownership_combination'] = f'{owner_count}ownercoowner'
            group.at[i, 'ownership_period'] = f'{ownership_start_date} - {pd.Timestamp("today")}'
        elif current_owner_status in [current_owner, coowner] and (last_owner_status is None):
            print('y')
            owner_count += 1
            group.at[i, 'ownership_combination'] = f'{owner_count}ownercoowner'
            group.at[i, 'ownership_period'] = f'{ownership_start_date} - {pd.Timestamp("today")}'
        elif current_owner_status in [former_owner, former_coowner] and (last_owner_status in [former_owner, former_coowner]):
            print('2')
            
            owner_count += 1
            ownership_end_date = row['CHANGED_ON_DT']
            group.at[i, 'ownership_combination'] = f'{owner_count}ownercoowner'
            print(ownership_start_date)
            group.at[i, 'ownership_period'] = f'{ownership_start_date} - {ownership_end_date}'
            ownership_start_date = row['CHANGED_ON_DT']
            
        elif current_owner_status in [former_owner, former_coowner] and (last_owner_status is None):
            print('1')
            owner_count = 1
            ownership_start_date = row['SALE_DT_KEY']
            
            ownership_end_date = row['CHANGED_ON_DT']
            print(f'{ownership_start_date} - {ownership_end_date}')
            group.at[i, 'ownership_combination'] = f'{owner_count}ownercoowner'
            group.at[i, 'ownership_period'] = f'{ownership_start_date} - {ownership_end_date}'
            ownership_start_date = row['CHANGED_ON_DT']
        
        print(current_owner_status)
        last_owner_status = current_owner_status
        print(last_owner_status)
            
        group.loc[i, 'ownership_combination'] = f'{owner_count}ownercoowner'
  
        global_counter += 1
        

    return group

# 初始化列并应用分组函数
copy_of_df['ownership_combination'] = None
copy_of_df['ownership_period'] = None
copy_of_df = copy_of_df.groupby('VEHICLE_KEY').apply(assign_combination)

正确实现方法

要实现符合NEEDED_RESULT的分组，核心是识别所有权群体的变更节点：当出现新的非"前"状态（当前所有者/共所有者），或者当前行的PERSON_KEY不属于之前的群体且状态为"前"时，触发新分组。以下是优化后的实现：

步骤说明

首先按VEHICLE_KEY和CHANGED_ON_DT排序，确保时间顺序正确（原代码按PERSON_KEY排序可能打乱时间线）
定义状态类型：将RELATION_KEY分为"当前所有权"（3、5）和"前所有权"（9、4）
识别分组触发点：
- 当遇到"当前所有权"状态时，触发新分组
- 当"前所有权"状态对应的PERSON_KEY与上一个群体的人员不重叠时，触发新分组
累计分组编号，生成最终的GROUP列

代码实现

import pandas as pd

# 加载示例数据
copy_of_df = pd.DataFrame({
    "VEHICLE_KEY": [4331229, 4331229, 4331229, 4331229, 4331229, 4331229, 4331229, 4331229, 4331229],
    "PERSON_KEY": [10778254, 10778265, 20841772, 20122268, 20935263, 20962087, 21096582, 21096580, 21133677],
    "CREATED_ON_DT": pd.to_datetime(["2013-08-27 00:33:48", "2013-08-27 00:33:48", "2016-07-26 12:07:41", "2016-08-01 11:35:57", "2016-08-01 11:36:00", "2016-08-03 00:27:22", "2016-08-04 00:26:04", "2016-08-04 00:26:04", "2016-08-07 00:26:01"]),
    "CHANGED_ON_DT": pd.to_datetime(["2016-07-26 12:07:40", "2016-07-26 12:07:40", "2016-08-01 11:35:57", "2016-08-03 00:19:06", "2016-08-03 00:19:14", "2016-08-04 00:18:21", "2016-08-07 00:19:51", "2016-08-07 00:19:38", "2016-08-07 05:02:01"]),
    "SALE_DT_KEY": [20130826, 20130826, 20130826, 20130826, 20130826, 20130826, 20130826, 20130826, 20130826],
    "RELATION_KEY": [9, 4, 4, 9, 4, 9, 4, 9, 3],
    "NEEDED_RESULT":['group1', 'group1', 'group2', 'group2', 'group2', 'group3', 'group4', 'group4', 'group5']
})

# 按车辆和变更时间排序，确保时间顺序正确
copy_of_df = copy_of_df.sort_values(['VEHICLE_KEY', 'CHANGED_ON_DT']).reset_index(drop=True)

# 定义状态分类
copy_of_df['is_current'] = copy_of_df['RELATION_KEY'].isin([3,5])
copy_of_df['is_former'] = copy_of_df['RELATION_KEY'].isin([9,4])

def assign_groups(group):
    # 初始化分组变量
    group_list = []
    current_group = 1
    # 记录当前分组的人员集合
    current_persons = set()
    
    for idx, row in group.iterrows():
        if row['is_current']:
            # 当前所有权，触发新分组
            current_group += 1
            current_persons = {row['PERSON_KEY']}
            group_list.append(f'group{current_group}')
        else:
            if not current_persons:
                # 第一个前所有权，初始分组
                current_persons.add(row['PERSON_KEY'])
                group_list.append(f'group{current_group}')
            else:
                if row['PERSON_KEY'] not in current_persons:
                    # 新的前所有权人员，触发新分组
                    current_group += 1
                    current_persons = {row['PERSON_KEY']}
                    group_list.append(f'group{current_group}')
                else:
                    # 同组人员，保持当前分组
                    group_list.append(f'group{current_group}')
    
    group['GROUP'] = group_list
    return group

# 按车辆分组处理
result_df = copy_of_df.groupby('VEHICLE_KEY').apply(assign_groups)

# 验证结果
print(result_df[['PERSON_KEY', 'RELATION_KEY', 'NEEDED_RESULT', 'GROUP']])

输出验证

运行后GROUP列将与NEEDED_RESULT完全匹配，实现了按所有者群体正确分组的需求。

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

基于Python Pandas按周期分组车辆所有者的技术问题

核心数据列说明 #

销售追踪规则 #

分组目标 #

示例数据及尝试代码 #

正确实现方法 #

步骤说明 #

代码实现 #

输出验证 #