0

I need help with a unique query, I couldn't find any solutions as yet. But that could be because I didn't know how to word what I am looking. Below is a sample dataset

name, position, start_date, end_date
ABC, Contractor, 09/02/2017, 07/01/2018
ABC, Associate Consultant, 08/01/2018, 31/12/2018
ABC, Consultant, 01/01/2019, 31/05/2019

Essentially ABC is a person who has had different positions over time. I want to transform this dataset so as to place ABC in a single row, and track their position over time. . Attached is an image that displays the solution I am looking for

I'd appreciate any help here!

Best, Rohit

Rohit Sinha
  • 3
  • 1
  • 5
  • Look up ?spread or ?reshape . The terminology to seek is “long to wide” or in database-speak: denormalize – IRTFM Jan 20 '20 at 10:57
  • I think you need to first demonstrate that you can make a valid R object with that text file. There are several other steps on the way to a reshaping operation. – IRTFM Jan 20 '20 at 11:05
  • Possible duplicate/Relevant : https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format – Ronak Shah Jan 20 '20 at 11:50

1 Answers1

0
library(dplyr)
library(tidyr)
df_txt <- c("name, position, start_date, end_date
ABC, Contractor, 09/02/2017, 07/01/2018
ABC, Associate Consultant, 08/01/2018, 31/12/2018
ABC, Consultant, 01/01/2019, 31/05/2019")

read_csv(df_txt) %>% 
  mutate_at(dplyr::vars(ends_with("_date")), .funs = dmy) %>% 
  rowwise() %>% 
  mutate(interval = list(seq(start_date, end_date, by ="month"))) %>% 
  select(name, position, interval) %>% 
  unnest(interval) %>% 
  mutate(interval = format(interval, "%Y-%m")) %>% 
  pivot_wider(id_cols = name, names_from = interval, values_from = position) %>% 
  view()
Yuriy Saraykin
  • 8,390
  • 1
  • 7
  • 14