Refer particular value in `dplyr::mutate()`

193 Views Asked by At

I have the following code:

library(dplyr)
library(quantmod)

# inflation data
getSymbols("CPIAUCSL", src='FRED')
avg.cpi <- apply.yearly(CPIAUCSL, mean)
cf <- avg.cpi/as.numeric(avg.cpi['1991']) # using 1991 as the base year
cf <- as.data.frame(cf)
cf$year <- rownames(cf)
cf <- tail(cf, 25)
rownames(cf) <- NULL
cf$year <- lapply(cf$year, function(x) as.numeric(head(unlist(strsplit(x, "-")), 1)))
rm(CPIAUCSL)
# end of inflation data get

tmp <- data.frame(year=c(rep(1991,2), rep(1992,2)), price=c(12.03, 12.98, 14.05, 14.58))
tmp %>% mutate(infl.price = price / cf[cf$year == year, ]$CPIAUCSL)

I want to get the following result:

year price
1991 12.03
1991 12.98
1992 13.64
1992 14.16

But I'm getting an error:

Warning message:
In cf$year == tmp$year :
  longer object length is not a multiple of shorter object length

And with %in% it produces and incorrect result.

2

There are 2 best solutions below

0
On BEST ANSWER

I think it might be easier to join the CPIAUCSL column in cf into tmp before you try to mutate:

cf$year = as.numeric(cf$year)
tmp = tmp %>% inner_join(cf, by = "year") %>% mutate(infl.price = price / CPIAUCSL)
0
On

Your cf structure is a list of lists which is unfriendly. It woud have been nicer to have

cf$year <- sapply(cf$year, function(x) as.numeric(head(unlist(strsplit(x, "-")), 1)))

which at least returns a simple vector.

Additional, the subsetting operator [] is not properly vectorized for this type of operation. The mutate() function does not iterate over rows, it operates on entire columns at a time. When you do

cf[cf$year == year, ]$CPIAUCSL

There is not just one year value, mutate is trying to do them all at once.

You'd be better off doing a proper merge with your data and then do the mutate. This will basically do the same thing as your pseudo-merge that you were trying to do in your version.

You can do

tmp %>% left_join(cf) %>% 
    mutate(infl.price = price / CPIAUCSL) %>% 
    select(-CPIAUCSL)

to get

  year price infl.price
1 1991 12.03   12.03000
2 1991 12.98   12.98000
3 1992 14.05   13.63527
4 1992 14.58   14.14962