I have a database (CSV format, and SQL if required) that contains records when a person (with a unique ID) passes a sensor site (siteid). Attached sample.
I need a script (preferably rails, python or SQL) that will output the journey time (in seconds) between sites, based on the database. The data should be outputted to CSV or SQL. I've attached a sample output that I would expect (output tab in excel file).
The script will need to consider:
- A journey for a user should only be calculated between two sensor readings that are closest in time. For example, user #234 passes sensor #1 at 2:30pm, sensor #4 at 2:32pm, sensor #2 at 2:40pm. In this case, two calculations should be made (1. time from sensor #1 to sensor #4, 2. time from sensor #4 to sensor #2).
- If siteid of origin and destination sensors is the same, then journey can't be valid. Insert a flag that not valid in output.
- If the time between two sensors > 1800 seconds, then the journey can't be valid.Insert a flag that not valid in output.
The number of records in the database will be in the millions, so it needs to be able to process a high quantity.
Developer will need to provide instructions for how to run script.
Script needs to be complete and working within the next few days.