rdata-cleaningeconomics
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)))
- rintrojs only shows first dialog in Safari
- Automatically read a column of lowercases True and False as logical
- How do I add counts to a stacked bar graph?
- Counting the number of rows between each pair of months?
- How to create a conditional panel using a reactive object that is passed from another module?
- Plot multiple normalized stock charts from different dates into a single plot
- Select columns based on string match - dplyr::select
- Looking for a more efficient way to replace matrix elements
- modelsummary modelplot: change linewidth
- custom R function with group argument does not work while using the filter
- ggsurvplot function, risk table alignment problem
- plot running average in ggplot2
- Calling variable in df within function
- How to find position of running minimum (runMin) in a vector in R?
- Using httr2::last_response() in conjunction with purrr::possibly()
- Cropping a raster using terra does not return the expected extent
- Can janitor::clean_names be used on only certain columns in a data frame?
- Efficient way of row binding time series in a data.table, with correctly sorted timestamps
- Defining optional arguments in R when more complex function
- Can I change the cursor in plotly only when hovering over points?
- Issue Loading RStoolbox: "Cannot find proj.db" Error
- Filter CSV files for specific value before importing
- Matching the same lines from 2 different files and 2 columns
- Conditional coloring and outer borders in pdf KableExtra table in R
- How can I use observe
- Posterior predictive check for GAM (mgcv in R)
- Embed images in plotly tick labels
- Product of two beta distributions
- R: how to include a character between two repeted items in R?
- Categorizing data from 7 columns into 2