How do I use R to get percentages of a category by another a category?

user_9665117 0
Viewed 80

Ok, so I have a table that looks a little something like this for the fist several rows:

Department Diagnosis Code
Dept. 1 Code1
Dept. 2 Code2
Dept. 3 Code3
Dept. 3 Code3
Dept. 3 Code4
Dept. 4 Code4
Dept. 4 Code4
Dept. 4 Code5
Dept. 4 Code5
Dept. 4 Code5

What I want is to develop a table that looks like this:

Department Code1% Code2% Code3% Code4% Code5%
Dept. 1 xx% xx% xx% xx% xx%

Where the above percentages are the percentage of each code for each department, ie it's the total number of times Code1 appears within Department 1, divided by the total number of occurrences of "Code instances" within Department 1. So if Code 1 appeared 50 times in Department 1, and Department 1 had 120 recorded Department-Code instances across all Codes, the percent ought to be 50/120.

I'm trying to use some combination of group_by(), mutate(), and summarise() to get the job done, but I'm having trouble figuring out how to properly combine and write code to get the output I want.

I've seen a lot example code showing something similar when the second column is of some numeric frequency type, but I have yet to find something that does the same when the second column consists of strings corresponding to discrete categories.

**EDIT: Also, the codes are an alphanumeric. For example, one code may be something like E77.09, while another might be something like C30, and another might be D24.3

exploratory-data-analysis tidyr dplyr data-analysis r
3 Answers
df<- data.frame(
  stringsAsFactors = FALSE,
             Department = c("Dept. 1","Dept. 2",
                            "Dept. 3","Dept. 3","Dept. 3","Dept. 4",
                            "Dept. 4","Dept. 4","Dept. 4","Dept. 4"),
         Diagnosis.Code = c("Code1","Code2",
                            "Code3","Code3","Code4","Code4","Code4","Code5",
                            "Code5","Code5")
     )

library(dplyr, warn.conflicts = FALSE)
library(janitor)


df %>% tabyl(Department, Diagnosis.Code) %>%
  adorn_percentages() %>%
  adorn_pct_formatting(2)

#>  Department   Code1   Code2  Code3  Code4  Code5
#>     Dept. 1 100.00%   0.00%  0.00%  0.00%  0.00%
#>     Dept. 2   0.00% 100.00%  0.00%  0.00%  0.00%
#>     Dept. 3   0.00%   0.00% 66.67% 33.33%  0.00%
#>     Dept. 4   0.00%   0.00%  0.00% 40.00% 60.00%

Created on 2021-07-21 by the reprex package (v2.0.0)

user_28848590

1 The simplest way is base R.

tbl <- table(df1[[1]], df1[[2]])
100*tbl/rowSums(tbl)
#              Code1     Code2     Code3     Code4     Code5
#  Dept. 1 100.00000   0.00000   0.00000   0.00000   0.00000
#  Dept. 2   0.00000 100.00000   0.00000   0.00000   0.00000
#  Dept. 3   0.00000   0.00000  66.66667  33.33333   0.00000
#  Dept. 4   0.00000   0.00000   0.00000  40.00000  60.00000

And another.

xtb <- xtabs(~ Department + Code, df1)
100*xtb/rowSums(xtb)

2 Here are solutions with dplyr and tidyr.

library(dplyr)
library(tidyr)

df1 %>%
  group_by(Department) %>%
  mutate(d = n()) %>%
  group_by(Department, Code) %>%
  summarise(Perc = n()/first(d), .groups = "drop") %>%
  pivot_wider(
    id_cols = Department,
    names_from = Code,
    values_from = Perc
  )
## A tibble: 4 x 6
#  Department Code1 Code2  Code3  Code4 Code5
#  <chr>      <dbl> <dbl>  <dbl>  <dbl> <dbl>
#1 Dept. 1        1    NA NA     NA      NA  
#2 Dept. 2       NA     1 NA     NA      NA  
#3 Dept. 3       NA    NA  0.667  0.333  NA  
#4 Dept. 4       NA    NA NA      0.4     0.6

To have the values in percentages with zeros where there are NA's, a simple change will do it.

df1 %>%
  group_by(Department) %>%
  mutate(d = n()) %>%
  group_by(Department, Code) %>%
  summarise(Perc = 100 * n()/first(d), .groups = "drop") %>%
  pivot_wider(
    id_cols = Department,
    names_from = Code,
    values_from = Perc,
    values_fill = 0
  )

Data

df1 <-
structure(list(Department = c("Dept. 1", "Dept. 2", "Dept. 3", 
"Dept. 3", "Dept. 3", "Dept. 4", "Dept. 4", "Dept. 4", "Dept. 4", 
"Dept. 4"), Code = c("Code1", "Code2", "Code3", "Code3", "Code4", 
"Code4", "Code4", "Code5", "Code5", "Code5")), row.names = c(NA, 
-10L), class = "data.frame")
user_82454060

If you want a simple base R solution:

tab = table(dat[,1], dat[,2])
tab / rowSums(tab) * 100

              Code1     Code2     Code3     Code4     Code5
  Dept. 1 100.00000   0.00000   0.00000   0.00000   0.00000
  Dept. 2   0.00000 100.00000   0.00000   0.00000   0.00000
  Dept. 3   0.00000   0.00000  66.66667  33.33333   0.00000
  Dept. 4   0.00000   0.00000   0.00000  40.00000  60.00000
user_57847570
Related