Separate() function! Splitting a string column on a common value

Samantha asked me a question in class about being able to just search on 31 instead of like employee_31. That was a great question.Below I review a little and then give the way to do this. Feel free to copy and paste this. It is complete and runs:

Loading the employee table:

library(tidyverse)
employees<- read_csv('https://raw.githubusercontent.com/amanda-nathan/phone_log_employee_fake/main/employee_data.csv')

• Tonight we noticed, after performing our left join* that employee_31 was not in the employees table even though they were in the phone_log table. Below is how we confirmed that employee_31 was not in the employees table:

# Search for employee_31 by just using 31 using dplyr filter()
employees%>%
  filter(employee_phone_id== 'employee_31') #no rows returned

Samantha’s question: follow-up

• I was asked tonight about searching for an employee just using their number which would be convenient. We can do that (yes) for this dataset because every element of the employee_phone_id column is of the same format: employee_# where # is an integer. Below you can see what I mean:

head(employees,2)

NEW! Answer here: Below I do that work by making two new columns by splitting the values of employee_phone_id on the underscore _:

employees_changed <- employees %>%
  separate(employee_phone_id, c("prefix", "id"), sep = "_", remove= FALSE) #see what remove=TRUE does...I wanted to keep the original employee_phone_id too.

head(employees_changed,2)

• So below, after doing that above awesome column creating to split the employee_id column, if we wanted to see if the employee_31 was really in the employees table even though they were in the phone_log table we saw then you can do this too now that we have this awesome split:

# Search for employee_31 by just using 31 using dplyr filter()
result <- employees_changed %>%
  filter(id == 31)
result  #no rows, as expected

• And say you wanted to look at all the employee who are employee_25 then you can do this:

#say you wanted to search for employee_25 one for which there were multiple people:
result <- employees_changed%>%
  filter(id == 25)
result

*Note: If you spaced on Left joins here’s a quick summary:

We saw that employee_31 was in phone_log but not in employeeswhen we left joined phone_log to employees on the common column, employee_phone_id:

phone_log<-read_csv('https://raw.githubusercontent.com/amanda-nathan/phone_log_employee_fake/main/phone_log_data.csv')

## Rows: 74015 Columns: 11
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (4): employee_phone_id, direction, call_to_name, call_weekday
## dbl  (4): call_log_id, duration_seconds, call_to_number, call_hour
## lgl  (1): answered
## dttm (1): start_time
## date (1): call_date
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

merged_data <- left_join(phone_log, employees, by = "employee_phone_id")
merged_data%>%select(employee_phone_id, employee_name)

To get the employee_phone_ids that are in the phone_log table but not in the employees table we can simply search on NA in the merged_data frame:

merged_data%>%filter(is.na(employee_name))%>%distinct(employee_phone_id)