Summing down columns in a simple table and rows as well

[Andrzej]

Hi All,
I would like to make an additional row with totals of three columns and one column with totals for summing values in rows:

aaa <- tibble::tribble(
      ~GDP, ~Percent,
    698358,      0.5,
   7521903,      4.5,
   1456387,     -3.2,
   1152673,     -1.1,
  10829321,      0.7
  )

^{Created on 2019-12-10 by the reprex package (v0.3.0)}

aaa <- tibble::tribble(
      ~GDP, ~Percent,
    698358,      0.5,
   7521903,      4.5,
   1456387,     -3.2,
   1152673,     -1.1,
  10829321,      0.7
  )

aaa 
#> # A tibble: 5 x 2
#>        GDP Percent
#>      <dbl>   <dbl>
#> 1   698358     0.5
#> 2  7521903     4.5
#> 3  1456387    -3.2
#> 4  1152673    -1.1
#> 5 10829321     0.7

^{Created on 2019-12-10 by the reprex package (v0.3.0)}

in order to finally look something like this:

To create a column "Total_1:", I think I would use mutate() and rowSums() ?
But more tricky would be to add an emty column with "Total_2:" in a last cell down there.

I tried to use janitor package:

aaa %>%  adorn_totals()

^{Created on 2019-12-10 by the reprex package (v0.3.0)}

but received an error:
Error: No common type for value <character> and x <double>.

Any help would be much appreciated.

Andrzej

[StatSteph]

This works but I'm not sure it's generalizable to a column with many columns.

library(tidyverse)

aaa <- tibble::tribble(
  ~GDP, ~Percent,
  698358,      0.5,
  7521903,      4.5,
  1456387,     -3.2,
  1152673,     -1.1,
  10829321,      0.7
)

aaa %>%
  summarise_all(sum) %>%
  mutate(FirstColumn="Total_2") %>%
  select(FirstColumn, everything()) %>%
  bind_rows(aaa) %>%
  arrange(GDP) %>%
  mutate(Total_1=GDP+Percent) 
#> # A tibble: 6 x 4
#>   FirstColumn      GDP Percent   Total_1
#>   <chr>          <dbl>   <dbl>     <dbl>
#> 1 <NA>          698358    0.5    698358.
#> 2 <NA>         1152673   -1.1   1152672.
#> 3 <NA>         1456387   -3.2   1456384.
#> 4 <NA>         7521903    4.5   7521908.
#> 5 <NA>        10829321    0.7  10829322.
#> 6 Total_2     21658642    1.40 21658643.

^{Created on 2019-12-10 by the reprex package (v0.3.0)}

[Andrzej]

Thank you @StatSteph for your prompt reply,
I can see it now that it must be a new "full" column created that is called FirstColumn filled with NAs values. What I did as a destination picture (in my first post), that was done in Excel which was pretty easy and fast to do. This is why on my picture, there is an empty column with only one cell at the bottom called "Total_2:"
Do you have any idea why janitor::adorn_totals() does not work here and what does that error that I received mean ?

kind regards,
Andrzej

[andresrcs]

This seems more suitable for a table rather than a dataframe, see this example

library(gt)
library(dplyr)

aaa <- tibble::tribble(
    ~GDP, ~Percent,
    698358,      0.5,
    7521903,      4.5,
    1456387,     -3.2,
    1152673,     -1.1,
    10829321,      0.7
)

aaa %>%
    mutate(Total_1 = rowSums(.)) %>% 
    gt() %>% 
    summary_rows(
        fns = c(Total_2 = ~sum(.))
    )

[Andrzej]

Thank you @andresrcs,

This is exactly what I wanted and looks like in Excel spreadsheet.
Would it be possible for you to help me to do it using Janitor package please (which is supposed to do this kind of summations) ?
I tried those janitor's functions like: add_totals_col(), add_totals_row() and adorn_totals(), but I got errors like: > Error: " i must have one dimension, not 2 " and other errors related I think to vctrs package output.

regards,
Andrzej

[andresrcs]

These functions are designed to work with tables containing counts for two variables and since you don't have a variable in the vertical margin it takes the values of GDP as so. This would be a dirty hack to get the desired result.

library(dplyr)
library(janitor)

aaa <- tibble::tribble(
    ~GDP, ~Percent,
    698358,      0.5,
    7521903,      4.5,
    1456387,     -3.2,
    1152673,     -1.1,
    10829321,      0.7
)

aaa %>%
    mutate(" " = "") %>%
    select(` `, everything()) %>% 
    adorn_totals(where = c("row", "col"))
#>             GDP Percent      Total
#>          698358     0.5   698358.5
#>         7521903     4.5  7521907.5
#>         1456387    -3.2  1456383.8
#>         1152673    -1.1  1152671.9
#>        10829321     0.7 10829321.7
#>  Total 21658642     1.4 21658643.4

[Andrzej]

Thank you very much indeed @andresrcs.
This is awesome, especially these lines:

@andresrcs

mutate(" " = "") %>% select(``, everything())

best regards,
Andrzej