Complex subsetting of dataframe

Question

Consider the following dataframe:

df <- data.frame(Asset = c("A", "B", "C"), Historical = c(0.05,0.04,0.03), Forecast = c(0.04,0.02,NA))

#  Asset Historical Forecast
#1     A       0.05     0.04
#2     B       0.04     0.02
#3     C       0.03       NA

as well as the variable x. x is set by the user at the beginning of the R script, and can take two values: either x = "Forecast" or x = "Historical".

If x = "Forecast", I would like to return the following: for each asset, if a forecast is available, return the appropriate number from the column "Forecast", otherwise, return the appropriate number from the column "Historical". As you can see below, both A and B have a forecast value which is returned below. C is missing a forecast value, so the historical value is returned.

   Asset     Return 
 1     A       0.04     
 2     B       0.02     
 3     C       0.03

If, however, x= "Historical",simply return the Historical column:

   Asset  Historical 
 1     A       0.05     
 2     B       0.04     
 3     C       0.03

I can't come up with an easy way of doing it, and brute force is very inefficient if you have a large number of rows. Any ideas?

Thanks!

re: `ifelse`, have a look at this question: http://stackoverflow.com/q/16275149/1492421 — Ricardo Saporta, Oct 23 '13 at 18:21
Why not just fill in the `NA` values at the start of the script? — A5C1D2H2I1M1N2O1R2T1, Oct 23 '13 at 18:31

score 4 · Accepted Answer · answered Oct 23 '13 at 18:36

4

First, pre-process your data:

df2 <- transform(df, Forecast = ifelse(!is.na(Forecast), Forecast, Historical))

Then extract the two columns of choice:

df2[c("Asset", x)]

answered Oct 23 '13 at 18:36

flodel

87,577
21
185
223

Complex subsetting of dataframe

1 Answers1