Building Scoring and Ranking Systems in R

This guest article was written by author and consultant Tristan Yates (see his bio below). It emphasizes R’s data object manipulation and scoring capabilities via a detailed financial analysis example.

Scoring and ranking systems are extremely valuable management tools. They can be used to predict the future, make decisions, and improve behavior – sometimes all of the above. Think about how the simple grade point average is used to motivate students and make admissions decisions.

R is a great tool for building scoring and ranking systems. It’s a programming language designed for analytical applications with statistical capabilities. The capability to store and manipulate data in list and table form is built right into the core language.
 

But there’s also some validity to the criticism that R provides too many choices and not enough guidance. The best solution is to share your work with others, so in this article we show a basic design workflow for one such scoring and ranking system that can be applied to many different types of projects.

The Approach
For a recent article in Active Trader, we analyzed the risk of different market sectors over time with the objective of building less volatile investment portfolios. Every month, we scored each sector by its risk, using its individual ranking within the overall population, and used these rankings to predict future risk.

Here’s the workflow we used, and it can be applied to any scoring and ranking system that must perform over time (which most do):

  1. Load in the historical data for every month and ticker symbol.
  2. Load in the performance data for every month and ticker symbol.
  3. Generate scores and rankings for every month and ticker symbol based upon their relative position in the population on various indicators.
  4. Review the summary and look for trends.

In these steps, we used four data frames, as shown below:

Name Contents
my.history historical data
my.scores scoring components, total scores, rankings
my.perf performance data
my.summary   summary or aggregate data

 
One of my habits is to prefix my variables – it helps prevent collisions in the R namespace.

Some people put all of their data in the same data.frame, but keeping it separate reinforces good work habits. First, the historical data and performance data should never be manipulated, so it makes sense to keep it away from the more volatile scoring data.

Second, it helps draw a clear distinction between what we know at one point in time – which is historical data – and what we will know later – which is the performance data. That’s absolutely necessary for the integrity of the scoring system.

My.history, my.scores, and my.perf are organized like this:

 yrmo   ticker    var1     var2     etc…  
200401   XLF      
200401   XLB      
etc…        

 
yrmo is the year and month and ticker is the item to be scored. We maintain our own list of dates (in yrmo format) and items in my.dates and my.items. Both these lists are called drivers, as they can help iterate through the data.frame, and we also have a useful data.frame called my.driver which has only the yrmo and ticker.

One trick – we keep the order the same for all of these data.frames. That way we can use indexes on one to query another. For example, this works just fine:

  Vol.spy <- my.history$vol.1[my.score$rank==1]

Loading Data
First, we get our driver lists and my.driver data.frame set up. We select our date range and items from our population, and then build a data.frame using the rbind command.

  #this is based on previous analysis
  my.dates <- m2$yrmo[13:(length(m2$yrmo)-3)]
  my.items <- ticker.list[2:10]

  #now the driver
  my.driver <- data.frame()
  for (z.date in my.dates) {
    my.driver <- rbind(my.driver,data.frame(ticker=my.items,yrmo=z.date))
  }

Next, let’s get our historical and performance data. We can make a function that can be called once for each row in my.driver that then loads any data needed.

  my.seq <- 1:length(my.driver[,1])
  my.history <- data.frame(ticker=my.driver$ticker,yrmo=my.driver$yrmo,
    vol.1=sapply(my.seq,calc.sd.fn,-1,-1))

Each variable can be loaded by a function called with the sapply command. The calc.sd.fn function first looks up the ticker and yrmo from my.driver using the index provided, and then returns the data. You would have one function for each indicator that you want to load. My.perf, which holds the performance data, is built in the exact same way.

The rbind command is slow unfortunately, but loading the historical and performance data only needs to be done once.

Scoring The Data
This is where R really shines. Let’s look at the highest level first.

  my.scores <- data.frame()
  for (z.yrmo in my.dates) {
    my.scores <- rbind(my.scores,calc.scores.fn(z.yrmo))
    }
  my.scores$p.tot <- (my.scores$p.vol.1)

