Replacing missing dates with dates from other rows based on a value in another column

General
1

Hi everyone! First time writing

I have a dataset with dates of birth. Sometimes the dates of birth are missing. If MG_FID is the same, this means that these individuals are siblings and father_dob and mother_dob are the same. So for those IIDs, for who MG_FID is the same, I want to replace the NAs in mother_dob and father_dob.

  • If MG_FID=0, then this means that they do not have any siblings and dob will have to remain NA.
mg_fid<-structure(list(IID = c(1101480L, 1101481L, 1101509L, 1101554L, 
1101600L, 1101619L, 1101623L, 1101625L, 1101637L, 1101639L, 1101644L, 
1101647L, 1101650L, 1101652L, 1101654L, 1101655L, 1101656L, 1101659L, 
1101660L, 1101661L, 1101662L, 1101674L, 1101675L, 1101678L, 1101679L, 
1101681L, 1101685L, 1101692L, 1101693L, 1101694L, 1101696L, 1101701L, 
1101705L, 1101709L, 1101712L, 1101715L, 1101717L, 1101718L, 1101720L, 
1131480L, 1131481L, 1131554L, 1131600L, 1131619L, 1131623L, 1131625L, 
1131639L, 1131644L, 1131652L, 1131659L, 1131660L, 1131661L, 1131678L, 
1131679L, 1131685L, 1131692L, 1131696L, 1131701L, 1131705L, 1131709L, 
1131715L, 1131717L, 1131718L, 1193598L, 1193602L, 1193607L, 1193609L, 
1193612L, 1193617L, 1193618L, 1193620L, 1193621L, 1193622L, 1193634L, 
1193638L, 1193639L, 1193640L, 1193642L, 1193653L, 1193656L, 1193659L, 
1193660L, 1193661L, 1193664L, 1193667L, 1193668L, 1193689L, 2202638L, 
2202647L, 2202652L, 2202655L, 2202660L, 2202661L, 2202665L, 2202673L, 
2202686L, 2202693L, 2232655L, 2232661L, 2232665L, 2232686L, 2296211L, 
2296214L, 2296217L, 3305120L, 3305129L, 3305135L, 3305139L, 3305141L, 
3305144L, 3305151L, 3305153L, 3305154L, 3305161L, 3305164L, 3305168L, 
3335154L, 3345153L, 3392379L, 3392380L, 3392385L, 3392389L, 3392390L, 
3392391L, 3392392L, 3392395L, 3392400L, 4406324L, 4406326L, 4406327L, 
4406339L, 4406341L, 4406343L, 4406349L, 4406352L, 4406356L, 4406366L, 
4406370L, 4406382L, 4406391L, 4406395L, 4406396L, 4406398L, 4406403L, 
4406409L, 4406416L, 4406421L, 4406422L, 4406425L, 4406436L, 4406438L, 
4406460L, 4406467L, 4406469L, 4406480L, 4406483L, 4406484L, 4406485L, 
4436326L, 4436366L, 4436382L, 4436396L, 4436416L, 4436422L, 4436425L, 
4436436L, 4436467L, 4436469L, 4436480L, 4436483L, 4494908L, 4494910L, 
4494916L, 4494919L), child_dob = structure(c(15454, 15405, 15112, 
14371, 14789, 15154, 12628, 15548, 12871, 16199, 14206, 13984, 
13718, 14602, 15889, 16216, 13309, 15490, 13870, 12242, 12740, 
14956, 11581, 14433, 16346, 14208, 16288, 12957, 15932, 13314, 
11487, 11768, 14650, 16247, 12741, 15075, 14823, 14160, 13284, 
14798, 16300, 14803, 14049, NA, 13496, 16183, NA, 14206, 11675, 
11226, 14630, 13035, 13767, 15667, 15201, NA, 12014, 12646, 15524, 
16247, 16884, 15722, 14792, 16310, 16959, 16173, 16765, 15535, 
NA, 16659, 14909, 16004, 15088, 15293, 15844, 14653, 16304, 15694, 
15958, 14993, 15274, 15332, 16363, 14846, 16304, 16597, 15226, 
11404, 15126, 12299, 14852, 15606, 15884, 16515, 15475, 15982, 
13819, 15996, 16512, 14823, 16631, 14442, 16672, 14120, 16499, 
15269, 13863, 16755, 15690, 15380, 13232, 13482, 13997, 16924, 
13431, 14692, 14789, 12156, 15839, 16585, 15884, 13266, 16027, 
13214, 14770, 13228, 15578, 13892, 15133, 12759, 13151, 14447, 
14922, 14841, 14572, 15804, 15260, 14869, 11111, 15579, 13473, 
15099, 15588, 11801, 13915, 14692, 12526, 14145, 13782, 15131, 
14759, 13354, 12747, 13389, 13530, 15554, 15211, 11365, NA, NA, 
12577, 15479, NA, 14759, NA, 16528, NA, 11559, NA, NA, NA, NA, 
NA, NA), class = "Date"), mother_dob = structure(c(3751, 4152, 
2012, 4033, 3220, 3547, 226, 4627, 936, 3971, 488, -751, NA, 
-152, 7709, 6247, 628, 1515, 2679, 1220, 982, 3470, -480, 1034, 
7168, 1783, 3356, 2352, 4166, 892, 1601, 1002, 3436, 3581, 2842, 
NA, 7439, 1380, -476, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 7939, 
7973, NA, 7396, 8002, NA, 8499, 7879, 7512, 7512, 7495, 7919, 
7503, 7412, 8069, 7823, 7321, 7399, 7686, 7682, 7504, 7738, 9053, 
7579, 1088, 3346, 2276, 1738, 3566, 5392, 1106, 214, 2348, 1863, 
NA, NA, NA, NA, 999, NA, 4484, 8885, 1958, 3197, 5721, 2815, 
5304, 5451, 2145, 5354, 4758, 2517, 1557, 5354, 2145, 6377, 6011, 
4084, -406, 332, 390, -716, 2218, 3982, 1251, 2842, 3158, 2008, 
5332, 5872, 1643, 7342, 3350, 5199, 1065, -2651, 6334, NA, 6508, 
2554, -835, 1502, 132, 1409, 4033, 3289, 1888, 2864, 1766, 629, 
739, 3158, 1915, 778, 3781, NA, 5199, NA, 6508, 132, 4033, 3289, 
1888, NA, NA, 3158, NA, 7075, NA, 2985, 431), class = "Date"), 
    father_dob = structure(c(3048, 4252, -485, 4363, 536, 565, 
    56, 1953, -534, 3535, -4109, -2086, NA, -321, 4858, 4287, 
    2169, -895, 2459, 873, 35, 3127, -1333, -73, 3866, 1573, 
    3192, 1424, -3378, -182, -139, 443, 3353, 5299, 97, NA, 5978, 
    1369, -3093, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 7606, 
    NA, 6055, 7062, NA, 7893, 8325, 7297, 7297, 6510, 7849, 6883, 
    7503, 7536, 7356, 6338, NA, 7600, 7908, 7339, 5150, 7532, 
    7128, 511, 454, 2154, 2348, 2703, 4071, 1491, -1679, 3894, 
    -1475, NA, NA, NA, NA, -179, NA, 2941, 6677, 1067, -1206, 
    4403, 981, 3626, 437, 829, 2087, 2980, 2364, 653, 2087, 829, 
    6281, 6839, 4344, -3220, 3175, 335, 410, 1961, 2730, -951, 
    2670, 2938, -3676, 1977, 5758, 1154, 7582, 3564, 3297, 4148, 
    -2770, 5088, NA, 7605, 4096, -1145, 948, 1022, 560, 3016, 
    -1902, 2022, -703, 40, 446, 91, 1807, 1433, 4119, NA, NA, 
    3297, NA, 7605, 1022, 3016, -1902, 2022, NA, NA, 1807, NA, 
    5903, NA, 2521, 849), class = "Date"), MG_FID = c(1L, 2L, 
    0L, 3L, 4L, 5L, 7L, 8L, 0L, 9L, 10L, 0L, 0L, 11L, 0L, 0L, 
    0L, 12L, 13L, 14L, 0L, 0L, 0L, 15L, 16L, 17L, 18L, 19L, 20L, 
    0L, 21L, 22L, 23L, 24L, 0L, 25L, 26L, 27L, 0L, 1L, 2L, 3L, 
    4L, 5L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 15L, 16L, 18L, 
    19L, 21L, 22L, 23L, 24L, 25L, 26L, 27L, 0L, 0L, 0L, 0L, 0L, 
    0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
    0L, 0L, 0L, 0L, 0L, 0L, 0L, 28L, 0L, 29L, 30L, 0L, 31L, 0L, 
    28L, 29L, 30L, 31L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 32L, 
    35L, 33L, 0L, 0L, 34L, 33L, 35L, 0L, 0L, 0L, 0L, 0L, 0L, 
    0L, 0L, 0L, 36L, 37L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 38L, 0L, 
    39L, 0L, 0L, 40L, 41L, 0L, 0L, 42L, 0L, 43L, 44L, 45L, 0L, 
    46L, 47L, 48L, 49L, 50L, 0L, 51L, 37L, 38L, 39L, 40L, 42L, 
    43L, 44L, 45L, 47L, 48L, 49L, 50L, 0L, 0L, 0L, 0L)), class = "data.frame", row.names = c(NA, 
-174L))

