Fundamentally, I think this is a graph connectivity problem: a fast way of solving it will be some manner of graph connectivity algorithm. Pandas doesn’t include such tools, but scipy does. You can use the compressed sparse graph (csgraph
) submodule in scipy to solve your problem like this:
from scipy.sparse.csgraph import connected_components
# convert to datetime, so min() and max() work
df.startDate = pd.to_datetime(df.startDate)
df.endDate = pd.to_datetime(df.endDate)
def reductionFunction(data):
# create a 2D graph of connectivity between date ranges
start = data.startDate.values
end = data.endDate.values
graph = (start <= end[:, None]) & (end >= start[:, None])
# find connected components in this graph
n_components, indices = connected_components(graph)
# group the results by these connected components
return data.groupby(indices).aggregate({'startDate': 'min',
'endDate': 'max',
'shipNo': 'first'})
df.groupby(['Cust']).apply(reductionFunction).reset_index('Cust')
If you want to do something different with shipNo
from here, it should be pretty straightforward.
Note that the connected_components()
function above is not brute force, but uses a fast algorithm to find the connections.