How to remove by group based on certain NA columns and certain time frame?
I have the below panel df containing worker layoff and wage information. I want to restrict my df to only those respondents who have 3 consecutive years of earnings before their layoff (layoff year is indicated by PermSeparation==1)
As I see it, the coding restrictions are --> keep only the PersonIDs who satisfy:
- Non-missing consecutive EmployeeQuarterlyWages_Qtr1/2/3/4
- Time_To_Layoff is between -3 and -1
I tried:
test2 <- test %>%
group_by(PersonID) %>%
filter(!is.na('EmployeeQuarterlyWages_Qtr1'), !is.na('EmployeeQuarterlyWages_Qtr2'),
!is.na('EmployeeQuarterlyWages_Qtr3'), !is.na('EmployeeQuarterlyWages_Qtr4') & Time_To_Layoff %in% -1:-3)
When I run the above, I get this error:
Error in filter():
! Can't transform a data frame with duplicate names.
I thought I could for sure puzzle this out myself, but something isn't adding up.
Current df:
The highlighted rows indicate the 3 years before a layoff for each respondent where I want non-missing earnings.
So for example, PersonID 1534 would not be dropped because they have a complete series of non-missing earnings for the 3 years before their layoff in 2016.
On the other hand, PersonID 636 would get dropped from the df because they have missing earnings for the 3 years before their layoff in 2014.
And here are the first 45 rows of the df:
structure(list(CalendarYear = c(2003, 2004, 2005, 2006, 2007,
2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018,
2019, 2020, 2021, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2012,
2013, 2014, 2016, 2017, 2018, 2019, 2020, 2021, 2003, 2010, 2011,
2012, 2013, 2014, 2015, 2016, 2017, 2018), PersonID = c(278,
278, 278, 278, 278, 278, 278, 278, 278, 278, 278, 278, 278, 278,
278, 278, 278, 278, 278, 636, 636, 636, 636, 636, 636, 636, 636,
636, 636, 636, 636, 636, 636, 636, 636, 1534, 1534, 1534, 1534,
1534, 1534, 1534, 1534, 1534, 1534), LayoffCalendarYear = c(NA,
NA, NA, NA, NA, 2008, NA, 2010, NA, NA, 2013, NA, NA, 2016, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 2014, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 2016, NA, NA),
LayoffCalendarQuarter = c(NA, NA, NA, NA, NA, 1, NA, 4, NA,
NA, 3, NA, NA, 1, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, 3, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, 1, NA, NA), LayoffTimeID = c(NA, NA, NA, NA,
NA, 20080331, NA, 20101231, NA, NA, 20130930, NA, NA, 20160331,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 20140930,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 20160331,
NA, NA), Time_To_Layoff = c(-5, -4, -3, -2, -1, 0, 1, 2,
3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, -9, -8, -7, -6, -5,
-4, -3, -2, -1, 0, 1, 2, 3, 4, 5, 6, -7, -6, -5, -4, -3,
-2, -1, 0, 1, 2), PermSeparation = c(0, 0, 0, 0, 0, 1, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0),
MassLayoffFlag = c(NA, NA, NA, NA, NA, 1, NA, 1, NA, NA,
1, NA, NA, 1, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, 1, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, 1, NA, NA), IndividualLayOff = c(NA, NA, NA, NA,
NA, 1, NA, 1, NA, NA, 1, NA, NA, 0, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, 1, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, 1, NA, NA), County = c(177, 177,
177, 177, 177, 177, 179, 123, 177, 177, 177, 53, 177, 177,
9, 177, 177, 177, 163, 53, 53, 53, 53, 53, 53, 53, 141, 53,
53, 123, 123, 123, 123, 123, 123, 103, 147, 147, 147, 147,
147, 147, 37, 147, 147), industryID = c(1086, 1086, 1086,
1086, 1086, 1086, 1078, 1086, 1086, 1086, 1086, 1084, 1086,
1086, 1086, 1086, 1086, 1086, 1086, 852, 852, 852, 852, 852,
852, 852, 926, 852, 852, 933, 933, 933, 933, 933, 933, 763,
933, 933, 909, 909, 909, 909, 930, 930, 930), EmployeeQuarterlyWages_Qtr1 = c(15018,
13125, 765, 14111, 6838, 4301, 13674, NA, 5475, NA, 11135,
NA, NA, 1710, NA, 1076, 1505, NA, 8130, 22115, 12813, 17856,
23338, 16250, 15872, 15996, NA, NA, 12946, NA, 20012, 19969,
17906, 16849, 25338, 2163, NA, 8211, 3666, 3297, 2931, 7076,
12696, 14413, NA), EmployeeQuarterlyWages_Qtr2 = c(13445,
14486, 5899, 269, 17872, 15704, 10477, 2723, NA, 317, 21157,
3356, 342, 1409, 5246, 8008, NA, 401, NA, 16488, 18739, 20200,
17769, 15322, 14638, 14045, NA, NA, 17337, NA, 17774, 16266,
16173, 14200, NA, 2174, NA, 8726, 6554, 2324, 3697, 6311,
11764, 13360, NA), EmployeeQuarterlyWages_Qtr3 = c(14663,
14557, 15616, 6283, 520, 16766, NA, 19605, 5784, 21487, 20961,
NA, 684, 804, 17231, 1740, 383, 1204, NA, 14981, 18706, 23152,
19728, 16680, 17457, 17082, NA, 11004, 14392, 19870, 19684,
18790, 18463, 18290, NA, 1025, 7210, 6461, 5858, 2426, 6301,
6903, 12340, 16158, 13736), EmployeeQuarterlyWages_Qtr4 = c(13549,
10396, 19659, 17556, NA, 7145, NA, 5601, 9341, 1119, NA,
21462, 7429, 2466, 11703, 1199, 1532, 2421, NA, 16866, 20898,
19682, 16284, 13736, 14440, NA, 7031, 15103, 16969, NA, 17157,
16741, 16148, 19480, NA, NA, 8268, NA, 3587, 2920, 5493,
5126, 6925, 12160, 15550), OwnershipCode_Qtr1 = c(50, 50,
50, 50, 50, 50, 50, NA, 50, NA, 50, NA, NA, 50, NA, 50, 50,
NA, 50, 50, 50, 50, 50, 50, 50, 50, NA, NA, 50, NA, 50, 50,
50, 50, 50, 50, NA, 50, 50, 50, 50, 50, 50, 50, NA), EmployeeQuarterlyHoursWorked_Qtr1 = c(486,
418, 24, 393, 193, 124, 401, NA, 140, NA, 269, NA, NA, 40,
NA, 24, 32, NA, 162, 517, 534, 521, 607, 14, 14, 609, NA,
NA, 125, NA, 145, 145, 145, 145, 520, 211, NA, 327, 168,
149, 130, 264, 331, 367, NA), EmployeeQuarterlyWages_Qtr1 = c(15018,
13125, 765, 14111, 6838, 4301, 13674, NA, 5475, NA, 11135,
NA, NA, 1710, NA, 1076, 1505, NA, 8130, 22115, 12813, 17856,
23338, 16250, 15872, 15996, NA, NA, 12946, NA, 20012, 19969,
17906, 16849, 25338, 2163, NA, 8211, 3666, 3297, 2931, 7076,
12696, 14413, NA), EmployeeTenure_Qtr1 = c(3, 7, 11, 2, 4,
1, 2, NA, 1, NA, 4, NA, NA, 4, NA, 1, 5, NA, 1, 5, 9, 13,
17, 21, 25, 29, NA, NA, 3, NA, 3, 7, 13, 17, 21, 4, NA, 2,
3, 7, 11, 15, 1, 2, NA), EmployerLocationTotalWagesPaid_Qtr1 = c(4376588,
5558459, 6463352, 6527722, 7827218, 8253747, 6188140, NA,
5070204, NA, 7478589, NA, NA, 13707751, NA, 1943390, 2072976,
NA, 5644314, 404837, 405388, 482224, 632307, 1003511, 775108,
359800, NA, NA, 313275, NA, 173963, 126200, 79439, 101462,
111767, 1734, NA, 927103, 9155355, 11392187, 12082773, 12082858,
24185966, 6725265, NA), QuarterlyAverageEmployment_Qtr1 = c(277,
345, 397, 364, 334, 409, 167, NA, 195, NA, 312, NA, NA, 393,
NA, 98, 91, NA, 147, 46, 69, 90, 137, 168, 154, 58, NA, NA,
53, NA, 8, 6, 5, 5, 5, 1, NA, 165, 416, 542, 526, 477, 1624,
374, NA), OwnershipCode_Qtr2 = c(50, 50, 50, 50, 50, 50,
50, 50, NA, 50, 50, 50, 50, 50, 50, 50, NA, 50, NA, 50, 50,
50, 50, 50, 50, 50, NA, NA, 50, NA, 50, 50, 50, 50, NA, 50,
NA, 50, 50, 50, 50, 50, 50, 50, NA), EmployeeQuarterlyHoursWorked_Qtr2 = c(434,
457, 185, 8, 506, 434, 303, 70, NA, 8, 484, 80, 8, 32, 117,
169, NA, 8, NA, 520, 640, 609, 544, 12, 14, 12, NA, NA, 149,
NA, 127, 127, 127, 127, NA, 212, NA, 345, 295, 103, 138,
229, 289, 353, NA), EmployeeQuarterlyWages_Qtr2 = c(13445,
14486, 5899, 269, 17872, 15704, 10477, 2723, NA, 317, 21157,
3356, 342, 1409, 5246, 8008, NA, 401, NA, 16488, 18739, 20200,
17769, 15322, 14638, 14045, NA, NA, 17337, NA, 17774, 16266,
16173, 14200, NA, 2174, NA, 8726, 6554, 2324, 3697, 6311,
11764, 13360, NA), EmployeeTenure_Qtr2 = c(4, 8, 12, 1, 5,
2, 3, 1, NA, 1, 5, 1, 1, 5, 1, 2, NA, 1, NA, 6, 10, 14, 18,
22, 26, 30, NA, NA, 4, NA, 4, 8, 14, 18, NA, 5, NA, 3, 4,
8, 12, 16, 2, 3, NA), EmployerLocationTotalWagesPaid_Qtr2 = c(4973009,
6501789, 6531662, 4508191, 7876558, 7305309, 6336353, 3486494,
NA, 5471015, 17116358, 15516511, 11592681, 12472180, 2293399,
2025045, NA, 2464048, NA, 367174, 483507, 603677, 752983,
1100656, 763058, 362176, NA, NA, 339512, NA, 129741, 89566,
85289, 88268, NA, 1949, NA, 1089508, 8844702, 12915214, 12343315,
9871327, 20808439, 5618925, NA), QuarterlyAverageEmployment_Qtr2 = c(287,
362, 372, 213, 344, 385, 192, 174, NA, 236, 492, 806, 495,
439, 130, 93, NA, 99, NA, 47, 89, 107, 114, 129, 151, 54,
NA, NA, 60, NA, 7, 5, 5, 5, NA, 1, NA, 174, 418, 438, 515,
469, 1617, 382, NA), OwnershipCode_Qtr3 = c(50, 50, 50, 50,
50, 50, NA, 50, 50, 50, 50, NA, 50, 50, 50, 50, 50, 50, NA,
50, 50, 50, 50, 50, 50, 50, NA, 50, 50, 50, 50, 50, 50, 50,
NA, 50, 30, 50, 50, 50, 50, 50, 50, 50, 50), EmployeeQuarterlyHoursWorked_Qtr3 = c(467,
455, 477, 174, 15, 435, NA, 475, 131, 489, 472, NA, 16, 17,
385, 37, 8, 24, NA, 524, 560, 603, 609, 14, 14, 608, NA,
73, 118, 147, 147, 147, 147, 147, NA, 700, 314, 279, 264,
107, 238, 251, 302, 450, 397), EmployeeQuarterlyWages_Qtr3 = c(14663,
14557, 15616, 6283, 520, 16766, NA, 19605, 5784, 21487, 20961,
NA, 684, 804, 17231, 1740, 383, 1204, NA, 14981, 18706, 23152,
19728, 16680, 17457, 17082, NA, 11004, 14392, 19870, 19684,
18790, 18463, 18290, NA, 1025, 7210, 6461, 5858, 2426, 6301,
6903, 12340, 16158, 13736), EmployeeTenure_Qtr3 = c(5, 9,
1, 2, 6, 3, NA, 2, 1, 2, 6, NA, 2, 6, 2, 3, 1, 2, NA, 7,
11, 15, 19, 23, 27, 31, NA, 1, 5, 3, 5, 11, 15, 19, NA, 6,
1, 4, 5, 9, 13, 17, 3, 4, 1), EmployerLocationTotalWagesPaid_Qtr3 = c(4908572,
5338866, 927399, 4451896, 9746087, 7631926, NA, 4433520,
5049863, 7699046, 10643057, NA, 17886321, 8584048, 2446268,
7350852, 2759886, 2797068, NA, 435628, 470361, 643822, 1035228,
1204524, 808669, 487927, NA, 348051, 350997, 162114, 142409,
104603, 88172, 94370, NA, 1949, 964890, 1115224, 7587133,
10845487, 12328106, 11529583, 24842917, 6301950, 6305531),
QuarterlyAverageEmployment_Qtr3 = c(311, 333, 57, 241, 448,
407, NA, 212, 225, 312, 396, NA, 569, 361, 134, 305, 118,
102, NA, 38, 89, 122, 135, 173, 139, 71, NA, 55, 54, 9, 7,
5, 4, 4, NA, 1, 146, 172, 421, 544, 485, 466, 1661, 387,
391), OwnershipCode_Qtr4 = c(50, 50, 50, 50, NA, 50, NA,
50, 50, 50, NA, 50, 50, 50, 50, 50, 50, 50, NA, 50, 50, 50,
50, 50, 50, NA, 50, 50, 50, NA, 50, 50, 50, 50, NA, NA, 50,
NA, 50, 50, 50, 50, 50, 50, 50), EmployeeQuarterlyHoursWorked_Qtr4 = c(431,
326, 539, 485, NA, 168, NA, 137, 237, 28, NA, 509, 171, 56,
261, 25, 32, 46, NA, 604, 605, 522, 518, 12, 12, NA, 320,
149, 147, NA, 125, 125, 125, 142, NA, NA, 347, NA, 159, 131,
206, 186, 171, 372, 399), EmployeeQuarterlyWages_Qtr4 = c(13549,
10396, 19659, 17556, NA, 7145, NA, 5601, 9341, 1119, NA,
21462, 7429, 2466, 11703, 1199, 1532, 2421, NA, 16866, 20898,
19682, 16284, 13736, 14440, NA, 7031, 15103, 16969, NA, 17157,
16741, 16148, 19480, NA, NA, 8268, NA, 3587, 2920, 5493,
5126, 6925, 12160, 15550), EmployeeTenure_Qtr4 = c(6, 10,
2, 3, NA, 2, NA, 3, 2, 3, NA, 1, 3, 7, 3, 4, 2, 3, NA, 8,
12, 16, 20, 24, 28, NA, 1, 2, 2, NA, 6, 12, 16, 20, NA, NA,
2, NA, 6, 10, 14, 18, 4, 5, 2), EmployerLocationTotalWagesPaid_Qtr4 = c(5532362,
5871533, 6298451, 8170601, NA, 5704992, NA, 3145261, 4800055,
8196639, NA, 21981416, 18956026, 7713089, 1519066, 2350844,
2997932, 2490561, NA, 398778, 444330, 504547, 1043201, 833779,
555474, NA, 5930182, 278577, 417996, NA, 111134, 103111,
76489, 96781, NA, NA, 1190514, NA, 9847418, 12459033, 10993349,
10002876, 21379229, 5508546, 5538134), QuarterlyAverageEmployment_Qtr4 = c(346,
362, 368, 439, NA, 186, NA, 152, 233, 271, NA, 864, 574,
339, 85, 96, 119, 104, NA, 28, 87, 90, 186, 157, 89, NA,
756, 68, 81, NA, 6, 5, 4, 5, NA, NA, 161, NA, 425, 537, 491,
475, 1678, 385, 379), CountyName.x = c("City of St. Paul",
"City of St. Paul", "City of St. Paul", "City of St. Paul",
"City of St. Paul", "City of St. Paul", "City of Duluth",
"Ramsey County", "City of St. Paul", "City of St. Paul",
"City of St. Paul", "Hennepin County", "City of St. Paul",
"City of St. Paul", "Benton County", "City of St. Paul",
"City of St. Paul", "City of St. Paul", "Washington County",
"Hennepin County", "Hennepin County", "Hennepin County",
"Hennepin County", "Hennepin County", "Hennepin County",
"Hennepin County", "Sherburne County", "Hennepin County",
"Hennepin County", "Ramsey County", "Ramsey County", "Ramsey County",
"Ramsey County", "Ramsey County", "Ramsey County", "Nicollet County",
"Steele County", "Steele County", "Steele County", "Steele County",
"Steele County", "Steele County", "Dakota County", "Steele County",
"Steele County"), IndustryGroup = c("Building Equipment Contractors",
"Building Equipment Contractors", "Building Equipment Contractors",
"Building Equipment Contractors", "Building Equipment Contractors",
"Building Equipment Contractors", "Building Foundation/Exterior Contractors",
"Building Equipment Contractors", "Building Equipment Contractors",
"Building Equipment Contractors", "Building Equipment Contractors",
"Building Equipment Contractors", "Building Equipment Contractors",
"Building Equipment Contractors", "Building Equipment Contractors",
"Building Equipment Contractors", "Building Equipment Contractors",
"Building Equipment Contractors", "Building Equipment Contractors",
"Employment Services", "Employment Services", "Employment Services",
"Employment Services", "Employment Services", "Employment Services",
"Employment Services", "Home Health Care Services", "Employment Services",
"Employment Services", "Nursing Care Facilities (Skilled Nursing Facilities)",
"Nursing Care Facilities (Skilled Nursing Facilities)", "Nursing Care Facilities (Skilled Nursing Facilities)",
"Nursing Care Facilities (Skilled Nursing Facilities)", "Nursing Care Facilities (Skilled Nursing Facilities)",
"Nursing Care Facilities (Skilled Nursing Facilities)", "Agencies, Brokerages, and Other Insurance Related Activities",
"Nursing Care Facilities (Skilled Nursing Facilities)", "Nursing Care Facilities (Skilled Nursing Facilities)",
"Offices of Physicians", "Offices of Physicians", "Offices of Physicians",
"Offices of Physicians", "General Medical and Surgical Hospitals",
"General Medical and Surgical Hospitals", "General Medical and Surgical Hospitals"
), industry = c("Plumbing and HVAC Contractors", "Plumbing and HVAC Contractors",
"Plumbing and HVAC Contractors", "Plumbing and HVAC Contractors",
"Plumbing and HVAC Contractors", "Plumbing and HVAC Contractors",
"Roofing Contractors", "Plumbing and HVAC Contractors", "Plumbing and HVAC Contractors",
"Plumbing and HVAC Contractors", "Plumbing and HVAC Contractors",
"Electrical Contractors", "Plumbing and HVAC Contractors",
"Plumbing and HVAC Contractors", "Plumbing and HVAC Contractors",
"Plumbing and HVAC Contractors", "Plumbing and HVAC Contractors",
"Plumbing and HVAC Contractors", "Plumbing and HVAC Contractors",
"Temporary Help Services", "Temporary Help Services", "Temporary Help Services",
"Temporary Help Services", "Temporary Help Services", "Temporary Help Services",
"Temporary Help Services", "Home Health Care Services", "Temporary Help Services",
"Temporary Help Services", "Nursing Care Facilities (Skilled Nursing Facilities)",
"Nursing Care Facilities (Skilled Nursing Facilities)", "Nursing Care Facilities (Skilled Nursing Facilities)",
"Nursing Care Facilities (Skilled Nursing Facilities)", "Nursing Care Facilities (Skilled Nursing Facilities)",
"Nursing Care Facilities (Skilled Nursing Facilities)", "Insurance Agencies and Brokerages",
"Nursing Care Facilities (Skilled Nursing Facilities)", "Nursing Care Facilities (Skilled Nursing Facilities)",
"Offices of Physicians", "Offices of Physicians", "Offices of Physicians",
"Offices of Physicians", "General Medical and Surgical Hospitals",
"General Medical and Surgical Hospitals", "General Medical and Surgical Hospitals"
), sector = c("Construction", "Construction", "Construction",
"Construction", "Construction", "Construction", "Construction",
"Construction", "Construction", "Construction", "Construction",
"Construction", "Construction", "Construction", "Construction",
"Construction", "Construction", "Construction", "Construction",
"Administrative and Support and Waste Management and Remediation Services",
"Administrative and Support and Waste Management and Remediation Services",
"Administrative and Support and Waste Management and Remediation Services",
"Administrative and Support and Waste Management and Remediation Services",
"Administrative and Support and Waste Management and Remediation Services",
"Administrative and Support and Waste Management and Remediation Services",
"Administrative and Support and Waste Management and Remediation Services",
"Health Care and Social Assistance", "Administrative and Support and Waste Management and Remediation Services",
"Administrative and Support and Waste Management and Remediation Services",
"Health Care and Social Assistance", "Health Care and Social Assistance",
"Health Care and Social Assistance", "Health Care and Social Assistance",
"Health Care and Social Assistance", "Health Care and Social Assistance",
"Finance and Insurance", "Health Care and Social Assistance",
"Health Care and Social Assistance", "Health Care and Social Assistance",
"Health Care and Social Assistance", "Health Care and Social Assistance",
"Health Care and Social Assistance", "Health Care and Social Assistance",
"Health Care and Social Assistance", "Health Care and Social Assistance"
), SectorCode = c(23, 23, 23, 23, 23, 23, 23, 23, 23, 23,
23, 23, 23, 23, 23, 23, 23, 23, 23, 56, 56, 56, 56, 56, 56,
56, 62, 56, 56, 62, 62, 62, 62, 62, 62, 52, 62, 62, 62, 62,
62, 62, 62, 62, 62), SubSector = c("Specialty Trade Contractors",
"Specialty Trade Contractors", "Specialty Trade Contractors",
"Specialty Trade Contractors", "Specialty Trade Contractors",
"Specialty Trade Contractors", "Specialty Trade Contractors",
"Specialty Trade Contractors", "Specialty Trade Contractors",
"Specialty Trade Contractors", "Specialty Trade Contractors",
"Specialty Trade Contractors", "Specialty Trade Contractors",
"Specialty Trade Contractors", "Specialty Trade Contractors",
"Specialty Trade Contractors", "Specialty Trade Contractors",
"Specialty Trade Contractors", "Specialty Trade Contractors",
"Administrative and Support Services", "Administrative and Support Services",
"Administrative and Support Services", "Administrative and Support Services",
"Administrative and Support Services", "Administrative and Support Services",
"Administrative and Support Services", "Ambulatory Health Care Services",
"Administrative and Support Services", "Administrative and Support Services",
"Nursing and Residential Care Facilities", "Nursing and Residential Care Facilities",
"Nursing and Residential Care Facilities", "Nursing and Residential Care Facilities",
"Nursing and Residential Care Facilities", "Nursing and Residential Care Facilities",
"Insurance Carriers and Related Activities", "Nursing and Residential Care Facilities",
"Nursing and Residential Care Facilities", "Ambulatory Health Care Services",
"Ambulatory Health Care Services", "Ambulatory Health Care Services",
"Ambulatory Health Care Services", "Hospitals", "Hospitals",
"Hospitals"), SubSectorCode = c(238, 238, 238, 238, 238,
238, 238, 238, 238, 238, 238, 238, 238, 238, 238, 238, 238,
238, 238, 561, 561, 561, 561, 561, 561, 561, 621, 561, 561,
623, 623, 623, 623, 623, 623, 524, 623, 623, 621, 621, 621,
621, 622, 622, 622), Sum_Non_NA = c(35, 35, 35, 35, 29, 35,
23, 30, 29, 29, 29, 24, 29, 35, 30, 35, 29, 29, 18, 36, 36,
36, 36, 36, 36, 30, 18, 24, 36, 18, 36, 36, 36, 36, 18, 30,
24, 30, 36, 36, 36, 36, 36, 36, 24), BirthMonthYear = c(NA,
NA, NA, NA, NA, "XX-5-1967", NA, "XX-5-1967", NA, NA, "XX-5-1967",
NA, NA, "XX-5-1967", NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, "XX-7-1967", NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, "XX-1-1969", NA, NA), BirthYear = c(NA,
NA, NA, NA, NA, "1967", NA, "1967", NA, NA, "1967", NA, NA,
"1967", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, "1967", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, "1969", NA, NA), AGEatLayoff = c(NA, NA, NA, NA, NA,
41, NA, 43, NA, NA, 46, NA, NA, 49, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, 47, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, 47, NA, NA), Gender = c(NA, NA,
NA, NA, NA, "M", NA, "M", NA, NA, "M", NA, NA, "M", NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "F", NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "F", NA,
NA), GroupCode = c(NA, NA, NA, NA, NA, 2382, NA, 2382, NA,
NA, 2382, NA, NA, 2382, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, 5613, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, 6211, NA, NA), DegreeLevel1 = c(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, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, 99, NA, NA, NA, NA, NA, NA, NA, NA), DegreeLevel2 = c(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, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, 99, NA, NA, NA, NA, NA, NA, NA, NA),
DegreeSeeking = c(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,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1, NA, NA, NA,
NA, NA, NA, NA, NA), DisablingCondition = c(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, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, 0, NA, NA, NA, NA, NA, NA, NA, NA), EnrollmentStatus = c(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, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, 1, NA, NA, NA, NA, NA, NA, NA, NA), IPEDSZip.y = c(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, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, "55912-1473", NA, NA, NA, NA, NA, NA,
NA, NA), cpi = c(184, 188.908333333333, 195.266666666667,
201.558333333333, 207.344166666667, 215.25425, 214.564666666667,
218.076166666667, 224.923, 229.586083333333, 232.95175, 236.715,
237.00175, 240.005416666667, 245.121, 251.098916666667, 255.6465,
258.83825, 270.965416666667, 184, 188.908333333333, 195.266666666667,
201.558333333333, 207.344166666667, 215.25425, 214.564666666667,
229.586083333333, 232.95175, 236.715, 240.005416666667, 245.121,
251.098916666667, 255.6465, 258.83825, 270.965416666667,
184, 218.076166666667, 224.923, 229.586083333333, 232.95175,
236.715, 237.00175, 240.005416666667, 245.121, 251.098916666667
), adj_factor = c(0.679053446242371, 0.697167688988601, 0.72063316813185,
0.743852613417764, 0.765205276811155, 0.794397501526179,
0.791852588814378, 0.804811807164813, 0.830080099397678,
0.84728924509124, 0.859710264378757, 0.87359856808295, 0.874656821211809,
0.885741876654001, 0.904620977154219, 0.926682525599054,
0.943465417634784, 0.95524459609698, 1, 0.679053446242371,
0.697167688988601, 0.72063316813185, 0.743852613417764, 0.765205276811155,
0.794397501526179, 0.791852588814378, 0.84728924509124, 0.859710264378757,
0.87359856808295, 0.885741876654001, 0.904620977154219, 0.926682525599054,
0.943465417634784, 0.95524459609698, 1, 0.679053446242371,
0.804811807164813, 0.830080099397678, 0.84728924509124, 0.859710264378757,
0.87359856808295, 0.874656821211809, 0.885741876654001, 0.904620977154219,
0.926682525599054), EmployeeQuarterlyWages_Qtr1_adj = c(22116.0794972826,
18826.1736843266, 1061.5664582622, 18970.1558419812, 8936.1641996198,
5414.16607144032, 17268.3655937449, NA, 6595.74901744153,
NA, 12952.0379846184, NA, NA, 1930.58502151861, NA, 1161.13120758851,
1595.18300498279, NA, 8130, 32567.392334692, 18378.6486413163,
24778.2100375555, 31374.4948650101, 21236.1316530889, 19979.9218521043,
20200.729562494, NA, NA, 14819.163484218, NA, 22121.9720804555,
21548.9117884154, 18978.9680313767, 17638.4143588387, 25338,
3185.31628396739, NA, 9891.81647163696, 4326.73968333592,
3835.01295332617, 3355.0879169043, 8090.03008768219, 14333.7470369592,
15932.6395960226, NA), EmployeeQuarterlyWages_Qtr2_adj = c(19799.6197124094,
20778.3582469452, 8185.85691148856, 361.630778930831, 23355.8243017849,
19768.4408244359, 13230.9979761346, 3383.39966655381, NA,
374.134337047868, 24609.453762063, 3841.58138830802, 391.010498867624,
1590.75689784779, 5799.11380841842, 8641.57872710854, NA,
419.787771256116, NA, 24280.8575543478, 26878.7557082359,
28030.9051724138, 23887.7966945053, 20023.3851808386, 18426.543351254,
17736.887140862, NA, NA, 19845.4995617092, NA, 19648.0077832309,
17552.9370098836, 17142.1227505559, 14865.3026230345, NA,
3201.51530344203, NA, 10512.2385253324, 7735.25692432722,
2703.23630680316, 4231.92085595195, 7215.40134021514, 13281.5217503772,
14768.6161800362, NA), EmployeeQuarterlyWages_Qtr3_adj = c(21593.292959692,
20880.1988817328, 21669.8324342779, 8446.56573986025, 679.556212898844,
21105.3030350543, NA, 24359.7320832859, 6968.00224965878,
25359.6987386358, 24381.4699771519, NA, 782.020997735249,
907.71365924033, 19047.7563920812, 1877.66570743867, 405.950226517216,
1260.41016606574, NA, 22061.5918863225, 26831.4213286867,
32127.3027995903, 26521.3829247116, 21798.0723676014, 21975.1446429048,
21572.1969483947, NA, 12799.6610671523, 16474.3859775116,
22433.1721506285, 21759.3892880115, 20276.6314038923, 19569.3447315597,
19146.9285193874, NA, 1509.45408740942, 8958.61608367719,
7783.58619208944, 6913.81371112433, 2821.88092956302, 7212.69497250562,
7892.23822714389, 13931.824073415, 17861.6242692385, 14822.7679065388
), EmployeeQuarterlyWages_Qtr4_adj = c(19952.7740783515,
14911.7639331245, 27280.1764744793, 23601.4496465043, NA,
8994.23775411325, NA, 6959.39094100915, 11253.1308807162,
1320.68240743396, NA, 24567.3479606278, 8493.61694762451,
2784.10682050579, 12936.9098169883, 1293.86274897642, 1623.80090606886,
2534.42941199765, NA, 24837.5147690217, 29975.5716308615,
27312.0928516558, 21891.4334725266, 17950.7387314972, 18177.297854359,
NA, 8298.22878165162, 17567.5464464923, 19424.2534499997,
NA, 18965.9541767127, 18065.5181656499, 17115.6246939948,
20392.6827532896, NA, NA, 10273.2091234179, NA, 4233.50115769938,
3396.49312214511, 6287.78503157806, 5860.58426080539, 7818.30483860607,
13442.0937686557, 16780.2883624548), EmployeeAnnualWages_adj = c(20865.4415619339,
18849.1236865323, 14549.358069627, 12844.9505018192, NA,
13820.5369212609, NA, NA, NA, NA, NA, NA, NA, 1803.29059977813,
NA, 3243.55959777803, NA, NA, NA, 25936.839136096, 25516.0993272751,
28062.1277153038, 25918.7769891884, 20252.0819832565, 19639.7269251556,
NA, NA, NA, 17640.8256183596, NA, 20623.8308321026, 19360.9995919603,
18201.5150518718, 18010.8320636376, NA, NA, NA, NA, 5802.32786912171,
3189.15582795937, 5271.87219423498, 7264.56347896165, 12341.3494248394,
15501.2434534883, NA)), class = c("grouped_df", "tbl_df",
"tbl", "data.frame"), row.names = c(NA, -45L), groups = structure(list(
PersonID = c(278, 636, 1534), .rows = structure(list(1:19,
20:35, 36:45), ptype = integer(0), class = c("vctrs_list_of",
"vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -3L), .drop = TRUE))
Solution
It's a bit unorthodox, but essentially I'm filtering based on the ID pulled from the values obtained when I slice for the 3 years before permanent separation and look for anyNA values within that subset
filter(df |> tibble::repair_names(), PersonID %in% (df |>
tibble::repair_names() |>
group_by(PersonID) |>
slice((which(PermSeparation == 1) - 3):which(PermSeparation == 1)) |>
filter(!anyNA(c_across(EmployeeQuarterlyWages_Qtr1:EmployeeQuarterlyWages_Qtr4))) |>
pull(PersonID)))
CalendarYear PersonID LayoffCalendarYear LayoffCalendarQuarter LayoffTimeID Time_To_Layoff PermSeparation MassLayoffFlag
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 2003 1534 NA NA NA -7 0 NA
2 2010 1534 NA NA NA -6 0 NA
3 2011 1534 NA NA NA -5 0 NA
4 2012 1534 NA NA NA -4 0 NA
5 2013 1534 NA NA NA -3 0 NA
6 2014 1534 NA NA NA -2 0 NA
7 2015 1534 NA NA NA -1 0 NA
8 2016 1534 2016 1 20160331 0 1 1
9 2017 1534 NA NA NA 1 0 NA
10 2018 1534 NA NA NA 2 0 NA
Edit:
filter seems to run slow. Not quite sure why, maybe someone with greater knowledge can inform us on that.
a base R replacement is
subset(df, PersonID %in% (df |>
tibble::repair_names() |>
group_by(PersonID) |>
slice((which(PermSeparation == 1) - 3):which(PermSeparation == 1)) |>
filter(!anyNA(c_across(EmployeeQuarterlyWages_Qtr1:EmployeeQuarterlyWages_Qtr4))) |>
pull(PersonID)))
- rlanguageserver: Disable specific linter and avoid line-breaking
- Replace truncated word in a string
- Add label to right y axis in R ggplot
- Popup offset without using markers
- Multi marker depending a variable in a data frame
- Drawing numerous polygons
- Select icons or CircleMarkers based on factor variable
- Why is a map not displayed when I call leaflet in a function
- Display SpatialPolygonsDataFrame on leaflet map
- Leaflet in R not showing variable circle colors
- Overlay two population pyramid plots in one ggplot graph
- How to most efficiently filter a dataframe conditionally of values in another one, in the tidyverse framework?
- R group_by multiple conditionnal aggregation functions dplyr
- Conditionally split strings cells in a dataframe R
- How to have Shiny app render multiple plots
- Disable Shiny button conditionally to a rHandsontable format
- Is there a way to render dynamic headings in Quarto using R?
- What is the time complexity of sample?
- Add geom_text with x bar and round values
- Color-fill ggplot2 maps with a single color
- lapply a ggplot function on multiple inputs with consistent groups
- Is there a way to save text in R without it actively implementing \n?
- How to get number of unique observations using tbl_summary
- Using if_else in a mutate command yields an error about false having the wrong size
- Rotate x-axis value in dendogram using fviz_dend
- Export data frames in list to xlsx with named sheets
- Query all primary roads from PostGis Database filled with OSM Data within a boundary
- Write a list in r to an excel file
- Calculating net assets with missing values: discrepancy in results
- Error in curl::curl_fetch_memory(url, handle = handle) : Couldn't connect to server in R: oauth2.0_token())