In R, how count if a value is in any specific columns in the same dataframe?

Question

I have this dataframe, i want to count a value in VAL1-VAL3, and sum it if is in T1-T5

VAL1 <- c(1,3,2,4,9)
VAL2 <- c(5,6,3,1,2)
VAL3 <- c(7,5,5,9,5)
T1   <- c(4,2,6,4,8)
T2   <- c(10,1,2,9,9)
T3   <- c(6,8,6,3,2)
T4   <- c(5,4,2,4,1)
T5   <- c(9,7,8,4,5)

df<- data.frame(VAL1,VAL2,VAL3,T1,T2,T3,T4,T5)`

VAL1	VAL2	VAL3	T1	T2	T3	T4	T5
1	5	7	4	10	6	5	9
3	6	5	2	1	8	4	7
2	3	5	6	2	6	2	8
4	1	9	4	9	3	4	4
9	2	5	8	9	2	1	5

desired output , new columns RESULT1-RESULT3 (same df):

VAL1	VAL2	VAL3	T1	T2	T3	T4	T5	RESULT1	RESULT2	RESULT3
1	5	7	4	10	6	5	9	0	1	0
3	6	5	2	1	8	4	7	0	0	0
2	3	5	6	2	6	2	8	2	0	0
4	1	9	4	9	3	4	4	3	0	1
9	2	5	8	9	2	1	5	1	1	1

How can i do that?

Langobard

Answer 1

You can try:

library(dplyr)

df |>
  mutate(across(VAL1:VAL3, ~ rowSums(pick(T1:T5) == .x), .names = "RESULT_{seq_along(col)}"))

  VAL1 VAL2 VAL3 T1 T2 T3 T4 T5 RESULT_1 RESULT_2 RESULT_3
1    1    5    7  4 10  6  5  9        0        1        0
2    3    6    5  2  1  8  4  7        0        0        0
3    2    3    5  6  2  6  2  8        2        0        0
4    4    1    9  4  9  3  4  4        3        0        1
5    9    2    5  8  9  2  1  5        1        1        1

Center

Answer 2

Here is a base R option

dfVAL <- df[startsWith(names(df), "VAL")]
dfT <- df[startsWith(names(df), "T")]
res <- as.data.frame(
    t(mapply(
        \(x, y) rowSums(outer(x, y, `==`)),
        asplit(dfVAL, 1),
        asplit(dfT, 1)
    ))
)
out <- cbind(df, setNames(res, sub("VAL", "RESULT", names(res))))

which gives

  VAL1 VAL2 VAL3 T1 T2 T3 T4 T5 RESULT1 RESULT2 RESULT3
1    1    5    7  4 10  6  5  9       0       1       0
2    3    6    5  2  1  8  4  7       0       0       0
3    2    3    5  6  2  6  2  8       2       0       0
4    4    1    9  4  9  3  4  4       3       0       1
5    9    2    5  8  9  2  1  5       1       1       1

Gentilism

Answer 3

A base R option searching each T variable within each VAL variable, and then summing the matches using Reduce:

valvar  <- paste0("VAL", 1:3)
srchvar <- paste0("T", 1:5)
outvar  <- paste0("RESULT", 1:3)
df[outvar] <- Reduce(`+`, lapply(df[srchvar], \(x) x == df[valvar]))

df
##  VAL1 VAL2 VAL3 T1 T2 T3 T4 T5 RESULT1 RESULT2 RESULT3
##1    1    5    7  4 10  6  5  9       0       1       0
##2    3    6    5  2  1  8  4  7       0       0       0
##3    2    3    5  6  2  6  2  8       2       0       0
##4    4    1    9  4  9  3  4  4       3       0       1
##5    9    2    5  8  9  2  1  5       1       1       1
    

Strickman

Answer 4

1) This is a base R solution.

ix <- grep("VAL", names(df))
cbind(df, RESULT = sapply(setNames(df[ix], ix), \(x) rowSums(x == df[-ix])))

giving

  VAL1 VAL2 VAL3 T1 T2 T3 T4 T5 RESULT.1 RESULT.2 RESULT.3
1    1    5    7  4 10  6  5  9        0        1        0
2    3    6    5  2  1  8  4  7        0        0        0
3    2    3    5  6  2  6  2  8        2        0        0
4    4    1    9  4  9  3  4  4        3        0        1
5    9    2    5  8  9  2  1  5        1        1        1

2) This is similar except it uses a list comprehension

library(listcompr)

ix <- grep("VAL", names(df))
cbind(df, gen.named.data.frame("RESULT{i}", rowSums(df[[i]] == df[-ix]), 
  i = ix, byrow = TRUE))

giving:

   VAL1 VAL2 VAL3 T1 T2 T3 T4 T5 RESULT1 RESULT2 RESULT3
V1    1    5    7  4 10  6  5  9       0       1       0
V2    3    6    5  2  1  8  4  7       0       0       0
V3    2    3    5  6  2  6  2  8       2       0       0
V4    4    1    9  4  9  3  4  4       3       0       1
V5    9    2    5  8  9  2  1  5       1       1       1

Pollack