Assign a unique value to a new column based on conditions

user_18943272 0
Viewed 57

I have a dataset that summarises car trips, but it does not identify how many unique cars there are. I would like to create a loop/if statement that assigns a unique number based on a location and time a trip starts to figure out an approximate unique number of cars.

So for example, if dropoff location of the first car matches pickup location of the second car, and time frame is within 2 minutes, assign the same car number as the first car. If completely different assign a new number.

I tried different options but can't make it work(a beginner). Any help at this time is greatly appreciated. (R or Python)

This is roughly what I have:

Pickup time Dropoff time Pickup location Dropoff location
2016-06-09 21:06:36 2016-06-09 21:13:08 A B
2016-06-09 21:13:31 2016-06-09 21:23:59 A C
2016-06-09 21:13:45 2016-06-09 21:26:29 B C
2016-06-09 21:15:33 2016-06-09 21:44:31 A B
2016-06-09 21:24:49 2016-06-09 21:39:29 C D

This is what I would like to achieve:

Pickup time Dropoff time Pickup location Dropoff location Car #
2016-06-09 21:06:36 2016-06-09 21:13:08 A B 1
2016-06-09 21:13:31 2016-06-09 21:23:59 A C 2
2016-06-09 21:13:45 2016-06-09 21:24:29 B C 1
2016-06-09 21:15:33 2016-06-09 21:44:31 A B 3
2016-06-09 21:24:49 2016-06-09 21:39:29 C D 2
r python
1 Answers

Here is a data.table approach, using a threshold of 120 seconds

library(data.table)

# Set threshold (in seconds)
threshold = 120

# Get the car identifier
result=melt(
  setDT(df)[,trip:=.I][df, on=.(`Dropoff location`=`Pickup location`), nomatch=0] %>% 
    .[between(`i.Pickup time`-`Dropoff time`,0,threshold),.(trip,i.trip)] %>% 
    .[,car:=.I],id.vars = "car",value.name="trip"
)[,variable:=NULL][df, on="trip"]

# add any other single-instance cars
result[is.na(car),car:=seq(max(result$car,na.rm=T)+1, length.out = result[is.na(car),.N])]

Output:

     car  trip         Pickup time        Dropoff time Pickup location Dropoff location
   <int> <int>              <POSc>              <POSc>          <char>           <char>
1:     1     1 2016-06-09 21:06:36 2016-06-09 21:13:08               A                B
2:     2     2 2016-06-09 21:13:31 2016-06-09 21:23:59               A                C
3:     1     3 2016-06-09 21:13:45 2016-06-09 21:26:29               B                C
4:     3     4 2016-06-09 21:15:33 2016-06-09 21:44:31               A                B
5:     2     5 2016-06-09 21:24:49 2016-06-09 21:39:29               C                D

Input:

structure(list(`Pickup time` = structure(c(1465506396, 1465506811, 
1465506825, 1465506933, 1465507489), class = c("POSIXct", "POSIXt"
), tzone = "UTC"), `Dropoff time` = structure(c(1465506788, 1465507439, 
1465507589, 1465508671, 1465508369), class = c("POSIXct", "POSIXt"
), tzone = "UTC"), `Pickup location` = c("A", "A", "B", "A", 
"C"), `Dropoff location` = c("B", "C", "C", "B", "D")), row.names = c(NA, 
-5L), class = "data.frame")
user_44475400
Related