Transforming data to consolidate multiple rows into one line based on a unique identifier-CodePudding

I'm using the UK understanding society dataset and am attempting link/ consolidate parent and child information.

Parent and child information is in a separate data file, so I've linked the child file with the parent information by using the unique identifiers of the parents and a "mother/father identifier" in the youth file. In the new data frame with both parent and child information, there are repeat

Personal_ID <- c(101,102) 
Youth_Personal_ID <- c(200,200)
Youth_reading <- c("once a week", "once a week")
Parent_education <- c("bachelors","HS diploma" )

example <- data.frame(Youth_Personal_ID,Personal_ID,Parent_education,Youth_reading)

  Youth_Personal_ID Personal_ID Parent_education Youth_reading
1               200         101        bachelors   once a week
2               200         102       HS diploma   once a week

Is there a way to get this restructured like so by using the parent identifiers?:

Youth_Personal_ID Youth_reading Mother_education Father_education
1               200   once a week        bachelors       HS diploma

CodePudding user response：

If there will always be 2 id's for each child and the order for personal id is always 'Mother' and 'Father` you can do -

library(dplyr)
library(tidyr)

example %>%
  group_by(Youth_Personal_ID) %>%
  mutate(Personal_ID = c('Mother_education', 'Father_education')) %>%
  pivot_wider(names_from = Personal_ID, values_from = Parent_education)

# Youth_Personal_ID Youth_reading Mother_education Father_education
#              <dbl> <chr>         <chr>            <chr>           
#1               200 once a week   bachelors        HS diploma

CodePudding user response：

Since you only have up to 2 parent IDs, then this should work,

library(dplyr)

example %>% 
 group_by(Youth_Personal_ID) %>% 
 mutate(father_ed = last(Parent_education)) %>% 
 slice(1L)

CodePudding user response：

We may also do

library(dplyr)
library(tidyr)
library(stringr)
example %>% 
   mutate(Personal_ID = rep(str_c(c('Mother_', 'Father_'), 'education'), 
         length.out = n())) %>% 
   pivot_wider(names_from = Personal_ID, values_from = Parent_education)
# A tibble: 1 × 4
  Youth_Personal_ID Youth_reading Mother_education Father_education
              <dbl> <chr>         <chr>            <chr>           
1               200 once a week   bachelors        HS diploma