I have a data frame with the following panel structure. However, I want to change the structure, so the columns will be collapse and corresponds to the value measured in a given year.
y <- data.frame(id = c(1, 1, 1, 2, 2, 2, 3, 3, 3),
year = c(2012, 2013, 2014, 2012, 2013, 2014, 2012, 2013, 2014),
P2012 = c(1, 1, 1, 2, 2, 2, 3, 3, 3),
P2013 = c(2, 2, 2, 1, 1, 1, 1, 1, 1),
P2014 = c(3, 3, 3, 2, 2, 2, 1, 1, 1))
id year P2012 P2013 P2014
1 1 2012 1 2 3
2 1 2013 1 2 3
3 1 2014 1 2 3
4 2 2012 2 1 2
5 2 2013 2 1 2
6 2 2014 2 1 2
7 3 2012 3 1 1
8 3 2013 3 1 1
9 3 2014 3 1 1
What I want is that i only get one column, consisting of the values from the other three columns.
id year P2012 P2013 P2014 P
1 1 2012 1 2 3 1
2 1 2013 1 2 3 2
3 1 2014 1 2 3 3
4 2 2012 2 1 2 2
5 2 2013 2 1 2 1
6 2 2014 2 1 2 2
7 3 2012 3 1 1 3
8 3 2013 3 1 1 1
9 3 2014 3 1 1 1
Can anyone help me here?
Thanks!
We can use tidyverse
library(dplyr)
library(stringr)
y %>%
rowwise %>%
mutate(P = get(str_subset(names(y)[3:5], as.character(year)))) %>%
ungroup
# A tibble: 9 x 6
id year P2012 P2013 P2014 P
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 1 2012 1 2 3 1
2 1 2013 1 2 3 2
3 1 2014 1 2 3 3
4 2 2012 2 1 2 2
5 2 2013 2 1 2 1
6 2 2014 2 1 2 2
7 3 2012 3 1 1 3
8 3 2013 3 1 1 1
9 3 2014 3 1 1 1