Weighted Average

dmw

New Member
#1
Hi,

I am a new member to this community and am having trouble calculating the weighted average of my dataset. Granted, I am a beginner in R barely scratching the surface of R and am trying to learn it as much as I can.
I know what I want the outcome to be, I don't know where to start with the commands. Any help is greatly appreciated! Criticism is more than welcome.


Here's my problem/hurdle:

I would like to display the weighted average of each security within its sector level, sorted by the composite rating (A, AA, BBB). For example:

Weighted average of Industrials (Sector Level 2) in Corporate (Sector Level 1).
Weighted average of Automotive (Sector Level 3) in Industrials (Sector Level 2).
Weighted average of each security in Auto Parts & Equipment (Sector Level 4) in Automotive (Sector Level 3) and so on.

I have no idea what commands would accomplish my goal. I am very open to suggestions and ideas. Thank you in advance.


Here's the code I have written so far, and you will find a link (dropbox) to my .csv document containing the dataset (if this is not the preferred method of sharing data, please let me know). I have formatted the data to only use the relevant information, as seen in the code below.

Link to the .csv file: Dropbox file link


Code:
mkt <- read.csv


library(plyr)
library(lubridate)


divisor <- ddply(mkt,.(Sector.Level.2,Sector.Level.3,Sector.Level.4,year,Composite.Rating),summarize,amt=sum(Face.Value))
divisor$id <- paste(divisor$Sector.Level.4,divisor$year,sep="")
subind.year <- divisor[,c("id","amt")]


mkt$Maturity <- mdy(as.character(mkt$Maturity))
mkt$year <- year(mkt$Maturity)


mkt$Composite.Rating <- gsub("^A1","A",mkt$Composite.Rating)
mkt$Composite.Rating <- gsub("^A2","A",mkt$Composite.Rating)
mkt$Composite.Rating <- gsub("^A3","A",mkt$Composite.Rating)
mkt$Composite.Rating <- gsub("^AA1","AA",mkt$Composite.Rating)
mkt$Composite.Rating <- gsub("^AA2","AA",mkt$Composite.Rating)
mkt$Composite.Rating <- gsub("^AA3","AA",mkt$Composite.Rating)
mkt$Composite.Rating <- gsub("^BBB1","BBB",mkt$Composite.Rating)
mkt$Composite.Rating <- gsub("^BBB2","BBB",mkt$Composite.Rating)
mkt$Composite.Rating <- gsub("^BBB3","BBB",mkt$Composite.Rating)

#head(mkt$Composite.Rating,50)


table <- ddply(mkt,.(Sector.Level.2,Sector.Level.3,Sector.Level.4,year,Composite.Rating),summarize,
               m.ytw = round(mean(Yield.to.Worst),digits=2),
               m.ytm = round(mean(Effective.Yield),digits=2),
               m.dtw = round(mean(Duration.To.Worst),digits=2),
               m.ed = round(mean(Effective.Duration),digits=2),
               m.oas = round(mean(OAS.vs.Govt),digits=2),
               m.avglife = round(mean(year),digits=2),
               num = length(Sector.Level.3),
               amt=sum(Face.Value))
 

Lazar

Phineas Packard
#2
What do you wish to weight by?

In passing you can reduce your gsub statements to a single statement:
Code:
gsub("([A-Z]+)([0-9]+)","\\1",mkt$Composite.Rating)
For example:
Code:
> test <- c("A1", "AA2", "BBB3")
> gsub("([A-Z]+)([0-9]+)","\\1",test)
[1] "A"   "AA"  "BBB"
 

dmw

New Member
#3
What do you wish to weight by?

In passing you can reduce your gsub statements to a single statement:
Code:
gsub("([A-Z]+)([0-9]+)","\\1",mkt$Composite.Rating)
For example:
Code:
> test <- c("A1", "AA2", "BBB3")
> gsub("([A-Z]+)([0-9]+)","\\1",test)
[1] "A"   "AA"  "BBB"
Hey Lazar,

thank you for your reply. Awesome, I'll change my code to what you have suggested, thank you!
I'd like to weight by the calculated ytw, ytm, dtw, ed, oas, and avglife. Preferably in a table format, sorted by sector level and composite ratings. I know it is possible, I just don't know how to tackle the code, what packages, if any, to add etc. Like I mentioned before, I am just now getting my feet wet with R and am enjoying it so far. It's a powerful tool and I'd like to know how to put its power to use.

Thank you for your help.
 

Lazar

Phineas Packard
#4
I find ddply code hard to read as I have not used it much so it is a bit hard for me to follow what is going on but have you looked into the weighted.mean function?
 

dmw

New Member
#5
I find ddply code hard to read as I have not used it much so it is a bit hard for me to follow what is going on but have you looked into the weighted.mean function?
No, I haven't yet. I'll take a look at it tomorrow. Thanks for the tip! I'll let you know what I come up with. If you have any other suggestions, or anyone else does, please let me know.

I also want to mention that I now have cross-posted my question to get more insights. Here is the link:
Cross-posted Link
 
Last edited: