Pandas 将时间序列数据重新采样为 15 分钟和 45 分钟 - 使用多索引或列 [英] Pandas resample timeseries data to 15 mins and 45 mins - using multi-index or column
问题描述
我有一些时间序列数据作为 Pandas 数据框，从一小时过去 15 分钟和过去 45 分钟(时间间隔为 30 分钟)的观察开始，然后将频率更改为每分钟.我想对数据重新采样，以便它在整个数据帧的每 30 分钟、过去 15 小时和过去 45 小时有规律的频率.
I have some timeseries data as a Pandas dataframe which starts off with observations at 15 mins past the hour and 45 mins past (time intervals of 30 mins) then changes frequency to every minute. I want to resample the data so that it has a regular frequency of every 30 minutes, at 15 past and 45 past the hours for the whole dataframe.
我想到了两种方法来实现这一点.
1. 使用时间序列数据作为数据帧中的一列，简单地过滤所有 15 分钟和 45 分钟观测值的数据帧.
2. 重新设置索引，使时间序列数据成为多索引的一部分(索引的第 0 级是气象站，第 1 级是观测时间)并使用 Pandas 日期时间时间序列resample() 等功能.
I thought of two ways of achieving this.
1. Simply filter the dataframe for all observations at 15min and 45min, using the time-series data as a column in the dataframe.
2. Re-set the index so the time-series data is part of a multi-index (the 0th level of the index is the weather station, the 1st level is the time of the observation) and use the Pandas date-time timeseries functionality such as resample().
原始数据框，天气，看起来像这样:
The original dataframe, weather, looks like this:
parsed_time Pressure Temp Hum
Station (index)
Bow 1 2018-04-15 14:15:00 1012 20.0 87
2 2018-04-15 14:45:00 1013 20.0 87
3 2018-04-15 15:15:00 1012 21.0 87
4 2018-04-15 15:45:00 1014 22.0 86
5 2018-04-15 16:00:00 1015 22.0 86
6 2018-04-15 16:01:00 1012 25.0 86
7 2018-04-15 16:02:00 1012 25.0 86
Stratford 8 2018-04-15 14:15:00 1011 18.0 87
9 2018-04-15 14:45:00 1011 18.0 87
10 2018-04-15 15:15:00 1012 18.0 87
11 2018-04-15 15:45:00 1014 19.0 86
12 2018-04-15 16:00:00 1014 19.0 86
13 2018-04-15 16:01:00 1015 19.0 86
14 2018-04-15 16:02:00 1016 20.0 86
15 2018-04-15 16:04:00 1016 20.0 86
使用方法 1，我遇到的问题是我的布尔选择操作似乎没有按预期工作.例如
With method 1, I have the problem that my boolean select operations don't seem to work as expected. For example
weather_test = weather[weather['parsed_time'].dt.minute == (15 & 45)]
给出这样的 parsed_time 值:
gives parsed_time values like this:
2018-04-15 14:13:002018-04-15 15:13:00
weather_test = weather[weather['parsed_time'].dt.minute == (15 | 45)]
导致 parsed_time 值如下:
results in parsed_time values like this:
2018-04-15 14:47:002018-04-15 14:47:00
我在文档中找不到任何内容来解释这种行为.我想要的是以下时间各站的压力、温度、湿度:
I can't find anything in the docs to explain this behaviour. What I want is pressure, temp, humidity by station at the following times:
2018-04-15 14:45:00
2018-04-15 15:15:00
2018-04-15 15:45:00
2018-04-15 16:15:00
等等.
使用方法 2，我想对数据进行重新采样，以便将我拥有逐分钟数据的观测值替换为前 30 分钟的平均值.此功能似乎仅在 parsed_time 列是索引的一部分时才起作用，因此我使用以下代码将 parsed_time 设置为多索引的一部分:
With method 2, I thought of resampling the data so that observations for which I have minute-by-minute data are replaced by the mean of the previous 30 minutes. This functionality only seems to work if the parsed_time column is part of the index, so I used the following code to set the parsed_time as part of a multi-index:
weather.set_index('parsed_time', append=True, inplace=True)
weather.index.set_names('station', level=0, inplace=True)
weather = weather.reset_index(level=1, drop=True)
最终得到如下所示的数据框:
to end up with a dataframe that looks like this:
Pressure Temp Hum
Station parsed_time
Bow 2018-04-15 14:15:00 1012 20.0 87
2018-04-15 14:45:00 1013 20.0 87
2018-04-15 15:15:00 1012 21.0 87
2018-04-15 15:45:00 1014 22.0 86
2018-04-15 16:00:00 1015 22.0 86
2018-04-15 16:01:00 1012 25.0 86
2018-04-15 16:02:00 1012 25.0 86
Stratford 2018-04-15 14:15:00 1011 18.0 87
2018-04-15 14:45:00 1011 18.0 87
2018-04-15 15:15:00 1012 18.0 87
2018-04-15 15:45:00 1014 19.0 86
2018-04-15 16:00:00 1014 19.0 86
2018-04-15 16:01:00 1015 19.0 86
2018-04-15 16:02:00 1016 20.0 86
2018-04-15 16:04:00 1016 20.0 86
请注意，观察的抽样从每 30 分钟过去 :15 和过去 :45 到每分钟不等(例如 :01、:02、:14 等)，并且也因站点而异 - 并非所有站点都有每一次观察.
Note that the sampling of observations varies from every 30 minutes at :15 past and :45 past to every minute (e.g. :01, :02, :14, etc), and it also varies by station - not all stations have every observation.
我试过了:
weather_test = weather.resample('30min', level=1).mean()
但这会在没有偏移的情况下重新采样，并且还摆脱了多索引中的站点级别.
but this resamples without an offset and also gets rid of the station level in the multi-index.
想要的结果是这样的:
Pressure Temp Hum
Station parsed_time
Bow 2018-04-15 14:15:00 1012 20.0 87
2018-04-15 14:45:00 1013 20.0 87
2018-04-15 15:15:00 1012 21.0 87
2018-04-15 15:45:00 1014 22.0 86
2018-04-15 16:15:00 1013 24.0 86
Stratford 2018-04-15 14:15:00 1011 18.0 87
2018-04-15 14:45:00 1011 18.0 87
2018-04-15 15:15:00 1012 18.0 87
2018-04-15 15:45:00 1014 19.0 86
2018-04-15 16:15:00 1015 19.5 86
每分钟的观察值已被重新采样为 30 分钟间隔内的平均值，时间为 :15 和 :45.
where the minute-by-minute observations have been resampled as the mean over a 30-minute interval at :15 and :45 past the hour.
将站点保持在多指标中的级别至关重要.我不能将时间索引单独用作索引，因为每个站的值都会重复(并且不是唯一的).
Keeping the station as a level in the multi-index is essential. I can't use the time index as an index on its own as the values repeat for each station (and are not unique).
感谢所有帮助，因为我已经和这个人循环了一段时间了.谢谢！
All help appreciated as I have been going round in circles with this one for a while now. Thanks!
我看了很多以前的帖子，包括:布尔过滤器在数据帧上使用时间戳值蟒蛇
如何将日期时间列四舍五入到最接近的四分之一小时
和:使用包含时间序列的多索引重新采样熊猫数据帧对于应该非常简单的事情来说，这似乎有点复杂......
I have looked at quite a few previous posts including:
Boolean filter using a timestamp value on a dataframe in Python
How do I round datetime column to nearest quarter hour
and: Resampling a pandas dataframe with multi-index containing timeseries
which seems a bit complicated for something that should be quite simple ...
和文档:http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.resample.html谢谢！
推荐答案
从倒数第二个数据帧开始(使用 weather.reset_index(Station, inplace=True) 后):
Starting from your second last dataframe (after using weather.reset_index(Station, inplace=True)):
Station Pressure Temp Hum
parsed_time
2018-04-15 14:15:00 Bow 1012.0 20.0 87.0
2018-04-15 14:45:00 Bow 1013.0 20.0 87.0
2018-04-15 15:15:00 Bow 1012.0 21.0 87.0
2018-04-15 15:45:00 Bow 1014.0 22.0 86.0
2018-04-15 16:00:00 Bow 1015.0 22.0 86.0
2018-04-15 16:01:00 Bow 1012.0 25.0 86.0
2018-04-15 16:02:00 Bow 1012.0 25.0 86.0
2018-04-15 14:15:00 Stratford 1011.0 18.0 87.0
2018-04-15 14:45:00 Stratford 1011.0 18.0 87.0
2018-04-15 15:15:00 Stratford 1012.0 18.0 87.0
2018-04-15 15:45:00 Stratford 1014.0 19.0 86.0
2018-04-15 16:00:00 Stratford 1014.0 19.0 86.0
2018-04-15 16:01:00 Stratford 1015.0 19.0 86.0
2018-04-15 16:02:00 Stratford 1016.0 20.0 86.0
2018-04-15 16:04:00 Stratford 1016.0 20.0 86.0
您可以结合使用 groupby 和 resample:
you could use a combination of groupby and resample:
res = weather.groupby('Station').resample('30min').mean().reset_index('Station')
默认情况下，resample 选择 bin 间隔 [16:00, 16:30) 和 [16:30, 17:00).正如您已经注意到的，时间索引是在没有偏移的情况下重新采样的，但您可以在之后使用 DateOffset:
By default, resample chooses the bin intervals [16:00, 16:30) and [16:30, 17:00). As you already noticed, the time index is resampled without an offset, but you can add it back afterwards using DateOffset:
res.index = res.index + pd.DateOffset(minutes=15)
产生:
Station Pressure Temp Hum
parsed_time
2018-04-15 14:15:00 Bow 1012.00 20.0 87.0
2018-04-15 14:45:00 Bow 1013.00 20.0 87.0
2018-04-15 15:15:00 Bow 1012.00 21.0 87.0
2018-04-15 15:45:00 Bow 1014.00 22.0 86.0
2018-04-15 16:15:00 Bow 1013.00 24.0 86.0
2018-04-15 14:15:00 Stratford 1011.00 18.0 87.0
2018-04-15 14:45:00 Stratford 1011.00 18.0 87.0
2018-04-15 15:15:00 Stratford 1012.00 18.0 87.0
2018-04-15 15:45:00 Stratford 1014.00 19.0 86.0
2018-04-15 16:15:00 Stratford 1015.25 19.5 86.0
或者，您可以直接在 resample 方法中指定偏移量:
Alternatively, you could specifiy the offset directly in the resample method:
weather.groupby('Station').resample('30min', loffset=pd.Timedelta('15min')).mean()
这篇关于Pandas 将时间序列数据重新采样为 15 分钟和 45 分钟 - 使用多索引或列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
