R - Adding a total row in Excel output

Question

I want to add a total row (as in the Excel tables) while writing my data.frame in a worksheet. Here is my present code (using openxlsx):

writeDataTable(wb=WB, sheet="Data", x=X, withFilter=F, bandedRows=F, firstColumn=T)

X contains a data.frame with 8 character variables and 1 numeric variable. Therefore the total row should only contain total for the numeric row (it will be best if somehow I could add the Excel total row feature, like I did with firstColumn while writing the table to the workbook object rather than to manually add a total row).

I searched for a solution both in StackOverflow and the official openxslx documentation but to no avail. Please suggest solutions using openxlsx.

EDIT:

Adding data sample:

A  B  C  D  E  F  G  H  I
a  b  s  r  t  i  s  5  j
f  d  t  y  d  r  s  9  s
w  s  y  s  u  c  k  8  f

After Total row:

    A  B  C  D  E  F  G  H  I
    a  b  s  r  t  i  s  5  j
    f  d  t  y  d  r  s  9  s
    w  s  y  s  u  c  k  8  f
    na na na na na na na 22 na

Please suggest improvements to the question without outright downvoting first. — Arani, Oct 11 '17 at 05:32

score 1 · Answer 1 · answered Oct 11 '17 at 07:06

Assuming your data is stored in a data.frame called df:

df <- read.table(text = 
    "A  B  C  D  E  F  G  H  I
    a  b  s  r  t  i  s  5  j
    f  d  t  y  d  r  s  9  s
    w  s  y  s  u  c  k  8  f", 
                 header = TRUE,
                 stringsAsFactors = FALSE)

You can create a row using lapply

totals <- lapply(df, function(col) {
  ifelse(!any(!is.numeric(col)), sum(col), NA)
})

and add it to df using rbind()

df <- rbind(df, totals)

head(df)
     A    B    C    D    E    F    G  H    I
1    a    b    s    r    t    i    s  5    j
2    f    d    t    y    d    r    s  9    s
3    w    s    y    s    u    c    k  8    f
4 <NA> <NA> <NA> <NA> <NA> <NA> <NA> 22 <NA>

score 1 · Accepted Answer · answered Aug 03 '19 at 02:29

library(janitor)
adorn_totals(df, "row")

#>      A B C D E F G  H I
#>      a b s r t i s  5 j
#>      f d t y d r s  9 s
#>      w s y s u c k  8 f
#>  Total - - - - - - 22 -

If you prefer empty space instead of - in the character columns you can specify fill = "" or fill = NA.

R - Adding a total row in Excel output

2 Answers2