Create a variable conditioned to the values that take different rows

user_13917839 0
Viewed 26

Create a variable conditioned to the values that take different rows. for example. I have a database, made up of 3 variables. (ID, COD1, COD2).

ID represents an identification code. COD1: represents the categories to which each individual belongs. COD2: represents a registry characteristic.

We want to create a variable 3: COD3 which represents how many COD1 categories the individual belongs to.

COD3 = A if the same ID have 2 categories in COD1

COD3 = B if the same ID have 3 categories in COD1

COD3 = C if the same ID have just one category in COD1

I was thinking of a code using dplyr as shown below but i dont know how to make conditios that contemplate not only the row but the whole group.

df %>% mutate(COD3 = case_when(ID == 1 & COD2 == ?? ~ A)

what I have.

ID COD1 COD2
01 10 1SW
01 11 232
01 10 12D
02 10 13V
02 11 2P0
02 12 PL4
03 10 55R
03 10 P01
03 10 MN7

What I want

ID COD1 COD2 COD3
01 10 1SW A
01 11 232 A
01 10 12D A
02 10 13V B
02 11 2P0 B
02 12 PL4 B
03 10 55R C
03 10 P01 C
03 10 MN7 C

I would appreciate any help in this regard.

dplyr r database
2 Answers

This can be solved in base R with ave. Split by groups of "ID" and see how many unique values of "COD1" there are in each group. Assign the letters accordingly.

with(df1, ave(COD1, ID, FUN = function(x) c("C", "A", "B")[length(unique(x))]))
#[1] "A" "A" "A" "B" "B" "B" "C" "C" "C"

df1$COD3 <- with(df1, ave(COD1, ID, FUN = function(x) c("C", "A", "B")[length(unique(x))]))

Data

df1 <-
structure(list(ID = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L), 
COD1 = c(10L, 11L, 10L, 10L, 11L, 12L, 10L, 10L, 10L), 
COD2 = c("1SW", "232", "12D", "13V", "2P0", "PL4", "55R", 
"P01", "MN7")), class = "data.frame", row.names = c(NA, -9L))
user_82454060 
library(dplyr)

df %>% 
  group_by(ID) %>% 
  mutate(COD3 = c("C", "A", "B")[n_distinct(COD1)]) %>% 
  ungroup()

Or in base R

df$COD3 <- unlist(lapply(with(df, split(COD1, ID)), function(x) c("C", "A", "B")[rep(length(unique(x)), length(x))]))

Output

     ID  COD1 COD2  COD3 
  <int> <int> <chr> <chr>
1     1    10 1SW   A    
2     1    11 232   A    
3     1    10 12D   A    
4     2    10 13V   B    
5     2    11 2P0   B    
6     2    12 PL4   B    
7     3    10 55R   C    
8     3    10 P01   C    
9     3    10 MN7   C
user_63824340
Related