Every indicator gets its own score, and then that can be combined in any conceivable way to create total score. In this very simple case, we’re only scoring one indicator, so we just use that score as the total score.

For more complex applications, the ideal strategy is to use multiple indicators from multiple data sources to tell the same story. Ignore those who advocate reducing variables and cross-correlations. Instead, think like a doctor that wants to run just one more test and get that independent confirmation.

Now the calc functions:

  scaled.score.fn <- function(z.raw)
    {pnorm(z.raw,mean(z.raw),sd(z.raw))*100}
  scaled.rank.fn <- function(z.raw) {rank(z.raw)}

  calc.scores.fn <- function(z.yrmo) {
    z.df <- my.history[my.history$yrmo==z.yrmo,]
    z.scores <- data.frame(ticker=z.df$ticker,yrmo=z.df$yrmo,
      p.vol.1=scaled.score.fn(z.df$vol.1),r.vol.1=scaled.rank.fn(z.df$vol.1))
    z.scores
    }

The calc.scores.fn function queries the data.frame to pull the population data for just a single point in time. Then, each indicator is passed to the scaled.score.fn and scaled.rank.fn function, returning a list of scores and ranks.

Here, we use the pnorm function to calculate a statistical Z-score, which is a good practice for ensuring that a scoring system isn’t dominated by a single indicator.

Checking the Scores
At this point, we create a new data.frame for summary analysis. We use the always useful and always confusing aggregate function and combine by rank. Notice how we easily we can combine data from my.history, my.scores and my.perf.

  data.frame(rank=1:9,p.tot=aggregate(my.scores$p.tot,
    list(rank=my.scores$r.vol.1),mean)$x,ret.1=aggregate(my.perf$ret.1,
    list(rank=my.scores$r.vol.1),mean)$x,sd.1=aggregate(my.perf$ret.1,
    list(rank=my.scores$r.vol.1),sd)$x,vol.1=aggregate(my.history$vol.1,
    list(rank=my.scores$r.vol.1),mean)$x,vol.p1=aggregate(my.history$vol.1,
    list(rank=my.scores$r.vol.1),mean)$x)

Here’s the result. We could check plots or correlations, but the trend – higher relative volatility in the past (vol.p1, p.tot) is more likely to mean higher relative volatility in the future (vol.1, sd.1) - is crystal clear.

rank  p.tot  ret.1   sd.1    vol.1   vol.p1  
1 12.1 0.131 4.03 16.5 13.8
2 19.4 0.0872 4.82 16.6 16.1
3 27.1 0.2474 4.96 20.1 18
4 35.6 0.4247 5.31 20.9 19.9
5 44.9 0.6865 5.98 22.1 21.7
6 53 0.3235 5.84 21.5 23.2
7 65.1 1.019 5.86 24.6 25.4
8 78 0.7276 6.04 26.9 28.4
9 96.4 0.0837 9.34 35.2 38.3

 
In the case of our analysis, the scores aren’t really necessary – we’re only ranking nine items every month. If we did have a larger population, we could use code like this to create subgroups (six groups shown here), and then use the above aggregate function with the new my.scores$group variable.

  my.scores$group <- cut(my.scores$p.tot,
    breaks=quantile(my.scores$p.tot,(0:6)/6),include.lowest=TRUE,labels=1:6)

Wrap-up
We ultimately only ended up scoring one variable, but it’s pretty easy to see how this framework could be expanded to dozens or more. Even so, it’s an easy system to describe – we grade each item by its ranking within the population. People don’t trust scoring systems that can’t be easily explained, and with good reason.

There’s not a lot of code here, and that’s a testimony to R’s capabilities. A lot of housekeeping work is done for you, and the list operations eliminate confusing nested loops. It can be a real luxury to program in R after dealing with some other “higher level” language.

