Append values of a column if values in another column are sequential in R

user_7885762 0
Viewed 60

I am looking for a convenient way to concatenate values of a column if the values of another column increment by 1. My dataframe

      st        row_index
 1    alpha        2
 2    beta         7
 3    gamma       11
 4    delta       12
 5    zero        15
 6    one         16
 7    two         17

Target data frame

    st             row_index
1   alpha              2
2   beta               7
3   gammadelta        11
4   zero one two      15
dplyr aggregate r
2 Answers

You can use lag and cumsum to create a helper group variable g, and then summarize by this variable; row_index - lag(row_index, default=0) != 1 checks the difference between the current row_index and the previous one, which returns TRUE if it's different from 1 (Used default=0 to removes NA introduced by lag), combined with cumsum it gives a unique id for each consecutive chunk of rows where the difference of row_index is one:

df %>% 
    group_by(g = cumsum(row_index - lag(row_index, default=0) != 1)) %>% 
    summarise(st = paste(st, collapse = " "), row_index = first(row_index)) %>% 
    select(-g)

# A tibble: 4 x 2
#            st row_index
#         <chr>     <int>
#1        alpha         2
#2         beta         7
#3  gamma delta        11
#4 zero one two        15
user_49834500

Here is an option with data.table. Grouped by the cumulative sum of difference of 'row_index' that are not 1, paste the elements of 'st' together and also take the first values of 'row_index'

library(data.table)
setDT(df1)[, .(st = paste(st, collapse= ' '),
     row_index = row_index[1]), .(grp = cumsum(c(TRUE, diff(row_index) != 1)))
       ][, .(st, row_index)]
#             st row_index
#1:        alpha         2
#2:         beta         7
#3:  gamma delta        11
#4: zero one two        15
user_37322710
Related