Here's the dataset (only a small portion of it):
data <- tibble::tribble(
~id, ~num_per_section, ~unit, ~section, ~incr, ~growth_factor, ~correlation,
4, 1L, 271367.963168022, 271367.963168022, 271367.963168022, 1, 1,
4, 2L, 256992.443966664, 513984.887933328, 242616.924765306, 0.944062483007389, 1,
4, 3L, 206939.767316042, 620819.301948127, 106834.414014798, 0.516258500724217, 0.975748579880399,
4, 4L, 158582.427610004, 634329.710440016, 13510.4084918897, 0.0851948648756681, 0.9186985364243,
4, 5L, 127126.510780628, 635632.55390314, 1302.84346312366, 0.0102484010229139, 0.860564652448833,
4, 6L, 106070.22993403, 636421.37960418, 788.825701039401, 0.00743682465409954, 0.809641295617285,
4, 7L, 90919.4208980013, 636435.946286009, 14.5666818296304, 0.000160215294881521, 0.765326468836825,
4, 8L, 79434.3035846462, 635474.428677169, -961.517608839669, 0.0121045639660586, 0.7257407579754,
4, 9L, 70627.2215170579, 635644.993653521, 170.564976351568, 0.00241500334697966, 0.691665008946981,
4, 10L, 63671.6985760963, 636716.985760963, 1071.992107442, 0.0168362417120194, 0.663041587889407,
4, 11L, 57972.7350176322, 637700.085193954, 983.099432991236, 0.0169579619228285, 0.638621317001256,
4, 12L, 53155.2026360466, 637862.431632559, 162.346438604756, 0.00305419658949175, 0.616719918453753,
4, 13L, 49126.6545424943, 638646.509052426, 784.077419866575, 0.0159603259608967, 0.59763004255326,
4, 14L, 45580.3606354286, 638125.048896, -521.460156425484, 0.0114404570116579, 0.579552254928678,
4, 15L, 42699.3834791916, 640490.752187874, 2365.7032918738, 0.0554036873395762, 0.565317100820534,
4, 16L, 39975.9783601757, 639615.653762811, -875.098425062839, 0.0218906068333932, 0.551076680741684,
5, 1L, 439535.977161331, 439535.977161331, 439535.977161331, 1, 1,
5, 2L, 412103.301486099, 824206.602972199, 384670.625810868, 0.933432526319721, 1,
5, 3L, 393783.44767209, 1181350.34301627, 357143.740044072, 0.906954678149578, 0.999770584392229,
5, 4L, 375990.713236545, 1503962.85294618, 322612.509929909, 0.85803318691796, 0.999235137693989,
5, 5L, 358043.636372839, 1790218.18186419, 286255.328918013, 0.799498440519493, 0.998328362148192,
5, 6L, 339974.019530321, 2039844.11718193, 249625.935317734, 0.734250033760213, 0.996967998718375,
5, 7L, 321466.030594252, 2250262.21415976, 210418.096977836, 0.65455779756531, 0.994935431404515,
5, 8L, 302757.451958908, 2422059.61567126, 171797.401511502, 0.567442354927795, 0.992036652852356,
5, 9L, 284759.126298074, 2562832.13668267, 140772.521011403, 0.494356485923645, 0.988334689801476,
5, 10L, 267615.045731673, 2676150.45731673, 113318.320634061, 0.423437779158653, 0.983831675422563,
5, 11L, 252734.396692853, 2780078.36362139, 103927.906304659, 0.411213937099991, 0.979198699489875,
5, 12L, 238664.419868466, 2863973.03842159, 83894.6748001985, 0.351517309729013, 0.974184722571896,
5, 13L, 226158.096295673, 2940055.25184374, 76082.2134221573, 0.336411628273921, 0.969101851742581,
5, 14L, 214628.003222772, 3004792.04511881, 64736.7932750708, 0.301623237895371, 0.9638976049554,
5, 15L, 204121.465970009, 3061821.98955013, 57029.9444313198, 0.279392195035965, 0.958636314047941,
5, 16L, 194288.667960388, 3108618.6873662, 46796.6978160706, 0.240861694649179, 0.953194475173269,
6, 1L, 202535.891337108, 202535.891337108, 202535.891337108, 1, 1,
6, 2L, 198706.171315704, 397412.342631409, 194876.451294301, 0.980726718269264, 1,
6, 3L, 138592.653876375, 415777.961629125, 18365.6189977166, 0.132515097186166, 0.902260601020887,
6, 4L, 104584.21765931, 418336.870637238, 2558.90900811279, 0.0244674489648966, 0.823252681313037,
6, 5L, 83408.3632407436, 417041.816203718, -1295.05443352013, 0.0155266736236291, 0.755790854653751,
6, 6L, 69187.8331017209, 415126.998610326, -1914.81759339251, 0.0276756404637983, 0.697531387824379,
6, 7L, 59995.6435933656, 419969.505153559, 4842.50654323329, 0.0807143027926246, 0.662293699494513,
6, 8L, 52630.0513876425, 421040.41110114, 1070.90594758117, 0.0203478035712618, 0.633321322555482,
6, 9L, 46447.2966960666, 418025.6702646, -3014.74083654024, 0.0649067018101731, 0.600633594471428,
6, 10L, 41788.0175038152, 417880.175038152, -145.495226447936, 0.00348174513027934, 0.572237015712451,
6, 11L, 38116.3925777319, 419280.318355051, 1400.14331689908, 0.0367333638419145, 0.550395272758551,
6, 12L, 35032.1020074645, 420385.224089574, 1104.90573452332, 0.0315398069544296, 0.532952626288351,
6, 13L, 32156.3021518871, 418031.927974533, -2353.29611504154, 0.073183045237166, 0.512365366795594,
6, 14L, 30000.8646601953, 420012.105242734, 1980.17726820102, 0.0660040065721269, 0.497723639487815,
6, 15L, 28275.9763526277, 424139.645289415, 4127.54004668153, 0.145973387274316, 0.491572490045196,
6, 16L, 26394.4613598599, 422311.381757758, -1828.26353165752, 0.0692669384963431, 0.481804294112416,
7, 1L, 294361.495673114, 294361.495673114, 294361.495673114, 1, 1,
7, 2L, 282409.77335912, 564819.54671824, 270458.051045126, 0.957679501768533, 1,
7, 3L, 271691.682063884, 815075.046191652, 250255.499473412, 0.921101071524775, 0.99974921587376,
7, 4L, 258078.182719266, 1032312.73087706, 217237.684685411, 0.841751450651368, 0.998822352063839,
7, 5L, 242681.588982707, 1213407.94491353, 181095.214036472, 0.746225598718066, 0.996976300519544,
7, 6L, 226524.244090506, 1359145.46454304, 145737.5196295, 0.643363893408566, 0.994000820947078,
7, 7L, 209710.770279867, 1467975.39195907, 108829.927416033, 0.518952494765985, 0.989324854125464,
7, 8L, 192658.083476745, 1541264.66781396, 73289.2758548886, 0.380411112434507, 0.982292369406254,
7, 9L, 176451.228906145, 1588061.06015531, 46796.3923413504, 0.265208650749843, 0.972845681450229,
7, 10L, 161574.559768055, 1615745.59768055, 27684.5375252427, 0.17134218137425, 0.961145763611559,
7, 11L, 148529.975764925, 1633829.73341418, 18084.1357336296, 0.121754114888236, 0.947943297115941,
7, 12L, 137289.373302697, 1647472.47963236, 13642.7462181821, 0.0993721938558383, 0.9340341272395,
7, 13L, 127380.659029686, 1655948.56738592, 8476.08775355737, 0.0665414028952544, 0.919612028855427,
7, 14L, 118544.462557324, 1659622.47580254, 3673.90841662209, 0.0309918180686466, 0.904679221969017,
7, 15L, 111111.653839217, 1666674.80758826, 7052.33178571961, 0.0634706760455981, 0.890239489912168,
7, 16L, 103792.45953541, 1660679.35256657, -5995.45502169523, 0.0577638784988017, 0.874669171011858,
8, 1L, 495277.706497856, 495277.706497856, 495277.706497856, 1, 1,
8, 2L, 492222.017085362, 984444.034170724, 489166.327672869, 0.993792050525111, 1,
8, 3L, 429239.623557611, 1287718.87067283, 303274.836502108, 0.706539703833757, 0.990952913106127,
8, 4L, 325687.467658675, 1302749.8706347, 15030.9999618672, 0.8461516068454311, 0.931410464454641,
8, 5L, 262043.279631908, 1310216.39815954, 7466.52752483846, 0.0284934898362083, 0.874127149474712,
8, 6L, 226176.894038357, 1357061.36423014, 46844.9660706057, 0.20711649733178, 0.844822515034449,
8, 7L, 205445.779904101, 1438120.45932871, 81059.0950985667, 0.394552251871047, 0.844489472082496,
8, 8L, 191131.595229991, 1529052.76183993, 90932.3025112166, 0.475757565889599, 0.859234961832996,
8, 9L, 180050.742280704, 1620456.68052634, 91403.9186864132, 0.50765643911599, 0.878607287120993,
8, 10L, 170206.517072185, 1702065.17072185, 81608.4901955102, 0.47946748220516, 0.896362624550621,
8, 11L, 161350.286269099, 1774853.14896009, 72787.9782382427, 0.451117750834648, 0.910892168881227,
8, 12L, 153718.198452349, 1844618.38142818, 69765.2324680893, 0.453851483887355, 0.922683462165025,
8, 13L, 147142.18173426, 1912848.36254539, 68229.9811172041, 0.463701029256367, 0.932340562597687,
8, 14L, 141541.608765245, 1981582.52271342, 68734.1601680373, 0.485610985827052, 0.940442971845519,
8, 15L, 136644.011678779, 2049660.17518168, 68077.6524682587, 0.498211752069274, 0.947288748449307,
8, 16L, 132351.994745419, 2117631.91592671, 67971.7407450292, 0.513567935834844, 0.953131738124198,
9, 1L, 588945.469123735, 588945.469123735, 588945.469123735, 1, 1,
9, 2L, 573367.568535258, 1146735.13707052, 557789.667946781, 0.972830865498248, 1,
9, 3L, 568519.684477685, 1705559.05343306, 558823.91636254, 0.982945589431871, 0.999999857014311,
9, 4L, 427690.636208834, 1710762.54483534, 5203.49140227959, 0.0121664842803311, 0.945283743279267,
9, 5L, 344390.633636859, 1721953.1681843, 11190.623348959, 0.0324939828670222, 0.889454352820632,
9, 6L, 284979.80208, 1709878.81248, -12074.3557042948, 0.0423691630640729, 0.833713166389403,
9, 7L, 244411.414277621, 1710879.89994335, 1001.08746334817, 0.00409591125810131, 0.786602904020877,
9, 8L, 215996.801993953, 1727974.41595162, 17094.5160082732, 0.0791424495662294, 0.75220149877714,
9, 9L, 192114.66097103, 1729031.94873927, 1057.53278764477, 0.00550469590555738, 0.721404275633303,
9, 10L, 172848.446692799, 1728484.46692799, -547.48181127524, 0.00316740949514155, 0.693379289386605,
9, 11L, 157245.924916903, 1729705.17408594, 1220.70715794619, 0.00776304478854555, 0.668480884493029,
9, 12L, 143139.616810011, 1717675.40172014, -12029.7723657996, 0.0840422283773939, 0.64195889835878,
9, 13L, 131865.732554029, 1714254.52320238, -3420.87851775927, 0.0259421341049134, 0.617278731059804,
9, 14L, 122374.875559349, 1713248.25783089, -1006.26537148841, 0.00822281017152405, 0.594954517359712,
9, 15L, 115797.988163052, 1736969.82244577, 23721.5646148829, 0.204852994349792, 0.582100766969999,
9, 16L, 108568.255551217, 1737092.08881947, 122.266373701161, 0.0011261705650551, 0.999803214791404
)
I need to get the breakeven point, first look for the 16th row of correlation for each id, if it's > 0.999, breakeven = 16, if not, go to the growth_factor column, count the last value that's >0.8 before <0.8 (there're times that is 0.9, 0.88, 0.7, 0.9 but the breakeven would be 2) And it will be a new data frame since for each id since it will have fewer rows.
I'm thinking about an if statement with two conditions. Here's the desired output:
output
id breakeven
4 2
5 4
6 2
7 4
8 2
9 16
CodePudding user response：
You may try
library(dplyr)
data %>%
group_by(id) %>%
filter(case_when(
last(correlation) > 0.999 ~ row_number() == n(),
T ~ (growth_factor > 0.8 & lead(growth_factor) < 0.8)
)) %>%
filter(row_number() == 1) %>%
select(id, num_per_section) %>%
rename(breakeven = num_per_section)
id breakeven
<dbl> <int>
1 4 2
2 5 4
3 6 2
4 7 4
5 8 2
6 9 16