Collapsing near-duplicates and adding difference as new column in R?

First you need to create a new field in your data frame which will contain the future column name (Job_history1, Job_history2, etc). We assign a sequential number (1, 2, ...) to each record, grouping by employee_ID, so the sequential starts from 1 for each employee.

library(dplyr)

people.data <-
  people.data %>%
  group_by(employee_ID) %>%
  mutate(seq = row_number()) %>%
  mutate(column_name = paste0("Job_history", seq)) %>%
  ungroup() %>%
  select(-c(seq))

print(people.data)

Output:

# A tibble: 8 x 5
  employee_ID name  Job_role Job_history  column_name 
  <fct>       <fct> <fct>    <fct>        <chr>       
1 1           Adam  Manager  Manager      Job_history1
2 1           Adam  Manager  Web designer Job_history2
3 2           Ben   CSO      CSO          Job_history1
4 2           Ben   CSO      Graduate     Job_history2
5 2           Ben   CSO      Intern       Job_history3
6 3           Chris Manager  0            Job_history1
7 4           Dan   CTO      CTO          Job_history1
8 4           Dan   CTO      Manager      Job_history2

Now all we need to do is to apply a pivot operation to transpose the values of desired fields onto new columns.

library(tidyr)

people.data <-
  people.data %>%
  pivot_wider(names_from = column_name, values_from = Job_history)

print(people.data)

Output:

# A tibble: 4 x 6
  employee_ID name  Job_role Job_history1 Job_history2 Job_history3
  <fct>       <fct> <fct>    <fct>        <fct>        <fct>       
1 1           Adam  Manager  Manager      Web designer NA          
2 2           Ben   CSO      CSO          Graduate     Intern      
3 3           Chris Manager  0            NA           NA          
4 4           Dan   CTO      CTO          Manager      NA       

An interesting improvement occurs if you think an employee can have more 10 or more history entries. Because in that case, columns will be placed in alphabetical order: Job_history1, Job_history10, Job_history2, etc which is not what we want. So we need to use two digits instead to format the column names: Job_history01, Job_history02, etc. On an extreme case, we may even have more than 99 entries, so 3 digits would be necessary, and so on. We want to keep the number of digits to a minimum and yet make the solution scalable. What we must do is fetch the highest sequential number assigned and determine how many digits it will need.

# Scalable solution
people.data <- data.frame(employee_ID, name, Job_role, Job_history)

people.data <-
  people.data %>%
  group_by(employee_ID) %>%
  mutate(seq = row_number()) %>%
  ungroup()

seq_digits <- nchar(as.character(max(people.data$seq)))

people.data <-
  people.data %>%
  mutate(column_name = sprintf("Job_history%0*d", seq_digits, seq)) %>%
  select(-c(seq))

people.data <-
  people.data %>%
  pivot_wider(names_from = column_name, values_from = Job_history)

To get the number of columns you need to add its a good idea to automatically query the number of rows by name to find out how deep the job history is:

employee_ID <- c('1','1','2','2','2','3','4','4')
name <- c('Adam','Adam','Ben','Ben','Ben','Chris','Dan','Dan')
Job_role <- c('Manager','Manager', 'CSO', 'CSO', 'CSO','Manager', 'CTO', 'CTO')
Job_history <- c('Manager', 'Web designer', 'CSO', 'Graduate', 'Intern', '0', 'CTO', 'Manager')

people.data <- data.frame(employee_ID, name, Job_role, Job_history)

#install required packages  
install.packages("tidyquery")
library(tidyquery)
install.packages("dplyr")
library(dplyr)

#get maximum entries per name to automatically get the columns need to be attached
maxrowcount <- query(people.data, "Select count(*) as 'Number', name GROUP BY name")
maxrowcount <- max(maxrowcount$Number, na.rm = TRUE)
maxrowcount

Another way is using dplyr within tidyverse:

people.data %>% 
pivot_longer(cols = starts_with("Job"), names_to = "Job", names_prefix = "job", values_to = "role") %>% 
group_by(employee_ID, name) %>% 
mutate(rn = cumsum(role != lag(role, default = first(role))), roles = if_else(rn == 0, "role", paste0("history", rn))) %>% 
ungroup() %>% 
select(-Job) %>% 
distinct() %>% 
pivot_wider(id_cols = c("employee_ID", "name"), names_from = roles, names_prefix = "job_", values_from = role)

leading to:

# A tibble: 4 x 6
  employee_ID name  job_role job_history1 job_history2 job_history3
  <fct>       <fct> <fct>    <fct>        <fct>        <fct>       
1 1           Adam  Manager  Web designer NA           NA          
2 2           Ben   CSO      Graduate     CSO          Intern      
3 3           Chris Manager  0            NA           NA          
4 4           Dan   CTO      Manager      NA           NA