Creating id variable based on specific string in tow columns

user_19228320 0
Viewed 39

I want to create a tourID that separates trips started from home and ended to home. Following is an example of my data.

personID TripID satartpurpose endpurpose
1 1 home shopping
1 2 shopping home
2 1 home work
2 2 work home
2 3 home visit friend
2 4 visit friend home

So, the final output would be as below:

personID TripID startpurpose endpurpose tourID
1 1 home shopping 1
1 2 shopping home 1
2 1 home work 2
2 2 work home 2
2 3 home visit friend 3
2 4 visit friend home 3

I am new to r and don't know how I can write the code for this. I really appreciate if someone can help me.

Thanks so much, Tina

group r loops variables
1 Answers

Since the first observation for tourID occurs when startpurpose == "home", only keep those rows. Create the tourID based on the row_number. Merge back with the rest of the data. Then fill in the rest of the tourID:

library(dplyr)
library(tidyr)

my_df <- data.frame(
matrix(
  c(
    1,1,"home", "shopping",
    1,2,"shopping", "home",
    2,1,"home","work",
    2,2,"work","home",
    2,3,"home","visit friend",
    2,4,"visit friend","home"
  ),
  nrow = 6,
  ncol = 4,
  byrow = TRUE,
  dimnames = list(NULL,
                  c("personID", "TripID", "startpurpose", "endpurpose"))
),
stringsAsFactors = FALSE
) %>% 
  mutate(personID = as.numeric(personID),
         TripID = as.numeric(TripID))

my_df_id <- my_df %>% 
  filter(startpurpose == "home") %>% 
  mutate(tourID = row_number()) %>% 
  merge(my_df,
        all = TRUE) %>% 
  fill(tourID)
user_153701420
Related