I have a dataset of soccer match results, and I am hoping to learn R by creating a running set of ratings similar to the World Football Elo formula. I am running into trouble with things that seem to be simple in Excel aren't exactly intuitive in R. For instance, the first 15 of 4270 observations with the necessary variables:
date t.1 t.2 m.result
1 19960406 DC SJ 0.0
2 19960413 COL KC 0.0
3 19960413 NE TB 0.0
4 19960413 CLB DC 1.0
5 19960413 LAG NYRB 1.0
6 19960414 FCD SJ 0.5
7 19960418 FCD KC 1.0
8 19960420 NE NYRB 1.0
9 19960420 DC LAG 0.0
10 19960420 CLB TB 0.0
11 19960421 COL FCD 1.0
12 19960421 SJ KC 0.5
13 19960427 CLB NYRB 1.0
14 19960427 DC NE 0.5
15 19960428 FCD TB 1.0
I want to be able to create a new variable that will be a running count of t.1 and t.2's total matches played (i.e., the instances up to the date in question that "DC" occurs in columns t.1 or t.2):
date t.1 t.2 m.result ##t.1m ##t.2m
1 19960406 DC SJ 0.0 1 1
2 19960413 COL KC 0.0 1 1
3 19960413 NE TB 0.0 1 1
4 19960413 CLB DC 1.0 1 2
5 19960413 LAG NYRB 1.0 1 1
6 19960414 FCD SJ 0.5 1 2
7 19960418 FCD KC 1.0 2 2
8 19960420 NE NYRB 1.0 2 2
9 19960420 DC LAG 0.0 3 2
10 19960420 CLB TB 0.0 2 2
11 19960421 COL FCD 1.0 2 3
12 19960421 SJ KC 0.5 3 3
13 19960427 CLB NYRB 1.0 3 3
14 19960427 DC NE 0.5 4 3
15 19960428 FCD TB 1.0 4 3
in Excel, this is a (relatively) simple =SUMPRODUCT equation, e.g:
E4=SUMPRODUCT((A:A<=A4)*(B:B=B4))+SUMPRODUCT((A:A<=A4)*(C:C=B4))
where E4 is t.1m for obs # 4, A:A is Date, B:B is t.1, C:C is t.2, etc.
But in R, I can get total sumproduct printed for me (i.e. "DC" has played 576 games across my dataset), but for some reason (probably that I'm new, impatient, rattled by trial and error) I'm just lost on how to make a running count on observation data, and especially how to make that running count into a variable, which is vital for any game rating index. I know 'PlayerRatings' exists, I feel that for my R education I should be able do this in the R suite without that package. plyr or dplyr is okay, of course.
For reference, here is my data for you to copy/paste into your R.
date<-c(19960406,19960413,19960413,19960413,19960413,19960414,19960418,19960420,19960420,19960420,19960421,19960421,19960427,19960427,19960428)
t.1<-c("DC","COL","NE","CLB","LAG","FCD","FCD","NE","DC","CLB","COL","SJ","CLB","DC","FCD")
t.2<-c("SJ","KC","TB","DC","NYRB","SJ","KC","NYRB","LAG","TB","FCD","KC","NYRB","NE","TB")
m.result<-c(0.0,0.0,0.0,1.0,1.0,0.5,1.0,1.0,0.0,0.0,1.0,0.5,1.0,0.5,1.0)
mtable<-data.frame(date,t.1,t.2,m.result)
mtable
Here's a very straightforward solution that isn't pretty but does the job.
First, just a change to your data to make comparisons easier:
mtable<-data.frame(date,t.1,t.2,m.result, stringsAsFactors = FALSE)
Edited in:
If you want to assure the matches are ordered by date, you can use order as pointed out by @eipi10:
mtable = mtable[order(mtable$date), ]
Just note that in case the dates are in a format that the chronological order isn't the integer order, you can first convert them to Date format using as.Date().
What we are going to do is, for each row, take a subset of the dataframe with the columns t.1 and t.2, with all the rows from 1 to the said row. So 1:1, 1:2, 1:3, etc. At each run, we count the number of times that team has appeared, and use that as the result for the new column.
mtable$t.1m <- sapply(1:nrow(mtable),
function(i) sum(mtable[1:i, c("t.1", "t.2")] == mtable$t.1[i]))
This was done for teams in t.1, with a small change on argument after ==we can make it for t.2:
mtable$t.2m <- sapply(1:nrow(mtable),
function(i) sum(mtable[1:i, c("t.1", "t.2")] == mtable$t.2[i]))
Now our dataframe looks like this:
> mtable
date t.1 t.2 m.result t.1m t.2m
1 19960406 DC SJ 0.0 1 1
2 19960413 COL KC 0.0 1 1
3 19960413 NE TB 0.0 1 1
4 19960413 CLB DC 1.0 1 2
5 19960413 LAG NYRB 1.0 1 1
6 19960414 FCD SJ 0.5 1 2
7 19960418 FCD KC 1.0 2 2
8 19960420 NE NYRB 1.0 2 2
9 19960420 DC LAG 0.0 3 2
10 19960420 CLB TB 0.0 2 2
11 19960421 COL FCD 1.0 2 3
12 19960421 SJ KC 0.5 3 3
13 19960427 CLB NYRB 1.0 3 3
14 19960427 DC NE 0.5 4 3
15 19960428 FCD TB 1.0 4 3