如何使用带有 Pandas 的时间戳按小时对数据帧进行分组

2022-01-13 00:00:00 python pandas dataframe pandas-groupby timestamp

问题描述

我有以下使用时间戳索引的数据帧结构:

I have the following dataframe structure that is indexed with a timestamp:

    neg neu norm    pol pos date
time                        
1520353341  0.000   1.000   0.0000  0.000000    0.000   
1520353342  0.121   0.879   -0.2960 0.347851    0.000   
1520353342  0.217   0.783   -0.6124 0.465833    0.000

我根据时间戳创建一个日期:

I create a date from the timestamp:

data_frame['date'] = [datetime.datetime.fromtimestamp(d) for d in data_frame.time]

结果:

    neg neu norm    pol pos date
time                        
1520353341  0.000   1.000   0.0000  0.000000    0.000   2018-03-06 10:22:21
1520353342  0.121   0.879   -0.2960 0.347851    0.000   2018-03-06 10:22:22
1520353342  0.217   0.783   -0.6124 0.465833    0.000   2018-03-06 10:22:22

我想按小时分组，同时获得除时间戳以外的所有值的平均值，应该是小时小组开始的地方.所以这是我要归档的结果:

I want to group by hour, while getting the mean for all the values, except the timestamp, that should be the hour from where the group started. So this is the result I want to archive:

    neg neu norm    pol pos
time                    
1520352000  0.027989    0.893233    0.122535    0.221079    0.078779
1520355600  0.028861    0.899321    0.103698    0.209353    0.071811

到目前为止，我得到的最接近的是这个 回答:

The closest I have gotten so far has been with this answer: 

data = data.groupby(data.date.dt.hour).mean()

结果:

    neg neu norm    pol pos
date                    
0   0.027989    0.893233    0.122535    0.221079    0.078779
1   0.028861    0.899321    0.103698    0.209353    0.071811

但我不知道如何保留考虑到 grouby 开始时间的时间戳.

But I cant figure out how to keep the timestamp that takes in account he hour where the grouby started.

解决方案

我遇到了这个 gem，pd.DataFrame.resample，在我发布了按小时计算的解决方案之后.

I came across this gem, pd.DataFrame.resample, after I posted my round-to-hour solution.

# Construct example dataframe
times = pd.date_range('1/1/2018', periods=5, freq='25min')
values = [4,8,3,4,1]
df = pd.DataFrame({'val':values}, index=times)

# Resample by hour and calculate medians
df.resample('H').median()

或者你可以使用 groupby 与 Grouper 如果您不想将时间作为索引:

Or you can use groupby with Grouper if you don't want times as index:

df = pd.DataFrame({'val':values, 'times':times})
df.groupby(pd.Grouper(level='times', freq='H')).median()

相关文章