Hi I am trying to compute a 1 year migration matrix and cannot figure out how to do it...any help would be greatly appreciated!
My dataset looks like below (unbalanced data):
bond_id ratings start_year end_year
1 a 2000 2004
1 b 2005 2008
1 c 2009 2010
2 b 2003 2005
2 a 2006 2007
3 c 2001 2006
My thinking is to 1) Create multiple observations from one obs based on year so look like (I cannot figure out how to code this...):
bond_id ratings start_year end_year current_year_new nextyear_rating
1 a 2000 2004 2000 a
1 a 2000 2004 2001 a
1 a 2000 2004 2002 a
1 a 2000 2004 2003 a
1 a 2000 2004 2004 b
1 b 2005 2008 2005 b
1 b 2005 2008 2006 b
1 b 2005 2008 2007 b
1 b 2005 2008 2008 c
1 c 2009 2010 2009 c
1 c 2009 2010 2010 deflt
2 b 2003 2005 2003 b
2 b 2003 2005 2004 b
2 b 2003 2005 2005 a
2 a 2006 2007 2006 a
2 a 2006 2007 2007 deflt
3 c 2001 2006 2001 c
3 c 2001 2006 2002 c
3 c 2001 2006 2003 c
3 c 2001 2006 2004 c
3 c 2001 2006 2005 c
3 c 2001 2006 2006 deflt
2) then calculate the average of each
to
a b c deflt
from a 5/7 1/7 0/7 1/7
b 1/7 5/7 1/7 0/7
c 0/8 0/8 6/8 2/8
Deflt 0 0 0 1
Any help would be greatly appreciated!