Create Date Ranges where Indicator Equals One-CodePudding

How do I create dataframe of date ranges for ids where indicator = 1?

#Proxy main high frequency dataframe
main_data = [['site a', '2021-03-05 01:00:00', 1], 
             ['site a', '2021-03-05 01:30:00', 1], 
             ['site a', '2021-03-05 02:00:00', 0],
             ['site a', '2021-03-05 02:30:00', 1],
             ['site a', '2021-03-05 02:30:00', 1],
             ['site b', '2021-04-08 20:00:00', 0],
             ['site b', '2021-04-09 20:00:00', 1],
             ['site b', '2021-04-10 20:00:00', 1],
             ['site b', '2021-04-10 20:30:00', 1]] 
 
# Create the pandas DataFrame
main_df = pd.DataFrame(main_data, columns = ['id', 'timestamp', 'indicator'])
main_df['timestamp'] = pd.to_datetime(main_df['timestamp'], infer_datetime_format=True)

print(main_df)
id  timestamp   indicator
0   site a  2021-03-05 01:00:00 1
1   site a  2021-03-05 01:30:00 1
2   site a  2021-03-05 02:00:00 0
3   site a  2021-03-05 02:30:00 1
4   site a  2021-03-05 02:30:00 1
5   site b  2021-04-08 20:00:00 0
6   site b  2021-04-09 20:00:00 1
7   site b  2021-04-10 20:00:00 1
8   site b  2021-04-10 20:30:00 1

Desired Output dataframe:

print(desired_df)

    id      start               end
0   site a  2021-03-05 01:00:00 2021-03-05 01:30:00
1   site a  2021-03-05 02:30:00 2021-03-05 02:30:00
2   site b  2021-04-09 20:00:00 2021-04-10 20:30:00

CodePudding user response：

You can use grouby with named aggregrations like this, first create groups of indicators 1, ind_grp, with eq to zero and cumsum:

ind_grp = main_df['indicator'].eq(0).cumsum()

main_df.groupby(['id', ind_grp], as_index=False)\
       .agg(start=('timestamp', 'min'),
              end=('timestamp','max'))

Output:

       id               start                 end
0  site a 2021-03-05 01:00:00 2021-03-05 01:30:00
1  site a 2021-03-05 02:00:00 2021-03-05 02:30:00
2  site b 2021-04-08 20:00:00 2021-04-10 20:30:00

CodePudding user response：

IIUC:

groupby sequences of the indicator column and record the min and max values for "start" and "end".
drop unneeded columns and duplicates.

main_df["start"] = main_df.groupby(main_df["indicator"].ne(main_df["indicator"].shift()).cumsum())["timestamp"].transform("min")
main_df["end"] = main_df.groupby(main_df["indicator"].ne(main_df["indicator"].shift()).cumsum())["timestamp"].transform("max")

output = main_df[main_df["indicator"].eq(1)].drop_duplicates(["start", "end"])

>>> output
       id               start                 end
0  site a 2021-03-05 01:00:00 2021-03-05 01:30:00
3  site a 2021-03-05 02:30:00 2021-03-05 02:30:00
6  site b 2021-04-09 20:00:00 2021-04-10 20:30:00

CodePudding user response：

Here's a solution:

group = main_df[main_df['indicator'] == 1].groupby(main_df['indicator'].ne(main_df['indicator'].shift(1)).cumsum()[main_df['indicator'] == 1])
pd.DataFrame({'id': group['id'].first().tolist(), 'start': group['timestamp'].first().tolist(), 'end': group['timestamp'].last().tolist()})

Output:

>>> desired_df
       id               start                 end
0  site a 2021-03-05 01:00:00 2021-03-05 01:30:00
1  site a 2021-03-05 02:30:00 2021-03-05 02:30:00
2  site b 2021-04-09 20:00:00 2021-04-10 20:30:00