5

I have df1:

Type     CA     AR     Total
alpha    2      3        5
beta     1      5        6
gamma    6      2        8
delta    8      1        9

I have df2:

Type     CA     AR     Total
alpha    3      4        7
beta     2      6        8
gamma    9      1        10
delta    4      1        5

I want to add the values in both the data frames to get 1 data frame with this result:

    Type     CA     AR     Total
    alpha    5      7        12
    beta     3      11       14
    gamma    15     3        18
    delta    12     2        14

Example --> (alpha, CA) = 2 (from df1) + 3 (from df2) = 5 (resulting df)

Does anyone know how to do this? It's not exactly merge I think because merge will override the value, where as, I want to add the value.

Thanks in advance!!

user4918087
  • 421
  • 1
  • 6
  • 14
  • I recognize this data from yesterday... and still think you should use matrices. With those, you can just add `mat1 + mat2`. http://stackoverflow.com/q/30488161/1191259 – Frank May 28 '15 at 20:19

3 Answers3

4

+ is vectorised, this is just a simple operation in R

cbind(df1[1], df1[-1] + df2[-1])
#    Type CA AR Total
# 1 alpha  5  7    12
# 2  beta  3 11    14
# 3 gamma 15  3    18
# 4 delta 12  2    14

If your data set are not order properly, you could use match (as mentioned in comments)

cbind(df1[1], df1[, -1] + df2[match(df1$Type, df2$Type), -1])
David Arenburg
  • 91,361
  • 17
  • 137
  • 196
  • @user4918087 Just a warning. Since you mentioned the word merge, if your row orders are not the same, you need a different answer probably with `match` – Vlo May 28 '15 at 20:10
  • @Vlo if the aren't ordered, I would just do `df1[order(df1[, 1]), -1] + df2[order(df2[, 1]), -1]` – David Arenburg May 28 '15 at 20:13
  • That works. Was thinking `df2[match(df[,1], df[,2]), -1] + df[,-1]` – Vlo May 28 '15 at 20:16
  • @Vlo I think you mean something like `df1[, -1] + df2[match(df1$Type, df2$Type), -1]` as you need to match the correct columns. Though I was just editing this. – David Arenburg May 28 '15 at 20:17
  • Yeah I meant to write `match(df1[,1], df2[,1])` but reference by name is better – Vlo May 28 '15 at 20:22
2

You can just sum them and re-add the factor column.

df_tot <- df1 + df2
df_tot$Type = df1$Type
Michele Usuelli
  • 1,970
  • 13
  • 15
  • 1
    This is not correct as there is also a character/factor column – akrun May 28 '15 at 20:08
  • True, that's why the second line re-defines it. – Michele Usuelli May 28 '15 at 20:10
  • But, based on the data provided by the OP, the datasets need to exclude the first column which was not shown in your code or at not found in the description. – akrun May 28 '15 at 20:10
  • You can sum the first column. The result will just be NA. – Michele Usuelli May 28 '15 at 20:11
  • At least I am not getting the result, `df1+df2# Error in FUN(left, right) : non-numeric argument to binary operator` – akrun May 28 '15 at 20:12
  • If the first column is a character, it will crash. If it's a factor, it'll return NAs. – Michele Usuelli May 28 '15 at 20:13
  • But, we don't know whether it is a factor/character column from the OP's description – akrun May 28 '15 at 20:14
  • I think this is a good answer, but the factor v char thing could be mentioned as a caveat. Despite how much I dislike them, factors are the default; and this works for that case. @DavidArenburg And yet it does work..? I just ran it. – Frank May 28 '15 at 20:24
  • @Frank I see what you mean. If we assume factors it will work in a sense. – David Arenburg May 28 '15 at 20:27
  • 1
    @Frank Even if it is a factor, it gives an NA column with a warning. (`Warning message: In Ops.factor(left, right) : ‘+’ not meaningful for factors`). It is easy to subset that factor column away from the calculation rather than having warnings and then reassign a column. – akrun May 28 '15 at 20:27
  • 1
    @akrun I guess I'm just about indifferent between the clutter of extra brackets/parentheses and getting a warning. The real solution (at least for this example) is to store numerical data in matrices, in which case the trusty vanilla `+` does work. – Frank May 28 '15 at 20:32
  • 1
    @Frank Yes, I respect your position. I noticed in some cases where some previous posters complain even about some friendly warning. `matrices` would be faster and more natural for this.. – akrun May 28 '15 at 20:35
1

You can do with dplyr + magrittr, if you want to go that route:

library("dplyr")
library("magrittr")

df1 %>% select(-type) %>%
    add(df2 %>% select(-type)) %>%
    mutate(type = df1$type)

Note: this assumes df1 and df2 are ordered in the same manner.

Josh W.
  • 1,123
  • 1
  • 10
  • 17