Finding last vehicle record

[MW19]

Hi!

I'm having difficulty with trying to find the last vehicle record and returning which camera it used.
This is NOT Homework as per community rules .

Here's a small sample of the data:

VehicleData <-  tibble::tribble(
    ~Vehicle,    ~Camera,
  "KXXX XXX", "CAMERA 1",
  "RXXX XXX", "CAMERA 2",
  "GXXX XXX", "CAMERA 3",
  "KXXX XXX", "CAMERA 2",
  "GXXX XXX", "CAMERA 1"
  )

What I would like to do, is for the Vehicle KXXX XXX on row 4, I would like the column next to it to return "CAMERA 1" as that was the last Camera it picked up on.

The second criteria I would also like, is if the result is "CAMERA 3", then I would like to return "No Previous Record".

So my output would be something like this. The reason the top three would return N/As, as It cannot find a previous camera location.

Result <- tibble::tribble(
    ~Vehicle,    ~Camera,              ~Result,
  "KXXX XXX", "CAMERA 1",                   NA,
  "RXXX XXX", "CAMERA 2",                   NA,
  "GXXX XXX", "CAMERA 3",                   NA,
  "KXXX XXX", "CAMERA 2",           "CAMERA 1",
  "GXXX XXX", "CAMERA 1", "No Previous Result"
  )

Any help appreciated!

[pieterjanvc]

Hello,

Here is a way of doing that with some handy dplyr functions

library(dplyr)

VehicleData <-  tibble::tribble(
  ~Vehicle,    ~Camera,
  "KXXX XXX", "CAMERA 1",
  "RXXX XXX", "CAMERA 2",
  "GXXX XXX", "CAMERA 3",
  "KXXX XXX", "CAMERA 2",
  "GXXX XXX", "CAMERA 1"
)

VehicleData %>% 
  group_by(Vehicle) %>% 
  mutate(
    result = lag(Camera),
    result = ifelse(result == "CAMERA 3", "No Previous Result", result))
#> # A tibble: 5 x 3
#> # Groups:   Vehicle [3]
#>   Vehicle  Camera   result            
#>   <chr>    <chr>    <chr>             
#> 1 KXXX XXX CAMERA 1 <NA>              
#> 2 RXXX XXX CAMERA 2 <NA>              
#> 3 GXXX XXX CAMERA 3 <NA>              
#> 4 KXXX XXX CAMERA 2 CAMERA 1          
#> 5 GXXX XXX CAMERA 1 No Previous Result

^{Created on 2021-01-22 by the reprex package (v0.3.0)}

The key function here is the lag(), which returns the previous result in the data frame, and since we group by Vehicle, it will only look at the previous result for that Vehicle type. Then we just replace "CAMERA 3" with "No Previous Result" in the results and that should be all.

Hope this helps,
PJ

[MW19]

Genius, thank you!

So simple!