Frequency table when there are multiple columns representing one value (R)

user_12860167 0
Viewed 590

I have a dataset like this:

ID    color1   color2  color3   shape1       shape2        size
55    red     blue     NA       circle       triangle      small
67    yellow  NA       NA       triangle     NA            medium
83    blue    yellow   NA       circle       NA            large
78    red     yellow   blue     square       circle        large
43    green   NA       NA       square       circle        small
29    yellow  green    NA       circle       triangle      medium

I would like to create a dataframe where I have the frequency and percentage of each variable, but I am having trouble because there are multiple columns of the same variable in some cases.


Variable      Level        Freq        Percent 
 
color         blue          3           27.27
              red           2           18.18
              yellow        4           36.36
              green         2           18.18
              total         11          100.00

shape         circle        5           50.0       
              triangle      3           30.0
              square        2           20.0
              total         10          100.0

size          small         2           33.3
              medium        2           33.3
              large         2           33.3
              total         6           100.0

I believe that I need to convert these variables to long and then use summarize/mutate to get the frequencies, but I can't seem to figure it out. Any help is greatly appreciated.

r
3 Answers

You can use tidyverse package to transform the data into a long format and then just summarise the desired stats.

library(tidyverse)

df |> 
  # Transform all columns into a long format
  pivot_longer(cols = -ID,
               names_pattern = "([A-z]+)",
               names_to = c("variable")) |>
  # Drop NA entries
  drop_na(value) |>
  # Group by variable
  group_by(variable) |>
  # Count
  count(value) |>
  # Calculate percentage as n / sum of n by variable
  mutate(perc = 100* n / sum(n))

# A tibble: 10 x 4
# Groups:   variable [3]
#   variable value        n  perc
#   <chr>    <chr>    <int> <dbl>
# 1 color    blue         3  27.3
# 2 color    green        2  18.2
# 3 color    red          2  18.2
# 4 color    yellow       4  36.4
# 5 shape    circle       5  50  
# 6 shape    square       2  20  
# 7 shape    triangle     3  30  
# 8 size     large        2  33.3
# 9 size     medium       2  33.3
#10 size     small        2  33.3
user_90226650

Combining and adding to:

  1. Merge multiple frequency tables together in R

  2. Adding a column of total n for each group in a stacked frequency table


library(dplyr)
library(tidyr)
library(janitor)

options(digits = 3)

df %>% 
  pivot_longer(
    -ID,
    names_to = "Variable",
    values_to = "Level"
  ) %>% 
  mutate(Variable = str_extract(Variable, '[A-Za-z]*')) %>% 
  group_by(Variable, Level) %>% 
  count(Level, name = "Freq") %>% 
  na.omit() %>% 
  group_by(Variable) %>% 
  mutate(Percent = Freq/sum(Freq)*100) %>% 
  group_split() %>% 
  adorn_totals() %>% 
  bind_rows() %>% 
  mutate(Level = ifelse(Level == last(Level), last(Variable), Level)) %>% 
  mutate(Variable = ifelse(duplicated(Variable) |
                             Variable == "Total", NA, Variable))


 Variable    Level Freq Percent
    color     blue    3    27.3
     <NA>    green    2    18.2
     <NA>      red    2    18.2
     <NA>   yellow    4    36.4
     <NA>    Total   11   100.0
    shape   circle    5    50.0
     <NA>   square    2    20.0
     <NA> triangle    3    30.0
     <NA>    Total   10   100.0
     size    large    2    33.3
     <NA>   medium    2    33.3
     <NA>    small    2    33.3
     <NA>    Total    6   100.0
user_133216470

Try this matrix in list in base R

uniq <- unique( sub( "[0-9]","", colnames(dat[,-1]) ) )
uniq
[1] "color" "shape" "size"

sapply( uniq, function(x){ tbl <- table( unlist( dat[,grep( x, colnames(dat) )] ) ); 
  rbind( cbind( Percent=round( tbl/sum(tbl)*100, digits=2 ), Freq=tbl ), 
         cbind( sum(tbl/sum(tbl)*100), sum(tbl) ) ) } )
$color
         Percent Freq
blue       27.27    3
green      18.18    2
red        18.18    2
yellow     36.36    4
          100.00   11

$shape
         Percent Freq
circle        50    5
square        20    2
triangle      30    3
             100   10

$size
         Percent Freq
large      33.33    2
medium     33.33    2
small      33.33    2
          100.00    6

To get a data.frame

data.frame( do.call( rbind, sapply( uniq, function(x){ 
  tbl <- table( unlist( dat[,grep( x, colnames(dat) )] ) ); 
  rbind( cbind( Percent=round( tbl/sum(tbl)*100, digits=2 ), Freq=tbl, var=x ), 
         cbind( sum(tbl/sum(tbl)*100), sum(tbl), var=x ) ) } )
 ) )
         Percent Freq   var
blue       27.27    3 color
green      18.18    2 color
red        18.18    2 color
yellow     36.36    4 color
X            100   11 color
circle        50    5 shape
square        20    2 shape
triangle      30    3 shape
X.1          100   10 shape
large      33.33    2  size
medium     33.33    2  size
small      33.33    2  size
X.2          100    6  size

Data

dat <- structure(list(ID = c(55L, 67L, 83L, 78L, 43L, 29L), color1 = c("red", 
"yellow", "blue", "red", "green", "yellow"), color2 = c("blue", 
NA, "yellow", "yellow", NA, "green"), color3 = c(NA, NA, NA, 
"blue", NA, NA), shape1 = c("circle", "triangle", "circle", "square", 
"square", "circle"), shape2 = c("triangle", NA, NA, "circle", 
"circle", "triangle"), size = c("small", "medium", "large", "large", 
"small", "medium")), class = "data.frame", row.names = c(NA, 
-6L))
user_94620950
Related