How to select 2nd and 3rd row for each group in R
I need to select the second and third records for each group in a dataframe. I try but I get an error.
Sample data:
USER.ID restaurant
3 aaaa
3 ababa
3 asddw
4 bbbb
4 wedwe
2 ewedw
1 qwqw
1 dwqd
1 dqed
1 ewewq
Desired output:
USER.ID 2nd_restaurant 3rd_restaurant
3 ababa asddw
3 ababa asddw
3 ababa asddw
4 wedwe NA
4 wedwe NA
2 NA NA
1 dwqd dqed
1 dwqd dqed
1 dwqd dqed
1 dwqd dqed
I tried using dplyr, but in my opinion, due to the huge amount of data, it takes a long time to compute. Is there a way to calculate it more efficiently?
My code:
data1 <- data %>%
arrange(USER.ID) %>%
group_by(USER.ID) %>%
mutate(second_restaurant = data[2,11]) %>%
mutate(third_restaurant = data[3,11])
11 is the restaurant column number in the original dataset.
+3
source to share
2 answers
Copy the restaurant column first and then use mutate
to extract the corresponding values:
mydf %>%
mutate(restaurant2 = restaurant) %>%
group_by(USER.ID) %>%
mutate(restaurant = restaurant[2], restaurant2 = restaurant2[3])
# Source: local data frame [10 x 3]
# Groups: USER.ID
#
# USER.ID restaurant restaurant2
# 1 3 ababa asddw
# 2 3 ababa asddw
# 3 3 ababa asddw
# 4 4 wedwe NA
# 5 4 wedwe NA
# 6 2 NA NA
# 7 1 dwqd dqed
# 8 1 dwqd dqed
# 9 1 dwqd dqed
# 10 1 dwqd dqed
Or better yet (courtesy @ StevenBeaupré):
mydf %>%
group_by(USER.ID) %>%
transmute(restaurant2 = nth(restaurant, 2),
restaurant3 = nth(restaurant, 3))
Or, if you prefer "data.table", to paraphrase @DavidArenburg, you can try:
library(data.table)
as.data.table(mydf)[, `:=`(restaurant_2 = restaurant[2L],
restaurant_3 = restaurant[3L]), by = USER.ID][]
Or you can use R base:
mydf[c("restaurant_2", "restaurant_3")] <- with(mydf, lapply(c(2, 3), function(x) {
ave(restaurant, USER.ID, FUN = function(y) y[x])
}))
+5
source to share