Conditional Averaging

Question

I have two numerical columns here(adm_muac1 and adm_muac2). I want create a third column which is an average of the two but on condition that both columns are non missing(I expect missing values in my data set), else it should not average but return either the non-missing columns as value for third column.

I wrote this code

df2<-mutate(df1,
   col3=ifelse((!is.na(col1)&!is.na(col2)),(col1+col2)/2,
                              col1|col2))

Here is the data set

df1<-data.frame(col1=c(12,23,NA,14,15,20),col2=c(NA,20,16,17,18,NA))
Expectedcol3
12
27.5
16
15.5
16.5
20

The first part is excuting really well, but the second part is not. The idea is that I want the average if both col1 and col2 are non missing, else no averaging but I should get either of the non missing column between col1 and col2 as my entry col3. Any help will highly be appreciated

Thank you

col1    col2    col3
15.6    15.5    15.55
17      17.3    17.15
11.1              1
12.4    12.4    12.4
12.3    12.2    12.25
13      13.1    13.05
10.1    10.2    10.15
10.4    10.5    10.45
11      11.1     11.05
9.1     9.3    9.2
11.6    11.9    11.75
        11.4    1
11.2    11.4    11.3
11.2    11.1    11.15
10.2    10.3    10.25
12.1    12.2    12.15
15.9    15.8    15.85
15.1    15.1    15.1
9.5     9.8     9.65
11.4    11.4    11.4
11.7    11.6    11.65
         9.3      1

There will be type coercion as you have a mean and a logical one. Please show a small reproducible example and expected output. How about just use rowMeans(df1[c('col1', 'col2')], na.rm = TRUE) — akrun
Based on your description, I think rowMeans would be enough ( please check the updated comment above) — akrun
@akrun I am trying to share a sample data set and I am not able to coz I do not know how, I am new to the forum! — Christopher
You can just click on the edit button. suppose your dataset is df1 <- data.frame(col1 = c(3, 4, NA, 2, NA), col2 = c(NA, 2, NA, 3, 2)) what would be the expected output — akrun

Matt W. Matt W. · Accepted Answer · 2017-11-19T21:17:32

You could do a nested ifelse. If one is NA, choose the other, otherwise average them.

df2 <- df1 %>% mutate(col3 = ifelse(is.na(col1), col2, 
                             ifelse(is.na(col2), col1, (col1+col2)/2)))

Conditional Averaging

2 Answers