python - pandas 按小时分组并计算缺货时间-6ren

python - pandas 按小时分组并计算缺货时间

转载作者：行者123 更新时间：2023-12-01 09:15:37

我有一个如下所示的时间序列:

| datetime_create         | quantity_old | quantity_new | quantity_diff | is_stockout |
| 2018-02-15 08:12:54.289 | 16           | 15           | -1            | False       |
| 2018-02-15 08:14:10.619 | 15           | 13           | -2            | False       |
| 2018-02-15 08:49:15.962 | 13           | 9            | -4            | False       |
| 2018-02-15 08:51:04.740 | 9            | 8            | -1            | False       |
| 2018-02-15 08:56:37.086 | 8            | 7            | -1            | False       |
| 2018-02-15 09:23:22.858 | 7            | 5            | -2            | False       |
| 2018-02-15 10:16:50.324 | 5            | 4            | -1            | False       |
| 2018-02-15 10:19:25.071 | 4            | 3            | -1            | False       |
| 2018-02-15 10:33:22.788 | 3            | 2            | -1            | False       |
| 2018-02-15 10:33:34.125 | 2            | 0            | -2            | True        |
| 2018-02-15 16:45:24.747 | 0            | 1            | 1             | False       |
| 2018-02-15 16:48:29.996 | 1            | 0            | -1            | True        |
| 2018-02-17 10:42:58.325 | 0            | 42           | 42            | False       |
| 2018-02-17 10:47:07.380 | 42           | 41           | -1            | False       |
| 2018-02-17 11:42:31.008 | 41           | 40           | -1            | False       |
| 2018-02-17 11:48:31.070 | 40           | 39           | -1            | False       |
| 2018-02-17 12:39:13.681 | 39           | 38           | -1            | False       |
| 2018-02-17 12:48:00.286 | 38           | 37           | -1            | False       |
| 2018-02-17 12:56:59.203 | 37           | 36           | -1            | False       |
| 2018-02-17 13:18:12.285 | 36           | 35           | -1            | False       |
| 2018-02-17 13:29:53.465 | 35           | 34           | -1            | False       |
| 2018-02-17 14:54:55.810 | 34           | 33           | -1            | False       |
| 2018-02-17 15:53:38.816 | 33           | 32           | -1            | False       |
| 2018-02-17 16:28:08.076 | 32           | 31           | -1            | False       |
| 2018-02-17 16:45:18.965 | 31           | 30           | -1            | False       |
| 2018-02-17 16:59:11.111 | 30           | 29           | -1            | False       |
| 2018-02-17 17:18:53.646 | 29           | 27           | -2            | False       |
| 2018-02-17 17:44:43.508 | 27           | 26           | -1            | False       |
| 2018-02-17 19:34:49.701 | 26           | 25           | -1            | False       |
| 2018-02-17 20:49:00.205 | 25           | 24           | -1            | False       |
| 2018-02-18 07:14:22.207 | 24           | 22           | -2            | False       |
| 2018-02-18 08:35:41.560 | 22           | 20           | -2            | False       |
| 2018-02-18 10:22:18.825 | 20           | 19           | -1            | False       |
| 2018-02-18 10:28:33.909 | 19           | 18           | -1            | False       |
| 2018-02-18 10:37:30.427 | 18           | 17           | -1            | False       |
| 2018-02-18 10:50:55.265 | 17           | 16           | -1            | False       |
| 2018-02-18 11:17:53.359 | 16           | 15           | -1            | False       |
| 2018-02-18 11:42:29.214 | 0            | 30           | 30            | False       |
| 2018-02-18 11:58:19.113 | 15           | 14           | -1            | False       |
| 2018-02-18 11:58:56.432 | 14           | 13           | -1            | False       |
| 2018-02-18 12:06:48.438 | 13           | 12           | -1            | False       |
| 2018-02-18 12:21:43.634 | 12           | 11           | -1            | False       |
| 2018-02-18 12:44:46.288 | 11           | 9            | -2            | False       |
| 2018-02-18 13:26:01.952 | 9            | 8            | -1            | False       |
| 2018-02-18 13:26:40.940 | 8            | 9            | 1             | False       |
| 2018-02-18 13:27:34.090 | 9            | 8            | -1            | False       |
| 2018-02-18 13:27:52.443 | 8            | 9            | 1             | False       |
| 2018-02-18 13:28:58.832 | 9            | 8            | -1            | False       |
| 2018-02-18 14:56:49.105 | 8            | 7            | -1            | False       |
| 2018-02-18 16:00:32.212 | 7            | 6            | -1            | False       |
| 2018-02-18 16:28:20.175 | 6            | 5            | -1            | False       |
| 2018-02-18 16:31:48.741 | 5            | 3            | -2            | False       |
| 2018-02-18 16:40:33.922 | 3            | 2            | -1            | False       |
| 2018-02-18 16:56:17.864 | 2            | 1            | -1            | False       |
| 2018-02-18 17:15:01.065 | 1            | 2            | 1             | False       |
| 2018-02-18 17:40:43.062 | 2            | 1            | -1            | False       |
| 2018-02-18 17:55:50.520 | 1            | 0            | -1            | True        |
| 2018-02-18 18:20:21.664 | 30           | 29           | -1            | False       |
| 2018-02-18 21:38:10.645 | 29           | 28           | -1            | False       |
| 2018-02-19 06:36:04.564 | 28           | 27           | -1            | False       |
| 2018-02-19 08:49:23.080 | 27           | 26           | -1            | False       |