mg_fid

I tried the code below, but it turns all my dates into random numbers.

parent_age %>% group_by(MG_FID) %>% 
mutate(father_dob = ifelse(is.na(father_dob),max(father_dob,na.rm=TRUE),father_dob), 
mother_dob = ifelse(is.na(mother_dob),max(mother_dob,na.rm=TRUE),mother_dob))

Does anyone have any ideas?
Let me know if what I wrote doesn't make sense.

2

Here is one method. ParentInfo contains the rows where MG_FID is not zero and the parent dobs are not NA. I then join that to the original data, matching on MG_FID so every row in the original data has the ParentInfo with the same MG_FID, if it exists, appended to its right. The coalesce then keeps the first non-NA value, checking the original data first and then the appended ParentInfo. The select() function keeps the desired columns and renames where necessary.

ParentInfo <- mg_fid |> filter(MG_FID != 0, 
                               !is.na(mother_dob), 
                               !is.na(father_dob)) |> 
  select(mother_dob, father_dob, MG_FID) |> 
  distinct()

mg_fid2 <- left_join(mg_fid, ParentInfo, by = "MG_FID") |> 
  mutate(mother_dob.x = coalesce(mother_dob.x, mother_dob.y),
         father_dob.x = coalesce(father_dob.x, father_dob.y)) |> 
  select(IID,child_dob, mother_dob=mother_dob.x,
         father_dob=father_dob.x, MG_FID)
