r - Change the value of one column based on the value of other column

Question

I wonder of can I change the value of the column CVE based on the value of the column state.

I have 50 different states and I would like to set a value in the column CVE depending the value of the column state.

Example

year   state    CVE
1980   TX        3
1986   PEN       3
1987   CAL       3
2000   TX        3 

I want that if the state == "TX" then change the value of the column CVE to "45" and the same logic for the other states.

I tried with this:

setDT(df)[state == "TX" , CVE:="45" ]
setDT(df)[state == "CAL" , CVE:="50" ]
setDT(df)[state == "PEN" , CVE:="56" ]

But I don't know how can I code a single command for the different states and set the CVE. I wonder if there is a way to do this avoiding repeat the same command 50 times

question from:https://stackoverflow.com/questions/66055160/change-the-value-of-one-column-based-on-the-value-of-other-column

Answer 1

It would be easier to do with a key/val dataset and a join which can be fast and efficient

library(data.table)
keydat <- data.table(state = c("TX", "CAL", "PEN"), CVE_GEO = c("45", "50", "56"))
setDT(df)[keydat,  CVE := i.CVE_GEO, on = .(state)]

-output

df
#  year state CVE
#1: 1980    TX  45
#2: 1986   PEN  56
#3: 1987   CAL  50
#4: 2000    TX  45

NOTE: Here we assume that the CVE column in the original dataset is of the same type as the 'keydat' 'CVE_GEO' i.e. character class

data

df <- structure(list(year = c(1980L, 1986L, 1987L, 2000L), state = c("TX", 
"PEN", "CAL", "TX"), CVE = c("3", "3", "3", "3")), row.names = c(NA, 
-4L), class = "data.frame")