我想计算一天中每小时的总缺货时间，例如

|    date    |  0  |  1  |  2  |  3  | ... | 23  |
| ---------- | --- | --- | --- | --- | --- | --- |
| 2018-02-15 | 10  | 0   | 0   | 10  | ... | 13  |
| 2018-02-16 | 6   | 0   | 7   | 10  | ... | 20  |
| 2018-02-17 | 6   | 0   | 0   | 10  | ... | 20  |

规则:

按小时分组
我可以在一小时内访问所有行。
计算之间的时间
- 起点:is_stockout从False到True
- 终点:is_stockout从True到False
一个小时后。可能有多个起点和终点
将索引更改为天，并将列更改为 24 小时。

看起来有点像new-syntax-to-window-and-resample-operations

我想我需要使用

df.resample('H').apply(caluclate_time_in_hour)

但这似乎还不够:

df.resample('H') 结果索引是小时，而不是列

如何编写正确的calculate_time_in_hour？我认为 apply 无法做到这一点。

我写了一个伪代码:

def caluclate_time_in_hour(item):
    # note: item here is stockcount . not just True or False

    global last_time
    global is_stockout
    global data

    cur_time = item.name

    # I need pandas return every row even that hour doesn't have data
    # so that no need to check the how many hours elasped.

    if item is np.nan:
        if is_stockout:
            data[cur_time.hour] = 60*60
        else:
            data[cur_time.hour] = 0

    if is_stockout:
        if item > 0:
            data[cur_time.hour] += cur_time - last_time
        else:
            is_stockout = False
    else:
        if item = 0:
            is_stockout = True

    last_time = item.name

    return data.copy()

如何知道该项目是这一小时内的最后一项，以便我可以返回数据？这就是apply问题。也许我需要 pandas 按小时返回所有行才能应用。

我只是想知道我是否可以通过 pandas 内置函数执行上述操作，而无需循环所有行来构造新的 DataFrame。

<小时/>

例如2018-02-15 ~ 2018-02-16有以下两条记录:

| datetime_create     | quantity_old | quantity_new | quantity_diff | is_stockout |
| 2018-02-14 00:45:00 | 40           | 10           | -30           | False       |
| 2018-02-15 12:45:00 | 10           | 2            | -8            | False       |
| 2018-02-15 13:45:00 | 2            | 1            | -1            | False       |
| 2018-02-15 16:45:00 | 1            | 0            | -1            | True        |
| 2018-02-16 10:42:00 | 0            | 42           | 42            | False       |
| 2018-02-16 13:42:00 | 42           | 40           | -2            | False       |
| 2018-02-16 19:42:00 | 40           | 38           | -2            | False       |
| 2018-02-17 20:42:00 | 38           | 40           | 2             | False       |
# duplicate above 
| 2018-02-18 00:45:00 | 40           | 10           | -30           | False       |
| 2018-02-19 12:45:00 | 10           | 2            | -8            | False       |
| 2018-02-19 13:45:00 | 2            | 1            | -1            | False       |
| 2018-02-19 16:45:00 | 1            | 0            | -1            | True        |
| 2018-02-20 10:42:00 | 0            | 42           | 42            | False       |
| 2018-02-20 13:42:00 | 42           | 40           | -2            | False       |
| 2018-02-20 19:42:00 | 40           | 38           | -2            | False       |
| 2018-02-21 20:42:00 | 38           | 40           | 2             | False       |

csv:

