I have a table df1 which consists of multiple time series represented by different ID. I want to resample the time series for each ID based on the start and end dates in another table df2. df1 and df2 are as below:
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 ...
... ... ... ... ... ...
I resampled the time series to have one date point per minute for a period of 10 days from 2010-01-01 to 2010-01-11 for each ID, which could be achieved with method below:
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()
)
But this is based on the same start and end date of 2010-01-01 and 2010-01-11 for all ID. Is there a way to introduce different start and end date from df2 for each ID, for example, for ID 1 I only extract time series between 2010-11-23 and 2010-12-03, and for ID 26 only for time series between 2010-03-28 and 2010-04-07?
The output would look like this:
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
... ... ... ... ...
Reproduciple example:
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
CodePudding user response：
I think this should work. Just join df1 and df2 to obtain the start and end dates for each ID. Tell me if I missed the point.
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()
)