Sequencing along two variables of interest in R

Question

I am trying to create a sequence along two different parameters about how people have moved from one location to another. I have the following information

name<- c("John", "John", "John", "Sam","Sam", "Robert", "Robert","Robert")
location<- c("London", "London", "Newyork", "Houston", "Houston", "London", "Paris","Paris")
start_yr<- c(2012, 2012, 2014, 2014, 2014,2012,2013, 2013)
end_yr<- c(2013, 2013, 2015, 2015,  2015, 2013, 2015, 2015)

df<- data.frame(name,location,start_yr, end_yr)

I need to seq_along the name and location and create a transition variable of year to know if this person has moved in that year or not. I tried this but it didn't work very well. I was getting strange years meaning the name column sometimes doesn't start with 1. Any suggestions on how to approach this problem?

ave(df$name,df$location, FUN = seq_along)

I would like to have

   name location move year
   John London   1    2012
   John London   0    2013
   John Newyork  1    2014
   John Newyork  0    2015

I don't see how you get to your desired output using your initial data — Steven Beaupré, May 19 '16 at 22:32
I was trying to sequence along to create a new variable curr_yr for year. So that i can create a year variable by adding start_yr with the curr_yr. — user3570187, May 19 '16 at 22:43
Actually you are right. The first move in 2012 is 0 as i don't have prior data but Newyork one is correct. Makes sense? — user3570187, May 19 '16 at 22:54
Am I right in saying that `end_yr` doesn't matter here either ? You only want to know if John changed city at the beginning of a given year compared to the previous one ? — Steven Beaupré, May 19 '16 at 22:57
yes exactly that's right. For calculating inflow end_yr doesn't matter but for outflows it can be useful. — user3570187, May 19 '16 at 23:00

Steven Beaupré · Accepted Answer · 2016-05-20T00:05:40.610

If I understand correctly, you could complete your dataframe by expanding it, for each name & location combination from the minimum start_yr to the maximum end_yr, then group by name and order by start_yr to check if location changed using lag():

library(dplyr)
library(tidyr)

df %>% 
  group_by(name, location) %>%
  complete(start_yr = full_seq(min(start_yr):max(end_yr), 1)) %>%
  group_by(name) %>%
  arrange(start_yr) %>%
  mutate(move = +(lag(location) != location))

This would return NA if, for a given name, there are no previous location, 0 if the location is the same and 1 if it changed:

#Source: local data frame [14 x 5]
#Groups: name [3]
#
#     name location start_yr end_yr  move
#   (fctr)   (fctr)    (dbl)  (dbl) (int)
#1    John   London     2012   2013    NA
#2    John   London     2012   2013     0
#3    John   London     2013     NA     0
#4    John  Newyork     2014   2015     1
#5    John  Newyork     2015     NA     0
#6  Robert   London     2012   2013    NA
#7  Robert   London     2013     NA     0
#8  Robert    Paris     2013   2015     1
#9  Robert    Paris     2013   2015     0
#10 Robert    Paris     2014     NA     0
#11 Robert    Paris     2015     NA     0
#12    Sam  Houston     2014   2015    NA
#13    Sam  Houston     2014   2015     0
#14    Sam  Houston     2015     NA     0

I meant move. I can see that in the pane. I don't see that in data frame — user3570187, May 19 '16 at 23:35
Great! I also need a 'year' variable which is an incremental variable for me to collapse the variables. Any thoughts? — user3570187, May 19 '16 at 23:42
I mean a variable called curr_yr which is basically increasing by 1 w.r.t start_yr until the end year for each person, location combination. — user3570187, May 19 '16 at 23:47

Sequencing along two variables of interest in R

1 Answers1