Count or sum specific string (strings) in each row (rowwise) using dplyr

user_5893454 0
Viewed 433

Let's say I have this dataset

ds <- data.frame(X1 = c("TP","TP","FN","FN"),
             X2 = c("TP","TP","FN","FN"),
             X3 = c("TN","TN","FP","FP"),
             X4 = c("FP","TN","FP","FP"))

I would like to count how many "TP" "FN" (etc) are in each row and achieve this result:
table

I have tried to use mutate(total_TP= sum(str_detect(select(X1:X4), "TP"))) but it's not working.

I would like to remain within tidyverse environment.

Thank you

dplyr tidyverse r string
3 Answers

I would suggest next approach. You can compute the variables and then merge. You can do all of this using dplyr. You will also require formating the data with pivot_longer() and pivot_wider(). These functions belong to tidyr. Previous packages are loaded when calling tidyverse. You can create the summary variables and then joining creating a common id variable. Here the code:

library(tidyverse)

#Code
ds %>% mutate(id=1:n()) %>%
  left_join(ds %>% mutate(id=1:n()) %>% pivot_longer(cols = -id) %>%
              group_by(id,value) %>% summarise(N=n()) %>%
              pivot_wider(names_from = value,values_from=N)) %>%
  select(-id) %>% replace(is.na(.), 0)

Output:

  X1 X2 X3 X4 TP FP TN FN
1 TP TP TN FP  2  1  1  0
2 TP TP TN TN  2  0  2  0
3 FN FN FP FP  0  2  0  2
4 FN FN FP FP  0  2  0  2
user_20808480

We can do this easily with mtabulate

library(qdapTools)
cbind(ds, mtabulate(as.data.frame(t(ds))))
#   X1 X2 X3 X4 FN FP TN TP
#V1 TP TP TN FP  0  1  1  2
#V2 TP TP TN TN  0  0  2  2
#V3 FN FN FP FP  2  2  0  0
#V4 FN FN FP FP  2  2  0  0

Or using base R

cbind(ds, as.data.frame.matrix(table(c(row(ds)), unlist(ds))))

Or using tidyverse with pmap and unnest_wider

library(dplyr)
library(tidyr)
library(purrr)
ds %>%
     mutate(new = pmap(., ~ table(factor(c(...), levels = c("FN", "FP", "TN", "TP"))))) %>% 
     unnest_wider(c(new))
# A tibble: 4 x 8
#  X1    X2    X3    X4       FN    FP    TN    TP
#  <chr> <chr> <chr> <chr> <int> <int> <int> <int>
#1 TP    TP    TN    FP        0     1     1     2
#2 TP    TP    TN    TN        0     0     2     2
#3 FN    FN    FP    FP        2     2     0     0
#4 FN    FN    FP    FP        2     2     0     0
user_37322710

One option utilizing dplyr and purrr could be:

ds %>%
 bind_cols(map_dfc(.x = unique(unlist(ds)),
                   ~ ds %>%
                    transmute(!!.x := rowSums(select(., everything()) == .x))))

  X1 X2 X3 X4 TP FN TN FP
1 TP TP TN FP  2  0  1  1
2 TP TP TN TN  2  0  2  0
3 FN FN FP FP  0  2  0  2
4 FN FN FP FP  0  2  0  2
user_59645570
Related