I have a large data table which looks as follows:
V1 V2 V3 V4 V5 V6 V7 V8 V9
1: XS0285400197 TR.IssuerRating F1 F1 F1 F1 F1 F1 F1
2: XS0041971275 TR.IssuerRating AAA AAA AAA AAA F1 F1 AAA
3: XS0043098127 TR.IssuerRating WD WD WD WD WD WD WD
structure(list(V1 = c("XS0285400197", "XS0041971275", "XS0043098127"
), V2 = c("TR.IssuerRating", "TR.IssuerRating", "TR.IssuerRating"
), V3 = c("F1", "AAA", "WD"), V4 = c("F1", "AAA", "WD"), V5 = c("F1",
"AAA", "WD"), V6 = c("F1", "AAA", "WD"), V7 = c("F1", "F1", "WD"
), V8 = c("F1", "F1", "WD"), V9 = c("F1", "AAA", "WD")), class = "data.frame", row.names = c(NA,
-3L))
The actual data table is much larger but this should serve as an example. Additionally, I have a key where I want to replace the ratings (here F1,AAA and WD) with numbers.
Rating CreditQuality
1: F1 2
2: AAA 1
3: WD 6
4: (P)B2 6
5: (P)Ba1 4
6: (P)Ba2 5
structure(list(Rating = c("F1", "AAA", "WD", "(P)B2", "(P)Ba1",
"(P)Ba2"), CreditQuality = c(2L, 1L, 6L, 6L, 4L, 5L)), class = "data.frame", row.names = c(NA,
-6L))
I want to replace these ratings with the CreditQuality I have assigned each rating in the key. This would mean that a cell with F1 is now a 2. A cell with WD would be a 6 and so on. The new table should look as follows:
V1 V2 V3 V4 V5 V6 V7 V8 V9
1: XS0285400197 TR.IssuerRating 2 2 2 2 2 2 2
2: XS0041971275 TR.IssuerRating 1 1 1 1 2 2 1
3: XS0043098127 TR.IssuerRating 6 6 6 6 6 6 6
I have tried using match and mapvalues however match only seems to work for a single column and mapvalues only works for an atomic vector not for a data.table. Some people have had similar problems however most of them only needed to replace values in a single column whereas I want to replace values across multiple columns in a data.table
CodePudding user response：
You can use melt and dcast:
dcast(
rating[melt(df, id=c("V1", "V2"),value.name = "Rating"), on="Rating"],
V1 V2~variable, value.var = "CreditQuality"
)
Output:
V1 V2 V3 V4 V5 V6 V7 V8 V9
1: XS0041971275 TR.IssuerRating 1 1 1 1 2 2 1
2: XS0043098127 TR.IssuerRating 6 6 6 6 6 6 6
3: XS0285400197 TR.IssuerRating 2 2 2 2 2 2 2
Note: I'm assuming your source data is df, and your Rating data is rating. I see that your frames are already of class data.table
CodePudding user response：
You can use dplyr and across.
library(dplyr)
# Define input data
df <- data.frame(
V1 = c("XS0285400197", "XS0041971275", "XS0043098127"),
V2 = c("TR.IssuerRating", "TR.IssuerRating", "TR.IssuerRating"),
V3 = c("F1", "AAA", "WD"),
V4 = c("F1", "AAA", "WD"),
V5 = c("F1", "AAA", "WD"),
V6 = c("F1", "AAA", "WD"),
V7 = c("F1", "F1", "WD"),
V8 = c("F1", "F1", "WD"),
V9 = c("F1", "AAA", "WD"),
stringsAsFactors = FALSE
)
lookup <- data.frame(
Rating = c("F1", "AAA", "WD", "(P)B2", "(P)Ba1", "(P)Ba2"),
CreditQuality = c(2, 1, 6, 6, 4, 5)
)
# Make a look up vector
lookup_vec <- lookup$CreditQuality
names(lookup_vec) <- lookup$Rating
# Use dplyr across to apply look up
df_mod <- df %>%
mutate(across(seq(3, dim(df)[2]), ~ lookup_vec[.x]))
# View
df_mod
# V1 V2 V3 V4 V5 V6 V7 V8 V9
# 1 XS0285400197 TR.IssuerRating 2 2 2 2 2 2 2
# 2 XS0041971275 TR.IssuerRating 1 1 1 1 2 2 1
# 3 XS0043098127 TR.IssuerRating 6 6 6 6 6 6 6
CodePudding user response：
df <-
structure(
list(
V1 = c("XS0285400197", "XS0041971275", "XS0043098127"),
V2 = c("TR.IssuerRating", "TR.IssuerRating", "TR.IssuerRating"),
V3 = c("F1", "AAA", "WD"),
V4 = c("F1", "AAA", "WD"),
V5 = c("F1", "AAA", "WD"),
V6 = c("F1", "AAA", "WD"),
V7 = c("F1", "F1", "WD"),
V8 = c("F1", "F1", "WD"),
V9 = c("F1", "AAA", "WD")),
class = "data.frame",
row.names = c(NA,-3L)
)
rating <-
structure(list(
Rating = c("F1", "AAA", "WD", "(P)B2", "(P)Ba1", "(P)Ba2"),
CreditQuality = c(2L, 1L, 6L, 6L, 4L, 5L)),
class = "data.frame",
row.names = c(NA,-6L))
df
#> V1 V2 V3 V4 V5 V6 V7 V8 V9
#> 1 XS0285400197 TR.IssuerRating F1 F1 F1 F1 F1 F1 F1
#> 2 XS0041971275 TR.IssuerRating AAA AAA AAA AAA F1 F1 AAA
#> 3 XS0043098127 TR.IssuerRating WD WD WD WD WD WD WD
#tidyverse
library(tidyverse)
df %>%
mutate(across(V3:V9, ~with(rating, CreditQuality[match(.x, table = Rating)])))
#> V1 V2 V3 V4 V5 V6 V7 V8 V9
#> 1 XS0285400197 TR.IssuerRating 2 2 2 2 2 2 2
#> 2 XS0041971275 TR.IssuerRating 1 1 1 1 2 2 1
#> 3 XS0043098127 TR.IssuerRating 6 6 6 6 6 6 6
# base
df[, 3:9] <- sapply(df[ ,3:9], function(x) with(rating, CreditQuality[match(x, table = Rating)]))
df
#> V1 V2 V3 V4 V5 V6 V7 V8 V9
#> 1 XS0285400197 TR.IssuerRating 2 2 2 2 2 2 2
#> 2 XS0041971275 TR.IssuerRating 1 1 1 1 2 2 1
#> 3 XS0043098127 TR.IssuerRating 6 6 6 6 6 6 6
Created on 2022-06-01 by the reprex package (v2.0.1)
CodePudding user response：
In base R:
lut = with(B, setNames(CreditQuality, Rating))
vars = paste0("V", 3:9)
A[vars] = lapply(A[vars], \(x) lut[x])
# V1 V2 V3 V4 V5 V6 V7 V8 V9
# 1 XS0285400197 TR.IssuerRating 2 2 2 2 2 2 2
# 2 XS0041971275 TR.IssuerRating 1 1 1 1 2 2 1
# 3 XS0043098127 TR.IssuerRating 6 6 6 6 6 6 6
Same logic in data.table:
setDT(A)
A[, (vars) := lapply(.SD, \(x) lut[x]), .SDcols = vars]
Data
A = structure(list(V1 = c("XS0285400197", "XS0041971275", "XS0043098127"
), V2 = c("TR.IssuerRating", "TR.IssuerRating", "TR.IssuerRating"
), V3 = c("F1", "AAA", "WD"), V4 = c("F1", "AAA", "WD"), V5 = c("F1",
"AAA", "WD"), V6 = c("F1", "AAA", "WD"), V7 = c("F1", "F1", "WD"
), V8 = c("F1", "F1", "WD"), V9 = c("F1", "AAA", "WD")), class = "data.frame", row.names = c(NA,
-3L))
B = structure(list(Rating = c("F1", "AAA", "WD", "(P)B2", "(P)Ba1",
"(P)Ba2"), CreditQuality = c(2L, 1L, 6L, 6L, 4L, 5L)), class = "data.frame", row.names = c(NA,
-6L))