Calculating max total over a period of time where values are increase and decrease

I have a situation where I need to calculate the total number of clients for a day from a DataFrame where the values increase and decrease. But here is the catch:

If I have a Dataframe like so

DATETIME                CLIENTS
2018-03-03 08:00:00     1
2018-03-03 09:00:00     2
2018-03-03 10:00:00     3
2018-03-03 11:00:00     4
2018-03-03 12:00:00     5
2018-03-03 13:00:00     3
2018-03-03 14:00:00     4
2018-03-03 15:00:00     5

The max total number of clients for this day is 7 because it rises to 5 at 12:00:00 then the value decreases the next hour BUT we do not subtract from 5 and then it rises to 4 at 14:00:00 so we ADD 1 and 5 at 15:00:00 so we ADD another 1 so in total there are 7 max clients throughout the day.

I have tried cumsum() and MAX() as thought these would be useful but alas…

I need to implement this either in SQL or Python. Would appreciate any help!

Answer

You logic is that you only want to count the coming-in visitors, not the leaving ones. Now, if you take diff(), then those coming-in are positive and leaving are negative. So we can just mask the negative with 0 and sum again.

Let’s try:

dates = df.DATETIME.dt.normalize()

max_visitors = (df.groupby(dates)['CLIENTS'].diff()  # find the difference
                  .fillna(df['CLIENTS'])             # these are the first records in the day
                  .clip(0)                           # replace negatives with 0
                  .groupby(dates).sum()              # sum by days
               )

Output:

DATETIME
2018-03-03    7.0
Name: CLIENTS, dtype: float64