I have a list of timestamps associated with account ids from different vendors and I am trying to reformat the list to account for overlap for users that show up in both vendor's timestamps.
Here is an image of an example table I am working with:
Here is an image of an example output table I desire to create:
In the example above, vendor ID 3 is a newly created vendor to indicate an overlap for when the user AXY_2 showed up in vendor ID 1 and 2. There are two cases where overlap can occur:
- Case 1) One vendor's ID timestamp completely overlaps with another vendor's ID timestamp (shown in the example image above).
- Case 2) There is only a partial overlap between the two vendors.
I have currently written a python script that works; I am still verifying that the script correctly accounts for overlap. However, since I am working on a huge list of data (over 10 million rows), the script takes over an hour to run.
I was wondering if anybody has a better way to solve this problem (ideally using Python).