根据Excel中的占用率提取每小时分钟数

Question

是否有一种简单的方法可以根据入住率提取房间每小时使用的分钟数？我想大致了解一下从08:00:00到08:59:59、09:00:00-09:59:59……房间1的使用时间

我手动创建了从fex 08:00:00开始到08:59:59结束的每小时的时间间隔。然后我使用了一个sumif公式来计算一天中房间每小时被占用的分钟数（每天总共9小时）

因为我想看看每小时有多少分钟不同的房间被占用并比较它们，我想知道是否有更简单的方法来做到这一点？如果有一个我可以用于所有房间的格式，那就太好了。但是，由于所有房间都有不同的时间戳，这可能很困难

如果有人知道如何在SQL或Python中实现这一点，那也会非常有帮助，尤其是在SQL中

下面的链接将为您提供一个数据示例

Answer 1

作为起点：

SELECT
    room_name, sum(start-stop)
FROM 
    room_table
WHERE 
    timestamp BETWEEN 'some_time' AND 'another_time'
GROUP BY
    room_name

其中，在上面的示例中，SQL表是room_table。还假定start和stop字段是time类型。“某个时间/另一个时间”只是您感兴趣的时间范围的占位符

Answer 2

在python中，与电子表格或SQL表最相似的数据结构是来自pandas库的DataFrame

首先，我们可以从电子表格中读取数据，如下所示：

import pandas as pd

df = pd.read_excel("<your filename>", parse_dates=[1])

df["Time"] = df.Timestamp.dt.time

在这里，我假设您已经删除了正在进行的工作（图中右侧的表），并且数据位于Excel文件的第一个工作表中（否则我们将不得不传递其他选项）

我已经确保第一列（Timestamp）正确理解为包含日期时间数据。默认情况下，它将假定09.01.2020 ...指的是美国式的9月1日——我猜这就是你想要的；如果你真的指的是1月9日（我就是这么看的），那么可以通过其他选项

然后，我用从Timestamp中提取的time对象重写了Time列，这实际上并不是必需的，但会使数据尽可能接近电子表格中的数据。数据帧现在看起来如下所示：

            Timestamp Room name  Occupancy %      Time
0 2020-09-01 08:04:01    Room 1            0  08:04:01
1 2020-09-01 09:04:01    Room 1          100  09:04:01
2 2020-09-01 09:19:57    Room 1            0  09:19:57
3 2020-09-01 09:48:57    Room 1            0  09:48:57
4 2020-09-01 09:53:01    Room 1          100  09:53:01
5 2020-09-01 10:05:01    Room 1          100  10:05:01
6 2020-09-01 10:08:57    Room 1          100  10:08:57
7 2020-09-01 10:13:01    Room 1          100  10:13:01

（注意，下一次，最好在你的问题中加入类似于本文的内容，如果不需要费劲地将数据组合在一起，那么构建答案就容易多了）

现在，我们可以用这样的数据帧做很多事情，但我将尝试尽可能直接地到达您想要去的地方

我们将首先使用Timestamp列作为“索引”，并为时间08:00:00预加一行，因为它当前不是数据集的一部分，但您表示需要它

df2 = df.set_index("Timestamp")

df2.loc[pd.Timestamp("09.01.2020 08:00:00")] = ("Room1", 0.0, None)

df2.sort_index(inplace=True)

结果如下所示：

                    Room name  Occupancy %      Time
Timestamp                                           
2020-09-01 08:00:00    Room 1          0.0      None
2020-09-01 08:04:01    Room 1          0.0  08:04:01
2020-09-01 09:04:01    Room 1        100.0  09:04:01
2020-09-01 09:19:57    Room 1          0.0  09:19:57
2020-09-01 09:48:57    Room 1          0.0  09:48:57
2020-09-01 09:53:01    Room 1        100.0  09:53:01
2020-09-01 10:05:01    Room 1        100.0  10:05:01
2020-09-01 10:08:57    Room 1        100.0  10:08:57
2020-09-01 10:13:01    Room 1        100.0  10:13:01

现在，最简单的方法是从上采样开始，然后向前填充数据

upsampled = df2.resample("1min").ffill()

upsampled是一个巨大的数据帧，在该范围内每秒钟有一个值。向前填充确保您的入住率每秒向前移动一次，直到您的一个原始数据点显示“此处已更改”。更改后，新值将结转至下一个数据点等

这样做是为了确保我们获得必要的时间分辨率。通常我现在会减少样本。您对每个小时都感兴趣：

downsampled = upsampled.resample("1h").mean()

通过取平均值，我们将只得到输出中的数字列，即“占用率”，在这里您将得到以下结果：

                     Occupancy %
Timestamp                       
2020-09-01 08:00:00     0.000000
2020-09-01 09:00:00    38.194444
2020-09-01 10:00:00   100.000000

但是您表示可能希望“每个房间”执行此操作，因此可能会有其他数据，例如“房间2”。在这种情况下，我们有一个分类列Room name，需要根据它进行分组

这有点难，因为这意味着我们必须在增加样本之前进行分组，以避免歧义。这将创建一个多索引。我们必须将指数的“组”水平崩溃，然后组和下样本

grouped = df.groupby("Room name", as_index=False).resample('1s').ffill()

grouped.index = grouped.index.get_level_values(1)

result = grouped.groupby("Room name").resample("1h").mean()

看起来是这样的：

                               Occupancy %
Room name Timestamp                       
Room 1    2020-09-01 08:00:00     0.000000
          2020-09-01 09:00:00    38.194444
          2020-09-01 10:00:00   100.000000
Room 2    2020-09-01 08:00:00     0.000000
          2020-09-01 09:00:00    38.194444
          2020-09-01 10:00:00   100.000000

（我刚刚将1号房间的数据复制为2号房间，所以数字相同）

为了简洁地完成，我们可以取消此多索引的堆栈，将房间名称旋转到列中。然后将这些百分比转换为最接近的分钟数

因此，整个解决方案是：

import pandas as pd

df = pd.read_excel("<your filename>", parse_dates=[1])

df2 = df.set_index("Timestamp")

# prepend some dummy rows for every different room name
for room_name in df2["Room name"].unique():
    df2.loc[pd.Timestamp("09.01.2020 08:00:00")] = (room_name, 0.0, None)


df2.sort_index(inplace=True)

grouped = df.groupby("Room name", as_index=False).resample('1s').ffill()

grouped.index = grouped.index.droplevel(0)

result = (
    grouped
        .groupby("Room name")
        .resample("1h")
        .mean()
        .unstack(level=0)
        .div(100)  # % -> fraction
        .mul(60)  # fraction -> minutes
        .astype(int)  # nearest number of whole minutes
)

# no longer 'Occupancy %', so drop the label
result.columns = result.columns.droplevel(0)  

产生类result的

Room name                Room 1 Room 2
Timestamp                             
2020-09-01 08:00:00           0      0
2020-09-01 09:00:00          22     22
2020-09-01 10:00:00          60     60

希望这和你想要的很接近