R - applying calculation pairwise on columns of data frame/data table

user_16375831 0
Viewed 62

Let's say I have the data frames with the same column names

DF1 = data.frame(a = c(0,1), b = c(2,3), c = c(4,5))

DF2 = data.frame(a = c(6,7), c = c(8,9))

and want to apply some basic calculation on them, for example add each column.

Since I also want the goal data frame to display missing data, I appended such a column to DF2, so I have

> DF2
  a c  b
1 6 8 NA
2 7 9 NA

What I tried here now is to create the data frame

for(i in names(DF2)){
   DF3 = data.frame(i = DF1[i] + DF2[i])
}

(and then bind this together) but this obviously doesn't work since the order of the columns is mashed up.

SO, what's the best way to do this pairwise calculation when the order of the columns is not the same, without reordering them?

I also tried doing (since this is what I thought would be a fix)

for(i in names(DF2)){
    DF3 = data.frame(i = DF1$i + DF2$i)
}

but this doesn't work because DF1$i is NULL for all i.

Conlusion: I want the data frame

>DF3
  a   b  c
1 6+0 NA 4+8
2 1+7 NA 5+9

Any help would be appreciated.

dataframe r datatable
3 Answers

This may help -

#Get column names from DF1 and DF2
all_cols <- union(names(DF1), names(DF2))

#Fill missing columns with NA in both the dataframe
DF1[setdiff(all_cols, names(DF1))] <- NA
DF2[setdiff(all_cols, names(DF2))] <- NA

#add the two dataframes arranging the columns
DF1[all_cols] + DF2[all_cols]

#  a  b  c
#1 6 NA 12
#2 8 NA 14
user_39629140

We can use bind_rows

library(dplyr)
library(data.table)
bind_rows(DF1, DF2, .id = 'grp') %>% 
    group_by(grp = rowid(grp)) %>%
    summarise(across(everything(), sum), .groups = 'drop') %>% 
    select(-grp)

-output

# A tibble: 2 x 3
      a     b     c
  <dbl> <dbl> <dbl>
1     6    NA    12
2     8    NA    14
user_37322710

Another base R option using aggregate + stack + reshae

aggregate(
  . ~ rid,
  transform(
    reshape(
      transform(rbind(
        stack(DF1),
        stack(DF2)
      ),
      rid = ave(seq_along(ind), ind, FUN = seq_along)
      ),
      direction = "wide",
      idvar = "rid",
      timevar = "ind"
    ),
    rid = 1:nrow(DF1)
  ),
  sum,
  na.action = "na.pass"
)[-1]

gives

  values.a values.b values.c
1        6       NA       12
2        8       NA       14
user_121587570
Related