I've got a dataset set up roughly like the below. The variable "drought" is equal to 1 if that year was a drought year in that district. I need to figure out to calculate droughtyears04, droughtyears05, droughtyears06, droughtyears07, which count how many of the 7 preceding years were drought years for 2004-2007. For example, droughtyears04 counts how many drought years there were between 1997-2003, droughtyears05 counts how many drought years there were between 1998-2004, so on and so forth.
I'm not really sure how to do this. I would really appreciate the help (as always!)
Also, it should be noted that the values can be carried through (i.e. droughtyears04 doesn't necessarily need to be missing for years other than 2004).
I'm not really sure how to do this. I would really appreciate the help (as always!)
Also, it should be noted that the values can be carried through (i.e. droughtyears04 doesn't necessarily need to be missing for years other than 2004).
region_id | region_name | drought | year | droughtyears04 | droughtyears05 | droughtyears06 | droughtyears07 |
1000 | Vellore | 0 | 1997 | ||||
1000 | Vellore | 1 | 1998 | ||||
1000 | Vellore | 0 | 1999 | ||||
1000 | Vellore | 0 | 2000 | ||||
1000 | Vellore | 1 | 2001 | ||||
1000 | Vellore | 1 | 2002 | ||||
1000 | Vellore | 0 | 2003 | ||||
1000 | Vellore | 0 | 2004 | 3 | |||
1000 | Vellore | 1 | 2005 | 3 | |||
1000 | Vellore | 0 | 2006 | 3 | |||
1000 | Vellore | 1 | 2007 | 3 | |||
254 | Faizabad | 1 | 1997 | ||||
254 | Faizabad | 1 | 1998 | ||||
254 | Faizabad | 0 | 1999 | ||||
254 | Faizabad | 0 | 2000 | ||||
254 | Faizabad | 0 | 2001 | ||||
254 | Faizabad | 1 | 2002 | ||||
254 | Faizabad | 1 | 2003 | ||||
254 | Faizabad | 0 | 2004 | 4 | |||
254 | Faizabad | 1 | 2005 | 3 | |||
254 | Faizabad | 0 | 2006 | 3 | |||
254 | Faizabad | 1 | 2007 | 3 | |||
256 | Farrukhabad | 1 | 1997 | ||||
256 | Farrukhabad | 1 | 1998 | ||||
256 | Farrukhabad | 1 | 1999 | ||||
256 | Farrukhabad | 0 | 2000 | ||||
256 | Farrukhabad | 1 | 2001 | ||||
256 | Farrukhabad | 1 | 2002 | ||||
256 | Farrukhabad | 0 | 2003 | ||||
256 | Farrukhabad | 0 | 2004 | 5 | |||
256 | Farrukhabad | 1 | 2005 | 4 | |||
256 | Farrukhabad | 0 | 2006 | 4 | |||
256 | Farrukhabad | 1 | 2007 | 3 |
Comment