Sum of hybrid data frames depending on multiple conditions in R

Question

This is a more complex follow-up to my previous question. The answer there was to use a matrix, but that doesn't work with data frames having values of different modes. I want to combine data frames of different sizes, with character and integer columns, and calculate their sum depending on multiple conditions. Conditions - sums are only calculated for those rows that have a matching "Name"-value - sums are calculated for matching column names only - if a cell in `df4` is not 0 and not NA, the sum should be `df3 + df4` - else the sum should be `df1 + df2 + df3` Example ``` > df1 <- data.frame(Name=c("Joe","Ann","Lee","Dan"), "1"=c(0,1,5,2), "2"=c(3,1,0,0), "3"=c(2,0,2,2), "4"=c(2,1,3,4)) > df1 Name X1 X2 X3 X4 1 Joe 0 3 2 2 2 Ann 1 1 0 1 3 Lee 5 0 2 3 4 Dan 2 0 2 4 > df2 <- data.frame(Name=c("Joe","Ann","Ken"), "1"=c(3,4,1), "2"=c(2,3,0), "3"=c(2,4,3)) > df2 Name X1 X2 X3 1 Joe 3 2 2 2 Ann 4 3 4 3 Ken 1 0 3 > df3 <- data.frame(Name=c("Lee","Ben"), "1"=c(1,3), "2"=c(3,4), "3"=c(4,3)) >…

Accepted Answer

If you use data.table instead of data.frame, you could use its `by=xxxx` feature, to add by name. The code below should give you your expected results.

Please note that I am padding the data.tables with extra empty columns. However, we compute `condTrue` prior to then.

```
library(data.table)
dt1 <- data.table(df1)
dt2 <- data.table(df2)
dt3 <- data.table(df3)
dt4 <- data.table(df4)

# make sure all dt's have the same columns 
#-----------------------------------------#

# identify which dt4 satisfy the condition 
condTrue <- as.data.table(which(!(is.na(dt4) | dt4==0), arr.ind=TRUE))

# ignore column "Name" from dt4
condTrue <- condTrue[col>1]

# convert from (row, col) index to ("Name", columnName) 
condTrue <- data.table(Name=dt4[condTrue$row, Name], colm=names(dt4)[condTrue$col], key="Name")

# First make a list of all the unique column names
allColumnNames <- unique(c(names(dt1), names(dt2), names(dt3), names(dt4)))

# add columns as necessary, using addCols (definted below)
addCols(dt1, allColumnNames)
addCols(dt2, allColumnNames)
addCols(dt3, allColumnNames)
addCols(dt4, allColumnNames)

sumD3D4  <- setkey(rbind(dt3, dt4)[, lapply(.SD, sum), by=Name], "Name")
dtsum    <- setkey(rbind(dt1, dt2, dt3)[, lapply(.SD, sum), by=Name], "Name")

for (Nam in condTrue$Name) {
  colsRepl <- condTrue[.(Nam)]$colm
  valsRepl <- unlist(sumD3D4[.(Nam), c(colsRepl), with=FALSE])
  dtsum[.(Nam), c(colsRepl) :=  as.list(valsRepl)]
}

dtsum
#    Name 1 2 3 4
# 1:  Ann 5 4 4 1
# 2:  Ben 3 4 3 0
# 3:  Dan 2 4 2 4
# 4:  Joe 3 5 4 2
# 5:  Ken 1 0 3 0
# 6:  Lee 7 3 6 3
```

```
addCols <- function(x, cols, padValue=0)  {
  # adds to x any columns that are in cols but not in x
  # Returns TRUE  if columns were added
  #         FALSE if no columns added 
  colsMissing <- setdiff(cols, names(x))

# grab the actual DT name that was passed to function
  dtName <- as.character(match.call()[2])

if (length(colsMissing)) {
    get(dtName, envir=parent.frame(1))[, c(colsMissing) := padValue]  
    return(TRUE)
  }

return(FALSE)
}
```

Sum of hybrid data frames depending on multiple conditions in R

Solution

Problem

Related problems