如何找到某列的最早时间戳和最新时间戳之间的时间窗口，并按另一列对其进行分组？

Question

我正在处理一个大型数据文件，我想要找到一列的时间窗口，从最早的时间戳到最近的时间戳，并根据另一列中的字符串进行分组。我在python和juypter中工作。

Test_Ordered_at *头衔

2020-04-07 15:06:00订单为绿色

2020-04-07 18:56:00订单为绿色

2020-04-07 15:07:00订单为绿色

2020-04-07 18:55:00订单为绿色

2020-03-07 16:55:00取消会员资格

2020-03-07 17:55:00取消会员资格

2020-03-07 18:55:00取消会员资格

结果应该是这样的：

Test_Ordered_at :标题

下午3:50:00-11:00，订单为绿色。

下午2:00:00:00:00:00:00:00:00:00:00:00:00:00:00:00:00:00:00北京时间:00:00:00:00:00:00北京时间:00:00:00北京时间:00:00:00北京时间:00:00:00北京时间:00:00北京时间:00:00:00:00北京时间:00:00:00北京时间:00:00:00北京时间:00:00:00北京时间:00:00:00北京时间:00:00-11:00

下面是我的代码

import pandas as pd
from datetime import datetime
from dateutil import parser
notmiss = df 

notmiss['Test_Ordered_At'] = notmiss['Test_Ordered_At'].astype('datetime64[ns]')
print(notmiss.head())
# Add a new column instance, this adds a value to each instance 
notmiss['instance'] = 1

# set index to time, this makes df a time series df and then you can apply pandas time series functions.
tf = notmiss.set_index(notmiss['Test_Ordered_At'], drop=True, inplace=True)

Answer 1

您可以使用np.ptp来完成此操作

import pandas as pd
import numpy as np

data = {'Test_Ordered_at': ['2020-04-07 15:06:00','2020-04-07 18:56:00','2020-04-07 15:07:00','2020-04-07 18:55:00','2020-03-07 16:55:00','2020-03-07 17:55:00','2020-03-07 18:55:00'],
'Title': ['The order is green','The order is green','The order is green','The order is green','The membership is cancelled','The membership is cancelled','The membership is cancelled']}

df = pd.DataFrame(data)

df['Test_Ordered_at'] = pd.to_datetime(df['Test_Ordered_at'])
print(df.groupby('Title')['Test_Ordered_at'].agg(np.ptp))

Title
 The membership is cancelled   02:00:00
 The order is green            03:50:00

Answer 2

1. 一旦到了日期时间，简单的聚合和计算

data = '''Test_Ordered_at,        Title
2020-04-07 15:06:00, The order is green
2020-04-07 18:56:00, The order is green
2020-04-07 15:07:00, The order is green
2020-04-07 18:55:00, The order is green 
2020-03-07 16:55:00, The membership is cancelled
2020-03-07 17:55:00, The membership is cancelled 
2020-03-07 18:55:00, The membership is cancelled'''
da = [[i.strip() for i in l.split(",")] for l in data.split("\n")]
da
df = pd.DataFrame(da[1:], columns=da[0])
df.Test_Ordered_at = pd.to_datetime(df.Test_Ordered_at)
df2 = df.groupby("Title")["Test_Ordered_at"].agg(["min","max"]).reset_index()
df2["Test_Ordered_at"] = df2["max"] - df2["min"]
df2.drop(columns=["min","max"])

输出

    Title   Test_Ordered_at
0   The membership is cancelled 02:00:00
1   The order is green  03:50:00