Combination of piso functions to create dataframe of last inspection dates for intervals of an asset?

[chrisrobertsnyc]

Hi,

I'm struggling with the right combo of piso functions to perform the following analysis:

Let's say I have a length of road with mile markers as such:
0---1---2---3---4---5

And let's say I have a record of inspections like so:
0---------------------5 --> (mile 0 to 5 inspected 1/1/2025)
2------------5 --> (mile 2 to 5 inspected 1/8/2025)
0-----------3 --> (mile 0 to 3 inspected 1/15/2025)
0--------2 --> (mile 0 to 2 inspected 1/22/2025)

The table/dataframe "all_df" representing these inspections would look like:
| From_Mi | To_Mi | Date |
| 0 | 5 | 1/1/2025 |
| 2 | 5 | 1/8/2025 |
| 0 | 3 | 1/15/2025 |
| 0 | 2 | 1/22/2025 |

The dataframe "recent_df" with most recent inspections would look like:
| From_Mi | To_Mi | Last Date |
| 0 | 2 | 1/22/2025 |
| 2 | 3 | 1/15/2025 |
| 3 | 5 | 1/8/2025 |

Would this be some operation involving .split() and .intersection()?

Any help is appreciated!

[venaturum]

Hi Chris,

Let's take this to stackoverflow.

Can you create a post with the following tags:
python pandas intervals

use this snippet for creating the all_df dataframe:

import pandas as pd

all_df = pd.DataFrame(
    data=(
        (0, 5, pd.Timestamp("2025-1-1")),
        (2, 5, pd.Timestamp("2025-1-8")),
        (0, 3, pd.Timestamp("2025-1-15")),
        (0, 2, pd.Timestamp("2025-1-22")),
    ),
    columns = ["From_Mi", "To_Mi", "Date"]
)

You can then either tag me:
https://stackoverflow.com/users/11472761/riley

or just post the link here.

[chrisrobertsnyc]

Hi venaturum,

Thanks a bunch, I've posted here:
https://stackoverflow.com/questions/79433821/combination-of-piso-functions-to-create-dataframe-of-last-inspection-dates-for-i