Pandas, how to find complementary time ranges?

user_3225309 0
Viewed 104

I have a dataframe with times when the court is not free:

df = pd.DataFrame(
    [
        {'court_name': 'Court 1', 'reserved_fr': '2021-11-15T08:00:00', 'reserved_to': '2021-11-15T12:00:00'}, 
        {'court_name': 'Court 1', 'reserved_fr': '2021-11-15T15:00:00', 'reserved_to': '2021-11-15T16:00:00'}, 
        {'court_name': 'Court 1', 'reserved_fr': '2021-11-15T16:00:00', 'reserved_to': '2021-11-15T21:00:00'}, 
        {'court_name': 'Court 2', 'reserved_fr': '2021-11-15T20:00:00', 'reserved_to': '2021-11-15T21:00:00'}
    ]
)


|    | court_name   | reserved_fr         | reserved_to         |
|---:|:-------------|:--------------------|:--------------------|
|  0 | Court 1      | 2021-11-15T08:00:00 | 2021-11-15T12:00:00 |
|  1 | Court 1      | 2021-11-15T15:00:00 | 2021-11-15T16:00:00 |
|  2 | Court 1      | 2021-11-15T16:00:00 | 2021-11-15T21:00:00 |
|  3 | Court 2      | 2021-11-15T20:00:00 | 2021-11-15T21:00:00 |

If each court working time is from 7 am to 11 pm, I would like to know when the court is free.

For example courts are free:

Court 1     2021-11-15 07:00:00   2021-11-15 08:00:00
Court 1     2021-11-15 12:00:00   2021-11-15 15:00:00
Court 1     2021-11-15 21:00:00   2021-11-15 23:00:00
Court 2     2021-11-15 07:00:00   2021-11-15 20:00:00
Court 2     2021-11-15 21:00:00   2021-11-15 23:00:00

How to transform dataframe to the another dataframe in format above?

pandas time-series transform python
2 Answers

Solution without defined exact days for times between 7:00 and 23:00 is:

#reshape for hours to one column date
L = [pd.date_range(s,e, freq='H') 
     for s, e in df[['reserved_fr','reserved_to']].to_numpy()]
df['date'] = L

df1 = df.explode('date').drop_duplicates(['court_name','date'])
print (df1)
  court_name          reserved_fr          reserved_to                date
0    Court 1  2021-11-15T08:00:00  2021-11-15T12:00:00 2021-11-15 08:00:00
0    Court 1  2021-11-15T08:00:00  2021-11-15T12:00:00 2021-11-15 09:00:00
0    Court 1  2021-11-15T08:00:00  2021-11-15T12:00:00 2021-11-15 10:00:00
0    Court 1  2021-11-15T08:00:00  2021-11-15T12:00:00 2021-11-15 11:00:00
0    Court 1  2021-11-15T08:00:00  2021-11-15T12:00:00 2021-11-15 12:00:00
1    Court 1  2021-11-15T15:00:00  2021-11-15T16:00:00 2021-11-15 15:00:00
1    Court 1  2021-11-15T15:00:00  2021-11-15T16:00:00 2021-11-15 16:00:00
2    Court 1  2021-11-15T16:00:00  2021-11-15T21:00:00 2021-11-15 17:00:00
2    Court 1  2021-11-15T16:00:00  2021-11-15T21:00:00 2021-11-15 18:00:00
2    Court 1  2021-11-15T16:00:00  2021-11-15T21:00:00 2021-11-15 19:00:00
2    Court 1  2021-11-15T16:00:00  2021-11-15T21:00:00 2021-11-15 20:00:00
2    Court 1  2021-11-15T16:00:00  2021-11-15T21:00:00 2021-11-15 21:00:00
3    Court 2  2021-11-15T20:00:00  2021-11-15T21:00:00 2021-11-15 20:00:00
3    Court 2  2021-11-15T20:00:00  2021-11-15T21:00:00 2021-11-15 21:00:00

#added missing values between 7:00 and 23:00 if not exist
def f(x):
    r = pd.date_range(x.index.min().normalize() + pd.Timedelta('7H'),
                      x.index.max().normalize() + pd.Timedelta('23H'), freq='H')
    return x.reindex(r)
        
    
s = df1.set_index('date').groupby('court_name')['court_name'].apply(f)

#create groups for missing values and aggregate first with last
mask = s.notna()
df = (mask.cumsum()[~mask].reset_index(name='new')
          .groupby(['court_name','new'])['level_1']
          .agg(['min','max'])
          .reset_index(level=1, drop=True))

#change by subtract and add 1 hour if not 7:00 and 23:00
df['min'] = df['min'].where(df['min'].dt.hour.eq(7), df['min'] - pd.Timedelta('1H'))
df['max'] = df['max'].where(df['max'].dt.hour.eq(23), df['max'] + pd.Timedelta('1H'))

print (df)
                           min                 max
court_name                                        
Court 1    2021-11-15 07:00:00 2021-11-15 08:00:00
Court 1    2021-11-15 12:00:00 2021-11-15 15:00:00
Court 1    2021-11-15 21:00:00 2021-11-15 23:00:00
Court 2    2021-11-15 07:00:00 2021-11-15 20:00:00
Court 2    2021-11-15 21:00:00 2021-11-15 23:00:00
user_29010020

Using pandas's built-in pd.DatetimeIndex features

  • Here's a short solution I think works quite well to utilize the existing pandas DatetimeIndex series functionalities most tersely.
  • This solution assumes one-hour slot reservations, and that you meant it closes at 11pm sharp (so e.g. no one could book say 11pm-midnight, just to be super clear - hence you'll see I set the date ranges to end instead actually on the 22nd hour).

1) Create new column "reserved_hours" of dtype pd.DatetimeIndex for all reservations (per court)

