0

I'm trying to make a matrix using a function that counts the number of "ID"s that were in "RATING" i in "YEAR" 1996 and then moved to "RATING" k in "YEAR" 1997 and then moved to "RATING" k in "YEAR" 1998.

I believe the row labels of the matrix would be the combinations of "RATING" i & "RATING" j and the column labels of the matrix would be the "RATING" k.

The sum of each row should be the sum of all "ID"s that were "RATING" i in 1996 and "RATING" k in 1997.

This is my sample data.

df <- rbind(df, data.frame("ID"=c('6387', '6387', '6387', '6403', '6403', '6403', '6408', '6408', '6408', '6411', '6411', '6411', '6413', '6413', '6413', '6422', '6422', '6422', '6427', '6427', '6427')))
df <- cbind(df, data.frame("YEAR"=c('1996', '1997', '1998', '1996', '1997', '1998', '1996', '1997', '1998', '1996', '1997', '1998', '1996', '1997', '1998', '1996', '1997', '1998', '1996', '1997', '1998')))
df <- cbind(df, data.frame("RATING"=c('Aa', 'Ba', 'Ba', 'B', 'Caa', 'Caa', 'A', 'Ba', 'Ba', 'B', 'Ba', 'B', 'B', 'Caa', 'Caa', 'B', 'B', 'B', 'Caa', 'B', 'Caa')))

Using the code provided by @Muffindorf :

df_long <- spread(df, YEAR, RATING)
df_long$c9697 <- paste(df_long$`1996`, df_long$`1997`, sep = '-')
as.matrix(table(df_long$c9697, df_long$`1998`))

gives me:

       A Aa B Ba Caa
 A-Ba  0  0 0  1   0
 Aa-Ba 0  0 0  1   0
 B-B   0  0 1  0   0
 B-Ba  0  0 1  0   0
 B-Caa 0  0 0  0   2
 Caa-B 0  0 0  0   1

The row labels tell us the previous ratings in 1996 and 1997, the column labels tell us the rating in 1998, and the elements tell us the count of IDs that followed the transitions.

What I need to do now is have my row labels be all the possible combinations of ratings, unobserved included. The code above only results in row labels of observed combinations.

gm007
  • 547
  • 4
  • 11

1 Answers1

0

Is this what you are looking for?

library(tidyr)

df_long <- spread(df, YEAR, RATING)

df_long$c9697 <- paste(df_long$`1996`, df_long$`1997`, sep = '-')

as.matrix(table(df_long$c9697, df_long$`1998`))

Granted, this does not have all the combinations of ratings, but I guess you can add them manually

Muffindorf
  • 113
  • 5
  • Thank you! This is very close. What is the most efficient way of adding all the combinations? – gm007 Oct 30 '18 at 16:37
  • https://stackoverflow.com/questions/23020826/how-do-you-map-every-combination-of-categorical-variables-in-r – Muffindorf Oct 30 '18 at 17:07