I'm creating two dataframes where I store minimum and maximum time series values which index is based on datetime. The two Dataframes, one for maximas and one for minimas, should each have only 3 entries. The problem I'm having is that I don't want there to be two maximas/minimas in a row, so every maxima index should be followed by a minima index.
Here is what i got so far:
import pandas as pd
from pandas import Timestamp
df=pd.Series({Timestamp('2020-11-30 00:35:00'): '0.03119', Timestamp('2020-11-30 00:40:00'): '0.03116', Timestamp('2020-11-30 00:45:00'): '0.03118', Timestamp('2020-11-30 00:50:00'): '0.03123', Timestamp('2020-11-30 00:55:00'): '0.03124', Timestamp('2020-11-30 01:00:00'): '0.03115', Timestamp('2020-11-30 01:05:00'): '0.03123', Timestamp('2020-11-30 01:10:00'): '0.03147', Timestamp('2020-11-30 01:15:00'): '0.03131', Timestamp('2020-11-30 01:20:00'): '0.03119', Timestamp('2020-11-30 01:25:00'): '0.03117', Timestamp('2020-11-30 01:30:00'): '0.03115', Timestamp('2020-11-30 01:35:00'): '0.03122', Timestamp('2020-11-30 01:40:00'): '0.03126', Timestamp('2020-11-30 01:45:00'): '0.03135', Timestamp('2020-11-30 01:50:00'): '0.03133', Timestamp('2020-11-30 01:55:00'): '0.03125', Timestamp('2020-11-30 02:00:00'): '0.03120', Timestamp('2020-11-30 02:05:00'): '0.03115', Timestamp('2020-11-30 02:10:00'): '0.03115', Timestamp('2020-11-30 02:15:00'): '0.03115', Timestamp('2020-11-30 02:20:00'): '0.03115', Timestamp('2020-11-30 02:25:00'): '0.03112', Timestamp('2020-11-30 02:30:00'): '0.03113', Timestamp('2020-11-30 02:35:00'): '0.03114', Timestamp('2020-11-30 02:40:00'): '0.03121', Timestamp('2020-11-30 02:45:00'): '0.03122', Timestamp('2020-11-30 02:50:00'): '0.03123', Timestamp('2020-11-30 02:55:00'): '0.03123', Timestamp('2020-11-30 03:00:00'): '0.03116', Timestamp('2020-11-30 03:05:00'): '0.03102', Timestamp('2020-11-30 03:10:00'): '0.03105', Timestamp('2020-11-30 03:15:00'): '0.03113', Timestamp('2020-11-30 03:20:00'): '0.03126', Timestamp('2020-11-30 03:25:00'): '0.03137', Timestamp('2020-11-30 03:30:00'): '0.03140', Timestamp('2020-11-30 03:35:00'): '0.03142', Timestamp('2020-11-30 03:40:00'): '0.03136', Timestamp('2020-11-30 03:45:00'): '0.03142', Timestamp('2020-11-30 03:50:00'): '0.03127', Timestamp('2020-11-30 03:55:00'): '0.03134', Timestamp('2020-11-30 04:00:00'): '0.03122', Timestamp('2020-11-30 04:05:00'): '0.03119', Timestamp('2020-11-30 04:10:00'): '0.03113', Timestamp('2020-11-30 04:15:00'): '0.03105', Timestamp('2020-11-30 04:20:00'): '0.03104', Timestamp('2020-11-30 04:25:00'): '0.03111', Timestamp('2020-11-30 04:30:00'): '0.03110', Timestamp('2020-11-30 04:35:00'): '0.03105', Timestamp('2020-11-30 04:40:00'): '0.03112', Timestamp('2020-11-30 04:45:00'): '0.03100', Timestamp('2020-11-30 04:50:00'): '0.03104', Timestamp('2020-11-30 04:55:00'): '0.03108', Timestamp('2020-11-30 05:00:00'): '0.03108', Timestamp('2020-11-30 05:05:00'): '0.03115', Timestamp('2020-11-30 05:10:00'): '0.03115', Timestamp('2020-11-30 05:15:00'): '0.03113', Timestamp('2020-11-30 05:20:00'): '0.03116', Timestamp('2020-11-30 05:25:00'): '0.03132', Timestamp('2020-11-30 05:30:00'): '0.03127', Timestamp('2020-11-30 05:35:00'): '0.03124', Timestamp('2020-11-30 05:40:00'): '0.03118', Timestamp('2020-11-30 05:45:00'): '0.03111', Timestamp('2020-11-30 05:50:00'): '0.03108', Timestamp('2020-11-30 05:55:00'): '0.03108', Timestamp('2020-11-30 06:00:00'): '0.03109', Timestamp('2020-11-30 06:05:00'): '0.03108', Timestamp('2020-11-30 06:10:00'): '0.03114', Timestamp('2020-11-30 06:15:00'): '0.03117', Timestamp('2020-11-30 06:20:00'): '0.03103', Timestamp('2020-11-30 06:25:00'): '0.03097', Timestamp('2020-11-30 06:30:00'): '0.03098', Timestamp('2020-11-30 06:35:00'): '0.03101', Timestamp('2020-11-30 06:40:00'): '0.03103', Timestamp('2020-11-30 06:45:00'): '0.03109', Timestamp('2020-11-30 06:50:00'): '0.03108', Timestamp('2020-11-30 06:55:00'): '0.03110', Timestamp('2020-11-30 07:00:00'): '0.03111', Timestamp('2020-11-30 07:05:00'): '0.03119', Timestamp('2020-11-30 07:10:00'): '0.03130', Timestamp('2020-11-30 07:15:00'): '0.03126', Timestamp('2020-11-30 07:20:00'): '0.03130', Timestamp('2020-11-30 07:25:00'): '0.03133', Timestamp('2020-11-30 07:30:00'): '0.03128', Timestamp('2020-11-30 07:35:00'): '0.03120', Timestamp('2020-11-30 07:40:00'): '0.03115', Timestamp('2020-11-30 07:45:00'): '0.03118', Timestamp('2020-11-30 07:50:00'): '0.03113', Timestamp('2020-11-30 07:55:00'): '0.03109', Timestamp('2020-11-30 08:00:00'): '0.03079', Timestamp('2020-11-30 08:05:00'): '0.03059', Timestamp('2020-11-30 08:10:00'): '0.03061', Timestamp('2020-11-30 08:15:00'): '0.03074', Timestamp('2020-11-30 08:20:00'): '0.03062', Timestamp('2020-11-30 08:25:00'): '0.03052', Timestamp('2020-11-30 08:30:00'): '0.03066', Timestamp('2020-11-30 08:35:00'): '0.03064', Timestamp('2020-11-30 08:40:00'): '0.03064', Timestamp('2020-11-30 08:45:00'): '0.03068', Timestamp('2020-11-30 08:50:00'): '0.03072', Timestamp('2020-11-30 08:55:00'): '0.03065', Timestamp('2020-11-30 09:00:00'): '0.03058', Timestamp('2020-11-30 09:05:00'): '0.03043', Timestamp('2020-11-30 09:10:00'): '0.03050', Timestamp('2020-11-30 09:15:00'): '0.03058', Timestamp('2020-11-30 09:20:00'): '0.03048', Timestamp('2020-11-30 09:25:00'): '0.03045', Timestamp('2020-11-30 09:30:00'): '0.03037', Timestamp('2020-11-30 09:35:00'): '0.03034', Timestamp('2020-11-30 09:40:00'): '0.03045', Timestamp('2020-11-30 09:45:00'): '0.03048', Timestamp('2020-11-30 09:50:00'): '0.03054', Timestamp('2020-11-30 09:55:00'): '0.03056', Timestamp('2020-11-30 10:00:00'): '0.03064', Timestamp('2020-11-30 10:05:00'): '0.03062', Timestamp('2020-11-30 10:10:00'): '0.03067', Timestamp('2020-11-30 10:15:00'): '0.03075', Timestamp('2020-11-30 10:20:00'): '0.03073', Timestamp('2020-11-30 10:25:00'): '0.03064', Timestamp('2020-11-30 10:30:00'): '0.03063'})
df=df.astype(float)
hi = df.dropna().copy()
lo = df.dropna().copy()
for i in range(2):
hi = hi.iloc[argrelmax(hi.values)[0]]
lo = lo.iloc[argrelmin(lo.values)[0]]
hi = hi[~hi.index.isin(lo.index)]
lo = lo[~lo.index.isin(hi.index)]
hig=pd.concat([hi.astype(float)tail(3)])
highs = pd.DataFrame({'Date':hig.index, 'Highs':hig.values})
highs=highs.set_index('Date')
los=pd.concat([lo.astype(float).tail(3)])
lows=pd.DataFrame({'Date':los.index, 'Lows':los.values})
lows=lows.set_index('Date')
print(lows)
print(highs)
Which outputs:
Lows
Date
2020-11-30 04:45:00 0.03100
2020-11-30 06:25:00 0.03097
2020-11-30 09:35:00 0.03034
Highs
Date
2020-11-30 06:15:00 0.03117
2020-11-30 07:25:00 0.03133
2020-11-30 08:50:00 0.03072
Here I've dropped the indexes that appear in both Series with .index.isin(). However there are two highs appearing sequentially without a Low between. How do I drop every index that is not followed/preceded by an index from the other Series?
Expected output:
Lows
Date
2020-11-30 04:45:00 0.03100
2020-11-30 06:25:00 0.03097
2020-11-30 09:35:00 0.03034
Highs
Date
2020-11-30 01:10:00 0.03147
2020-11-30 06:15:00 0.03117
2020-11-30 07:25:00 0.03133