Note: This introduces a list trickiness, easily handled though, where we will have to later on ensure we combine and remove dupes from all such lists of reservations (stored as pd.DatetimeIndex objects) - such functionality is totally built-in to pandas already 

import pandas as pd
from itertools import chain

df["reserved_hours"] = [
    pd.date_range(df.loc[i, "reserved_fr"], df.loc[i, "reserved_to"], freq="H")
    for i in range(df.shape[0])
]
df
court_name reserved_fr reserved_to reserved_hours
0 Court 1 2021-11-15T08:00:00 2021-11-15T12:00:00 DatetimeIndex(['2021-11-15 08:00:00', '2021-11-15 09:00:00',
'2021-11-15 10:00:00', '2021-11-15 11:00:00',
'2021-11-15 12:00:00'],
dtype='datetime64[ns]', freq='H')
1 Court 1 2021-11-15T15:00:00 2021-11-15T16:00:00 DatetimeIndex(['2021-11-15 15:00:00', '2021-11-15 16:00:00'], dtype='datetime64[ns]', freq='H')
2 Court 1 2021-11-15T16:00:00 2021-11-15T21:00:00 DatetimeIndex(['2021-11-15 16:00:00', '2021-11-15 17:00:00',
'2021-11-15 18:00:00', '2021-11-15 19:00:00',
'2021-11-15 20:00:00', '2021-11-15 21:00:00'],
dtype='datetime64[ns]', freq='H')
3 Court 2 2021-11-15T20:00:00 2021-11-15T21:00:00 DatetimeIndex(['2021-11-15 20:00:00', '2021-11-15 21:00:00'], dtype='datetime64[ns]', freq='H')

2) Create a pd.DatetimeIndex which includes all possible one-hour slots

available_hours = pd.date_range("2021-11-15T07:00:00",
                                "2021-11-15T22:00:00",
                                freq="H")
available_hours

DatetimeIndex(['2021-11-15 07:00:00', '2021-11-15 08:00:00',
               '2021-11-15 09:00:00', '2021-11-15 10:00:00',
               '2021-11-15 11:00:00', '2021-11-15 12:00:00',
               '2021-11-15 13:00:00', '2021-11-15 14:00:00',
               '2021-11-15 15:00:00', '2021-11-15 16:00:00',
               '2021-11-15 17:00:00', '2021-11-15 18:00:00',
               '2021-11-15 19:00:00', '2021-11-15 20:00:00',
               '2021-11-15 21:00:00', '2021-11-15 22:00:00'],
              dtype='datetime64[ns]', freq='H') ```

3) Finally, use the built-in pd.DatetimeIndex.difference method to calculate remaining open one-hour slots

Because it's possible (and likely) some courts will have multiple datetime indeces, and thus, a list of pd.DatetimeIndex objects, we must first flatten such lists of lists, hence the use of chain below and the resetting of pd.DatatimeIndex.

reserved_hours = df.groupby("court_name").reserved_hours.apply(list)
available = {
    court: available_hours.difference(
        pd.DatetimeIndex(list(chain.from_iterable(res))))
    for court, res in reserved_hours.to_dict().items()
}
print('\n\n'.join(map(str, list(available.items()))))

('Court 1', DatetimeIndex(['2021-11-15 07:00:00', '2021-11-15 13:00:00',
               '2021-11-15 14:00:00', '2021-11-15 22:00:00'],
              dtype='datetime64[ns]', freq=None))

('Court 2', DatetimeIndex(['2021-11-15 07:00:00', '2021-11-15
08:00:00',
               '2021-11-15 09:00:00', '2021-11-15 10:00:00',
               '2021-11-15 11:00:00', '2021-11-15 12:00:00',
               '2021-11-15 13:00:00', '2021-11-15 14:00:00',
               '2021-11-15 15:00:00', '2021-11-15 16:00:00',
               '2021-11-15 17:00:00', '2021-11-15 18:00:00',
               '2021-11-15 19:00:00', '2021-11-15 22:00:00'],
              dtype='datetime64[ns]', freq=None))

So now we've ended up with a neat dictionary where the courts are the keys, and the values are all currently available hour long slots per court. For example the final available reservation slot of any day is the 22nd hour's slot (i.e., up until closing time).

Re-create dict into dataframe showing all available one-hour slots as a datetime index

I think this method may make it easiest to then just list each item from the indeces, but of course further editing like windowing/collapsing together consecutive hours etc. could be done here.

df_open = pd.DataFrame([(court, time) for court, time in available.items()],
                       columns=["court", "availabilities"])
df_open.set_index("court")
court availabilities
Court 1 DatetimeIndex(['2021-11-15 07:00:00', '2021-11-15 13:00:00', '2021-11-15 14:00:00', '2021-11-15 22:00:00'], dtype='datetime64[ns]', freq=None)
Court 2 DatetimeIndex(['2021-11-15 07:00:00', '2021-11-15 08:00:00', '2021-11-15 09:00:00', '2021-11-15 10:00:00', '2021-11-15 11:00:00', '2021-11-15 12:00:00', '2021-11-15 13:00:00', '2021-11-15 14:00:00', '2021-11-15 15:00:00', '2021-11-15 16:00:00', '2021-11-15 17:00:00', '2021-11-15 18:00:00', '2021-11-15 19:00:00', '2021-11-15 22:00:00'], dtype='datetime64[ns]', freq=None)
user_67146270
Related