dplyr how to calculate percent of multiple columns and output columns with custom names

user_7192318 0
Viewed 421

I want to calculate percent of each column in a Dataframe and make a custom name for each one.

Consider following code:

a<-structure(list(year = 2000:2005, Col1 = 1:6, Col2 = c(1L, 4L, 
9L, 16L, 25L, 36L)), row.names = c(NA, -6L), class = "data.frame")
> a
  year Col1 Col2
1 2000    1    1
2 2001    2    4
3 2002    3    9
4 2003    4   16
5 2004    5   25
6 2005    6   36
a<-a %>% rowwise() %>%
mutate(total = sum(across(starts_with("Col")), na.rm = T)) %>% data.frame()
a %>%
    mutate_at(vars(starts_with("Col")) , funs(P = ./a$total * 100))

The output is as follows:

enter image description here

How to handle the name of last two columns(for example per_Col1 and per_Col2 instead Col1_P and Col2_P, main question) ? Is there a better way(using dplyr package) to do this?( instead of calculate sum of columns and then divide each one to it)

dplyr r
3 Answers

You can use across along with the .names argument:

a %>%
  rowwise() %>%
  mutate(total = sum(across(starts_with("Col")), na.rm = TRUE)) %>% 
  mutate(across(starts_with("Col") , ~./total * 100, .names = 'per_{col}')) %>%
  ungroup()

which gives:

# A tibble: 6 x 6
   year  Col1  Col2 total per_Col1 per_Col2
  <int> <int> <int> <int>    <dbl>    <dbl>
1  2000     1     1     2     50       50  
2  2001     2     4     6     33.3     66.7
3  2002     3     9    12     25       75  
4  2003     4    16    20     20       80  
5  2004     5    25    30     16.7     83.3
6  2005     6    36    42     14.3     85.7
user_27257730

Here is a slightly different approach using scales:

library(scales)
library(dplyr)
a %>%
    rowwise() %>% 
    mutate(Total = sum(c_across(Col1:Col2), na.rm = TRUE), 
           across(Col1:Col2, ~percent(./sum(Total), accuracy = 0.1), .names ="percent_{.col}")
           )

output

   year  Col1  Col2 Total percent_Col1 percent_Col2
  <int> <int> <int> <int> <chr>        <chr>       
1  2000     1     1     2 50.0%        50.0%       
2  2001     2     4     6 33.3%        66.7%       
3  2002     3     9    12 25.0%        75.0%       
4  2003     4    16    20 20.0%        80.0%       
5  2004     5    25    30 16.7%        83.3%       
6  2005     6    36    42 14.3%        85.7%
user_133216470

We may use rowSums to make this vectorized

library(dplyr)
a %>%
    mutate(total = rowSums(across(starts_with('Col'))), 
     across(starts_with('Col'), ~ ./total * 100, .names = 'per_{.col}'))
  year Col1 Col2 total per_Col1 per_Col2
1 2000    1    1     2 50.00000 50.00000
2 2001    2    4     6 33.33333 66.66667
3 2002    3    9    12 25.00000 75.00000
4 2003    4   16    20 20.00000 80.00000
5 2004    5   25    30 16.66667 83.33333
6 2005    6   36    42 14.28571 85.71429
user_37322710
Related