# Generating new ID for column if ROW - PREVIOUS_ROW >= 45 seconds? [closed]

I don't want this code to be distributed

## closed as off-topic by gene, BERA, PolyGeo♦May 30 at 20:15

This question appears to be off-topic. The users who voted to close gave this specific reason:

If this question can be reworded to fit the rules in the help center, please edit the question.

• I might not understand the problem..it looks like you've already solved it. Just create an empty list "trip_id = []" and append the trip_id of each row to it as you iterate through the csv...you already have the logic there commented out. If the "if" statement is true, just use trip_id.append(trip_id[-1] + 1)), else trip_id.append(trip_id[-1]). – Jon May 30 at 19:00
• Thanks @Jon, I think I have the logic down, just don't know how to implement that. Looking into datetime module (everything is read in as a string).What are your thoughts on that? I like your idea of using that list and +- the trip_id based on that. – Wazzy24 May 30 at 19:37
• There does not appear to be a GIS component to your pure Python question. Stack Overflow is the place to research/ask similar questions to this. – PolyGeo May 30 at 20:17
• @PolyGeo The GIS component is the lat/long values, grouping them by id, then finding a way to do what I am asking with the timestamps to differentiate trips for the same id. This is a clear GIS problem – Wazzy24 May 30 at 23:11
• In that respect they are no different than any other numeric values. – PolyGeo May 30 at 23:48

Try this code. It creates a sorted csv (`csv_sorted`) and appends two new columns: 'time_seconds' and 'trip_id'.

``````import pandas as pd
import numpy as np

def getSec(time):
return sum(x * int(t) for x, t in zip([1, 60, 3600], reversed(time.split(":"))))

my_file = r"C:\Users\Jon\Desktop\output2019_05_25-11.csv"

trip_threshold = 45 # in seconds

# Sort CSV by scooter name, then time
csv_sorted = csv.sort_values(by=[1, 10])
trip_id = np.zeros(csv_sorted.shape[0])

# Add a column of time in seconds
csv_sorted['time_seconds'] = [getSec(t) for t in csv_sorted[10].values]

diff_times = np.diff(csv_sorted['time_seconds'])
new_trip_idcs = np.where(diff_times> trip_threshold)[0] + 1
new_scooter_idcs = np.where(csv_sorted[1].values[:-1] != csv_sorted[1].values[1:])[0] + 1
new_trip_idcs = np.sort(np.unique(np.r_[new_trip_idcs, new_scooter_idcs]))
for nti in new_trip_idcs:
trip_id[nti:] =  trip_id[nti:] + 1
csv_sorted['trip_id'] = trip_id
``````

If you need the order of your original csv preserved, you can map back to it using the "Index" column in the pandas dataframe--I'll leave that up to you.

• @Wazzy24 Put it on a google drive and share the link. For the HH:MM:SS thing, you'll need a line that converts them to just seconds (datetime should do this), and then everything else should work normally. – Jon May 30 at 21:10
• @Wazzy24 Can you explain more what you're trying to do? You mention "trips" but all I see is one time in the csv--presumably either a check-in or check-out time. How do you want to define a "trip"? Just whenever that time is greater than 45 seconds from one row to the next? I ask because the maximum time difference between times is 24 seconds, so using a threshold of 45 seconds would assign all rows the same trip_id (of 0). Nevertheless, I put updated code that includes the conversion of the HH:MM:SS to just seconds. – Jon May 31 at 14:34
• Ok, so the first thing you need to do is to collect each scooter's entries; i.e. sort based on scooter id (which is presumably that 13 character hash-looking string). Then for each of those you want to determine when new trips have started. Is that right? @Wazzy24 – Jon May 31 at 15:38
• @Wazzy24 check out the new code. – Jon May 31 at 15:59
• Ok, I made one more edit. I am out of time to work on this, so I hope this accomplishes what you want. In the future, it would be helpful if you provide an expected output--i.e. state exactly what you want done. In this case, it would be something like "I want to assign a new trip id to each scooter whenever its time stamp jumps more than a threshold amount." Oftentimes by thinking extensively about how to ask your question, you will stumble across a new idea! – Jon May 31 at 16:56