How to change the direction of application of mutate across from column-wise to row-wise?

Suppose we start with the below dataframe, generated via code immediately beneath:

> data
  To A B C
1  A 1 3 5
2  B 2 4 6
3  C 4 5 7

data <- 
  data.frame(
    To = c("A","B","C"),
    A = c(1,2,4),
    B = c(3,4,5),
    C = c(5,6,7)
  )

Now we add column and row totals resulting in the below revised dataframe, generated by the code shown immediately beneath:

> data
   To A  B  C Sum
1   A 1  3  5   9
2   B 2  4  6  12
3   C 4  5  7  16
4 Sum 7 12 18  37

data <- data %>% 
  replace(is.na(.), 0) %>%
  bind_rows(summarise_all(., ~(if(is.numeric(.)) sum(.) else "Sum")))
data <- cbind(data, Sum = rowSums(data[,-1]))

Finally, we calculate the percentages of the column totals represented by each element in data:

   To         A         B         C       Sum
1   A 0.1428571 0.2500000 0.2777778 0.2432432
2   B 0.2857143 0.3333333 0.3333333 0.3243243
3   C 0.5714286 0.4166667 0.3888889 0.4324324
4 Sum 1.0000000 1.0000000 1.0000000 1.0000000

library(tidyverse)
data %>% mutate(across(-c(To), ~ ./.[To == "Sum"]))

Question: how to modify the above code so that we instead calculate the percentage of row totals, instead of column totals? So we would end up with the below proportions (hand calculated so please pardon any minor errors, and don't worry about rounding):

  To         A         B         C       Sum
1   A 0.1111111 0.3333333 0.5555555 1.0000000
2   B 0.1666666 0.3333333 0.5000000 1.0000000
3   C 0.2500000 0.3125000 0.4375000 1.0000000
4 Sum 0.5277771 0.9791666 1.4930555 3.0000000

2 answers

  • answered 2022-05-04 10:44 Carl

    Try this. (Note row 4 also sums across to 1.)

    library(tidyverse)
    
    data <- 
      data.frame(
        To = c("A","B","C"),
        A = c(1,2,4),
        B = c(3,4,5),
        C = c(5,6,7)
      )
    
    data <- data %>% 
      replace(is.na(.), 0) %>%
      bind_rows(summarise_all(., ~(if(is.numeric(.)) sum(.) else "Sum")))
    data <- cbind(data, Sum = rowSums(data[,-1]))
    
    data %>% 
      rowwise() %>%
      mutate(across(A:Sum, ~ sum(.) / Sum))
    #> # A tibble: 4 × 5
    #> # Rowwise: 
    #>   To        A     B     C   Sum
    #>   <chr> <dbl> <dbl> <dbl> <dbl>
    #> 1 A     0.111 0.333 0.556     1
    #> 2 B     0.167 0.333 0.5       1
    #> 3 C     0.25  0.312 0.438     1
    #> 4 Sum   0.189 0.324 0.486     1
    

    Created on 2022-05-04 by the reprex package (v2.0.1)

  • answered 2022-05-04 11:13 PaulS

    A possible solution:

    library(dplyr)
    
    data %>% 
      mutate(Sum = rowSums(across(A:C))) %>% 
      mutate(across(A:Sum, ~ .x / Sum)) %>% 
      bind_rows(data.frame(To = "Sum", t(colSums(.[-1]))))
    
    #>    To         A         B         C Sum
    #> 1   A 0.1111111 0.3333333 0.5555556   1
    #> 2   B 0.1666667 0.3333333 0.5000000   1
    #> 3   C 0.2500000 0.3125000 0.4375000   1
    #> 4 Sum 0.5277778 0.9791667 1.4930556   3
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum