R Identifying Dataframe Change Patterns by Groups

user_13275613 0
Viewed 68

I have a dataframe looks like below:

person   year   location     salary
Harry    2002   Los Angeles  $2000
Harry    2006   Boston       $3000
Harry    2007   Los Angeles  $2500
Peter    2001   New York     $2000
Peter    2002   New York     $2300
Lily     2007   New York     $7000
Lily     2008   Boston       $2300
Lily     2011   New York     $4000
Lily     2013   Boston       $3300

I want to identify a pattern at the person level. I want to know who moves out of a location and came back later. For example, Harry moves out of Los Angeles and came back later. Lily moved out of new York and came back later. Also for Lily, we can say she also moved out of Boston and came back later. I only am interested in who has this pattern and does not care the number of back and forth. Therefore, ideally, the output can look like:

   person   move_back (yes/no)
   Harry    1
   Peter    0
   Lily     1
dplyr dataframe r
3 Answers

With the help of data.table rleid you can do -

library(dplyr)

df %>%
  arrange(person, year) %>%
  group_by(person) %>%
  mutate(val = data.table::rleid(location)) %>%
  arrange(person, location) %>%
  group_by(location, .add = TRUE) %>%
  summarise(move_back = any(val != lag(val, default = first(val)))) %>%
  summarise(move_back = as.integer(any(move_back)))

#  person move_back
#  <chr>      <int>
#1 Harry          1
#2 Lily           1
#3 Peter          0
user_39629140

You could use rle to identify situations where the are one or more instances of repeats. (I think your item Lily had two repeats.)

lapply( split(dat, dat$person), function(x) duplicated( rle(x$location)$values))
$Harry
[1] FALSE FALSE  TRUE

$Lily
[1] FALSE FALSE  TRUE  TRUE

$Peter
[1] FALSE

You could use sapply with sum or any to determine the number of move-backs or whether any move-backs occurred. If you only want to know if there's a move-back to the first site then the logic would be different.

user_18556770

A slightly different data.table method, based on joins and row number (.I).
Basically I'm flagging all the times that a location for a person matches a row that is not the next row, then aggregating.

library(data.table)
setDT(dat)

dat[, rn   := .I]
dat[, rnp1 := .I + 1]

dat[dat, on=.(person, location, rn > rnp1), back := TRUE]
dat[, .(move_back = any(back, na.rm=TRUE)), by=person]

#   person move_back
#1:  Harry      TRUE
#2:  Peter     FALSE
#3:   Lily      TRUE

Where dat was:

dat <- read.csv(text="person,year,location,salary
Harry,2002,Los Angeles,$2000
Harry,2006,Boston,$3000
Harry,2007,Los Angeles,$2500
Peter,2001,New York,$2000
Peter,2002,New York,$2300
Lily,2007,New York,$7000
Lily,2008,Boston,$2300
Lily,2011,New York,$4000
Lily,2013,Boston,$3300", header=TRUE)
user_4968030
Related