Additional column based on paired data (mutation)

Question

I have a dataset with paired data (members of the same household).

Id is an individual identifier, and homeid is a partner identifier (and vice versa).

I need to add an additional column (lesson) for each id of my partner.

My data is as follows

dta = rbind( c(1013661,101366, 'Never worked'), c(1013662, 101366, 'Intermediate occs'), c(1037552, 103755, 'Managerial & professional occs'), c(1037551, 103755, 'Intermediate occs') ) colnames(dta) = c('idno', 'householdid', 'occup') dta idno householdid occup "1013661" "101366" "Never worked" "1013662" "101366" "Intermediate occs" "1037552" "103755" "Managerial & professional occs" "1037551" "103755" "Intermediate occs" 

What I need should look like this:

  idno householdid occup occupPartner "1013661" "101366" "Never worked" "Intermediate occs" "1013662" "101366" "Intermediate occs" "Never worked" "1037552" "103755" "Managerial & professional occs" "Intermediate occs" "1037551" "103755" "Intermediate occs" "Managerial & professional occs" 

I think there is a solution with a mutation, but I'm not sure what group_by is.

Any ideas?

Answer 1

Try

 library(dplyr) dta1 <- as.data.frame(dta) %>% group_by(householdid) %>% mutate(occupPartner= rev(occup)) as.data.frame(dta1) # idno householdid occup #1 1013661 101366 Never worked #2 1013662 101366 Intermediate occs #3 1037552 103755 Managerial & professional occs #4 1037551 103755 Intermediate occs # occupPartner #1 Intermediate occs #2 Never worked #3 Intermediate occs #4 Managerial & professional occs 

If the data is already ordered,

  indx <- c(rbind(seq(2, nrow(dta), by=2), seq(1, nrow(dta), by=2))) cbind(dta, occupPartner=dta[,3][indx]) 

Answer 2

Another option using data.table

 library(data.table) out = as.data.table(dta)[, occupPartner := rev(occup), by = householdid] #> out # idno householdid occup #1: 1013661 101366 Never worked #2: 1013662 101366 Intermediate occs #3: 1037552 103755 Managerial & professional occs #4: 1037551 103755 Intermediate occs # occupPartner #1: Intermediate occs #2: Never worked #3: Intermediate occs #4: Managerial & professional occs