dataframe – conditional if statement and loop over in R

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

Leave a Comment