3

I think this should also work

library(tidyverse)

# Sample data on a copy/paste friendly format, replace with your own data frame
mg_fid <- data.frame(
         IID = c(1101480L,1101481L,1101509L,1101554L,
                 1101600L,1101619L,1101623L,1101625L,1101637L,1101639L,
                 1101644L,1101647L,1101650L,1101652L,1101654L,1101655L,1101656L,
                 1101659L,1101660L,1101661L,1101662L,1101674L,1101675L,
                 1101678L,1101679L,1101681L,1101685L,1101692L,1101693L,
                 1101694L,1101696L,1101701L,1101705L,1101709L,1101712L,1101715L,
                 1101717L,1101718L,1101720L,1131480L,1131481L,1131554L,
                 1131600L,1131619L,1131623L,1131625L,1131639L,1131644L,
                 1131652L,1131659L,1131660L,1131661L,1131678L,1131679L,
                 1131685L,1131692L,1131696L,1131701L,1131705L,1131709L,1131715L,
                 1131717L,1131718L,1193598L,1193602L,1193607L,1193609L,
                 1193612L,1193617L,1193618L,1193620L,1193621L,1193622L,
                 1193634L,1193638L,1193639L,1193640L,1193642L,1193653L,1193656L,
                 1193659L,1193660L,1193661L,1193664L,1193667L,1193668L,
                 1193689L,2202638L,2202647L,2202652L,2202655L,2202660L,
                 2202661L,2202665L,2202673L,2202686L,2202693L,2232655L,2232661L,
                 2232665L,2232686L,2296211L,2296214L,2296217L,3305120L,
                 3305129L,3305135L,3305139L,3305141L,3305144L,3305151L,
                 3305153L,3305154L,3305161L,3305164L,3305168L,3335154L,
                 3345153L,3392379L,3392380L,3392385L,3392389L,3392390L,3392391L,
                 3392392L,3392395L,3392400L,4406324L,4406326L,4406327L,
                 4406339L,4406341L,4406343L,4406349L,4406352L,4406356L,
                 4406366L,4406370L,4406382L,4406391L,4406395L,4406396L,4406398L,
                 4406403L,4406409L,4406416L,4406421L,4406422L,4406425L,
                 4406436L,4406438L,4406460L,4406467L,4406469L,4406480L,
                 4406483L,4406484L,4406485L,4436326L,4436366L,4436382L,4436396L,
                 4436416L,4436422L,4436425L,4436436L,4436467L,4436469L,
                 4436480L,4436483L,4494908L,4494910L,4494916L,4494919L),
   child_dob = c("2012-04-24","2012-03-06","2011-05-18",
                 "2009-05-07","2010-06-29","2011-06-29","2004-07-29",
                 "2012-07-27","2005-03-29","2014-05-09","2008-11-23","2008-04-15",
                 "2007-07-24","2009-12-24","2013-07-03","2014-05-26",
                 "2006-06-10","2012-05-30","2007-12-23","2003-07-09","2004-11-18",
                 "2010-12-13","2001-09-16","2009-07-08","2014-10-03",
                 "2008-11-25","2014-08-06","2005-06-23","2013-08-15","2006-06-15",
                 "2001-06-14","2002-03-22","2010-02-10","2014-06-26",
                 "2004-11-19","2011-04-11","2010-08-02","2008-10-08","2006-05-16",
                 "2010-07-08","2014-08-18","2010-07-13","2008-06-19",NA,
                 "2006-12-14","2014-04-23",NA,"2008-11-23","2001-12-19",
                 "2000-09-26","2010-01-21","2005-09-09","2007-09-11","2012-11-23",
                 "2011-08-15",NA,"2002-11-23","2004-08-16","2012-07-03",
                 "2014-06-26","2016-03-24","2013-01-17","2010-07-02",
                 "2014-08-28","2016-06-07","2014-04-13","2015-11-26","2012-07-14",NA,
                 "2015-08-12","2010-10-27","2013-10-26","2011-04-24",
                 "2011-11-15","2013-05-19","2010-02-13","2014-08-22","2012-12-20",
                 "2013-09-10","2011-01-19","2011-10-27","2011-12-24",
                 "2014-10-20","2010-08-25","2014-08-22","2015-06-11","2011-09-09",
                 "2001-03-23","2011-06-01","2003-09-04","2010-08-31",
                 "2012-09-23","2013-06-28","2015-03-21","2012-05-15","2013-10-04",
                 "2007-11-02","2013-10-18","2015-03-18","2010-08-02",
                 "2015-07-15","2009-07-17","2015-08-25","2008-08-29","2015-03-05",
                 "2011-10-22","2007-12-16","2015-11-16","2012-12-16",
                 "2012-02-10","2006-03-25","2006-11-30","2008-04-28","2016-05-03",
                 "2006-10-10","2010-03-24","2010-06-29","2003-04-14",
                 "2013-05-14","2015-05-30","2013-06-28","2006-04-28","2013-11-18",
                 "2006-03-07","2010-06-10","2006-03-21","2012-08-26",
                 "2008-01-14","2011-06-08","2004-12-07","2006-01-03","2009-07-22",
                 "2010-11-09","2010-08-20","2009-11-24","2013-04-09",
                 "2011-10-13","2010-09-17","2000-06-03","2012-08-27","2006-11-21",
                 "2011-05-05","2012-09-05","2002-04-24","2008-02-06",
                 "2010-03-24","2004-04-18","2008-09-23","2007-09-26","2011-06-06",
                 "2010-05-30","2006-07-25","2004-11-25","2006-08-29",
                 "2007-01-17","2012-08-02","2011-08-25","2001-02-12",NA,NA,
                 "2004-06-08","2012-05-19",NA,"2010-05-30",NA,"2015-04-03",NA,
                 "2001-08-25",NA,NA,NA,NA,NA,NA),
  mother_dob = c("1980-04-09","1981-05-15","1975-07-06",
                 "1981-01-16","1978-10-26","1979-09-18","1970-08-15",
                 "1982-09-02","1972-07-25","1980-11-15","1971-05-04","1967-12-12",
                 NA,"1969-08-02","1991-02-09","1987-02-08","1971-09-21",
                 "1974-02-24","1977-05-03","1973-05-05","1972-09-09",
                 "1979-07-03","1968-09-08","1972-10-31","1989-08-17","1974-11-19",
                 "1979-03-11","1976-06-10","1981-05-29","1972-06-11",
                 "1974-05-21","1972-09-29","1979-05-30","1979-10-22","1977-10-13",NA,
                 "1990-05-15","1973-10-12","1968-09-12",NA,NA,NA,NA,NA,
                 NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,
                 NA,NA,NA,"1991-09-27","1991-10-31",NA,"1990-04-02",
                 "1991-11-29",NA,"1993-04-09","1991-07-29","1990-07-27",
                 "1990-07-27","1990-07-10","1991-09-07","1990-07-18","1990-04-18",
                 "1992-02-04","1991-06-03","1990-01-17","1990-04-05",
                 "1991-01-17","1991-01-13","1990-07-19","1991-03-10","1994-10-15",
                 "1990-10-02","1972-12-24","1979-03-01","1976-03-26",
                 "1974-10-05","1979-10-07","1984-10-06","1973-01-11","1970-08-03",
                 "1976-06-06","1975-02-07",NA,NA,NA,NA,"1972-09-26",NA,
                 "1982-04-12","1994-04-30","1975-05-13","1978-10-03",
                 "1985-08-31","1977-09-16","1984-07-10","1984-12-04","1975-11-16",
                 "1984-08-29","1983-01-11","1976-11-22","1974-04-07",
                 "1984-08-29","1975-11-16","1987-06-18","1986-06-17","1981-03-08",
                 "1968-11-21","1970-11-29","1971-01-26","1968-01-16",
                 "1976-01-28","1980-11-26","1973-06-05","1977-10-13","1978-08-25",
                 "1975-07-02","1984-08-07","1986-01-29","1974-07-02",
                 "1990-02-07","1979-03-05","1984-03-27","1972-12-01","1962-09-29",
                 "1987-05-06",NA,"1987-10-27","1976-12-29","1967-09-19",
                 "1974-02-11","1970-05-13","1973-11-10","1981-01-16","1979-01-03",
                 "1975-03-04","1977-11-04","1974-11-02","1971-09-22",
                 "1972-01-10","1978-08-25","1975-03-31","1972-02-18","1980-05-09",
                 NA,"1984-03-27",NA,"1987-10-27","1970-05-13",
                 "1981-01-16","1979-01-03","1975-03-04",NA,NA,"1978-08-25",NA,
                 "1989-05-16",NA,"1978-03-05","1971-03-08"),
  father_dob = c("1978-05-07","1981-08-23","1968-09-03",
                 "1981-12-12","1971-06-21","1971-07-20","1970-02-26",
                 "1975-05-08","1968-07-16","1979-09-06","1958-10-02","1964-04-16",
                 NA,"1969-02-14","1983-04-21","1981-09-27","1975-12-10",
                 "1967-07-21","1976-09-25","1972-05-23","1970-02-05",
                 "1978-07-25","1966-05-09","1969-10-20","1980-08-02","1974-04-23",
                 "1978-09-28","1973-11-25","1960-10-02","1969-07-03",
                 "1969-08-15","1971-03-20","1979-03-08","1984-07-05","1970-04-08",NA,
                 "1986-05-15","1973-10-01","1961-07-14",NA,NA,NA,NA,NA,
                 NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,
                 NA,NA,NA,NA,"1990-10-29",NA,"1986-07-31","1989-05-03",
                 NA,"1991-08-12","1992-10-17","1989-12-24","1989-12-24",
                 "1987-10-29","1991-06-29","1988-11-05","1990-07-18",
                 "1990-08-20","1990-02-21","1987-05-10",NA,"1990-10-23","1991-08-27",
                 "1990-02-04","1984-02-07","1990-08-16","1989-07-08",
                 "1971-05-27","1971-03-31","1975-11-25","1976-06-06","1977-05-27",
                 "1981-02-23","1974-01-31","1965-05-28","1980-08-30",
                 "1965-12-18",NA,NA,NA,NA,"1969-07-06",NA,"1978-01-20",
                 "1988-04-13","1972-12-03","1966-09-13","1982-01-21","1972-09-08",
                 "1979-12-06","1971-03-14","1972-04-09","1975-09-19",
                 "1978-02-28","1976-06-22","1971-10-16","1975-09-19","1972-04-09",
                 "1987-03-14","1988-09-22","1981-11-23","1961-03-09",
                 "1978-09-11","1970-12-02","1971-02-15","1975-05-16","1977-06-23",
                 "1967-05-26","1977-04-24","1978-01-17","1959-12-09",
                 "1975-06-01","1985-10-07","1973-02-28","1990-10-05","1979-10-05",
                 "1979-01-11","1981-05-11","1962-06-02","1983-12-07",NA,
                 "1990-10-28","1981-03-20","1966-11-13","1972-08-06",
                 "1972-10-19","1971-07-15","1978-04-05","1964-10-17","1975-07-16",
                 "1968-01-29","1970-02-10","1971-03-23","1970-04-02",
                 "1974-12-13","1973-12-04","1981-04-12",NA,NA,"1979-01-11",NA,
                 "1990-10-28","1972-10-19","1978-04-05","1964-10-17","1975-07-16",
                 NA,NA,"1974-12-13",NA,"1986-03-01",NA,"1976-11-26",
                 "1972-04-29"),
      MG_FID = c(1L,2L,0L,3L,4L,5L,7L,8L,0L,9L,10L,
                 0L,0L,11L,0L,0L,0L,12L,13L,14L,0L,0L,0L,15L,16L,
                 17L,18L,19L,20L,0L,21L,22L,23L,24L,0L,25L,26L,27L,
                 0L,1L,2L,3L,4L,5L,7L,8L,9L,10L,11L,12L,13L,14L,
                 15L,16L,18L,19L,21L,22L,23L,24L,25L,26L,27L,0L,0L,
                 0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,
                 0L,0L,0L,0L,0L,0L,0L,0L,0L,28L,0L,29L,30L,0L,31L,
                 0L,28L,29L,30L,31L,0L,0L,0L,0L,0L,0L,0L,0L,0L,
                 32L,35L,33L,0L,0L,34L,33L,35L,0L,0L,0L,0L,0L,0L,0L,
                 0L,0L,36L,37L,0L,0L,0L,0L,0L,0L,0L,38L,0L,39L,0L,
                 0L,40L,41L,0L,0L,42L,0L,43L,44L,45L,0L,46L,47L,
                 48L,49L,50L,0L,51L,37L,38L,39L,40L,42L,43L,44L,45L,
                 47L,48L,49L,50L,0L,0L,0L,0L)
)

