行和列

2024-04-25 15:19:10 发布

您现在位置：Python中文网/ 问答频道 /正文
3211 3

我有一个包含各种日期的csv文件。如何计算每天每小时的事件数？输出应该有行（表示天）和列（表示小时）。你知道吗


Tags： 文件csv事件小时
1条回答
网友
1楼 · 发布于 2024-04-25 15:19:10

一种方法是创建binned事件计数，然后扩展生成的DataFrame以覆盖整个月份，并pivot_table以生成所需的行/列格式。假设您的DataFrame被称为df，则如下所示：

df['datetime'] = pd.to_datetime(df.REGIS_DATE + ' ' + df.REGIS_TIME, dayfirst=True)
index = pd.date_range('1/1/2011', periods=24*31, freq='H')
events = df.set_index('datetime').resample('H').count().REGIS_DATE.reindex(index, fill_value=0)
pd.DataFrame(events).pivot_table(index=events.index.date, columns=events.index.hour)

示例输入df：

   REGIS_DATE REGIS_TIME
0  20/01/2011   23:07:42
1  26/01/2011   12:14:35
2  11/01/2011   14:34:29
3  11/01/2011   17:53:17
4  29/01/2011   11:02:32

相应输出：

           REGIS_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
2011-01-01      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-02      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-03      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-04      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-05      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-06      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-07      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-08      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-09      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-10      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-11      0  0  0  0  0  0  0  0  0  0  0  0  0  0  1  0  0  1  0  0  0  0  0  0
2011-01-12      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-13      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-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
2011-01-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
2011-01-16      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-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
2011-01-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
2011-01-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
2011-01-20      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  1
2011-01-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
2011-01-22      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-23      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-24      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-25      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-26      0  0  0  0  0  0  0  0  0  0  0  0  1  0  0  0  0  0  0  0  0  0  0  0
2011-01-27      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-28      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-29      0  0  0  0  0  0  0  0  0  0  0  1  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-30      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
2011-01-31      0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0

相关问题 更多 >

    编程相关推荐