R: colSums when not all columns are numeric

Question

I have the following data frame

Type   CA   AR
alpha   1   5  
beta    4   9 
gamma   3   8

I want to get the column and row sums such that it looks like this:

    Type   CA   AR Total
    alpha   1   5    6
    beta    4   9    13
    gamma   3   8    11
    Total   8   22   30

I am able to do rowSums (as shown above) I guess because they are all numeric.

colSums(df)

However, when I do colSums I get the error 'x must be numeric.' I realize that this is because the "Type" column is not numeric.

If I do the following code such that I try to print the value into the 4th row (and only the 2nd through 4th columns are summed)

df[,4] = colSums(df[c(2:4)]

Then I get an error that replacement isn't same as data size.

Does anyone know how to work around this? I want to print the column sums for columns 2-4, and leave the 1st column total blank or allow me to print "Total"?

Thanks in advance!!

The erros is because you are asking R to bind a `n` column object with an `n-1` vector and maybe R doesn't know hot to compute this due to length difference. Try this `data[4, ] <- c(NA, colSums(data[, 2:3]) )` — SabDeM, May 27 '15 at 16:34
The error is because rows are not columns. There's no `n` versus `n-1` conceptual problem. Imagine a dataframe with 20 rows and 5 columns. — IRTFM, May 27 '15 at 16:42
A similar post is http://stackoverflow.com/questions/4946873/how-do-i-add-a-row-to-a-data-frame-with-totals — akrun, May 27 '15 at 17:33

aaronwolen · Answer 1 · 2015-05-27T16:58:35.843

8

Checkout numcolwise() in the plyr package.

library(plyr)

df <- data.frame(
  Type = c("alpha", "beta", "gamme"),
  CA = c(1, 4, 3),
  AR = c(5, 9, 8)
)

numcolwise(sum)(df)

Result:

  CA AR
1  8 22

edited May 27 '15 at 16:58

answered May 27 '15 at 16:35

aaronwolen

3,723
1
20
21

1

It shouldn't. Try the reproducible example I just posted. – aaronwolen May 27 '15 at 16:58
I get this error; `Error in match.fun(FUN) : '2.55108265612583e+24' is not a function, character or symbol` – IRTFM May 27 '15 at 18:06
The error persists after updating to plyr ver 1.0.1. I cannot figure out what might be different although I'm still using R 3.1.2 – IRTFM May 27 '15 at 18:13

Frank · Answer 2 · 2015-05-27T17:19:39.260

4

Use a matrix:

m <- as.matrix(df[,-1])
rownames(m) <- df$Type
#       CA AR
# alpha  1  5
# beta   4  9
# gamma  3  8

Then add margins:

addmargins(m,FUN=c(Total=sum),quiet=TRUE)
#       CA AR Total
# alpha  1  5     6
# beta   4  9    13
# gamma  3  8    11
# Total  8 22    30

The simpler addmargins(m) also works, but defaults to labeling the margins with "Sum".

edited May 27 '15 at 17:19

answered May 27 '15 at 17:14

Frank

66,179
8
96
180

score 3 · Answer 3 · answered May 27 '15 at 20:42

You are right, it is because the first column is not numeric.

Try to use the first column as rownames:

df <- data.frame(row.names = c("alpha", "beta", "gamma"), CA = c(1, 4, 3), AR = c(5, 9, 8))
df$Total <- rowSums(df)
df['Total',] <- colSums(df)
df

The output will be:

      CA AR Total
alpha  1  5     6
beta   4  9    13
gamma  3  8    11
Total  8 22    30

If you need the word 'Type', just remove the rownames and add the column back:

Type <- rownames(df)
df <- data.frame(Type, df, row.names=NULL)
df

And it's output:

   Type CA AR Total
1 alpha  1  5     6
2  beta  4  9    13
3 gamma  3  8    11
4 Total  8 22    30

Chris · Answer 4 · 2015-05-27T16:57:53.903

Use:

df$Total <- df$CA + df$AR

A more general solution:

data$Total <- Reduce('+',data[, sapply(data, is.numeric)])

EDIT: I realize I completely misunderstood the question. you are indeed looking for the sum of rows, and I gave sum of columns.

To do rows instead:

data <- data.frame(x = 1:3, y = 4:6, z = as.character(letters[1:3]))
data$z <- as.character(data$z)
rbind(data,sapply(data, function(y) ifelse(test = is.numeric(y), Reduce('+',y), "Total")))

IRTFM · Answer 5 · 2015-05-27T18:05:30.990

If you do not know which columns are numeric, but rather want the sums across rows then do this:

df$Total = rowSums( df[ sapply(df, is.numeric)] )

The is.numeric function will return a logical value which is valid for selecting columns and sapply will return the logical values as a vector. To add a set of column totals and a grand total we need to rewind to the point where the dataset was created and prevent the "Type" column from being constructed as a factor:

 dat <- read.table(text="Type   CA   AR
 alpha   1   5  
 beta    4   9 
 gamma   3   8 ",stringsAsFactors=FALSE)

 dat$Total = rowSums( dat[ sapply(dat, is.numeric)] )

 rbind( dat, append(c(Type="Total"),  
                    as.list(colSums( dat[ sapply(dat, is.numeric)] ))))
#----------
   Type CA AR Total
1 alpha  1  5     6
2  beta  4  9    13
3 gamma  3  8    11
4 Total  8 22    30

That's a data.frame:

> str( rbind( dat, append(c(Type="Total"),  as.list(colSums( dat[ sapply(dat, is.numeric)] )))) )
'data.frame':   4 obs. of  4 variables:
 $ Type : chr  "alpha" "beta" "gamma" "Total"
 $ CA   : num  1 4 3 8
 $ AR   : num  5 9 8 22
 $ Total: num  6 13 11 30

I think we misunderstood the question. user4918087 is looking to do column sums, not row sums. Note the Total at the bottom — Chris, May 27 '15 at 16:59

score 1 · Answer 6 · answered Jun 02 '15 at 18:53

1

I think this should solve your problem

x<-data.frame(type=c('alpha','beta','gama'), x=c(1,2,3), y=c(4,5,6))
x[,'Total'] <- rowSums(x[,c(2:3)])
x<-rbind(x,c(type = c('Total'), c(colSums(x[,c(2:4)]))))

answered Jun 02 '15 at 18:53

Saksham

9,037
7
45
73

score 0 · Answer 7 · edited Jul 30 '20 at 19:19

0

library(tidyverse)

df <- data.frame(
  Type = c("alpha", "beta", "gamme"),
  CA = c(1, 4, 3),
  AR = c(5, 9, 8)
)

df2 <- colSums(df[, c("CA", "AR")])
# CA AR 
# 8 22

edited Jul 30 '20 at 19:19

Dharman

30,962
25
85
135

answered Jul 30 '20 at 19:14

Tho Vu

1,304
2
8
20

R: colSums when not all columns are numeric

7 Answers7

Linked