使用鍵替換整個data.table中的值-有解無憂

我有一個大資料表，如下所示：

    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))

實際的資料表要大得多，但這應該作為一個例子。此外，我有一個鍵，我想用數字替換評級（這里是 F1、AAA 和 WD）。

    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))

我想用我在鍵中分配的每個評級的 CreditQuality 替換這些評級。這意味著帶有 F1 的單元格現在是 2。帶有 WD 的單元格將是 6，依此類推。新表應如下所示：

    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

我嘗試過使用match，mapvalues但match似乎只適用于單列，并且mapvalues只適用于原子向量而不適用于 data.table。有些人遇到過類似的問題，但是他們中的大多數人只需要替換單個列中的值，而我想替換 data.table 中多個列中的值

uj5u.com熱心網友回復：

您可以使用melt和dcast：

dcast(
  rating[melt(df, id=c("V1", "V2"),value.name = "Rating"), on="Rating"],
  V1 V2~variable, value.var = "CreditQuality"
)

輸出：

             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

注意：我假設您的源資料是df，而您的評級資料是rating。我看到你的框架已經是一流的了data.table

uj5u.com熱心網友回復：

您可以使用dplyr和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

uj5u.com熱心網友回復：

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

^{由reprex 包于 2022-06-01 創建(v2.0.1)}

uj5u.com熱心網友回復：

在基礎 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

相同的邏輯data.table：

setDT(A)
A[, (vars) := lapply(.SD, \(x) lut[x]), .SDcols = vars]

資料

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))

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/484940.html

標籤：r 数据框数据表 plyr

上一篇：如何從HTML/CSS中提取串列元素并放入R資料框中？

下一篇：根據這些列的值創建包含其他列名稱的新列