How to count conditionally within a column (for all rows) and loop that through all the columns to create a contingency table in R

md hossain 0
Viewed 58

This is my dataframe:

disease <- c("high", "high", "high", "high", "low","low","low","low");
ToA <- c("P","A","P","P","A","A","A","P");
ToB <- c("P","A","A","P","A","P","A","P");
ToC <- c("P","P","A","P","A","A","A","P"); 
df <- data.frame(disease, ToA, ToB, ToC)

I am looking for contingency table as the image [where, row 1 = ToA, row 2 = ToB, row 3 = ToC]

    high_P high_A low_P low_A
1      3      1     1     3
2      2      2     2     2
3      2      1     1     3

For each column in the dataframe, I need to calculate the frequency (count) for combination of P & high (disease col), P-low, A-high and A-low as you can see in the image above. I can do that by nrow for each column separately as below:

##count for col 2 in df

high_P=nrow(df[df$disease=="high" & df$ToA=="P", ])
high_A=nrow(df[df$disease=="high" & df$ToA=="A", ])
low_P=nrow(df[df$disease=="low" & df$ToA=="P", ])
low_A=nrow(df[df$disease=="low" & df$ToA=="A", ])
ToA_df=data.frame(high_P,high_A,low_P,low_A)

#count for col 3 in df

high_P=nrow(df[df$disease=="high" & df$ToB=="P", ])
high_A=nrow(df[df$disease=="high" & df$ToB=="A", ])
low_P=nrow(df[df$disease=="low" & df$ToB=="P", ])
low_A=nrow(df[df$disease=="low" & df$toB=="A", ])
ToB_df=data.frame(high_P,high_A,low_P,low_A)

#count for col 4 in df

high_P=nrow(df[df$disease=="high" & df$ToC=="P", ])
high_A=nrow(df[df$disease=="high" & df$ToC=="A", ])
low_P=nrow(df[df$disease=="low" & df$ToC=="P", ])
low_A=nrow(df[df$disease=="low" & df$ToC=="A", ])
ToC_df=data.frame(high_P,high_A,low_P,low_A)


Data = rbind(ToA_df,ToB_df,ToC_df)

It does what I want but I want to calculate that for each column one after another using loops, as for a big data set it would be difficult to calculate manually (col by col). Could anyone suggest/help how I can calculate the contingency table in R using loops or....as in the image?

tidyr reshape r
3 Answers

You can do this:

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(!disease, names_to = 'columns', values_to = 'vals') %>%
  count(disease, columns, vals) %>%
  pivot_wider(names_from = c(disease, vals), values_from = n, 
              names_sep = '_')

# A tibble: 3 × 5
  columns high_A high_P low_A low_P
  <chr>    <int>  <int> <int> <int>
1 ToA          1      3     3     1
2 ToB          2      2     2     2
3 ToC          1      3     3     1
Anoushiravan R0

Your data at one point has a p instead of a P change that and then use the following code:

using reshape::recast:

reshape2::recast(df, variable~disease+value, id.var = 'disease')    
  variable high_A high_P low_A low_P
1      ToA      1      3     3     1
2      ToB      2      2     2     2
3      ToC      1      3     3     1

using tidyverse:

df %>%
   pivot_longer(-disease, values_transform = toupper)%>%
   pivot_wider(name, names_from = c(disease, value), 
               values_from = disease, values_fn = length)

# A tibble: 3 x 5
  name  high_P high_A low_A low_P
  <chr>  <int>  <int> <int> <int>
1 ToA        3      1     3     1
2 ToB        2      2     2     2
3 ToC        3      1     3     1

Base R options:

table(rev(stack(Map(\(x,y)paste(y,x,sep='_'), df[-1], df[1]))))
     values
ind   high_A high_P low_A low_P
  ToA      1      3     3     1
  ToB      2      2     2     2
  ToC      1      3     3     1
onyambu0

Looping table by disease over the columns.

by(df[-1], df$disease, \(x) t(sapply(x, table))) |> do.call(what=cbind)
#     A P A P
# ToA 1 3 3 1
# ToB 2 2 2 2
# ToC 1 3 3 1

Data:

df <- structure(list(disease = c("high", "high", "high", "high", "low", 
"low", "low", "low"), ToA = c("P", "A", "P", "P", "A", "A", "A", 
"P"), ToB = c("P", "A", "A", "P", "A", "P", "A", "P"), ToC = c("P", 
"P", "A", "P", "A", "A", "A", "P")), row.names = c(NA, -8L), class = "data.frame")
jay.sf0
Related