我有一個表df1，它由多個時間序列組成，由不同的ID. 我想ID根據另一個表中的開始和結束日期對每個時間序列重新采樣df2。df1并且df2是如下：
df1:
Index Timestamp Data ID
0 1 2010-03-04 13:16:44.310 125.0 1
4 6 2010-03-04 13:17:01.777 130.0 1
5 7 2010-03-04 13:17:01.943 135.0 1
12 16 2010-03-04 13:19:19.997 135.0 1
16 21 2010-03-04 13:19:27.047 135.0 1
... ... ... ... ... ...
45863344 45871285 2010-11-30 17:07:54.730 126.0 26
45863345 45871286 2010-11-30 17:08:00.367 125.5 26
45883410 45892266 2010-12-01 15:03:11.587 125.5 26
45883411 45892267 2010-12-01 15:03:12.587 145.0 26
45883619 45892475 2010-12-01 15:25:04.097 185.0 26
df2:
End Date Start Date ID Name ...
0 2010-12-03 2010-11-23 1 AA01 ...
1 2010-04-07 2010-03-28 26 BB10 ...
... ... ... ... ... ...
我對時間序列重新采樣，使其每分鐘有一個日期點，從2010-01-01到2010-01-11每個10 天ID，這可以通過以下方法實作：
start = '2010-01-01'
end = '2010-01-11'
def f(x):
r = pd.date_range(start=start, end = end, freq='1min')
return x.reindex(r, method='ffill').bfill()
df_sub = (df1
.set_index('Timestamp')
.groupby('ID', sort=False)['Data']
.apply(f)
.rename_axis(['ID','Timestamp'])
.reset_index()
)
但是，這是基于相同的開始和結束日期2010-01-01，并2010-01-11為所有ID。有沒有一種方法，其將從不同的開始和結束日期df2為每個ID，例如，對于ID 1我只能提取之間的時間序列2010-11-23和2010-12-03，并ID 26只對之間的時間序列2010-03-28和2010-04-07？
輸出將如下所示：
ID Timestamp Data
0 1 2010-12-03 00:00:00 125.5
1 1 2010-12-03 00:01:00 125.5
2 1 2010-12-03 00:02:00 185.5
3 1 2010-12-03 00:03:00 225.5
4 1 2010-12-03 00:04:00 215.5
... ... ... ... ...
2167409 26 2020-12-09 23:55:00 125.0
2167410 26 2010-12-09 23:56:00 135.0
2167411 26 2010-12-09 23:57:00 145.0
2167412 26 2010-12-09 23:58:00 125.0
... ... ... ... ...
重現示例：
df1:
from pandas import Timestamp
df1 = pd.DataFrame({'Index': {(2, 1): 2,
(2, 6): 8,
(2, 37): 47,
(2, 81): 92,
(2, 88): 101,
(2, 132): 146,
(2, 139): 155,
(2, 436): 453,
(2, 545): 564,
(2, 816): 835,
(10, 172): 188,
(10, 450): 469,
(10, 565): 584,
(10, 830): 849,
(10, 1000): 1019,
(10, 271312): 271331,
(10, 271313): 271332,
(10, 271314): 271333,
(10, 271315): 271334,
(10, 271316): 271335,
(120, 1614): 1633,
(120, 1665): 1684,
(120, 1666): 1685,
(120, 1733): 1752,
(120, 1734): 1753,
(120, 1835): 1854,
(120, 1836): 1855,
(120, 1957): 1976,
(120, 1958): 1977,
(120, 2091): 2110},
'Timestamp': {(2, 1): Timestamp('2014-03-04 13:16:44.310000'),
(2, 6): Timestamp('2014-03-04 13:17:01.777000'),
(2, 37): Timestamp('2014-04-17 11:59:57.470000'),
(2, 81): Timestamp('2014-04-17 12:01:08.973000'),
(2, 88): Timestamp('2014-04-17 12:05:55.153000'),
(2, 132): Timestamp('2014-04-17 12:08:58.933000'),
(2, 139): Timestamp('2014-04-17 12:35:58.290000'),
(2, 436): Timestamp('2014-04-17 12:41:42.147000'),
(2, 545): Timestamp('2014-04-17 12:46:14.450000'),
(2, 816): Timestamp('2014-04-17 13:05:53.077000'),
(10, 172): Timestamp('2014-04-17 12:35:58.633000'),
(10, 450): Timestamp('2014-04-17 12:41:42.067000'),
(10, 565): Timestamp('2014-04-17 12:46:14.747000'),
(10, 830): Timestamp('2014-04-17 13:05:53.153000'),
(10, 1000): Timestamp('2014-04-17 13:10:20.127000'),
(10, 271312): Timestamp('2014-05-13 14:59:44.627000'),
(10, 271313): Timestamp('2014-05-13 14:59:44.780000'),
(10, 271314): Timestamp('2014-05-13 14:59:45.600000'),
(10, 271315): Timestamp('2014-05-13 14:59:45.757000'),
(10, 271316): Timestamp('2014-05-13 14:59:46.687000'),
(120, 1614): Timestamp('2014-04-17 15:39:52.673000'),
(120, 1665): Timestamp('2014-04-17 15:46:41.260000'),
(120, 1666): Timestamp('2014-04-17 15:46:41.417000'),
(120, 1733): Timestamp('2014-04-17 16:07:54.657000'),
(120, 1734): Timestamp('2014-04-17 16:07:54.817000'),
(120, 1835): Timestamp('2014-04-17 16:23:59.943000'),
(120, 1836): Timestamp('2014-04-17 16:24:00.103000'),
(120, 1957): Timestamp('2014-04-17 16:53:00.543000'),
(120, 1958): Timestamp('2014-04-17 16:53:00.703000'),
(120, 2091): Timestamp('2014-04-17 17:29:21.163000')},
'Data': {(2, 1): 30.0,
(2, 6): 30.0,
(2, 37): 25.0,
(2, 81): 25.0,
(2, 88): 25.0,
(2, 132): 25.0,
(2, 139): 25.0,
(2, 436): 25.0,
(2, 545): 25.0,
(2, 816): 25.0,
(10, 172): 25.0,
(10, 450): 25.0,
(10, 565): 25.0,
(10, 830): 25.0,
(10, 1000): 25.0,
(10, 271312): 25.0,
(10, 271313): 27.5,
(10, 271314): 27.5,
(10, 271315): 30.5,
(10, 271316): 30.5,
(120, 1614): 31.0,
(120, 1665): 30.5,
(120, 1666): 30.0,
(120, 1733): 29.5,
(120, 1734): 29.0,
(120, 1835): 28.5,
(120, 1836): 28.0,
(120, 1957): 27.5,
(120, 1958): 27.0,
(120, 2091): 26.5},
'ID': {(2, 1): 2,
(2, 6): 2,
(2, 37): 2,
(2, 81): 2,
(2, 88): 2,
(2, 132): 2,
(2, 139): 2,
(2, 436): 2,
(2, 545): 2,
(2, 816): 2,
(10, 172): 10,
(10, 450): 10,
(10, 565): 10,
(10, 830): 10,
(10, 1000): 10,
(10, 271312): 10,
(10, 271313): 10,
(10, 271314): 10,
(10, 271315): 10,
(10, 271316): 10,
(120, 1614): 120,
(120, 1665): 120,
(120, 1666): 120,
(120, 1733): 120,
(120, 1734): 120,
(120, 1835): 120,
(120, 1836): 120,
(120, 1957): 120,
(120, 1958): 120,
(120, 2091): 120}
})
df2:
df2 = pd.DataFrame({'ID': {8: 10, 9: 2, 116: 120},
'Start Date': {8: Timestamp('2014-04-20 00:00:00'),
9: Timestamp('2014-03-04 00:00:00'),
116: Timestamp('2014-04-17 00:00:00')},
'End Date': {8: Timestamp('2014-04-30 00:00:00'),
9: Timestamp('2014-03-14 00:00:00'),
116: Timestamp('2014-04-27 00:00:00')},
'comment': {8: 'TBA', 9: 'TBA', 116: 'TBA'},
'Name': {8: 'NN95', 9: 'AA01', 116: 'BB10'}})
df2
uj5u.com熱心網友回復：
我認為這應該有效。只需加入df1并df2獲取每個ID. 告訴我是否我錯過了重點。
def f(sub_df):
r = pd.date_range(start=sub_df['Start Date'].iloc[0], end=sub_df['End Date'].iloc[0], freq='1min')
return sub_df.reindex(r, method='ffill').bfill()['Data']
df_sub = (pd.merge(df1, df2, on='ID')
.set_index('Timestamp')
.groupby('ID', sort=False)
.apply(f)
.rename_axis(['ID','Timestamp'])
.reset_index()
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/362970.html