We hope you find this useful and encourage you to share your own solutions as well.

Tristan Yates is the Executive Director of Yates Management, a management and analytical consulting firm serving financial and military clients. He is also the author of Enhanced Indexing Strategies and his writing and research have appeared in publications including the Wall Street Journal and Forbes/Investopedia.

Data Manipulation with R – Spector (2008)

Title: Data Manipulation with R
Author(s): Phil Spector
Publisher/Date: Springer/2008
Statistics level: N/A
Programming level: Intermediate
Overall recommendation: Highly recommended

If there is one book that every beginning R user coming from a programming background should have, it is Spector’s Data Manipulation with R. New R users with analytic backgrounds and experience with software packages such as SAS and SPSS will do well to start with Muenchen’s R for SPSS and SAS users, especially given that a free abbreviated version is available, but those users should also make Data Manipulation with R a quick second addition to their library.

The text of this book is as concise and to the point as its title. It covers almost every relevant data manipulation topic in R, from modes and classes, through accessing data via database connections, to complex reshaping and aggregating functions. It has copious examples and the text hits just the right level of sophistication for the individual who has some experience with programming, but little experience with R idioms and data manipulation techniques.

My only critique of this book is that it skips over the basics of creating user-defined functions for data manipulation tasks. Spector addresses mapping functions to various data structures, but it seems likely that, at this level, the average R analyst would be better served by a discussion of how to simply create a function in R. Keep in mind that if you are looking for that type of information, you will need to look elsewhere. The same is true if you are looking for any sort of statistical instruction, as Data Manipulation with R focuses almost exclusively on programming.

Overall, I highly recommend this book. At around $45 USD, it is well worth the price. You’ll breeze through it on your first pass, but if you’re new to R you will get your money’s worth out of it as a reference text.

Webscraping using readLines and RCurl

There is a massive amount of data available on the web. Some of it is in the form of precompiled, downloadable datasets which are easy to access. But the majority of online data exists as web content such as blogs, news stories and cooking recipes. With precompiled files, accessing the data is fairly straightforward; just download the file, unzip if necessary, and import into R. For “wild” data however, getting the data into an analyzeable format is more difficult. Accessing online data of this sort is sometimes reffered to as “webscraping”. Two R facilities, readLines() from the base package and getURL() from the RCurl package make this task possible.

readLines

For basic webscraping tasks the readLines() function will usually suffice. readLines() allows simple access to webpage source data on non-secure servers. In its simplest form, readLines() takes a single argument – the URL of the web page to be read:

web_page <- readLines("http://www.interestingwebsite.com")

