r - 为 R 中的债券设置最低评级

Question

如何根据已知机构(标准普尔、穆迪和惠誉)的评级信息设置债券的最低评级？

我有一个带有相应数值的评级表:

#define rating scale table
RTG_table <- data.frame(SP_FITCH = c("AAA", "AA+", "AA", "AA-", "A+", "A", "A-", "BBB+", "BBB", "BBB-",
                                  "BB+", "BB", "BB-", "B+", "B", "B-", "CCC+", "CCC", "CCC-", "CC", "C"),
                        MOODY = c("Aaa", "Aa1", "Aa2", "Aa3", "A1", "A2", "A3", "Baa1", "Baa2", "Baa3",
                              "Ba1", "Ba2", "Ba3", "B1", "B2", "B3", "Caa1", "Caa2", "Caa3", "Ca", "C"),
                        VALUE = c(seq(1, 20), 22), stringsAsFactors = FALSE)

我的数据框应该是这样的:

Corp <- data.frame(SECURITY_NAME = LETTERS[seq(1, 6)],
               RTG_SP_NO_WATCH = c("AA+", "A-", "BBB-", "AAA", "B-", "AA"),
               RTG_MOODY_NO_WATCH = c("Aa2", "A2", "Baa3", "Aaa", "B2", "Aa2"),
               RTG_FITCH_NO_WATCH = c("AA+", "BBB+", "BBB-", "AA+", "B-", "AA-"))

结果应该是:

Corp_MIN_RTG <- data.frame(SECURITY_NAME = LETTERS[seq(1, 6)],
               RTG_SP_NO_WATCH = c("AA+", "A-", "BBB-", "AAA", "B-", "AA"),
               RTG_MOODY_NO_WATCH = c("Aa2", "A2", "Baa3", "Aaa", "B2", "Aa2"),
               RTG_FITCH_NO_WATCH = c("AA+", "BBB+", "BBB-", "AA+", "B-", "AA-"),
               MIN_RTG = c("Aa2", "BBB+", "BBB-", "AA+", "B-", "AA-"))

> Corp_MIN_RTG
 SECURITY_NAME RTG_SP_NO_WATCH RTG_MOODY_NO_WATCH RTG_FITCH_NO_WATCH MIN_RTG
1             A             AA+                Aa2                AA+     Aa2
2             B              A-                 A2               BBB+    BBB+
3             C            BBB-               Baa3               BBB-    BBB-
4             D             AAA                Aaa                AA+     AA+
5             E              B-                 B2                 B-      B-
6             F              AA                Aa2                AA-     AA-
> 

为此，我需要提取所有 3 个评分中的最高数值，并将该值替换为 RTG_table 中的相应评分。

希望我的例子很清楚。

非常感谢任何帮助!

Answer 1

您可以按如下方式处理:

library(data.table)
# convert the dataframes into datatables and melt them into long format
corp2 <- melt(setDT(Corp), id="SECURITY_NAME")
rtg2 <- melt(setDT(RTG_table), id="VALUE", value.name = "rtgval")

# join them together and calculate the minimum rating for each 'SECURITY_NAME'
# and set those values in a new look-up data.table
DT <- corp2[rtg2, on=c("value"="rtgval"), nomatch=0
            ][, .(min_rtg = value[which.max(VALUE)]), by = SECURITY_NAME]

# join the original 'Corp' data.table with the look-up data.table
Corp <- Corp[DT, on=c("SECURITY_NAME")][order(SECURITY_NAME)]

给出:

> Corp
   SECURITY_NAME RTG_SP_NO_WATCH RTG_MOODY_NO_WATCH RTG_FITCH_NO_WATCH min_rtg
1:             A             AA+                Aa2                AA+     Aa2
2:             B              A-                 A2               BBB+    BBB+
3:             C            BBB-               Baa3               BBB-    BBB-
4:             D             AAA                Aaa                AA+     AA+
5:             E              B-                 B2                 B-      B-
6:             F              AA                Aa2                AA-     AA-