datetime_create,quantity_old,quantity_new,quantity_diff,is_stockout
2018-02-14 00:45:00,40,10,-30,False
2018-02-15 12:45:00,10,2,-8,False
2018-02-15 13:45:00,2,1,-1,False
2018-02-15 16:45:00,1,0,-1,True
2018-02-16 10:42:00,0,42,42,False
2018-02-16 13:42:00,42,40,-2,False
2018-02-16 19:42:00,40,38,-2,False
2018-02-17 20:42:00,38,40,2,False
2018-02-18 00:45:00,40,10,-30,False
2018-02-19 12:45:00,10,2,-8,False
2018-02-19 13:45:00,2,1,-1,False
2018-02-19 16:45:00,1,0,-1,True
2018-02-20 10:42:00,0,42,42,False
2018-02-20 13:42:00,42,40,-2,False
2018-02-20 19:42:00,40,38,-2,False
2018-02-21 20:42:00,38,40,2,False

结果(这里时间单位是分钟，为了美观):

date,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23
2018-02-14,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0
2018-02-15,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,15.0,60.0,60.0,60.0,60.0,60.0,60.0,60.0,60.0,60.0
2018-02-16,60.0,60.0,60.0,60.0,60.0,60.0,60.0,60.0,60.0,60.0,42.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0
2018-02-17,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0
2018-02-18,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0
2018-02-19,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,15.0,60.0,60.0,60.0,60.0,60.0,60.0,60.0,60.0,60.0
2018-02-20,60.0,60.0,60.0,60.0,60.0,60.0,60.0,60.0,60.0,60.0,42.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0
2018-02-21,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0

最佳答案

我认为首先需要resample以分钟为单位向前填充 NaN，转换为 inetger 并为 Series 添加 DataFrame.squeeze .

然后按日期和小时使用sum进行聚合，最后按unstack进行整形:

s = df[['is_stockout']].resample('T').ffill().astype(int).squeeze()
df1 = s.groupby([s.index.date, s.index.hour]).sum().unstack(fill_value=0)
print (df1)
datetime_create  0   1   2   3   4   5   6   7   8   9  ...  14  15  16  17  \
2018-02-14        0   0   0   0   0   0   0   0   0   0 ...   0   0   0   0   
2018-02-15        0   0   0   0   0   0   0   0   0   0 ...   0   0  15  60   
2018-02-16       60  60  60  60  60  60  60  60  60  60 ...   0   0   0   0   
2018-02-17        0   0   0   0   0   0   0   0   0   0 ...   0   0   0   0   
2018-02-18        0   0   0   0   0   0   0   0   0   0 ...   0   0   0   0   
2018-02-19        0   0   0   0   0   0   0   0   0   0 ...   0   0  15  60   
2018-02-20       60  60  60  60  60  60  60  60  60  60 ...   0   0   0   0   
2018-02-21        0   0   0   0   0   0   0   0   0   0 ...   0   0   0   0   

datetime_create  18  19  20  21  22  23  
2018-02-14        0   0   0   0   0   0  
2018-02-15       60  60  60  60  60  60  
2018-02-16        0   0   0   0   0   0  
2018-02-17        0   0   0   0   0   0  
2018-02-18        0   0   0   0   0   0  
2018-02-19       60  60  60  60  60  60  
2018-02-20        0   0   0   0   0   0  
2018-02-21        0   0   0   0   0   0

关于python - pandas 按小时分组并计算缺货时间，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/51299618/

文章推荐： java - Spring 管理的事务未启动

文章推荐： R – 如何按最近的时间日期连接两个数据框？

文章推荐： java - 查看字符串是否包含所有数字的方法

文章推荐： css - 这是您构建 CSS 样式表的方式吗？