As an example of a (somewhat) practical use of webscraping, imagine a scenario in which we wanted to know the 10 most frequent posters to the R-help listserve for January 2009. Because the listserve is on a secure site (e.g. it has https:// rather than http:// in the URL) we can't easily access the live version with readLines(). So for this example, I've posted a local copy of the list archives on the this site.

One note, by itself readLines() can only acquire the data. You'll need to use grep(), gsub() or equivalents to parse the data and keep what you need.

# Get the page's source
web_page <- readLines("http://www.programmingr.com/jan09rlist.html")

# Pull out the appropriate line
author_lines <- web_page[grep("&lt;I&gt;", web_page)]

# Delete unwanted characters in the lines we pulled out
authors <- gsub("&lt;I&gt;", "", author_lines, fixed = TRUE)

# Present only the ten most frequent posters
author_counts <- sort(table(authors), decreasing = TRUE)
author_counts[1:10]

We can see that Gabor Grothendieck was the most frequent poster to R-help in January 2009.

The RCurl package

To get more advanced http features such as POST capabilities and https access, you'll need to use the RCurl package. To do webscraping tasks with the RCurl package use the getURL() function. After the data has been acquired via getURL(), it needs to be restructured and parsed. The htmlTreeParse() function from the XML package is tailored for just this task. Using getURL() we can access a secure site so we can use the live site as an example this time.

# Install the RCurl package if necessary
install.packages("RCurl", dependencies = TRUE)
library("RCurl")

# Install the XML package if necessary
install.packages("XML", dependencies = TRUE)
library("XML")

# Get first quarter archives
jan09 <- getURL("https://stat.ethz.ch/pipermail/r-help/2009-January/date.html", ssl.verifypeer = FALSE)

jan09_parsed <- htmlTreeParse(jan09)

# Continue on similar to above
...

For basic webscraping tasks readLines() will be enough and avoids over complicating the task. For more difficult procedures or for tasks requiring other http features getURL() or other functions from the RCurl package may be required. For more information on cURL visit the project page here.

Helpful statistical references

In a previous article I provided a list of R programming resources. As a complement to that post, I’ve compiled a list of statistically oriented websites that colleagues and I have found useful below. For the most part, these sites focus on statistics and quantitative research methods rather than programming.

This first grouping lists sites that are mostly one-stop-shops for research design and analytical information. The first two, (and especially the UCLA website) are Tier I statistics/research methods sites. They are indispensable. The three remaining sites in this section cover less advanced topics and focus more on basics, but may be helpful for the R user who is more programmer than statistician.

The second group of sites is comprised of technical references such as statistical dictionaries and notation guides. The final section list two sites that have detailed information and examples focused on running statistical analyses in R. Note that the UCLA site also includes many examples using R.

Comprehensive coverage

Statistical computing at UCLA

Statnotes: Topics in Multivariate Analysis, by G. David Garson

Introductory Statistics: Concepts, models, and applications

Social Research Methods Knowledge Base

Wolfram MathWorld

Technical References

StatSoft statistical glossary

Glossary of technical notation

Dictionary of Algorithms and Data Structures

R specific sites

Journal of Statistical Software

QuickR

If you know of another site for either R programming or statistics that I’ve missed, mention it in the comments below and I’ll add it to the proper list.

Data Analysis and Graphics Using R – Maindonald and Braun (2003)

Title: Data Analysis and Graphics Using R: An Example-Based Approach
Author(s): John Maindonald; John Braun
Publisher/Date: Cambridge University Press/2003
Statistics level: Intermediate to advanced
Programming level: Beginner to intermediate
Overall recommendation: Highly recommended

Data Analysis and Graphics Using R (DAAG) covers an exceptionally large range of topics. Because of the book’s breadth, new and experienced R users alike will find the text helpful as a learning tool and resource, but it will be of most service to those who already have a basic understanding of statistics and the R system.

Although the text includes both an Introduction to R section (chapter one) and a discussion of the basics of quantitative data analysis (chapters two through four), these chapters will be most useful as overviews (or reviews for more experienced readers), as they lack the detail required to take a reader from no knowledge of these subjects to a functional understanding. For example, chapter one discusses importing data in .txt and .csv format, but the foreign package is not discussed until chapter fourteen – the final chapter of the book. In practice, .txt data structures are not common enough to justify relegating a discussion of the foreign package to the supplemental materials and a researcher stuck with a .sav or .dbf file would not leave chapter one with enough knowledge to import their data into R.

Chapters five through thirteen deal primarily with different flavors of regression techniques. These chapters are the truly valuable pieces of this work as each chapter covers one or two approaches in detail. The major analyses covered in this section include bivariate and multivariate regression, GLM and survival models, time-series analyses, repeated measures, classification trees, and factor analysis. As regression techniques are a core component of quantitative methods these chapters will be useful to many researchers across many industries and disciplines. Much of the discussion of graphing comes via diagnostic and exploratory techniques that are related to the analyses in this section.

As the subtitle suggests, examples of code accompany most significant discussions of analyses. Additionally, several full color plates of graphs are included in the appendices, allowing the authors to provide examples of color options.

DAAG is highly recommended for readers who have at least a basic understanding of quantitative analysis and at least some limited experience with R, however, more advanced readers will also find this book useful as a review and reference.