Convert values using a conversion table R

Question

I am currently running statistical models on ACT and SAT scores. To help clean my data, I want to convert the ACT scores into its SAT equivalent. I found the following table online:

     ACT   SAT
   <dbl> <dbl>
 1    36  1590
 2    35  1540
 3    34  1500
 4    33  1460
 5    32  1430
 6    31  1400
 7    30  1370
 8    29  1340
 9    28  1310
10    27  1280

I want to replace the column ACT_Composite with the number in the SAT column of the conversion table. For instance, if one row displays an ACT_Composite score of 35, I want to input 1540. If anyone has ideas on how to accomplish this, I would greatly appreciate it.

Use a `data.table` join by reference. or take a look at `match()`. If you provide some good sample data, some valid answer will probably soon pop up.. https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Wimpel, Mar 26 '21 at 18:33
What you are describing sounds like a pretty straightforward merge: https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right — MrFlick, Mar 26 '21 at 18:41

Chriss Paul · Answer 1 · 2021-03-26T19:13:45.540

In base you can you use merge directly:

#Reading score table
df <- read.table(header = TRUE, text ="ACT   SAT
    36  1590
    35  1540
    34  1500
    33  1460
    32  1430
    31  1400
    30  1370
    29  1340
    28  1310
    27  1280")
#Setting seed to reproduce df1
set.seed(1234) 
# Create a data.frame with 50 sample scores
df1 <- data.frame(ACT_Composite = sample(27:36, 50, replace = TRUE))
# left-join df1 with df with keys ACT_Composite and ACT
result <- merge(df1, df,
            by.x = "ACT_Composite", 
            by.y = "ACT", 
            all.x = TRUE,
            sort = FALSE)
#The first 6 values of result 
head(result)
  ACT_Composite  SAT
1            31 1400
2            31 1400
3            31 1400
4            31 1400
5            31 1400
6            36 1590

In data.table you can you use merge

library(data.table)

#Setting seed to reproduce df1
set.seed(1234) 
# Create a data.table with 50 sample scores
df1 <- data.table(ACT_Composite = sample(27:36, 50, replace = TRUE)) 
# left-join df1 with df with keys ACT_Composite and ACT
result <- merge(df1, df, 
            by.x = "ACT_Composite", 
            by.y = "ACT", 
            all.x = TRUE,
            sort = FALSE)

#The first 6 values of result 
head(result) 

   ACT_Composite  SAT
1:            36 1590
2:            32 1430
3:            31 1400
4:            35 1540
5:            31 1400
6:            32 1430

Alternatively in data.table you can try also

df1 <- data.table(ACT_Composite = sample(27:36, 50, replace = TRUE))
setDT(df)# you need to convert your look-up table df into data.table 
result <- df[df1, on = c(ACT = "ACT_Composite")] 

head(result) 

   ACT_Composite  SAT
1:            36 1590
2:            32 1430
3:            31 1400
4:            35 1540
5:            31 1400
6:            32 1430

Convert values using a conversion table R

1 Answers1