# Relevant code
mg_fid %>%
    filter(MG_FID != 0) %>% 
    arrange(MG_FID) %>% 
    group_by(MG_FID) %>% 
    fill(mother_dob, father_dob, .direction = "down") %>% 
    bind_rows(mg_fid %>% filter(MG_FID == 0))
#> # A tibble: 174 × 5
#> # Groups:   MG_FID [51]
#>        IID child_dob  mother_dob father_dob MG_FID
#>      <int> <chr>      <chr>      <chr>       <int>
#>  1 1101480 2012-04-24 1980-04-09 1978-05-07      1
#>  2 1131480 2010-07-08 1980-04-09 1978-05-07      1
#>  3 1101481 2012-03-06 1981-05-15 1981-08-23      2
#>  4 1131481 2014-08-18 1981-05-15 1981-08-23      2
#>  5 1101554 2009-05-07 1981-01-16 1981-12-12      3
#>  6 1131554 2010-07-13 1981-01-16 1981-12-12      3
#>  7 1101600 2010-06-29 1978-10-26 1971-06-21      4
#>  8 1131600 2008-06-19 1978-10-26 1971-06-21      4
#>  9 1101619 2011-06-29 1979-09-18 1971-07-20      5
#> 10 1131619 <NA>       1979-09-18 1971-07-20      5
#> # … with 164 more rows

