Count how many rows have the same ID and add the number in an new column-CodePudding

My dataframe contains data about political careers, such as a unique identifier (called: ui) column for each politician and the electoral term(called: electoral_term) in which they were elected. Since a politician can be elected in multiple electoral terms, there are multiple rows that contain the same ui.

Now I would like to add another column to my dataframe, that counts how many times the politician got re-elected.

So e.g. the politician with ui=1 was re-elected 2 times, since he occured in 3 electoral_terms.

I already tried

df %>% count(ui)

But that only gives out a table which can't be added into my dataframe.

Thanks in advance!

CodePudding user response：

We may use base R

df$reelected <- with(df, ave(ui, ui, FUN = length)-1)

-output

> df
  ui electoral reelected
1  1         1         2
2  1         2         2
3  1         3         2
4  2         2         0
5  3         7         1
6  3         9         1

data

df <- structure(list(ui = c(1, 1, 1, 2, 3, 3), electoral = c(1, 2, 
3, 2, 7, 9)), class = "data.frame", row.names = c(NA, -6L))

CodePudding user response：

mydf <- tibble::tribble(~ui, ~electoral, 1, 1, 1, 2, 1, 3, 2, 2, 3, 7, 3, 9)

library(dplyr)

df |> 
  add_count(ui, name = "re_elected") |> 
  mutate(re_elected = re_elected - 1)

# A tibble: 6 × 3
     ui electoral re_elected
  <dbl>     <dbl>      <dbl>
1     1         1          2
2     1         2          2
3     1         3          2
4     2         2          0
5     3         7          1
6     3         9          1

CodePudding user response：

library(tidyverse)

df %>% 
  group_by(ui) %>% 
  mutate(re_elected = n() - 1)

# A tibble: 6 × 3
# Groups:   ui [3]
     ui electoral re_elected
  <dbl>     <dbl>      <dbl>
1     1         1          2
2     1         2          2
3     1         3          2
4     2         2          0
5     3         7          1
6     3         9          1