基于组添加计数器

Question

我正在尝试将计数器列(n_team和n_bird)添加到数据帧中，但使用dplyr::row_number等没有成功。下面是输入数据帧(df)和所需输出数据帧(df_counts)以及几个错误输出的代码的代表。

library(dplyr)

# Input
df <- 
  tribble(
    ~id, ~team,     ~bird,
    1,  "blue",  "parrot",
    2, "green",     "owl",
    3,  "blue",  "toucan",
    3,  "blue",   "finch",
    4, "green", "penguin",
    4,  "blue", "sparrow"
  )

# Desired output
# n_team is the team number within an id
# n_bird is the bird number within a team within an id
df_counts <- 
  tribble(
    ~id, ~team,     ~bird, ~n_team, ~n_bird,
    1,  "blue",  "parrot",      1,        1,
    2, "green",     "owl",      1,        1,
    3,  "blue",  "toucan",      1,        1,
    3,  "blue",   "finch",      1,        2,
    4, "green", "penguin",      1,        1,
    4,  "blue", "sparrow",      2,        1
  )

# Incorrect
df %>% 
  add_count(id, team, name = "n_team")
#> # A tibble: 6 x 4
#>      id team  bird    n_team
#>   <dbl> <chr> <chr>    <int>
#> 1     1 blue  parrot       1
#> 2     2 green owl          1
#> 3     3 blue  toucan       2
#> 4     3 blue  finch        2
#> 5     4 green penguin      1
#> 6     4 blue  sparrow      1

df %>% 
  group_by(id) %>% 
  mutate(n_team =  row_number(team))
#> # A tibble: 6 x 4
#> # Groups:   id [4]
#>      id team  bird    n_team
#>   <dbl> <chr> <chr>    <int>
#> 1     1 blue  parrot       1
#> 2     2 green owl          1
#> 3     3 blue  toucan       1
#> 4     3 blue  finch        2
#> 5     4 green penguin      2
#> 6     4 blue  sparrow      1

df %>% 
  group_by(id, team) %>% 
  mutate(n_team =  1:n())
#> # A tibble: 6 x 4
#> # Groups:   id, team [5]
#>      id team  bird    n_team
#>   <dbl> <chr> <chr>    <int>
#> 1     1 blue  parrot       1
#> 2     2 green owl          1
#> 3     3 blue  toucan       1
#> 4     3 blue  finch        2
#> 5     4 green penguin      1
#> 6     4 blue  sparrow      1

df %>% 
  group_by(id) %>% 
  mutate(n_team =  n_distinct(team))
#> # A tibble: 6 x 4
#> # Groups:   id [4]
#>      id team  bird    n_team
#>   <dbl> <chr> <chr>    <int>
#> 1     1 blue  parrot       1
#> 2     2 green owl          1
#> 3     3 blue  toucan       1
#> 4     3 blue  finch        1
#> 5     4 green penguin      2
#> 6     4 blue  sparrow      2

df %>% 
  add_count(team)
#> # A tibble: 6 x 4
#>      id team  bird        n
#>   <dbl> <chr> <chr>   <int>
#> 1     1 blue  parrot      4
#> 2     2 green owl         2
#> 3     3 blue  toucan      4
#> 4     3 blue  finch       4
#> 5     4 green penguin     2
#> 6     4 blue  sparrow     4

# Counts alphabetically
df %>% 
  group_by(id, team) %>% 
  mutate(n_bird =  row_number(bird))
#> # A tibble: 6 x 4
#> # Groups:   id, team [5]
#>      id team  bird    n_bird
#>   <dbl> <chr> <chr>    <int>
#> 1     1 blue  parrot       1
#> 2     2 green owl          1
#> 3     3 blue  toucan       2
#> 4     3 blue  finch        1
#> 5     4 green penguin      1
#> 6     4 blue  sparrow      1

# Counts in order
df %>% 
  group_by(id, team) %>% 
  mutate(n_bird =  row_number())
#> # A tibble: 6 x 4
#> # Groups:   id, team [5]
#>      id team  bird    n_bird
#>   <dbl> <chr> <chr>    <int>
#> 1     1 blue  parrot       1
#> 2     2 green owl          1
#> 3     3 blue  toucan       1
#> 4     3 blue  finch        2
#> 5     4 green penguin      1
#> 6     4 blue  sparrow      1

• 通过排列两个变量来添加计数器列(dplyr)
• https://community.rstudio.com/t/how-to-add-a-counter-to-each-group-in-dplyr/12986/2
• https://dplyr.tidyverse.org/reference/tally.html
• https://dplyr.tidyverse.org/reference/n_distinct.html

Answer 1

以下是4种非常相似但又不同的方法：

1. 匹配+唯一:

library(dplyr)
df %>%
  group_by(id) %>%
  mutate(n_teams = match(team, unique(team))) %>%
  group_by(team, .add = TRUE) %>%
  mutate(n_bird =  match(bird, unique(bird)))

#    id team  bird    n_teams n_bird
#  <dbl> <chr> <chr>     <int>  <int>
#1     1 blue  parrot        1      1
#2     2 green owl           1      1
#3     3 blue  toucan        1      1
#4     3 blue  finch         1      2
#5     4 green penguin       1      1
#6     4 blue  sparrow       2      1

df %>%
  group_by(id) %>%
  mutate(n_teams = as.integer(factor(team))) %>%
  group_by(team, .add = TRUE) %>%
  mutate(n_bird =  as.integer(factor(bird)))

df %>%
  group_by(id) %>%
  mutate(n_teams = data.table::rleid(team)) %>%
  group_by(team, .add = TRUE) %>%
  mutate(n_bird =  data.table::rleid(bird))

df %>%
  group_by(id) %>%
  mutate(n_teams = dense_rank(team)) %>%
  group_by(team, .add = TRUE) %>%
  mutate(n_bird =  dense_rank(bird))