如何递增DataFrame中JSON对象的Datetime值以修正航班时序

问题背景 #

我有如下DataFrame：

index  json_col 
   1      json_1
   2      json_2
   ...

其中json_1、json_2等均为JSON对象，示例json_1结构如下：

[
    {
        "origin": "a",
        "destination": "b",
        "leg": "a->b",
        "flights": [
            {
                "aircraftType": "763",
                "departureTimeZulu": "2022-10-08 18:10:00",
                "arrivalTimeZulu": "2022-10-08 22:30:00"
            }
        ]
    },
    {
        "origin": "b",
        "destination": "c",
        "leg": "b->c",
        "flights": [
            {
                "aircraftType": "73H",
                "departureTimeZulu": "2022-10-08 14:51:00",
                "arrivalTimeZulu": "2022-10-08 18:07:00"
            }
        ]
    },
    {
        "origin": "c",
        "destination": "d",
        "leg": "c-d",
        "flights": [
            {
                "aircraftType": "763",
                "departureTimeZulu": "2022-10-08 01:30:00",
                "arrivalTimeZulu": "2022-10-08 05:24:00"
            }
        ]
    }
]

处理逻辑 #

需要对json_col中的每个JSON对象应用以下规则：

• 若第一段行程的arrivalTimeZulu大于第二段的departureTimeZulu，则将第二段的departureTimeZulu和arrivalTimeZulu递增若干天（如x天），直到第一段的arrivalTimeZulu小于第二段的departureTimeZulu。
• 若第二段行程的arrivalTimeZulu大于第三段的departureTimeZulu，则将第三段的departureTimeZulu和arrivalTimeZulu递增若干天（如x天），直到第二段的arrivalTimeZulu小于第三段的departureTimeZulu。注意：第二段的arrivalTimeZulu可能已在上一步中更新。

逻辑执行示例 #

• "arrivalTimeZulu":"2022-10-08 22:30:00" > "departureTimeZulu":"2022-10-08 14:51:00"，因此给第二段行程的departureTimeZulu/arrivalTimeZulu增加1天。
• "arrivalTimeZulu":"2022-10-09 18:07:00" > "departureTimeZulu":"2022-10-08 14:51:00"，因此给第三段行程的departureTimeZulu/arrivalTimeZulu增加2天。

期望输出 #

[
    {
        "origin": "a",
        "destination": "b",
        "leg": "a->b",
        "flights": [
            {
                "aircraftType": "763",
                "departureTimeZulu": "2022-10-08 18:10:00",
                "arrivalTimeZulu": "2022-10-08 22:30:00"
            }
        ]
    },
    {
        "origin": "b",
        "destination": "c",
        "leg": "b->c",
        "flights": [
            {
                "aircraftType": "73H",
                "departureTimeZulu": "2022-10-09 14:51:00",
                "arrivalTimeZulu": "2022-10-09 18:07:00"
            }
        ]
    },
    {
        "origin": "c",
        "destination": "d",
        "leg": "c-d",
        "flights": [
            {
                "aircraftType": "763",
                "departureTimeZulu": "2022-10-10 01:30:00",
                "arrivalTimeZulu": "2022-10-10 05:24:00"
            }
        ]
    }
]

实现代码 #

步骤说明 #

1. 解析JSON列：将json_col中的数据转为可操作的Python列表对象。
2. 编写时间调整函数：遍历行程列表，依次检查相邻行程的时间关系，调整后续行程的时间。
3. 应用函数到DataFrame：用apply方法将调整函数作用到每一行的json_col。

完整代码 #

import pandas as pd
from datetime import datetime, timedelta

# 定义时间格式
TIME_FORMAT = "%Y-%m-%d %H:%M:%S"

def adjust_leg_times(legs):
    # 遍历相邻行程对
    for i in range(len(legs)-1):
        current_leg = legs[i]
        next_leg = legs[i+1]
        
        # 解析当前行程的到达时间
        current_arrival = datetime.strptime(
            current_leg['flights'][0]['arrivalTimeZulu'],
            TIME_FORMAT
        )
        # 解析下一行程的出发时间
        next_departure = datetime.strptime(
            next_leg['flights'][0]['departureTimeZulu'],
            TIME_FORMAT
        )
        
        # 计算需要增加的天数
        days_to_add = 0
        while current_arrival >= next_departure:
            days_to_add += 1
            next_departure += timedelta(days=1)
        
        if days_to_add > 0:
            # 更新下一行程的出发时间
            next_leg['flights'][0]['departureTimeZulu'] = (
                datetime.strptime(next_leg['flights'][0]['departureTimeZulu'], TIME_FORMAT)
                + timedelta(days=days_to_add)
            ).strftime(TIME_FORMAT)
            # 更新下一行程的到达时间
            next_leg['flights'][0]['arrivalTimeZulu'] = (
                datetime.strptime(next_leg['flights'][0]['arrivalTimeZulu'], TIME_FORMAT)
                + timedelta(days=days_to_add)
            ).strftime(TIME_FORMAT)
    
    return legs

# 示例DataFrame
data = {
    'index': [1],
    'json_col': [
        [
            {
                "origin": "a",
                "destination": "b",
                "leg": "a->b",
                "flights": [{"aircraftType": "763", "departureTimeZulu": "2022-10-08 18:10:00", "arrivalTimeZulu": "2022-10-08 22:30:00"}]
            },
            {
                "origin": "b",
                "destination": "c",
                "leg": "b->c",
                "flights": [{"aircraftType": "73H", "departureTimeZulu": "2022-10-08 14:51:00", "arrivalTimeZulu": "2022-10-08 18:07:00"}]
            },
            {
                "origin": "c",
                "destination": "d",
                "leg": "c-d",
                "flights": [{"aircraftType": "763", "departureTimeZulu": "2022-10-08 01:30:00", "arrivalTimeZulu": "2022-10-08 05:24:00"}]
            }
        ]
    ]
}

df = pd.DataFrame(data)

# 应用调整函数
df['json_col'] = df['json_col'].apply(adjust_leg_times)

# 查看结果
print(df['json_col'][0])

代码说明 #

• adjust_leg_times函数：接收行程列表，逐个检查相邻行程的时间逻辑，计算需要补加的天数后，同步更新后续行程的出发和到达时间。
• 时间处理：通过datetime.strptime解析字符串格式的时间，用timedelta实现天数递增，最后转回指定格式的字符串。
• DataFrame应用：利用apply方法将调整逻辑批量应用到每一行的json_col数据。

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