selecting the last row of a group

Question

I have a data frame.

       household    person     trip      loop
          1           1         1          1
          1           1         2          1
          1           1         3          1
          1           1         4          2
          1           1         5          2
          1           2         1          1
          1           2         2          1
          1           2         3          2
          2           1         1          1
          2           1         2          1
          2           1         3          2
          2           1         4          2

for each person in each household I want to change some of index in column trip as below:

when loop is changed I want the trip index Strats from 1 agin.

output

       household    person     trip      loop
          1           1         1          1
          1           1         2          1
          1           1         3          1
          1           1         1          2
          1           1         2          2
          1           2         1          1
          1           2         2          1
          1           2         1          2
          2           1         1          1
          2           1         2          1
          2           1         1          2
          2           1         2          2

`ave(d$trip, d$household, d$person, d$loop, FUN = seq_along)` — d.b, Sep 10 '19 at 16:07

score 1 · Accepted Answer · answered Sep 10 '19 at 17:14

We can use

library(dplyr)
df1 %>% 
  group_by(household, person, loop) %>%
  mutate(trip = row_number())
# A tibble: 12 x 4
# Groups:   household, person, loop [6]
#   household person  trip  loop
#       <int>  <int> <int> <int>
# 1         1      1     1     1
# 2         1      1     2     1
# 3         1      1     3     1
# 4         1      1     1     2
# 5         1      1     2     2
# 6         1      2     1     1
# 7         1      2     2     1
# 8         1      2     1     2
# 9         2      1     1     1
#10         2      1     2     1
#11         2      1     1     2
#12         2      1     2     2

data

df1  <- structure(list(household = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
2L, 2L, 2L, 2L), person = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 1L, 
1L, 1L, 1L), trip = c(1L, 2L, 3L, 4L, 5L, 1L, 2L, 3L, 1L, 2L, 
3L, 4L), loop = c(1L, 1L, 1L, 2L, 2L, 1L, 1L, 2L, 1L, 1L, 2L, 
2L)), class = "data.frame", row.names = c(NA, -12L))

score 0 · Answer 2 · answered Sep 10 '19 at 17:33

0

Using data.table :

library(data.table)

df <- setDT(df) # Making sure your data is a data table
df[, trip := seq_len(.N), by = .(household, person, loop)]

answered Sep 10 '19 at 17:33

Samsa

148
8

selecting the last row of a group

2 Answers2

data