r - 如何将可扩展时间数据作为 r ggplot 包中的一个因素呈现？ (即 1 小时、5 小时、10 小时)
我有 0 小时、3 小时、12 小时、24 小时、48 小时的数据组……我想绘制这些数据的图表，以便保留时间的比例。 runs <- c(1:25) hours <- as.factor(c(0, 3
swift - 如何计算从选定时间(小时)到给定持续时间的时间(小时)
例如，如果我选择了时间:下午 3 点和小时数:5 小时，则得到 (8pm) 作为答案“ 最佳答案 let calendar = Calendar.current let date = calendar
php - 两个相同的日期时间；一个显示 12 小时，另一个显示 24 小时
我有一个包含两个日期时间字段的表单。用户输入日期 (yyyy-mm-dd) 和时间(3 个框；小时、分钟、上午/下午)。出于某种原因，第一个没有保存为 24 小时制。以下数据为输入结果: 2011
javascript - 使用导出单位进行计算(例如 1 小时 * 1 英里/小时)
我一直在尝试使用导出单位进行计算，但到目前为止我还没有取得任何成果。我已经尝试过mathjs ，但如果我输入 1 小时 * 1 英里/小时，我会得到 UnsupportedTypeError: Fu
ubuntu - 每 3 小时 cron 作业和每 3+1 小时 cron 作业
我有两组要运行的 cronjob。第一个应该每 3 小时运行一次，第二个也应该每 3 小时运行一次，但比第一组晚一个小时。什么是正确的语法？ // every 3 hours 17 */3 * *
amazon-web-services - EC2 预留实例 - 24 个实例运行 1 小时(同一小时)是否等于 1 个实例运行 24 小时？
我知道 AWS 中的预留实例更多的是计费而不是实际实例——它们没有附加到实际实例——我想知道: 如果我在特定区域和可用区中购买特定时间的预留实例 - 如果我每天 24 小时使用单个实例与运行 24 个
r - 时间顺序(小时)
我试过: seq( from=as.POSIXct("2012-1-1 0", tz="UTC"), to=as.POSIXct("2012-1-3 23", tz="UTC"),
sql - SQL按日期分组(小时)
我有一个带有“日期”列的表。我想按小时分组指定日期。最佳答案 Select TO_CHAR(date,'HH24') from table where date = TO_DATE('2011022
sql - 小时、月份等的开始
我知道如何在 SQL (SQL Server) 中获取当前日期，但要获取当天的开始时间: select dateadd(DAY, datediff(day, 0, getdate()),0) (res
游戏结束后快速倒计时 + 1 小时
我正在尝试在游戏之间创建一个计时器，以便用户在失去生命后必须等待 5 分钟才能再次玩游戏。但是我不确定最好的方法是什么。我还需要它来防止用户在“设置”中编辑他们的时间。实现这一目标的最佳方法是什么
MySQL - 小时(日期时间)
我的查询有误。该错误显示预期的已知函数，得到“HOUR”。如果我删除这部分，查询将正常工作 (AND HOUR({$nowDate}) = 11) SELECT c FROM ProConvocati
javascript - 从javascript中的字符串中获取差异(小时)
var d1 = new Date(); var d2 = new Date(); d2.setHours(d1.getHours() +01); alert(d2); 这部分没问题。现在我试图在 (
ios - 为什么时间不自动更改为最新的分钟/小时？
我正在构建一个用于练习的基本时钟应用程序，但出于某种原因，时间不会自动更改为最新的分钟或小时。例如，当前时间是 17:56，但它显示的是 17:54，这是我打开应用程序的最后时间。 NSDate *n
html - 部分下划线(小时)
我创建了一张图片，我想将其用作页面的 hr。当它被上传时，它一直向左对齐。我希望它居中，在标题下。这是我的 CSS 代码: .section-underline { height: 35px
javascript - 如何使用毫秒计算超过一秒/小时？
这个问题已经有答案了: Getting difference in seconds from two dates in JavaScript (2 个回答) 已关闭 4 年前。我想计算两个具有不同格
php - 显示特定时间之前的天/小时
我需要计算到某个日期/时间的剩余时间(天/小时)。但是，我没有使用静态日期。假设我在每个星期日的 17:00 有一个事件。我需要显示到下一个事件的剩余时间，即即将到来的星期日 17:00。我
MySQL - -838 小时？
我正在执行这个脚本: SELECT EXTRACT(HOUR FROM TIMEDIFF('2009-12-12 13:13:13', NOW())); 我得到:-838。这是提取时 MySQL 可以
javascript 小时:分钟的正则表达式
复制代码代码如下: /** * 小时:分钟的正则表达式检查<br> * <br> * @param pInput 要检查的字符串 * @return boolean 返
WiFi名称个性好听沙雕连wifi5元/小时
连wifi5元/小时独领风骚朕好帅今晚你是我的人十里桃花高端定制厕所VP专用一只老母猪在家好无聊你爹的wifi 密码是叫爸爸全拼关晓彤和鹿晗分手了吗蹭了我的
python - 如何将以天、小时、分钟和秒为单位的时间转换为仅以秒为单位
我有以下数据框列: 我需要将 csv 列中的对象字符串数据转换为总秒数。示例:10m -> 600s 我试过这段代码: df.duration = str(datetime.timedelta(df

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

python - pandas 按小时分组并计算缺货时间