How to merge rows based on conditions with characters values? (Household data)

user_15399579 0
Viewed 82

I have a data frame in which the first column indicates the work (manager, employee or worker), the second indicates whether the person works at night or not and the last is a household code (if two individuals share the same code then it means that they share the same house).

#Here is the reproductible data : 
     PCS <- c("worker", "manager","employee","employee","worker","worker","manager","employee","manager","employee")
     work_night <- c("Yes","Yes","No", "No","No","Yes","No","Yes","No","Yes")
    HHnum <- c(1,1,2,2,3,3,4,4,5,5)
     df <- data.frame(PCS,work_night,HHnum)

My problem is that I would like to have a new data frame with households instead of individuals. I would like to group individuals based on HHnum and then merge their answers.

  • For the variable "PCS" I have new categories based on the combination of answers : Manager+work ="I" ; manager+employee="II", employee+employee=VI, worker+worker=III etc

  • For the variable "work_night", I would like to apply a score (is both answered Yes then score=2, if one answered YES then score =1 and if both answered No then score = 0).

To be clear, I would like my data frame to look like this :

HHnum      PCS      work_night
1          "I"           2
2          "VI"          0
3          "III"         1
4          "II"          1
5          "II"          1

How can I do this on R using dplyr ? I know that I need group_by() but then I don't know what to use.

Best, Victor

dplyr dataframe r merge
1 Answers

Here is one way to do it (though I admit it is pretty verbose). I created a reference dataframe (i.e., combos) in case you had more categories than 3, which is then joined with the main dataframe (i.e., df_new) to bring in the PCS roman numerals.

library(dplyr)
library(tidyr)

# Create a dataframe with all of the combinations of PCS.
combos <- expand.grid(unique(df$PCS), unique(df$PCS))
combos <- unique(t(apply(combos, 1, sort))) %>% 
  as.data.frame() %>% 
  dplyr::mutate(PCS = as.roman(row_number()))
# Create another dataframe with the columns reversed (will make it easier to join to the main dataframe).
combos2 <- data.frame(V1 = c(combos$V2), V2 = c(combos$V1), PCS = c(combos$PCS)) %>% 
  dplyr::mutate(PCS = as.roman(PCS))
combos <- rbind(combos, combos2)

# Get the count of "Yes" for each HHnum group. 
# Then, put the PCS into 2 columns to join together with "combos" df.
df_new <- df %>% 
  dplyr::group_by(HHnum) %>% 
  dplyr::mutate(work_night = sum(work_night == "Yes")) %>%
  dplyr::group_by(grp = rep(1:2, length.out = n())) %>%
  dplyr::ungroup() %>%
  tidyr::pivot_wider(names_from = grp, values_from = PCS) %>%
  dplyr::rename("V1" = 3, "V2" = 4) %>% 
  dplyr::left_join(combos, by = c("V1", "V2")) %>% 
  unique() %>% 
  dplyr::select(HHnum, PCS, work_night)
user_152931910
Related