rdataframe

One entity in more than one category, count half in each category


I have stumbled onto a problem I haven't been able to solve. I have a person in two different categories, and I need to count them as 0.5 in each category.

Here is the sample data:

enter image description here

Notice that ID Number 18 has a slash indicating that the two departments. I need the output to read as follows.

enter image description here

My first thought was to split the DEPT column, but I am not sure where to go from there since the two rows are identical. My code is as follows. Any suggestions?

Grad_Applied_Formatted1 <-  Grad_AppAccMat %>%
separate_wider_delim(DEPT,"/", names= c("DEPT1","DEPT2"), 
too_few = "align_start",cols_remove = F)

Solution

  • You can use tidyr::separate_longer_delim() and then remove duplicated rows.

    df <- data.frame(
      ID = c(17, 18, 18),
      DEPT = c("VZX", "EPI/ENH", "EPI/ENH"),
      COUNT = c(1, 0.5, 0.5)
    )
    
    df %>%
      tidyr::separate_longer_delim(DEPT, delim = '/') %>%
      dplyr::distinct()
    
    #   ID DEPT COUNT
    # 1 17  VZX   1.0
    # 2 18  EPI   0.5
    # 3 18  ENH   0.5