I am looking at data on methods of recycling for towns in the UK for five years, however my data on methods is in the wrong format.
In order to prepare my data for panel analysis I need to remove the 'method' column and just have one observation (one row) for each town each year. To start this I created dummy variables for whether that town uses that method.
There are several methods of recycling, but not every town uses every method.
I currently have something that looks like this
town year method dummymethod1 dummymethod2 dummymethod3
Adur 2012 1 1 0 0
Adur 2012 2 0 0 0
Adur 2012 3 0 0 1
.........
Brent2012 1 0 0 0
Brent 2012 2 0 1 0
Brent 2012 3 0 0 1
......
(Z 2016 3 )
Where I have a new line for each method, and the dummymethod# will take the value 1 if that town uses the method in that row. eg Adur in 2012 uses method 1 and 3, Brent in 2012 uses methods 2 and 3
However I want just one line for each town in each time period that captures all the method columns dummies (for that town in that year) in one row. This could basically mean vertically summing the columns, giving either 0 or 1(if the town uses that method) value for each town and year and having it all in one row.
Can anyone help me with how to do this?
*In the picture I have browsed just four dummies for simplicity but there are 30 methods and therefore 30 dummies for each town each year. I want just one observation for East Hampshire in 2015*
In order to prepare my data for panel analysis I need to remove the 'method' column and just have one observation (one row) for each town each year. To start this I created dummy variables for whether that town uses that method.
There are several methods of recycling, but not every town uses every method.
I currently have something that looks like this
town year method dummymethod1 dummymethod2 dummymethod3
Adur 2012 1 1 0 0
Adur 2012 2 0 0 0
Adur 2012 3 0 0 1
.........
Brent2012 1 0 0 0
Brent 2012 2 0 1 0
Brent 2012 3 0 0 1
......
(Z 2016 3 )
Where I have a new line for each method, and the dummymethod# will take the value 1 if that town uses the method in that row. eg Adur in 2012 uses method 1 and 3, Brent in 2012 uses methods 2 and 3
However I want just one line for each town in each time period that captures all the method columns dummies (for that town in that year) in one row. This could basically mean vertically summing the columns, giving either 0 or 1(if the town uses that method) value for each town and year and having it all in one row.
Can anyone help me with how to do this?
*In the picture I have browsed just four dummies for simplicity but there are 30 methods and therefore 30 dummies for each town each year. I want just one observation for East Hampshire in 2015*
Attached Files
Comment