Search code examples
rtidyversetidyr

How to add percents next to a column value

I have a large dataframe but this is a small subsample of it. I am trying to add a percent of each value right next to the daily value of each of my columns but in this case for only column 'F'. I am able to create two extra columns of the percents and daily values together surrounded by curly brackets but I was wondering if it is possible to get rid of the newly 'Perc_F' and 'both' columns and make the computations within the 'F' column. Let me know if is not clear what I am trying to achieve.

library(tidyverse)

  SampleDate year F W  S LF
1 2013-01-05 2013 0 2  4  1
2 2013-01-06 2013 1 0  0  0
3 2013-01-07 2013 5 1  2  5
4 2013-01-08 2013 2 0  0  1
5 2013-01-09 2013 0 6  1  4
6 2013-01-10 2013 1 0  7  0
7 2013-01-11 2013 0 1  0  2
8 2013-01-12 2013 7 4 11  1

#Data
a <- structure(list(SampleDate = structure(c(15710, 15711, 15712, 
15713, 15714, 15715, 15716, 15717), class = "Date"), year = c("2013", 
"2013", "2013", "2013", "2013", "2013", "2013", "2013"), F = c(0, 
1, 5, 2, 0, 1, 0, 7), W = c(2, 0, 1, 0, 6, 0, 1, 4), S = c(4, 
0, 2, 0, 1, 7, 0, 11), LF = c(1, 0, 5, 1, 4, 0, 2, 1)), row.names = c(NA, 
8L), class = "data.frame")
a$SampleDate <- as.Date(a$SampleDate)

#Create columns 'Perc_F' and 'both'with percents and values and percents respectively 
a %>% 
    mutate(Perc_F = F/sum(F),Perc_F = scales::percent(Perc_F)) |>
    mutate(both = paste0(F,"(",Perc_F,")"))
    
   SampleDate year F W  S LF Perc_F     both
1 2013-01-05 2013 0 2  4  1   0.0%  0(0.0%)
2 2013-01-06 2013 1 0  0  0   6.2%  1(6.2%)
3 2013-01-07 2013 5 1  2  5  31.2% 5(31.2%)
4 2013-01-08 2013 2 0  0  1  12.5% 2(12.5%)
5 2013-01-09 2013 0 6  1  4   0.0%  0(0.0%)
6 2013-01-10 2013 1 0  7  0   6.2%  1(6.2%)
7 2013-01-11 2013 0 1  0  2   0.0%  0(0.0%)
8 2013-01-12 2013 7 4 11  1  43.8% 7(43.8%)

#Instead of creating 'Perc_F' and 'both' columns, I would like
#The percents with curly brackets added to each value of the "F" column.
  
#Final dataframe should look like this for column "F"    
  SampleDate year F           W  S LF 
1 2013-01-05 2013 0(0.0%)     2  4  1   
2 2013-01-06 2013 1(6.2%)     0  0  0   
3 2013-01-07 2013 5(31.2%)    1  2  5  
4 2013-01-08 2013 2(12.5%)    0  0  1  
5 2013-01-09 2013 0(0.0%)     6  1  4   
6 2013-01-10 2013 1(6.2%)     0  7  0   
7 2013-01-11 2013 0(0.0%)     1  0  2   
8 2013-01-12 2013 7(43.8%)    4 11  1
Solution

  • You could write a function like this:

    add_percent <-
  function(x)
    paste0(x, "(", round(x / sum(x) * 100, 1), "%)")

    And then apply it to the columns you want to add percent to.

    library(tidyverse)

a |>
  mutate(across(F:LF, add_percent))
#>   SampleDate year        F        W       S       LF
#> 1 2013-01-05 2013    0(0%) 2(14.3%)  4(16%)  1(7.1%)
#> 2 2013-01-06 2013  1(6.2%)    0(0%)   0(0%)    0(0%)
#> 3 2013-01-07 2013 5(31.2%)  1(7.1%)   2(8%) 5(35.7%)
#> 4 2013-01-08 2013 2(12.5%)    0(0%)   0(0%)  1(7.1%)
#> 5 2013-01-09 2013    0(0%) 6(42.9%)   1(4%) 4(28.6%)
#> 6 2013-01-10 2013  1(6.2%)    0(0%)  7(28%)    0(0%)
#> 7 2013-01-11 2013    0(0%)  1(7.1%)   0(0%) 2(14.3%)
#> 8 2013-01-12 2013 7(43.8%) 4(28.6%) 11(44%)  1(7.1%)

    The problem with this strategy is that you’ll end up with columns that are not useful for any further calculations. If you are aiming to create result tables, you’d be better off using a package for result tables, like gt, which has functions to merge n and percent in the fashion you are looking for, and you’ll end up with a table that looks great for publication/sharing.

    library(gt)

a |>
  mutate(across(F:LF, \(x) x / sum(x), .names = "perc_{.col}")) |>
  gt() |>
  fmt_percent(perc_F:perc_LF) |>
  cols_merge_n_pct(F, perc_F) |>
  cols_merge_n_pct(W, perc_W) |>
  cols_merge_n_pct(S, perc_S) |>
  cols_merge_n_pct(LF, perc_LF)