Created on 2022-02-24 by the reprex package (v2.0.1)

4

Hi FJCC,

Thanks! Unfortunately I get an error with this code:

"Error: unexpected '>' in "ParentInfo <- mg_fid |>"

5

Hi @andresrcs and thanks for your answer! Unfortunately even though there are no errors, the dataset stays the same. :confused:

6

The reproducible example incuestionably shows that the data do change, I suspect the problem is that you haven't realized that R doesn't perform changes "in-place", it outputs a new data frame instead, if you want the changes to persist you need to explicitly assign them (with te assign operator <-) to a new variable or to the original variable if you want to overwrite its content e.g.

mg_fid <- mg_fid %>%
    filter(MG_FID != 0) %>% 
    arrange(MG_FID) %>% 
    group_by(MG_FID) %>% 
    fill(mother_dob, father_dob, .direction = "down") %>% 
    bind_rows(mg_fid %>% filter(MG_FID == 0))
7

If the |> operator is causing an error, you are probably using an older version of R that does not include that operator. Using the operator %>% instead should fix the problem. Here is a reproducible example of my code.

library(tidyverse)
#> Warning: package 'tibble' was built under R version 4.1.2
mg_fid <- data.frame(
  IID = c(1101480L,1101481L,1101509L,1101554L,
          1101600L,1101619L,1101623L,1101625L,1101637L,1101639L,
          1101644L,1101647L,1101650L,1101652L,1101654L,1101655L,1101656L,
          1101659L,1101660L,1101661L,1101662L,1101674L,1101675L,
          1101678L,1101679L,1101681L,1101685L,1101692L,1101693L,
          1101694L,1101696L,1101701L,1101705L,1101709L,1101712L,1101715L,
          1101717L,1101718L,1101720L,1131480L,1131481L,1131554L,
          1131600L,1131619L,1131623L,1131625L,1131639L,1131644L,
          1131652L,1131659L,1131660L,1131661L,1131678L,1131679L,
          1131685L,1131692L,1131696L,1131701L,1131705L,1131709L,1131715L,
          1131717L,1131718L,1193598L,1193602L,1193607L,1193609L,
          1193612L,1193617L,1193618L,1193620L,1193621L,1193622L,
          1193634L,1193638L,1193639L,1193640L,1193642L,1193653L,1193656L,
          1193659L,1193660L,1193661L,1193664L,1193667L,1193668L,
          1193689L,2202638L,2202647L,2202652L,2202655L,2202660L,
          2202661L,2202665L,2202673L,2202686L,2202693L,2232655L,2232661L,
          2232665L,2232686L,2296211L,2296214L,2296217L,3305120L,
          3305129L,3305135L,3305139L,3305141L,3305144L,3305151L,
          3305153L,3305154L,3305161L,3305164L,3305168L,3335154L,
          3345153L,3392379L,3392380L,3392385L,3392389L,3392390L,3392391L,
          3392392L,3392395L,3392400L,4406324L,4406326L,4406327L,
          4406339L,4406341L,4406343L,4406349L,4406352L,4406356L,
          4406366L,4406370L,4406382L,4406391L,4406395L,4406396L,4406398L,
          4406403L,4406409L,4406416L,4406421L,4406422L,4406425L,
          4406436L,4406438L,4406460L,4406467L,4406469L,4406480L,
          4406483L,4406484L,4406485L,4436326L,4436366L,4436382L,4436396L,
          4436416L,4436422L,4436425L,4436436L,4436467L,4436469L,
          4436480L,4436483L,4494908L,4494910L,4494916L,4494919L),
  child_dob = c("2012-04-24","2012-03-06","2011-05-18",
                "2009-05-07","2010-06-29","2011-06-29","2004-07-29",
                "2012-07-27","2005-03-29","2014-05-09","2008-11-23","2008-04-15",
                "2007-07-24","2009-12-24","2013-07-03","2014-05-26",
                "2006-06-10","2012-05-30","2007-12-23","2003-07-09","2004-11-18",
                "2010-12-13","2001-09-16","2009-07-08","2014-10-03",
                "2008-11-25","2014-08-06","2005-06-23","2013-08-15","2006-06-15",
                "2001-06-14","2002-03-22","2010-02-10","2014-06-26",
                "2004-11-19","2011-04-11","2010-08-02","2008-10-08","2006-05-16",
                "2010-07-08","2014-08-18","2010-07-13","2008-06-19",NA,
                "2006-12-14","2014-04-23",NA,"2008-11-23","2001-12-19",
                "2000-09-26","2010-01-21","2005-09-09","2007-09-11","2012-11-23",
                "2011-08-15",NA,"2002-11-23","2004-08-16","2012-07-03",
                "2014-06-26","2016-03-24","2013-01-17","2010-07-02",
                "2014-08-28","2016-06-07","2014-04-13","2015-11-26","2012-07-14",NA,
                "2015-08-12","2010-10-27","2013-10-26","2011-04-24",
                "2011-11-15","2013-05-19","2010-02-13","2014-08-22","2012-12-20",
                "2013-09-10","2011-01-19","2011-10-27","2011-12-24",
                "2014-10-20","2010-08-25","2014-08-22","2015-06-11","2011-09-09",
                "2001-03-23","2011-06-01","2003-09-04","2010-08-31",
                "2012-09-23","2013-06-28","2015-03-21","2012-05-15","2013-10-04",
                "2007-11-02","2013-10-18","2015-03-18","2010-08-02",
                "2015-07-15","2009-07-17","2015-08-25","2008-08-29","2015-03-05",
                "2011-10-22","2007-12-16","2015-11-16","2012-12-16",
                "2012-02-10","2006-03-25","2006-11-30","2008-04-28","2016-05-03",
                "2006-10-10","2010-03-24","2010-06-29","2003-04-14",
                "2013-05-14","2015-05-30","2013-06-28","2006-04-28","2013-11-18",
                "2006-03-07","2010-06-10","2006-03-21","2012-08-26",
                "2008-01-14","2011-06-08","2004-12-07","2006-01-03","2009-07-22",
                "2010-11-09","2010-08-20","2009-11-24","2013-04-09",
                "2011-10-13","2010-09-17","2000-06-03","2012-08-27","2006-11-21",
                "2011-05-05","2012-09-05","2002-04-24","2008-02-06",
                "2010-03-24","2004-04-18","2008-09-23","2007-09-26","2011-06-06",
                "2010-05-30","2006-07-25","2004-11-25","2006-08-29",
                "2007-01-17","2012-08-02","2011-08-25","2001-02-12",NA,NA,
                "2004-06-08","2012-05-19",NA,"2010-05-30",NA,"2015-04-03",NA,
                "2001-08-25",NA,NA,NA,NA,NA,NA),
  mother_dob = c("1980-04-09","1981-05-15","1975-07-06",
                 "1981-01-16","1978-10-26","1979-09-18","1970-08-15",
                 "1982-09-02","1972-07-25","1980-11-15","1971-05-04","1967-12-12",
                 NA,"1969-08-02","1991-02-09","1987-02-08","1971-09-21",
                 "1974-02-24","1977-05-03","1973-05-05","1972-09-09",
                 "1979-07-03","1968-09-08","1972-10-31","1989-08-17","1974-11-19",
                 "1979-03-11","1976-06-10","1981-05-29","1972-06-11",
                 "1974-05-21","1972-09-29","1979-05-30","1979-10-22","1977-10-13",NA,
                 "1990-05-15","1973-10-12","1968-09-12",NA,NA,NA,NA,NA,
                 NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,
                 NA,NA,NA,"1991-09-27","1991-10-31",NA,"1990-04-02",
                 "1991-11-29",NA,"1993-04-09","1991-07-29","1990-07-27",
                 "1990-07-27","1990-07-10","1991-09-07","1990-07-18","1990-04-18",
                 "1992-02-04","1991-06-03","1990-01-17","1990-04-05",
                 "1991-01-17","1991-01-13","1990-07-19","1991-03-10","1994-10-15",
                 "1990-10-02","1972-12-24","1979-03-01","1976-03-26",
                 "1974-10-05","1979-10-07","1984-10-06","1973-01-11","1970-08-03",
                 "1976-06-06","1975-02-07",NA,NA,NA,NA,"1972-09-26",NA,
                 "1982-04-12","1994-04-30","1975-05-13","1978-10-03",
                 "1985-08-31","1977-09-16","1984-07-10","1984-12-04","1975-11-16",
                 "1984-08-29","1983-01-11","1976-11-22","1974-04-07",
                 "1984-08-29","1975-11-16","1987-06-18","1986-06-17","1981-03-08",
                 "1968-11-21","1970-11-29","1971-01-26","1968-01-16",
                 "1976-01-28","1980-11-26","1973-06-05","1977-10-13","1978-08-25",
                 "1975-07-02","1984-08-07","1986-01-29","1974-07-02",
                 "1990-02-07","1979-03-05","1984-03-27","1972-12-01","1962-09-29",
                 "1987-05-06",NA,"1987-10-27","1976-12-29","1967-09-19",
                 "1974-02-11","1970-05-13","1973-11-10","1981-01-16","1979-01-03",
                 "1975-03-04","1977-11-04","1974-11-02","1971-09-22",
                 "1972-01-10","1978-08-25","1975-03-31","1972-02-18","1980-05-09",
                 NA,"1984-03-27",NA,"1987-10-27","1970-05-13",
                 "1981-01-16","1979-01-03","1975-03-04",NA,NA,"1978-08-25",NA,
                 "1989-05-16",NA,"1978-03-05","1971-03-08"),
  father_dob = c("1978-05-07","1981-08-23","1968-09-03",
                 "1981-12-12","1971-06-21","1971-07-20","1970-02-26",
                 "1975-05-08","1968-07-16","1979-09-06","1958-10-02","1964-04-16",
                 NA,"1969-02-14","1983-04-21","1981-09-27","1975-12-10",
                 "1967-07-21","1976-09-25","1972-05-23","1970-02-05",
                 "1978-07-25","1966-05-09","1969-10-20","1980-08-02","1974-04-23",
                 "1978-09-28","1973-11-25","1960-10-02","1969-07-03",
                 "1969-08-15","1971-03-20","1979-03-08","1984-07-05","1970-04-08",NA,
                 "1986-05-15","1973-10-01","1961-07-14",NA,NA,NA,NA,NA,
                 NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,
                 NA,NA,NA,NA,"1990-10-29",NA,"1986-07-31","1989-05-03",
                 NA,"1991-08-12","1992-10-17","1989-12-24","1989-12-24",
                 "1987-10-29","1991-06-29","1988-11-05","1990-07-18",
                 "1990-08-20","1990-02-21","1987-05-10",NA,"1990-10-23","1991-08-27",
                 "1990-02-04","1984-02-07","1990-08-16","1989-07-08",
                 "1971-05-27","1971-03-31","1975-11-25","1976-06-06","1977-05-27",
                 "1981-02-23","1974-01-31","1965-05-28","1980-08-30",
                 "1965-12-18",NA,NA,NA,NA,"1969-07-06",NA,"1978-01-20",
                 "1988-04-13","1972-12-03","1966-09-13","1982-01-21","1972-09-08",
                 "1979-12-06","1971-03-14","1972-04-09","1975-09-19",
                 "1978-02-28","1976-06-22","1971-10-16","1975-09-19","1972-04-09",
                 "1987-03-14","1988-09-22","1981-11-23","1961-03-09",
                 "1978-09-11","1970-12-02","1971-02-15","1975-05-16","1977-06-23",
                 "1967-05-26","1977-04-24","1978-01-17","1959-12-09",
                 "1975-06-01","1985-10-07","1973-02-28","1990-10-05","1979-10-05",
                 "1979-01-11","1981-05-11","1962-06-02","1983-12-07",NA,
                 "1990-10-28","1981-03-20","1966-11-13","1972-08-06",
                 "1972-10-19","1971-07-15","1978-04-05","1964-10-17","1975-07-16",
                 "1968-01-29","1970-02-10","1971-03-23","1970-04-02",
                 "1974-12-13","1973-12-04","1981-04-12",NA,NA,"1979-01-11",NA,
                 "1990-10-28","1972-10-19","1978-04-05","1964-10-17","1975-07-16",
                 NA,NA,"1974-12-13",NA,"1986-03-01",NA,"1976-11-26",
                 "1972-04-29"),
  MG_FID = c(1L,2L,0L,3L,4L,5L,7L,8L,0L,9L,10L,
             0L,0L,11L,0L,0L,0L,12L,13L,14L,0L,0L,0L,15L,16L,
             17L,18L,19L,20L,0L,21L,22L,23L,24L,0L,25L,26L,27L,
             0L,1L,2L,3L,4L,5L,7L,8L,9L,10L,11L,12L,13L,14L,
             15L,16L,18L,19L,21L,22L,23L,24L,25L,26L,27L,0L,0L,
             0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,
             0L,0L,0L,0L,0L,0L,0L,0L,0L,28L,0L,29L,30L,0L,31L,
             0L,28L,29L,30L,31L,0L,0L,0L,0L,0L,0L,0L,0L,0L,
             32L,35L,33L,0L,0L,34L,33L,35L,0L,0L,0L,0L,0L,0L,0L,
             0L,0L,36L,37L,0L,0L,0L,0L,0L,0L,0L,38L,0L,39L,0L,
             0L,40L,41L,0L,0L,42L,0L,43L,44L,45L,0L,46L,47L,
             48L,49L,50L,0L,51L,37L,38L,39L,40L,42L,43L,44L,45L,
             47L,48L,49L,50L,0L,0L,0L,0L)
  
)

ParentInfo <- mg_fid |> filter(MG_FID != 0, 
                               !is.na(mother_dob), 
                               !is.na(father_dob)) |> 
  select(mother_dob, father_dob, MG_FID) |> 
  distinct()

mg_fid2 <- left_join(mg_fid, ParentInfo, by = "MG_FID") |> 
  mutate(mother_dob.x = coalesce(mother_dob.x, mother_dob.y),
         father_dob.x = coalesce(father_dob.x, father_dob.y)) |> 
  select(IID,child_dob, mother_dob=mother_dob.x,
         father_dob=father_dob.x, MG_FID)

Created on 2022-02-28 by the reprex package (v2.0.1)

8

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.