CODEWITHSUNDEEP
r
Roger
Roger

Reputation: 395

Select values based on other columns

I have a dataframe (df, a sample of which is shown below). I want to choose values from column a1, b1 and c1 and take the average, if values in a2, b2, and c2 are positive. For example, in the first row of the df, all values in a2, b2, and c2 are positive, I then pick the corresponding values in a1, b1, and c1, and average them. The result is 0.4933. In the second column, only the value in c2 is positive, I will then pick the value in c1 (0.01). 

a1       b1      c1      a2      b2      c2   desired outcome
0.51    0.49    0.48    0.05    0.03    0.09    0.493333
0.33    0.31    0.3    -0.03    -0.05   0.01    0.01
0.22    0.2     0.19    0.04    0.02    0.08    0.203333
0.54    0.52    0.51    -0.05   0.08    -0.01   0.08
0.45    0.43    0.42    -0.03   -0.05   0.01    0.01

Below is my code where I listed all scenarios. I am looking for more efficient codes that can handle more columns. 

df2 <- df1 %>% select(c(a2,b2,c2)) %>% 
  mutate(outcome = ifelse(a2 >0 & b2>0 & c2>0, mean(a1,b1,c1),
                          ifelse(a2>0 & b2>0 &c2<0, mean(a1,b1),
                                 ifelse(a2>0&b2<0&c2<0, mean(a1),
                                        ifelse(a2<0&b2>0&c2>0, mean(b2,c2),
                                               ifelse(a2<0&b2<0&c2>0, mean(c2),
                                                      mean(b2)))))))

Upvotes: 0

Views: 57

Answers (2)

Emil Bode
Emil Bode

Reputation: 1830

Subsetting is just choosing some value based on some condition, but this need not be a condition based on this value itself.
Sounds hard, but is easy with an example:

 df[1,1:3][df[1,4:6]>0]

We take from the first row, the first three columns, but only those for which the corresponding values are TRUE. The coresponding values, are the answers to the questions "are you positive" to the first row, 4th-6th columns.

For this first row all three are TRUE, but for the 2nd one we only get one value: .3. And now we can just take the mean, and if we want to do it for all rows, we can use sapply:

outcome <- sapply(1:nrow(df), function(i) {mean(df[i,1:3][df[i,4:6]>0])})

Only if there are rows where a2, b2 and c2 are all three negative, then mean will return NaN, for "Not a Number"

Upvotes: 1

G. Grothendieck
G. Grothendieck

Reputation: 269421

1) Here Mean does the calculation for one row and we apply it to each row separately. We are assuming here you want to zero elements in the first 3 columns whose corresponding column among the last 3 columns are positive and then take the mean of that.

Mean <- function(x) mean(x[1:3] * (x[4:6] > 0))
transform(df2, desired = apply(df2, 1, Mean))

giving:

    a1   b1   c1    a2    b2    c2   desired
1 0.51 0.49 0.48  0.05  0.03  0.09 0.4933333
2 0.33 0.31 0.30 -0.03 -0.05  0.01 0.1000000
3 0.22 0.20 0.19  0.04  0.02  0.08 0.2033333
4 0.54 0.52 0.51 -0.05  0.08 -0.01 0.1733333
5 0.45 0.43 0.42 -0.03 -0.05  0.01 0.1400000

2) or without apply:

transform(df2, desired = rowMeans(df2[1:3] * (df2[4:6] > 0)))

giving:

    a1   b1   c1    a2    b2    c2   desired
1 0.51 0.49 0.48  0.05  0.03  0.09 0.4933333
2 0.33 0.31 0.30 -0.03 -0.05  0.01 0.1000000
3 0.22 0.20 0.19  0.04  0.02  0.08 0.2033333
4 0.54 0.52 0.51 -0.05  0.08 -0.01 0.1733333
5 0.45 0.43 0.42 -0.03 -0.05  0.01 0.1400000

Note

The input df2 in reproducible form:

Lines <- "
a1       b1      c1      a2      b2      c2 
0.51    0.49    0.48    0.05    0.03    0.09
0.33    0.31    0.3    -0.03    -0.05   0.01
0.22    0.2     0.19    0.04    0.02    0.08
0.54    0.52    0.51    -0.05   0.08    -0.01
0.45    0.43    0.42    -0.03   -0.05   0.01"
df2 <- read.table(text = Lines, header = TRUE)

Upvotes: 2

Related Questions