如何将Dataframe中两列日期列表匹配拆分生成新Dataframe

问题需求 #

需要将包含日期列表的DataFrame按以下规则转换：

• 将Start列的日期与最匹配的End列日期配对，每对生成一行新数据
• 若Start有多个日期，End日期位于两个Start日期之间时，匹配较晚的那个Start日期
• 即使End列为空，也要拆分Start列并保留最后一个日期；Start或End为空时，保留该行数据

输入示例 #

Name               Start                               End
AAA               2017-09-13    
BBB         2021-11-20, 2022-06-04                 2022-04-07
CCC                                                2022-09-29
DDD 
EEE        2021-04-28, 2022-06-14
FFF        2021-06-25, 2022-06-19                2022-03-18, 2024-07-22
GGG      2020-10-23,2021-06-10, 2022-03-02       2021-03-06, 2022-01-04, 2024-08-15

期望输出 #

Name        Start                    End
AAA         2017-09-13          
BBB         2022-06-04             2022-04-07
CCC                                2022-09-29
DDD 
EEE         2022-06-14
FFF         2021-06-25             2022-03-18
FFF         2022-06-19             2024-07-22
GGG         2020-10-23             2021-03-06
GGG         2021-06-10             2022-01-04
GGG         2022-03-02             2024-08-15

（注：输入示例中AAA的Start为2017-09-13，期望输出中的2016-09-13应为笔误）

解决方案代码 #

import pandas as pd
from datetime import datetime

def process_row(row):
    # 处理Start列，转换为排序后的日期列表
    start_str = row['Start'].strip()
    starts = []
    if start_str:
        starts = [datetime.strptime(s.strip(), '%Y-%m-%d') for s in start_str.split(',')]
        starts.sort()
    
    # 处理End列，转换为排序后的日期列表
    end_str = row['End'].strip()
    ends = []
    if end_str:
        ends = [datetime.strptime(e.strip(), '%Y-%m-%d') for e in end_str.split(',')]
        ends.sort()
    
    result = []
    name = row['Name']
    
    # 情况1：Start为空
    if not starts:
        if not ends:
            result.append({'Name': name, 'Start': '', 'End': ''})
        else:
            for e in ends:
                result.append({'Name': name, 'Start': '', 'End': e.strftime('%Y-%m-%d')})
        return result
    
    # 情况2：End为空
    if not ends:
        last_start = starts[-1].strftime('%Y-%m-%d')
        result.append({'Name': name, 'Start': last_start, 'End': ''})
        return result
    
    # 情况3：Start和End都有数据
    if len(starts) == len(ends):
        # 数量相等时按排序后顺序一一配对
        for s, e in zip(starts, ends):
            result.append({
                'Name': name,
                'Start': s.strftime('%Y-%m-%d'),
                'End': e.strftime('%Y-%m-%d')
            })
    elif len(starts) > len(ends):
        # End数量更少，每个End匹配第一个大于等于它的Start，无匹配则用最后一个Start
        used_starts = []
        for e in ends:
            match_s = None
            for s in starts:
                if s >= e:
                    match_s = s
                    break
            if not match_s:
                match_s = starts[-1]
            used_starts.append(match_s)
            result.append({
                'Name': name,
                'Start': match_s.strftime('%Y-%m-%d'),
                'End': e.strftime('%Y-%m-%d')
            })
    else:
        # Start数量更少，每个Start匹配第一个大于等于它的End，剩余End单独成行
        used_ends = []
        for s in starts:
            match_e = None
            for e in ends:
                if e >= s:
                    match_e = e
                    break
            if not match_e:
                match_e = ends[-1]
            used_ends.append(match_e)
            result.append({
                'Name': name,
                'Start': s.strftime('%Y-%m-%d'),
                'End': match_e.strftime('%Y-%m-%d')
            })
        # 添加未被匹配的End
        for e in ends:
            if e not in used_ends:
                result.append({'Name': name, 'Start': '', 'End': e.strftime('%Y-%m-%d')})
    
    return result

# 创建输入DataFrame
data = {
    'Name': ['AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'FFF', 'GGG'],
    'Start': ['2017-09-13', '2021-11-20, 2022-06-04', '', '', '2021-04-28, 2022-06-14', '2021-06-25, 2022-06-19', '2020-10-23,2021-06-10, 2022-03-02'],
    'End': ['', '2022-04-07', '2022-09-29', '', '', '2022-03-18, 2024-07-22', '2021-03-06, 2022-01-04, 2024-08-15']
}
df = pd.DataFrame(data)

# 处理所有行并生成结果
all_rows = []
for _, row in df.iterrows():
    all_rows.extend(process_row(row))

result_df = pd.DataFrame(all_rows)[['Name', 'Start', 'End']]

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

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