Invert value based on string

user_10416032 0
Viewed 32

I'm trying to find a simple way of replace the value in the FX.SPOT column based on a string (without looping through the data).

my data structure looks like this:

test = structure(list(FX.QUOTATION = c("AUD-USD", "JPY-USD", "AUD-USD", 
                                "AUD-USD", "AUD-USD", "AUD-USD"), CURRENCY = c("AUD", "JPY", 
                                                                               "AUD", "AUD", "AUD", "AUD"), FX.SPOT = c(0.7016, 115.455, 0.7016, 
                                                                                                                        0.7016, 0.7016, 0.7016)), row.names = c(NA, 6L), class = "data.frame")

and I've been trying to find a way to convert the FX.SPOT value when FX.QUOTATION = 'JPY-USD' so that the value in the FX.SPOT column changes from JPY to -1/JPY.

I tried the below which obviously doesn't work, and could make it work via a loop but think there must be a more efficient method?

within(test, FX.SPOT[FX.QUOTATION %in% 'JPY-USD'] <- -1/FX.SPOT)
r
3 Answers

You may try subsetting your data frame based on the FX.QUOTATION value, and then using sub() in fixed mode.

update <- test[test$FX.QUOTATION == "JPY-USD", "FX.SPOT"]
update <- sub("JPY", "-1/JPY", update, fixed=TRUE)
user_18632290

I would ditch the within(), and use ifelse() instead of logical subsetting.

test$FX.SPOT <- ifelse(
  test$FX.QUOTATION %in% 'JPY-USD',
  -1/test$FX.SPOT,
  test$FX.SPOT
)

Result:

FX.QUOTATION CURRENCY      FX.SPOT
1      AUD-USD      AUD  0.701600000
2      JPY-USD      JPY -0.008661383
3      AUD-USD      AUD  0.701600000
4      AUD-USD      AUD  0.701600000
5      AUD-USD      AUD  0.701600000
6      AUD-USD      AUD  0.701600000

But if you’d prefer to use something similar to your original approach, you can. You’d need to make two changes: you need to assign your result, and you need to subset your replacement vector so that it matches the values to be replaced.

test <- within(
  test, 
  FX.SPOT[FX.QUOTATION %in% 'JPY-USD'] <- -1/FX.SPOT[FX.QUOTATION %in% 'JPY-USD']
)
user_173038050

Here is a version with an ifelse:

library(dplyr)

test %>% 
  mutate(FX.SPOT = ifelse(FX.QUOTATION=="JPY-USD", -1/FX.SPOT[FX.QUOTATION == 'JPY-USD'], FX.SPOT))

  FX.QUOTATION CURRENCY      FX.SPOT
1      AUD-USD      AUD  0.701600000
2      JPY-USD      JPY -0.008661383
3      AUD-USD      AUD  0.701600000
4      AUD-USD      AUD  0.701600000
5      AUD-USD      AUD  0.701600000
6      AUD-USD      AUD  0.701600000
user_133216470
Related