Consider the following dataset:
mydata<-data.frame(id = c("R007", "R008"),
cohort = c(1,2),
maledummy = c(1,0),
employment_2016_12_01 = c(1,0),
employment_2017_01_01 = c(1,1),
employment_2017_02_01 =c(1,1))
In this dataset, I observe the individuals R007 and R008 (id-variable). From these individuals, I observe their gender (maledummy) as well as whether they worked in a particular month (employment_2016_12_01, …, employment_2017_02_01). Now I would like to rearrange the dataset to get to this final result:
#Final dataset
mydatafinal<-data.frame(id = c("R007", "R007", "R007",
"R008", "R008", "R008"),
cohort = c(1,1,1,2,2,2),
maledummy = c(1,1,1,0,0,0),
employment = c(1,1,1,0,0,0),
date = c("2016-12-01", "2017-01-01", "2017-02-01",
"2016-12-01", "2017-01-01", "2017-02-01"))
mydatafinal$date<-as.Date(mydatafinal$date, "%Y-%m-%d")
In other words, I would like to use the ending of employment variable and change this into a date. In addition, the columns now become additional rows. Is there anyone who has an idea on how to do this?
I appreciate any help. Thanks in advance.
library(tidyverse)
mydata |>
pivot_longer(starts_with("employment_"),
names_to = "date",
values_to = "employment") |>
mutate(date = date %>%
str_remove("employment_") %>%
ymd())
Result
id cohort maledummy date employment
<chr> <dbl> <dbl> <date> <dbl>
1 R007 1 1 2016-12-01 1
2 R007 1 1 2017-01-01 1
3 R007 1 1 2017-02-01 1
4 R008 2 0 2016-12-01 0
5 R008 2 0 2017-01-01 1
6 R008 2 0 2017-02-01 1