0

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"))

enter image description here

Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
Montana_innit96
  • 7
  • 5

1 Answers1

2

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  -
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Thank you @Ronak, this is brilliant! Only one issue - not all columns start with 'Col', actually a wide variety of names? – Montana_innit96 Aug 11 '20 at 08:07
  • In `cols` you can also give columns by position `cols = 4:15` or `cols = c(2:5, 7, 10:12)` or range by name `cols = A:F` meaning from column A to F. How can you uniquely identify those columns which you want in long format? – Ronak Shah Aug 11 '20 at 08:09
  • I keep getting errors where function not found? Package conflict? For example: Error in mutate(., across(.fns = as.character)) : could not find function "mutate" Error in across(.fns = as.character) : could not find function "across" – Montana_innit96 Aug 11 '20 at 08:16
  • Did you load `library(dplyr)` ? Also if `packageVersion('dplyr')` is less than 1.0.0 you need to use `mutate_all` as mentioned in the comment of the answer. – Ronak Shah Aug 11 '20 at 08:19
  • ah, thank you! It is version 0.8.5, I still have the following error - Error in across(.fns = as.character) : could not find function "across" – Montana_innit96 Aug 11 '20 at 08:22
  • Read my answer and previous comment again. You need to use `mutate_all(as.character) ` instead of `mutate(across(.fns = as.character))` – Ronak Shah Aug 11 '20 at 08:24
  • sir, you are simply brilliant! – Montana_innit96 Aug 11 '20 at 08:29
  • actually, one small adjustment - the 'total' label falls under the date column now, it needs to be under 'Names'? Is there an easy way to fix this? – Montana_innit96 Aug 11 '20 at 08:33
  • Make `Name` column the first column. Try `df %>% select(Name, everything()) %>% janitor::adorn_totals(....rest of the code.....` – Ronak Shah Aug 11 '20 at 08:36
  • What do you mean by it does not work? Did it return an error? It works for me, I can `select` any column and the 'Total' value is under that column. `df1 <- df %>% select(Name, everything()) %>% janitor::adorn_totals() %>% mutate_all(as.character) %>% tidyr::pivot_longer(cols = starts_with('Col'))` should work. Change `starts_with` with the code that you have used to select columns. – Ronak Shah Aug 11 '20 at 08:49