Merge duplicate columns and summing values in R

Question

I have a large matrix with species as columns and sites as rows. It is a presence/absence matrix (i.e. each species can be present = 1 or absent = 0).

Some species are duplicated, but their values are not the same (i.e. the same species can be present in one record and absent in another one).

I need to merge columns, i.e. when a species is duplicated I want to keep only one record and sum all values.

For example, given this matrix:

A = matrix(c(1,0,1,1,1,1,0,0,1,1), nrow=2, ncol=5)
colnames(A)<-c("A","B","A","A","B")

   A B A A B
   1 1 1 0 1
   0 1 1 0 1

The resulting matrix that I want should be:

   A B
   2 2
   1 2

But I have 948 columns and 454 rows in my original data frame, this is just a simple example.

I have tried to transpose and then aggregate, but it is not working.

Answer 1

it's basically a loop where we iterate through each of the unique names, then usaing grepl we extract the columns with those names and perform rowsums

sapply(unique(colnames(A)), function(x) rowSums(A[,grepl(x, colnames(A))]))
#      A B
# [1,] 2 2
# [2,] 1 2

---

Now coming to the problem you were facing : take the example below :

A = data.frame(c("JOEL", "WILSON"),c(1,0),c(1,1),c(1,1),c(0,0),c(1,1))
colnames(A)<-c("id","A","B","A","A","B")
#       id A B A A B
# 1   JOEL 1 1 1 0 1
# 2 WILSON 0 1 1 0 1

# assuming you have first column as factor(id) 
col <- unique(colnames(A))[-1]  # -1 means remove the first unique column name which in this case is "id" a factor column

cbind(A[1], sapply(col, function(x) rowSums(A[,grepl(x, colnames(A))])))
#      id A B
#1   JOEL 2 2
#2 WILSON 1 2

Note :I'm not subsetting the ORIGINAL data here because, when we do that, the duplicated column names now have a suffix attached to it : for e.g. :-

A1 <- A[-1]
#A1
#  A B A.1 A.2 B.1
#1 1 1   1   0   1
#2 0 1   1   0   1

Therefore, you were facing problems. I hope this helps you!

Lets go into debug mode since you still getting errors :

func <- function(x){
  w <- grepl(x, colnames(A))
  h <- A[, w]
  rowSums(h)
}  
debug(func)  
sapply(col, func)   # col is as above

Now check through the func step-by-step..

Answer 2

I found a solution by transposing the matrix and then summing rows, following the instructions in this post: Checking duplicates, sum them and delete one row after summing

Answer 3

Joel Wilson's code was just about there, but focused on the unique columns not the duplicate ones. My answer here is Joel's code slightly modified to instead focus on the duplicated columns.

# two functions
df2 <- sapply(unique(colnames(df)[duplicated(colnames(df))]), function(x) rowSums(df[,grepl(paste(x, "$", sep=""), colnames(df))]))
df2 <- cbind(df2, df[,!duplicated(colnames(df)) & !duplicated(colnames(df), fromLast = TRUE)])

# one long function
df2 <- cbind(sapply(unique(colnames(df)[duplicated(colnames(df))]), function(x) rowSums(df[,grepl(paste(x, "$", sep=""), colnames(df))])), df[,!duplicated(colnames(df)) & !duplicated(colnames(df), fromLast = TRUE)])

---

Explanation

Building up to the answer, first view column names:

colnames(df)

Create logical vector indicating columns that are duplicates:

duplicated(colnames(df))

Return column names of duplicated columns names (sounds roundabout but the logical vector is selecting the duplicated columns and then colnames returns the names):

colnames(df)[duplicated(colnames(df))]

Wrap that with the unique function to return a single copy of each of the duplicated columns:

unique(colnames(df)[duplicated(colnames(df))])

Use this code in place of Joel Wilson's column names code unique(colnames(A)):

sapply(unique(colnames(df)[duplicated(colnames(df))]), function(x) rowSums(df[,grepl(x, colnames(df))]))

What we are doing here is creating a vector with the names of the duplicated columns then applying a function iteratively across those names. For each column name, R searches the data frame and selects the columns that have that name and sums their rows.

Finally, assign this function to a new data frame and add back in the columns that were not summed (the columns that did not have duplicate names).

df2 <- sapply(unique(colnames(df)[duplicated(colnames(df))]), function(x) rowSums(df[,grepl(paste(x, "$", sep=""), colnames(df))]))
df2 <- cbind(df2, df[,!duplicated(colnames(df)) & !duplicated(colnames(df), fromLast = TRUE)])

Edit

I wasn't aware that the duplicate function marks the first occurrence of a duplicated name as FALSE. I found this post helpful in debugging my answer so that the first occurrence of duplicated columns was not included in the final dataset.

Edit 2

Using this code in practice, I found that the regular expression search summed columns that were subsets of each other. For example, if there were columns named OTU_3, OTU_35, and OTU_301, all columns named OTU_35 would be collapsed and summed, all columns named OTU_301 would be collapsed and summed, but OTU_3 would be the sum of all columns named OTU_3, OTU_35, and OTU_301. Changing the regular expression from x to paste(x, "$", sep="") resolves this issue. The dollar sign indicates that there shouldn't be any other characters after x for a successful match.