Sum columns and new row in dataframe...again

[jose_perez]

This is my first post, so sorry if I do anything wrong

I have the same problem mentioned here:
https://forum.posit.co/t/how-to-sum-columns-to-create-a-new-row-of-data-in-a-dataframe/96673, but when I try to apply the solution I get an error: Error in colSums (dat1): 'x' must be numeric.

I assume that this is because one of the columns has character format, but I don't know how to include it in the proposed solution: dat1 [nrow (dat1) + 1,] <- colSums (dat1).

Any idea? Thanks to all in advance.

[lars]

Hi Jose,

Can you please provide a small sample of your dataset which produces the issue you encounter? This helps us to better understand and provide a solution for your issue.

You might find the following guide helpful:

FAQ: How to do a minimal reproducible example ( reprex ) for beginners

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

[jose_perez]

Sorry for this, the same sample of the original post is valid

test
a b c d e f g
1 27 56 87 32 0 5 6
2 0 0 20 21 12 5 6

and create a new row with totals

test1
a b c d e f g
1 27 56 87 32 0 5 6
2 0 0 20 21 12 5 6
3 27 56 107 53 12 10 12

[lars]

Well, the provided dataset is inconsistent: it contains 7 column names, but has 8 column values..

Anyway, there are several ways to achieve the desired outcome and the following is along the lines of the referenced post:

library(dplyr)

## create a dataframe
df <- tribble(
  ~a, ~b, ~c, ~d, ~e, ~f, ~g,
  1, 27, 56, 87, 32, 0, 5,
  2, 0, 0, 20, 21, 12, 5,
  )
df
#> # A tibble: 2 x 7
#>       a     b     c     d     e     f     g
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1     1    27    56    87    32     0     5
#> 2     2     0     0    20    21    12     5
  
## add a new row containing the sum of each column
df[nrow(df) + 1, ] <- as.list(colSums(df))
df
#> # A tibble: 3 x 7
#>       a     b     c     d     e     f     g
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1     1    27    56    87    32     0     5
#> 2     2     0     0    20    21    12     5
#> 3     3    27    56   107    53    12    10

^{Created on 2021-03-19 by the reprex package (v1.0.0)}

HTH

[jose_perez]

Thanks for your help Lars, but the same error appears : Error in colSums (df): 'x' must be numeric.

I made a mistake on the table, sorry for this. First column is a character one (chr) not numeric.

a, b, c, d, e, f, g,
men, 27, 56, 87, 32, 0, 5,
women, 0, 0, 20, 21, 12, 5,

How can I avoid first column in your solution? I think that will remove the error message...

[lars]

Hi Jose,

You can do it like below.
However, I have to mention that there are better and more robust ways of doing column manipulation, especially when your real dataset is not as simple as this one.

library(dplyr)

## create sample dataframe
df <- tribble(
  ~a, ~b, ~c, ~d, ~e, ~f, ~g,
  "men", 27, 56, 87, 32, 0, 5,
  "women", 0, 0, 20, 21, 12, 5,
  )
df
#> # A tibble: 2 x 7
#>   a         b     c     d     e     f     g
#>   <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 men      27    56    87    32     0     5
#> 2 women     0     0    20    21    12     5

## get totals from column 2 onwards 
row_totals <- colSums(df[,2:NCOL(df)])

## append row with totals
df[nrow(df) + 1, ] <- c(a = "totals", as.list(row_totals))
df
#> # A tibble: 3 x 7
#>   a          b     c     d     e     f     g
#>   <chr>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 men       27    56    87    32     0     5
#> 2 women      0     0    20    21    12     5
#> 3 totals    27    56   107    53    12    10

^{Created on 2021-03-19 by the reprex package (v1.0.0)}

[jose_perez]

Perfect! Thanks a lot lars.

I will continue to investigate column manipulation, its something I have to do often.

[lars]

Cool!

I don't know if you're familiar with the R for Data Science book, otherwise you might be interested in chapter 5 Data transformation:

Visualisation is an important tool for insight generation, but it is rare that you get the data in exactly the right form you need. Often you’ll need to create some new variables or summaries, or maybe you just want to rename the variables or reorder the observations in order to make the data a little easier to work with. You’ll learn how to do all that (and more!) in this chapter [..]

Welcome | R for Data Science

[sgenter]

You could also try the adorn_totals function from the janitor package

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(janitor)
#> 
#> Attaching package: 'janitor'
#> The following objects are masked from 'package:stats':
#> 
#>     chisq.test, fisher.test
  
df <- tribble(
  ~a, ~b, ~c, ~d, ~e, ~f, ~g,
  "men", 27, 56, 87, 32, 0, 5,
  "women", 0, 0, 20, 21, 12, 5,
)
df
#> # A tibble: 2 x 7
#>   a         b     c     d     e     f     g
#>   <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 men      27    56    87    32     0     5
#> 2 women     0     0    20    21    12     5

df %>% adorn_totals()
#>      a  b  c   d  e  f  g
#>    men 27 56  87 32  0  5
#>  women  0  0  20 21 12  5
#>  Total 27 56 107 53 12 10

^{Created on 2021-03-19 by the reprex package (v0.3.0)}