How to define calculations in a data frame depending on values from another data frame?

user_8938317 0
Viewed 119

I have to calculate a coefficient based on a dataset stored in a data frame (A) of size 4936 obs. x 1025 var.

In a first row [1,] time in seconds is presented, each row is a sample collected from a different place. A sample of the data frame A:

#        V1   V2   V3   V4
# [1,] 26.4 26.5 26.6 26.7
# [2,]  -15   -5    2    3
# [3,]    6   -7    5    8
# [4,]    9    4    4   -2

In another data frame (B) I stored the time from which I should start my calculations for each row in A. An example of the data frame B:

#      time
# [1,] 26.4
# [2,] 26.6
# [3,] 26.5

Let's simplify that the coefficient is a sum of the data collected in one place (data frame A), depending on the time of their collection (data frame B). For the example above, the calculation should work like this:

sum1=-15+(-5)+2+3
sum2=5+8
sum3=4+4+(-2)

The results of the calculations I would like to store in a new data frame, which would look like this: 

#       Sum
# [1,]  -15
# [2,]   13
# [3,]    6

How to link the calculations between the two data frames depending on a value stored in the second data frame?

filtering dataframe r
3 Answers

Solution using sapply to iterate and select columns depending on time of collection:

# Time from original table
foo <- df1[1, ]
# Time from table B
time <- c(26.4, 26.6, 26.5)

# Remove time row from original table
df1 <- df1[-1, ]

# Iterate over and select columns with foo >= time
sapply(1:length(time), function(x)
    sum(df1[x, which(foo >= time[x])])
)

# [1] -15  13   6
user_12865280

Solution using a simple for-loop: 

# recreate your data
V1 <- c(26.4, -15, 6, 9)
V2 <- c(26.5, -5, -7, 4)
V3 <- c(26.6, 2, 5, 4)
V4 <- c(26.7, 3, 8, -2)

A <- data.frame(V1, V2, V3, V4)
B <- data.frame(time = c(26.4, 26.6, 26.5))

#initialize empty variable to store sums in
sum_frame <- numeric()

# calculating sums
for (i in 1:NROW(B)) {
  sum_frame[i] <- sum(A[(i + 1), (which(A[1, ] == B$time[i])):NCOL(A)])
}

# turning sum-vector into a dataframe
sum_frame <- data.frame(sums = sum_frame)

Output:

> sum_frame
  sum_frame
1       -15
2        13
3         6
user_30521480
Related