How to add row of total and percentage in list of data table in R?

I just added a sample here. I just want to add the fourth row of totals to sum the columns y and z, not PROVINCE.



#> [[1]]
#>   PROVINCE         Y        Z
#> 1        2  89.98376 90.79929
#> 2        2  97.12802 90.68774
#> 3        4 100.62682 91.20288
#> 
#> [[2]]
#>   PROVINCE         Y         Z
#> 1        4 106.09127  94.19237
#> 2        6 111.30426 107.42695
#> 3        5  84.44623 102.04221

Hi @Shadan

You can use the adorn_totals() function, which is in the janitor package. Here, I provide two solutions: one with base R and one with the purrr package. Let me know if you have more questions:

# Load the janitor package
library(janitor)

# Create sample data
set.seed(1)
d <- lapply(1:2, function(x) data.frame(province = sample(6, size = 3), Y = rnorm(3)*100, Z = rnorm(3)* 100))

# Solution using base R
lapply(d, function(x) adorn_totals(dat = x, where = "row"))

[[1]]
 province          Y         Z
        6 -137.70596  -5.93134
        2  -41.49946 110.00254
        1  -39.42900  76.31757
    Total -218.63441 180.38877

[[2]]
 province          Y          Z
        6  -83.20433 -156.37821
        3 -116.65705  115.65370
        1 -106.55906   83.20471
    Total -306.42044   42.48021

# Solution using the tidyverse
library(purrr)
map(d, ~ adorn_totals(.x, where = "row"))

[[1]]
 province          Y         Z
        6 -137.70596  -5.93134
        2  -41.49946 110.00254
        1  -39.42900  76.31757
    Total -218.63441 180.38877

[[2]]
 province          Y          Z
        6  -83.20433 -156.37821
        3 -116.65705  115.65370
        1 -106.55906   83.20471
    Total -306.42044   42.48021

1 Like

Actually, even though I answered the question with adorn_totals(), I would say that adding a row for total and for percentage in your data is not the best way to do things. I understand that you may want to do it to present your results, in which case I recommend the gt package for creating tables with summary rows. The following article will teach you how to do it: https://gt.rstudio.com/articles/creating-summary-lines.html However, you should not add total and percentage rows to your data.

Also, I have a question about the percentage value that you are expecting. Could you please tell me what percentage values you are expecting given your examples above?

Thanks for sharing the link. The percentage must be like:

			
province	y	z	
2	0	8.670000076	
3	0	0	
6	0	19.92000008	
23	30.90999985	2346.489988	
Total	30.90999985	2375.079988	
percentage 	0.012847102	0.987152898

First must calculate the Total row then Totalcolumny/totalof rowtotal.

@Shadan

I would like to repeat again that this is not really a good practice; however, if you really need to do that, here is a code that I wrote for you:

# Load the janitor package
library(janitor)

# Create sample data
set.seed(1)
d <- lapply(1:2, function(x) data.frame(province = sample(6, size = 3), Y = runif(3)*100, Z = runif(3)* 100))
d
[[1]]
  province        Y        Z
1        1 90.82078 94.46753
2        4 20.16819 66.07978
3        3 89.83897 62.91140

[[2]]
  province        Y        Z
1        2 68.70228 49.76992
2        3 38.41037 71.76185
3        5 76.98414 99.19061

# Solution using base R
lapply(d, function(x){
  
  last_row <- nrow(x)
  total <- sapply(x[, c("Y", "Z")], sum)
  pct <- total / sum(total)
  
  x[last_row+1, ] <- c("Total", total)
  x[last_row+2, ] <- c("Percentage", pct)
  
  x

[[1]]
    province                 Y                 Z
1          1  90.8207789994776  94.4675268605351
2          4  20.1681931037456  66.0797792486846
3          3  89.8389684967697   62.911404389888
4      Total  200.827940599993  223.458710499108
5 Percentage 0.473330801428126 0.526669198571874

[[2]]
    province                 Y                 Z
1          2  68.7022846657783  49.7699242085218
2          3  38.4103718213737  71.7618508264422
3          5  76.9841419998556  99.1906094830483
4      Total  184.096798487008  220.722384518012
5 Percentage 0.454763030547208 0.545236969452792
 
})

@gueyenono, Thank you for writing the code which I am looking for. Just want to know if I have a different number of columns in the list. I do not know you change the below code to apply for all columns in each data frame in the list.
Here we have two columns( Y, Z) in both data frames in the list. My list data frame has different numbers of columns. I just added a screenshot of my list
image

 total <- sapply(x[, c("Y", "Z")], sum)

@Shadan Your picture is not showing.

I upload again:
image

Is the first column "province" in each data frame of the list?

Yes, "province" is the first column for all. Your code is great. It is the code that I am looked for, just I do not know to change this line of code: sapply(x[, c("Y", "Z")], sum). As you wrote c("Y", "Z") my list has a different number of columns. Instead of c("Y", "Z") should be something to represent all columns. Thank you.

This should work then:

lapply(d, function(x){
  
  last_row <- nrow(x)
  total <- sapply(x[, -1)], sum)
  pct <- total / sum(total)
  
  x[last_row+1, ] <- c("Total", total)
  x[last_row+2, ] <- c("Percentage", pct)
  
  x

})

@gueyenono there is "province, district, sub_district, and village" should not get the total and percentage.
The data frame look like this : image
I used the following code :

lapply(d, function(x){
  
  last_row <- nrow(x)
  total <- sapply(x[, -4)], sum)
  pct <- total / sum(total)
  
  x[last_row+1, ] <- c("Total", total)
  x[last_row+2, ] <- c("Percentage", pct)
  
  x

})
 Instead "-1 " -4 but did not work.

I guess the closest solution would be:

lapply(d, function(x){
  
  last_row <- nrow(x)
  total <- sapply(x[, -(1:4)], sum)
  pct <- total / sum(total)
  
  x[last_row+1, ] <- c(rep("Total", 4), total)
  x[last_row+2, ] <- c(rep("Percentage", 4), pct)
  
  x

})

Once again, you should really look into the gt package to make nice summary tables instead of adding your summaries right into the data itself.

Thank you, sir. It has done!

Thank sir for all your time and solving my problems.
Is it possible to move the percentage row to top ( after column names) and total row to be in the second row of each data frame in the list?

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

@gueyenono Thank you for answering.

@gueyenono If I have PROVINCE and DISTRICT like bellow. Which column DISTRICT should calculate.

#> [[1]]
#>   PROVINCE  DISTRICT        Y        Z
#> 1        2         2     89.98376 90.79929
#> 2        2        7      97.12802 90.68774
#> 3        4       10    100.62682 91.20288
#> 
#> [[2]]
#>   PROVINCE DISTRICT         Y         Z
#> 1        4         2     106.09127  94.19237
#> 2        6         3    111.30426 107.42695
#> 3        5         4    84.44623 102.04221