Python中实现SQL中窗口函数lag

Python的 shift

代码示例

#!/usr/bin/env python3
# -*- coding: UTF-8 -*-

import os.path
import pandas as pd
from datetime import datetime

if __name__ == "__main__":
    top_file_dir = r"C:Usersfilter"
    according_dir = [
        datetime.strptime(file_name.split("_")[-1], '%Y%m%d%H%M') for file_name in os.listdir(top_file_dir)
        if os.path.isdir(os.path.join(top_file_dir, file_name)) and file_name.startswith("test")]
    calculate_dir = sorted(according_dir, reverse=False)
    dat_time = sorted(according_dir)
    all_daytime_df = pd.DataFrame({'day_time': sorted(dat_time)})
    # 窗口函数 .LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
    # daytime_df['lag_day'] = daytime_df.shift(-1).fillna(0).astype('')
    all_daytime_df['lag_day'] = all_daytime_df.shift(-1)
    # pandas计算时间差
    all_daytime_df['interval_day'] = (all_daytime_df['lag_day'] - all_daytime_df['day_time']).dt.seconds/60
    file_start_time = all_daytime_df.loc[:, "day_time"][[0]]
    # 数据筛选
    file_filter_time = all_daytime_df['lag_day'][all_daytime_df['interval_day'] > 20]
    # Series 时间转字符串
    res = pd.concat([file_start_time, file_filter_time]).apply(lambda x: "test"+x.strftime("%Y%m%d%H%M")+".json")
    out_file = os.path.join(top_file_dir, "J_DAT_json_name.txt")
    res.to_csv(out_file, header=False, index=False)