DataFrame在日期范围上重新采样 [英] DataFrame resample on date ranges
问题描述
我有一个包含列的DataFrame 开始时间"(日期时间),结束时间"(日期时间),模式"和其他一些列. 该表的不同行的范围内没有重叠.
I have a DataFrame that has the columns 'start_time' (datetime), 'end_time' (datetime), 'mode' and some other columns. There is no overlap in the ranges of different rows of the table.
我想创建一个新的DataFrame,对原始DataFrame的每一行进行重新采样,如下所示: 'current_time','mode',其他列
I would like to create a new DataFrame, that resamples each row of the original DataFrame like so: 'current_time', 'mode', other columns
"current_time"是在原始"start_time"和"end_time"之间以给定频率进行的重采样,而所有其他列只是原始表中值的副本.
Where 'current_time' is a resample between original 'start_time' and 'end_time' with a given frequency, and all other columns are just copies of values from the original table.
示例: 原始DataFrame:
Example: original DataFrame:
start_time end_time mode
2017-06-01 06:38:00.000 2017-06-01 06:39:00.000 x
2017-06-01 17:22:00.000 2017-06-01 17:22:30.000 y
对于给定的"10S"频率,我想获取以下DataFrame:
For a given 'freq' of '10S', I'd like to get the following DataFrame:
current_time mode
2017-06-01 06:38:00.000 x
2017-06-01 06:38:10.000 x
2017-06-01 06:38:20.000 x
2017-06-01 06:38:30.000 x
2017-06-01 06:38:40.000 x
2017-06-01 06:38:50.000 x
2017-06-01 17:22:00.000 y
2017-06-01 17:22:10.000 y
2017-06-01 17:22:20.000 y
我正在寻找一种合理有效且优雅的方法.
I'm looking for a reasonably efficient and elegant way of doing this.
非常感谢!
推荐答案
您可以使用:
#convert columns to datetimes if necessary
df['start_time']= pd.to_datetime(df['start_time'])
df['end_time']= pd.to_datetime(df['end_time'])
#subtract 10s for no last row from values from end_time column
df['end_time']= df['end_time'] - pd.Timedelta(10, unit='s')
#loop by list comprehension for list of date ranges
#concat to one big DataFrame
df1 = (pd.concat([pd.Series(r.Index,
pd.date_range(r.start_time, r.end_time, freq='10S'))
for r in df.itertuples()])
.reset_index())
df1.columns = ['current_time','idx']
print (df1)
current_time idx
0 2017-06-01 06:38:00 0
1 2017-06-01 06:38:10 0
2 2017-06-01 06:38:20 0
3 2017-06-01 06:38:30 0
4 2017-06-01 06:38:40 0
5 2017-06-01 06:38:50 0
6 2017-06-01 17:22:00 1
7 2017-06-01 17:22:10 1
8 2017-06-01 17:22:20 1
根据OP的评论进行
如果使用参数closed=left
:
pd.date_range(r.start_time, r.end_time, freq='10S', closed='left')
那么可以省略减法.
#join all another columns by index
df2 = df1.set_index('idx').join(df.drop(['start_time','end_time'], 1)).reset_index(drop=True)
print (df2)
current_time mode
0 2017-06-01 06:38:00 x
1 2017-06-01 06:38:10 x
2 2017-06-01 06:38:20 x
3 2017-06-01 06:38:30 x
4 2017-06-01 06:38:40 x
5 2017-06-01 06:38:50 x
6 2017-06-01 17:22:00 y
7 2017-06-01 17:22:10 y
8 2017-06-01 17:22:20 y
另一种解决方案:
#create column from index for last join (index values has to be unique)
df = df.reset_index()
#reshape dates to datetimeindex
df1 = (df.melt(df.columns.difference(['start_time','end_time']),
['start_time', 'end_time'],
value_name='current_time')
.drop('variable', 1)
.set_index('current_time'))
print (df1)
index mode
current_time
2017-06-01 06:38:00 0 x
2017-06-01 17:22:00 1 y
2017-06-01 06:38:50 0 x
2017-06-01 17:22:20 1 y
#group by index column and resample, NaNs are replaced by forward filling
df2 = df1.groupby('index').resample('10S').ffill().reset_index(0, drop=True).drop('index', 1)
print (df2)
mode
current_time
2017-06-01 06:38:00 x
2017-06-01 06:38:10 x
2017-06-01 06:38:20 x
2017-06-01 06:38:30 x
2017-06-01 06:38:40 x
2017-06-01 06:38:50 x
2017-06-01 17:22:00 y
2017-06-01 17:22:10 y
2017-06-01 17:22:20 y
这篇关于DataFrame在日期范围上重新采样的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!