Add total to and pivot a dataframe that has both numeric and text data

Question

I have what I thought was an easy question but turns out to be quite tricky.... I am trying to pivot a df from wide to long, adding column totals before pivoting. The df contains text and data however and this seems to make it problematic? Also, the real df (not the example Dput below) has a quite large number of columns and rows so ideally I would prefer not to do each individually. Please see below for what I am attempting:

structure(list(Name = c("John", "Marco", "Tony"), Date = c("23/04/2020", 
"23/04/2020", "23/04/2020"), Col1 = c(27, 30, 56), Col2 = c(26, 25, 45), Col3 = c("red", "blue","green")), row.names = c(NA, 
-3L), class = c("tbl_df", "tbl", "data.frame"))

Answer 1

You can use adorn_totals from janitor to add a new row with total. This adds dash ("-") for non-numeric columns. You can then get the data in long format using pivot_longer.

library(dplyr)

df %>%
   janitor::adorn_totals() %>%
   mutate(across(.fns = as.character)) %>%
   #mutate_all in `dplyr` < 1.0.0
   #mutate_all(as.character) %>%
   tidyr::pivot_longer(cols = starts_with('Col'))

   
# A tibble: 12 x 4
#   Name  Date       name  value
#   <chr> <chr>      <chr> <chr>
# 1 John  23/04/2020 Col1  27   
# 2 John  23/04/2020 Col2  26   
# 3 John  23/04/2020 Col3  red  
# 4 Marco 23/04/2020 Col1  30   
# 5 Marco 23/04/2020 Col2  25   
# 6 Marco 23/04/2020 Col3  blue 
# 7 Tony  23/04/2020 Col1  56   
# 8 Tony  23/04/2020 Col2  45   
# 9 Tony  23/04/2020 Col3  green
#10 Total -          Col1  113  
#11 Total -          Col2  96   
#12 Total -          Col3  -