CODEWITHSUNDEEP
rdplyr
simon brocard
simon brocard

Reputation: 61

Compare rows by ID

I have a data frame like this:

ID col1 Col2
AB 1 3
AB 1 3
CD 2 4
CD 2 4
EF 5 10
EF 1 1
GH 5 10
GH 1 1

I would like to compare row within each ID, and for each column with equal values, add one point in the Total column.

Output:

ID col1 Col2 Total mismatch_extract_col1 mismatch_extract_Col2
AB 1 3 2 Na Na
AB 1 3 2 Na Na
CD 2 4 1 Na 4:3
CD 2 3 1 Na 4:3
EF 5 10 0 Na Na
EF 1 1 0 Na Na
GH 5 10 1 5:1 Na
GH 1 10 1 5:1 Na

I tried this:

df <- df %>% 
  group_by(ID) %>% 
  mutate(change = ifelse(col1 == lag(col1), 1, 0))

But it didn’t work.

Upvotes: 0

Views: 69

Answers (1)

Ma&#235;l
Ma&#235;l

Reputation: 51914

library(dplyr) #version 1.1.0 and above
df %>% 
  mutate(Total = sum(across(col1:Col2, ~ n_distinct(.x) == 1)), .by = ID)

  ID col1 Col2 Total
1 AB    1    3     2
2 AB    1    3     2
3 CD    2    4     1
4 CD    2    3     1
5 EF    5   10     0
6 EF    1    1     0
7 GH    5   10     1
8 GH    1   10     1

Below 1.1.0:

df %>% 
  group_by(ID) %>% 
  mutate(Total = sum(across(col1:Col2, ~ n_distinct(.x) == 1))) %>% 
  ungroup()

Upvotes: 3

Related Questions