[R] Help - Group Subsector Weighted Average

[dmw]

Hi,

I am new to this forum and R in general. I have a database that I would like
to analyze. My ultimate goal is to show the weighted average of each
security in its sector to its predecessor Sector level. I want it to be as
granular as possible.   

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'd like to weight the securities and sector levels 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. Any
help is greatly appreciated, I am very open to suggestions and ideas.

Here is the dropbox link to my database:
Dropbox Database Link
<https://www.dropbox.com/s/yr8olrj0v5mp4nu/dmw_dataset.csv?dl=0>  


My code so far:

-----------------------------------------
#Read file into R
mkt <- read.csv

#Add librarys to work with data
library(plyr)
library(lubridate)

#Split data frames by variables
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")]

#Format output format of maturity and year
mkt$Maturity <- mdy(as.character(mkt$Maturity))
mkt$year <- year(mkt$Maturity)

#Format composite rating
mkt$Composite.Rating <- gsub("([A-Z]+)([0-9]+)","\\1",mkt$Composite.Rating)

#Test composite rating formating
test <- c("A1", "AA2", "BBB3")
gsub("([A-Z]+)([0-9]+)","\\1",test)

#Show output format
#head(mkt$Composite.Rating,50)


#Split data frames by variables
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))


#Show output format
head(table,100)
-----------------------------------------

This post is also cross-listed in "Talk Stats". Link to that post:
Talk Stats Post
<http://www.talkstats.com/showthread.php/61584-Weighted-Average>  



--
View this message in context: http://r.789695.n4.nabble.com/Help-Group-Subsector-Weighted-Average-tp4709901.html
Sent from the R help mailing list archive at Nabble.com.