CODEWITHSUNDEEP
r
arielle
arielle

Reputation: 945

R: Sum columns based on match table, merging column names

I have a data frame like this (where the letters are column names):

a   b   c   B   C   A
1   2   3   6   7   8
1   2   3   6   7   8
1   2   3   6   7   8
1   2   3   6   7   8

And I would like to sum the columns based on this match table:

a   A
b   B
c   C

While also merging the column names, so that the result would be:

a/A b/B c/C
9   8   10
9   8   10
9   8   10
9   8   10

Please keep in mind that the solution need to be applicable to large data frames, so I cannot be specifying the new column names manually.

Thanks a lot!

Upvotes: 0

Views: 225

Answers (4)

989
989

Reputation: 12937

You could do this:

res <- apply(df.match, 1, function(x) rowSums(df[,c(x[1], x[2])]))
colnames(res) <- paste0(df.match[,1], "/", df.match[,2])

#     a/A    b/B   c/C
#[1,]    9    8   10
#[2,]    9    8   10
#[3,]    9    8   10
#[4,]    9    8   10
#[5,]    9    8   10

where df is your data frame and df.match is your matched col-names.

Upvotes: 2

d.b
d.b

Reputation: 32548

Basically involves using match a couple of times. Using @Lyngbakr's data.

#DATA
df = structure(list(a = c(1, 1, 1, 1, 1), b = c(2, 2, 2, 2, 2), c = c(3, 
3, 3, 3, 3), A = c(8, 8, 8, 8, 8), C = c(7, 7, 7, 7, 7), B = c(6, 
6, 6, 6, 6)), .Names = c("a", "b", "c", "A", "C", "B"), row.names = c(NA, 
-5L), class = "data.frame")
df.names = structure(list(First = c("a", "b", "c"), Second = c("A", "B", 
"C")), .Names = c("First", "Second"), row.names = c(NA, -3L), class = "data.frame")

toadd = which(colnames(df) %in% df.names[,1])
addto = match(df.names[,2][match(colnames(df)[toadd], df.names[,1])], colnames(df))
setNames(object = df[,addto] + df[,toadd], nm = paste(colnames(df)[toadd], colnames(df)[addto], sep = "/"))
#  a/A b/B c/C
#1   9   8  10
#2   9   8  10
#3   9   8  10
#4   9   8  10
#5   9   8  10

Upvotes: 2

Dan
Dan

Reputation: 12074

Something like this?

df <- data.frame(a = rep(1, 5), b = rep(2, 5), c = rep(3, 5), 
                 A = rep(8, 5), C = rep(7, 5), B = rep(6, 5))

df.names <- data.frame(First = c("a", "b", "c"), Second =  c("A", "B", "C"))

apply(df.names, MAR = 1, FUN = function(mynames, mydf) rowSums(df[,colnames(df) %in% mynames]), mydf = df)

Gives,

     [,1] [,2] [,3]
[1,]    9    8   10
[2,]    9    8   10
[3,]    9    8   10
[4,]    9    8   10
[5,]    9    8   10

Upvotes: 0

Andrew Gustar
Andrew Gustar

Reputation: 18425

Here is one way of doing it...

df <- data.frame(a=c(1,1,1,1),b=c(2,2,2,2),c=c(3,3,3,3),B=c(6,6,6,6),C=c(7,7,7,7),A=c(8,8,8,8))
matchtab <- data.frame(V1=c("a","b","c"),V2=c("A","B","C"),stringsAsFactors = FALSE)

df2 <- do.call(cbind,lapply(seq_len(nrow(matchtab)),function(i) 
                           data.frame(df[,matchtab$V1[i]]+df[,matchtab$V2[i]])))
names(df2) <- paste0(matchtab$V1,"/",matchtab$V2)

df2
  a/A b/B c/C
1   9   8  10
2   9   8  10
3   9   8  10
4   9   8  10

Upvotes: 1

Related Questions