How do I use conditional expressions to filter a dataframe in pandas?

user_18439321 0
Viewed 45

Here is an example of the csv file I am starting with. In reality the file will be about 43,000 rows and have many instances where the status changes from 0 to 1 and back to 0.

         date       status
0   3/8/2022 10:01  0
1   3/8/2022 10:02  0
2   3/8/2022 10:03  1
3   3/8/2022 10:04  1
4   3/8/2022 10:05  1
5   3/8/2022 10:06  1
6   3/8/2022 10:07  1
7   3/8/2022 10:08  1
8   3/8/2022 10:09  1
9   3/8/2022 10:10  0
10  3/8/2022 10:11  0

From this csv file I need to create a new dataframe that only contains the time stamps from the date column of when the status changes from both 0 to 1 (closed) and then 1 to 0 (open).

So far I have used the code below to create two new columns that contain the information I'm looking for.

df['Closed'] = df.loc[((df['status'].shift(+1)==0) & (df['status']==1)), 'date']
df['Open'] = df.loc[(df['status'].shift(+1)==1) & (df['status']==0), 'date']

         date      status Closed          Open
0   3/8/2022 10:01   0     NaN            NaN
1   3/8/2022 10:02   0     NaN            NaN
2   3/8/2022 10:03   1     3/8/2022 10:03 NaN
3   3/8/2022 10:04   1     NaN            NaN
4   3/8/2022 10:05   1     NaN            NaN
5   3/8/2022 10:06   1     NaN            NaN
6   3/8/2022 10:07   1     NaN            NaN
7   3/8/2022 10:08   1     NaN            NaN
8   3/8/2022 10:09   1     NaN            NaN
9   3/8/2022 10:10   0     NaN            3/8/2022 10:10
10  3/8/2022 10:11   0     NaN            NaN

I'm not sure what to do next to get the final result that I need shown below. Every "Closed" instance will have an accompanying "Open" instance.

         Closed            Open
0    3/8/2022 10:03    3/8/2022 10:10
pandas python
1 Answers

IIUC, you could slice with a mask and GroupBy.agg:

# "1" rows
mask = df['status'].eq(1)
# group by stretches starting with "1"
group = df['status'].diff().eq(1).cumsum()


(df[mask|mask.shift()]             # keep rows with "1" and the 1st "0" after
 .groupby(group)['date']           # group
 .agg(Closed='first', Open='last') # get 1st and last date of group
 .reset_index(drop=True)
)

output:

           Closed            Open
0  3/8/2022 10:03  3/8/2022 10:10
user_163434640
Related