Sum columns and new row in dataframe...again

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

I have the same problem mentioned here:
https://community.rstudio.com/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.

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:

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

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

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...

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)

1 Like

Perfect! :grinning: Thanks a lot lars.

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

1 Like

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 [..]

1 Like

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)

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.