Vinterwoo Vinterwoo - 21 days ago 8
R Question

Calculate mean of rows when matching separate dataframe

Given a data frame with index and data columns like so:

AIndex <- c(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)
AData <- c(3,5,6,7,3,2,1,2,3,4,5,6,7,8,9)
DF <- data.frame(AIndex,AData)

And given a second data frame with some overlap in the index like so:

BIndex <- c(1,4,8,11,13)
BData <- c(3,5,7,6,5)
DF2 <- data.frame(BIndex,BData)

My goal is to be able to find where the Index in A and the Index in B match up in the A data frame, and then calculate the mean of that row in A + 2 rows.

For example, the first time A and B match up in A is row# 1. So I would want to find the corresponding data point in A (3) and the next two data points (5 and 6). So the mean would be 4.6

The final result would be a new data frame that looked like this:

Index Data
1 4.6
4 4
8 3
11 6
13 8


There are a few ways of doing this. The first will step usually involves finding where the elements match:

mats = match(DF2$BIndex, DF$AIndex)

To find the means, just add up the relevant values

(AData[mats] + AData[mats+1] + AData[mats+2])/3