Home > Software engineering >  How can I cut an amount of rows by group based in the number of one column?
How can I cut an amount of rows by group based in the number of one column?

Time:11-10

I would like to cut rows from my data frame by groups (Column "Group") based on the number asigned in the column "Count".

Data looks like this

Group       Count    Result      Result 2
  <chr>     <dbl>  <dbl>        <dbl>
1 Ane           3      5           NA
2 Ane           3      6            5
3 Ane           3      4            5
4 Ane           3      8            5
5 Ane           3      7            8
6 John          2      9           NA
7 John          2      2           NA
8 John          2      4            2
9 John          2      3            2

Expected results

Group       Count    Result      Result 2
  <chr>     <dbl>    <dbl>        <dbl>
1 Ane           3      5           NA
2 Ane           3      6            5
3 Ane           3      4            5
6 John          2      9           NA
7 John          2      2           NA

Thanks!

CodePudding user response:

We may use slice on the first value of 'Count' after grouping by 'Group'

library(dplyr)
df1 %>% 
    group_by(Group) %>% 
    slice(seq_len(first(Count))) %>%
    ungroup

-output

# A tibble: 5 × 4
  Group Count Result Result2
  <chr> <int>  <int>   <int>
1 Ane       3      5      NA
2 Ane       3      6       5
3 Ane       3      4       5
4 John      2      9      NA
5 John      2      2      NA

Or use filter with row_number() to create a logical vector

df1 %>% 
   group_by(Group) %>% 
   filter(row_number() <= Count) %>%
   ungroup

data

df1 <- structure(list(Group = c("Ane", "Ane", "Ane", "Ane", "Ane", "John", 
"John", "John", "John"), Count = c(3L, 3L, 3L, 3L, 3L, 2L, 2L, 
2L, 2L), Result = c(5L, 6L, 4L, 8L, 7L, 9L, 2L, 4L, 3L), Result2 = c(NA, 
5L, 5L, 5L, 8L, NA, NA, 2L, 2L)), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9"))
  • Related