CODEWITHSUNDEEP
r
TiberiusGracchus2020
TiberiusGracchus2020

Reputation: 409

Creating table with average column values given row "matches" in R

I'm hoping someone can help me with this problem. Let's say I have to data frames like the ones below.

      A     B     C     D
1     1     1    10   100
2     2     1    30   200
3     1     2    30   200
4     2     2    10   400

      A     B     C     D
1     1     1    10   300
2     2     1    20   400
3     1     2    30   300
4     2     2    20   600

My desired result is creating a combined frame with average values in columns C and D given a complete combined match of values in columns A and B, yielding a frame that looks like this:

      A     B     C     D
1     1     1    10   200
2     2     1    25   300
3     1     2    30   250
4     2     2    15   500

Anyone know a snippet of code that will help me in this pinch?

Upvotes: 4

Views: 52

Answers (3)

IceCreamToucan
IceCreamToucan

Reputation: 28695

You can do a data.table update join on a copy of df1. 

library(data.table)
setDT(df1)
setDT(df2)

copy(df1)[df2, on = .(A, B), 
    c('C', 'D') := Map(function(x, y) (x + y)/2, .(C, D), .(i.C, i.D))][]

#    A B  C   D
# 1: 1 1 10 200
# 2: 2 1 25 300
# 3: 1 2 30 250
# 4: 2 2 15 500

Upvotes: 1

tmfmnk
tmfmnk

Reputation: 40091

One base R option could be:

aggregate(cbind(C, D) ~ ., FUN = mean, data = rbind(df1, df2))

  A B  C   D
1 1 1 10 200
2 2 1 25 300
3 1 2 30 250
4 2 2 15 500

Upvotes: 6

akash87
akash87

Reputation: 3994

Using library tidyverse I did:

df1 <- data.frame(A = c(1,2,1,2), B = c(1,1,2,2), C = c(10,30,30,10), D = c(100,200,200,400))
df2 <- data.frame(A = c(1,2,1,2), B = c(1,1,2,2), C = c(10,20,30,20), D = c(300,400,300,600))
rbind(df1,df2) %>% group_by(A,B) %>% summarise(meanC = mean(C), meanD = mean(D)) %>% ungroup()

Resulting in:

# A tibble: 4 x 4
      A     B meanC meanD
  <dbl> <dbl> <dbl> <dbl>
1     1     1    10   200
2     1     2    30   250
3     2     1    25   300
4     2     2    15   500

Upvotes: 3

Related Questions