Python groupby dates but with if statement

Question

My data looks like this:

id   Open    Close
1    1/1/15   1/1/15 
2    1/1/15   2/1/15
3    3/1/15   4/1/15

I need to create a dataframe that shows the number of open cases on any day, so the result of the data above would look like:

Date   #Open
1/1/15   1
2/1/15   0
3/1/15   1

Any ideas?

I think open is in 3/1/15 equal 1 and is missing 4/1/15 with 1 too. — jezrael
– jezrael, Commented Apr 20, 2016 at 16:57
shouldn't the count for 1/1/15 be 1? At end of the day, one event got closed? — Zero
– Zero, Commented Apr 20, 2016 at 17:18
updated data to satisfy a date<closed, instead of a <=closed — Marcus
– Marcus, Commented Apr 20, 2016 at 17:30

Alexander · Accepted Answer · 2016-04-20 17:35:07Z

1

This method creates an index of all days between the first case open and the max of the last case opened or closed. It then iterates through each of these dates and filters the dataframe for the relevant date, checking the resulting size.

df['Open'] = pd.to_datetime(df.Open)
df['Close'] = pd.to_datetime(df.Close)

idx = pd.date_range(df.Open.min(), max(df.Open.max(), df.Close.max()))

cases = pd.DataFrame([len(df[(date >= df.Open) & (date < df.Close)]) 
                      for date in idx], 
                     index=idx, columns=['case_count'])

>>> cases.head(3)
            case_count
2015-01-01           1
2015-01-02           1
2015-01-03           1

>>> cases.tail(3)
            case_count
2015-03-30           1
2015-03-31           1
2015-04-01           0

answered Apr 20, 2016 at 17:35

Alexander

111k32 gold badges212 silver badges208 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python groupby dates but with if statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related