I'm having a tough time figuring this out and I will try to explain where I need help the best I can.
I have a dataframe consisting of participant ID (column 1), possible dates of an appointment for that participant (column 2, ranging from V1 to V3, indicating that there are a maximum of three possible dates for each participant), the dates (column 3) and a column with values (column 4). For each participant, we want to find one date for their appointment. The 2nd column (vis) indicates the preference of dates (V1 highest preference, if not possible then V2, if not possible then V3, if not possible then NO DATE). The possibility of getting each date depends on the value in column 4. if this column is equal to 60, then no more appointments on this date can be added. Each appointment made counts for a value of 2 to be added to this value. So, for example, if date 2021-09-07 already has 60 as a value, then no more appointments can be added. But if date 2021-08-25 only has 44 as a value, a maximum of 8 appointments can still be made for this date.
Example:
# A tibble: 9 x 4
Included.y vis date ficol
<dbl> <chr> <date> <chr>
1 316 V1 2021-07-01 NA
2 1027 V1 2021-07-01 NA
3 1101 V1 2021-07-01 NA
4 73 V1 2021-07-07 24
5 479 V1 2021-07-07 24
6 1026 V1 2021-07-07 24
7 1186 V1 2021-07-07 24
8 13 V2 2021-07-07 24
9 139 V2 2021-07-07 24
Would become:
# A tibble: 9 x 4
Included.y vis date ficol
<dbl> <chr> <date> <dbl>
1 316 V1 2021-07-01 6
2 1027 V1 2021-07-01 6
3 1101 V1 2021-07-01 6
4 73 V1 2021-07-07 36
5 479 V1 2021-07-07 36
6 1026 V1 2021-07-07 36
7 1186 V1 2021-07-07 36
8 13 V2 2021-07-07 36
9 139 V2 2021-07-07 36
Since the column containing the values for 2021-07-01 used to be NA (meaning 0) and now 3 appointments take place on that date, the value in total becomes 6 (we are only interested in the total value since this will be the restriction (max 60)). For 2021-07-07 the value started out at 24, after 6 new appointments on that date, the value will be 36. In this example everything turns out okay and the appointments can be scheduled but it could also be that the value of 2021-07-02 is already at a value of 58.
# A tibble: 1 x 4
Included.y vis date ficol
<dbl> <chr> <date> <dbl>
1 317 V1 2021-07-02 58
This would then become:
# A tibble: 1 x 4
Included.y vis date ficol
<dbl> <chr> <date> <dbl>
1 317 V1 2021-07-02 60
And now the 2021-07-02 date is 'full' so no more appointments can fall on this date.
I really hope i made this as clear as possible otherwise please let me know.
I put the dput of the first 100 rows of the actual dataset here:
structure(list(Included.y = c(1L, 1L, 3L, 3L, 4L, 4L, 4L, 5L,
5L, 6L, 6L, 6L, 7L, 7L, 8L, 8L, 8L, 9L, 9L, 12L, 12L, 13L, 13L,
13L, 14L, 14L, 15L, 15L, 17L, 17L, 20L, 20L, 21L, 21L, 22L, 22L,
22L, 23L, 23L, 25L, 25L, 27L, 27L, 28L, 28L, 28L, 30L, 31L, 31L,
32L, 32L, 33L, 33L, 35L, 35L, 35L, 37L, 37L, 37L, 39L, 39L, 41L,
42L, 42L, 44L, 44L, 45L, 45L, 45L, 46L, 46L, 46L, 47L, 47L, 48L,
48L, 49L, 49L, 51L, 51L, 54L, 54L, 55L, 55L, 55L, 56L, 57L, 57L,
59L, 59L, 60L, 60L, 60L, 62L, 62L, 63L, 63L, 64L, 64L, 68L),
vis = c("V1", "V3", "V1", "V3", "V1", "V2", "V3", "V1", "V3",
"V1", "V2", "V3", "V1", "V3", "V1", "V2", "V3", "V1", "V2",
"V1", "V3", "V1", "V2", "V3", "V1", "V3", "V1", "V3", "V1",
"V3", "V1", "V2", "V1", "V2", "V1", "V2", "V3", "V1", "V3",
"V1", "V2", "V1", "V2", "V1", "V2", "V3", "V2", "V1", "V3",
"V1", "V2", "V1", "V3", "V1", "V2", "V3", "V1", "V2", "V3",
"V1", "V2", "V2", "V1", "V2", "V1", "V2", "V1", "V2", "V3",
"V1", "V2", "V3", "V1", "V3", "V1", "V3", "V1", "V3", "V1",
"V3", "V1", "V3", "V1", "V2", "V3", "V2", "V1", "V3", "V1",
"V2", "V1", "V2", "V3", "V1", "V2", "V1", "V2", "V1", "V2",
"V1"), date = structure(c(18865, 18864, 18928, 18927, 18919,
18920, 18918, 18942, 18941, 18878, 18879, 18877, 18963, 18962,
18857, 18858, 18856, 18897, 18898, 18970, 18969, 18814, 18815,
18813, 18942, 18941, 18921, 18920, 18851, 18850, 18911, 18912,
18911, 18912, 18822, 18823, 18821, 18865, 18864, 18953, 18954,
18813, 18814, 18962, 18963, 18961, 18869, 18830, 18829, 18827,
18828, 18935, 18934, 18954, 18955, 18953, 18955, 18956, 18954,
18862, 18863, 18904, 18911, 18912, 18911, 18912, 18822, 18823,
18821, 18926, 18927, 18925, 18844, 18843, 18914, 18913, 18984,
18983, 18956, 18955, 18949, 18948, 18878, 18879, 18877, 18813,
18991, 18990, 18967, 18968, 18857, 18858, 18856, 18967, 18968,
18869, 18870, 18918, 18919, 18954), class = "Date"), ficol = c(NA,
"44", " 4", "60", "20", NA, "60", NA, NA, "20", NA, "60",
NA, "52", NA, NA, "36", "60", "60", NA, NA, "60", "24", "60",
NA, NA, NA, NA, NA, "16", "56", "16", "56", "16", "16", NA,
"60", NA, "44", "60", "60", "60", "60", "52", NA, "60", "60",
NA, NA, "60", "32", NA, "56", "60", NA, "60", NA, NA, "60",
"60", "60", "60", "56", "16", "56", "16", "16", NA, "60",
"60", "60", "60", NA, "16", NA, NA, NA, NA, NA, NA, NA, "44",
"20", NA, "60", "60", NA, NA, "60", "40", NA, NA, "36", "60",
"40", "60", "60", "60", "20", "60")), row.names = c(NA, -100L
), class = c("tbl_df", "tbl", "data.frame"), na.action = structure(c(`2` = 2L,
`5` = 5L, `11` = 11L, `17` = 17L, `24` = 24L, `26` = 26L, `32` = 32L,
`35` = 35L, `38` = 38L, `42` = 42L, `45` = 45L, `50` = 50L, `54` = 54L,
`57` = 57L, `61` = 61L, `63` = 63L, `65` = 65L, `69` = 69L, `71` = 71L,
`81` = 81L, `82` = 82L, `84` = 84L, `87` = 87L, `90` = 90L, `98` = 98L,
`101` = 101L, `104` = 104L, `107` = 107L, `110` = 110L, `115` = 115L,
`117` = 117L, `119` = 119L, `123` = 123L, `129` = 129L, `132` = 132L,
`135` = 135L, `150` = 150L, `155` = 155L, `159` = 159L, `161` = 161L,
`163` = 163L, `165` = 165L, `167` = 167L, `171` = 171L, `173` = 173L,
`178` = 178L, `180` = 180L, `186` = 186L, `199` = 199L, `201` = 201L,
`207` = 207L, `209` = 209L, `219` = 219L, `223` = 223L, `225` = 225L,
`228` = 228L, `231` = 231L, `233` = 233L, `238` = 238L, `240` = 240L,
`243` = 243L, `249` = 249L, `251` = 251L, `255` = 255L, `258` = 258L,
`261` = 261L, `265` = 265L, `267` = 267L, `270` = 270L, `273` = 273L,
`277` = 277L, `279` = 279L, `287` = 287L, `299` = 299L, `302` = 302L,
`306` = 306L, `327` = 327L, `328` = 328L, `330` = 330L, `334` = 334L,
`336` = 336L, `337` = 337L, `339` = 339L, `348` = 348L, `349` = 349L,
`351` = 351L, `355` = 355L, `357` = 357L, `359` = 359L, `362` = 362L,
`366` = 366L, `369` = 369L, `372` = 372L, `377` = 377L, `381` = 381L,
`382` = 382L, `384` = 384L, `387` = 387L, `390` = 390L, `397` = 397L,
`399` = 399L, `402` = 402L, `405` = 405L, `407` = 407L, `411` = 411L,
`414` = 414L, `420` = 420L, `429` = 429L, `444` = 444L, `450` = 450L,
`451` = 451L, `453` = 453L, `456` = 456L, `458` = 458L, `466` = 466L,
`468` = 468L, `472` = 472L, `474` = 474L, `483` = 483L, `487` = 487L,
`489` = 489L, `492` = 492L, `493` = 493L, `495` = 495L, `498` = 498L,
`499` = 499L, `501` = 501L, `509` = 509L, `514` = 514L, `516` = 516L,
`529` = 529L, `531` = 531L, `532` = 532L, `534` = 534L, `536` = 536L,
`541` = 541L, `543` = 543L, `552` = 552L, `555` = 555L, `563` = 563L,
`568` = 568L, `570` = 570L, `573` = 573L, `575` = 575L, `578` = 578L,
`581` = 581L, `583` = 583L, `585` = 585L, `587` = 587L, `594` = 594L,
`598` = 598L, `600` = 600L, `614` = 614L, `617` = 617L, `619` = 619L,
`621` = 621L, `624` = 624L, `628` = 628L, `630` = 630L, `631` = 631L,
`633` = 633L, `634` = 634L, `636` = 636L, `639` = 639L, `647` = 647L,
`653` = 653L, `656` = 656L, `658` = 658L, `660` = 660L, `665` = 665L,
`668` = 668L, `676` = 676L, `678` = 678L, `681` = 681L, `683` = 683L,
`687` = 687L, `689` = 689L, `693` = 693L, `704` = 704L, `706` = 706L,
`708` = 708L, `713` = 713L, `715` = 715L, `717` = 717L, `720` = 720L,
`723` = 723L, `725` = 725L, `732` = 732L, `733` = 733L, `735` = 735L,
`738` = 738L, `750` = 750L, `753` = 753L, `754` = 754L, `756` = 756L,
`762` = 762L, `771` = 771L, `773` = 773L, `782` = 782L, `784` = 784L,
`786` = 786L, `787` = 787L, `789` = 789L, `793` = 793L, `795` = 795L,
`798` = 798L, `800` = 800L, `816` = 816L, `817` = 817L, `819` = 819L,
`822` = 822L, `833` = 833L, `837` = 837L, `845` = 845L, `848` = 848L,
`851` = 851L, `853` = 853L, `855` = 855L, `861` = 861L, `872` = 872L,
`874` = 874L, `876` = 876L, `882` = 882L, `883` = 883L, `885` = 885L,
`888` = 888L, `889` = 889L, `891` = 891L, `896` = 896L, `900` = 900L,
`913` = 913L, `915` = 915L, `916` = 916L, `918` = 918L, `920` = 920L,
`926` = 926L, `929` = 929L, `951` = 951L, `954` = 954L, `974` = 974L,
`979` = 979L, `981` = 981L, `983` = 983L, `986` = 986L, `988` = 988L,
`990` = 990L, `991` = 991L, `993` = 993L, `996` = 996L, `998` = 998L,
`1002` = 1002L, `1010` = 1010L, `1017` = 1017L, `1021` = 1021L,
`1023` = 1023L, `1027` = 1027L, `1029` = 1029L, `1042` = 1042L,
`1044` = 1044L, `1050` = 1050L, `1054` = 1054L, `1056` = 1056L,
`1058` = 1058L, `1064` = 1064L, `1073` = 1073L, `1077` = 1077L,
`1078` = 1078L, `1080` = 1080L, `1090` = 1090L, `1092` = 1092L,
`1095` = 1095L, `1101` = 1101L, `1116` = 1116L, `1119` = 1119L,
`1122` = 1122L, `1126` = 1126L, `1128` = 1128L, `1132` = 1132L,
`1134` = 1134L, `1138` = 1138L, `1140` = 1140L, `1143` = 1143L,
`1144` = 1144L, `1146` = 1146L, `1158` = 1158L, `1160` = 1160L,
`1164` = 1164L, `1166` = 1166L, `1170` = 1170L, `1172` = 1172L,
`1174` = 1174L, `1176` = 1176L, `1180` = 1180L, `1182` = 1182L,
`1184` = 1184L, `1186` = 1186L, `1188` = 1188L, `1190` = 1190L,
`1201` = 1201L, `1203` = 1203L, `1206` = 1206L, `1212` = 1212L,
`1213` = 1213L, `1215` = 1215L, `1218` = 1218L, `1221` = 1221L,
`1225` = 1225L, `1227` = 1227L, `1233` = 1233L, `1235` = 1235L,
`1241` = 1241L, `1245` = 1245L, `1247` = 1247L, `1251` = 1251L,
`1259` = 1259L, `1262` = 1262L, `1265` = 1265L, `1267` = 1267L,
`1269` = 1269L, `1272` = 1272L, `1275` = 1275L, `1281` = 1281L,
`1286` = 1286L, `1289` = 1289L, `1292` = 1292L, `1301` = 1301L,
`1303` = 1303L, `1305` = 1305L, `1310` = 1310L, `1314` = 1314L,
`1323` = 1323L, `1326` = 1326L, `1342` = 1342L, `1344` = 1344L,
`1347` = 1347L, `1358` = 1358L, `1361` = 1361L, `1366` = 1366L,
`1368` = 1368L, `1370` = 1370L, `1373` = 1373L, `1379` = 1379L,
`1388` = 1388L, `1390` = 1390L, `1392` = 1392L, `1394` = 1394L,
`1396` = 1396L, `1398` = 1398L, `1401` = 1401L, `1403` = 1403L,
`1406` = 1406L, `1411` = 1411L, `1413` = 1413L, `1420` = 1420L,
`1422` = 1422L, `1424` = 1424L, `1427` = 1427L, `1429` = 1429L,
`1431` = 1431L, `1435` = 1435L, `1437` = 1437L, `1439` = 1439L,
`1443` = 1443L, `1449` = 1449L, `1455` = 1455L, `1457` = 1457L,
`1461` = 1461L, `1469` = 1469L, `1471` = 1471L, `1473` = 1473L,
`1478` = 1478L, `1480` = 1480L, `1482` = 1482L, `1483` = 1483L,
`1485` = 1485L, `1487` = 1487L, `1491` = 1491L, `1496` = 1496L,
`1501` = 1501L, `1503` = 1503L, `1506` = 1506L, `1507` = 1507L,
`1509` = 1509L, `1511` = 1511L, `1515` = 1515L, `1521` = 1521L,
`1525` = 1525L, `1527` = 1527L, `1529` = 1529L, `1531` = 1531L,
`1533` = 1533L, `1534` = 1534L, `1536` = 1536L, `1539` = 1539L,
`1556` = 1556L, `1566` = 1566L, `1575` = 1575L, `1578` = 1578L,
`1579` = 1579L, `1581` = 1581L, `1584` = 1584L, `1587` = 1587L,
`1595` = 1595L, `1598` = 1598L, `1604` = 1604L, `1611` = 1611L,
`1615` = 1615L, `1617` = 1617L, `1618` = 1618L, `1620` = 1620L,
`1624` = 1624L, `1626` = 1626L, `1632` = 1632L, `1633` = 1633L,
`1635` = 1635L, `1637` = 1637L, `1640` = 1640L, `1642` = 1642L,
`1644` = 1644L, `1646` = 1646L, `1649` = 1649L, `1653` = 1653L,
`1657` = 1657L, `1659` = 1659L, `1660` = 1660L, `1662` = 1662L,
`1667` = 1667L, `1671` = 1671L, `1673` = 1673L, `1676` = 1676L,
`1678` = 1678L, `1680` = 1680L, `1684` = 1684L, `1686` = 1686L,
`1689` = 1689L, `1698` = 1698L, `1705` = 1705L, `1707` = 1707L,
`1709` = 1709L, `1714` = 1714L, `1716` = 1716L, `1730` = 1730L,
`1732` = 1732L, `1734` = 1734L, `1740` = 1740L, `1742` = 1742L,
`1747` = 1747L, `1749` = 1749L, `1752` = 1752L, `1753` = 1753L,
`1755` = 1755L, `1764` = 1764L, `1769` = 1769L, `1774` = 1774L,
`1776` = 1776L, `1782` = 1782L, `1783` = 1783L, `1785` = 1785L,
`1790` = 1790L, `1792` = 1792L, `1794` = 1794L, `1797` = 1797L,
`1800` = 1800L, `1803` = 1803L, `1806` = 1806L, `1810` = 1810L,
`1812` = 1812L, `1817` = 1817L, `1822` = 1822L, `1824` = 1824L,
`1828` = 1828L, `1830` = 1830L, `1833` = 1833L, `1849` = 1849L,
`1851` = 1851L, `1856` = 1856L, `1859` = 1859L, `1863` = 1863L,
`1866` = 1866L, `1871` = 1871L, `1874` = 1874L, `1878` = 1878L,
`1879` = 1879L, `1881` = 1881L, `1884` = 1884L, `1890` = 1890L,
`1897` = 1897L, `1899` = 1899L, `1902` = 1902L, `1904` = 1904L,
`1907` = 1907L, `1914` = 1914L, `1915` = 1915L, `1917` = 1917L,
`1918` = 1918L, `1920` = 1920L, `1923` = 1923L, `1927` = 1927L,
`1929` = 1929L, `1932` = 1932L, `1935` = 1935L, `1939` = 1939L,
`1941` = 1941L, `1947` = 1947L, `1948` = 1948L, `1950` = 1950L,
`1951` = 1951L, `1953` = 1953L, `1958` = 1958L, `1961` = 1961L,
`1968` = 1968L, `1974` = 1974L, `1975` = 1975L, `1977` = 1977L,
`1979` = 1979L, `1982` = 1982L, `1985` = 1985L, `1988` = 1988L,
`1996` = 1996L, `1998` = 1998L, `1999` = 1999L, `2001` = 2001L,
`2010` = 2010L, `2012` = 2012L, `2020` = 2020L, `2022` = 2022L,
`2026` = 2026L, `2028` = 2028L, `2032` = 2032L, `2034` = 2034L,
`2039` = 2039L, `2044` = 2044L, `2046` = 2046L, `2052` = 2052L,
`2054` = 2054L, `2056` = 2056L, `2058` = 2058L, `2063` = 2063L,
`2066` = 2066L, `2072` = 2072L, `2076` = 2076L, `2079` = 2079L,
`2082` = 2082L, `2084` = 2084L, `2086` = 2086L, `2088` = 2088L,
`2091` = 2091L, `2093` = 2093L, `2097` = 2097L, `2098` = 2098L,
`2100` = 2100L, `2104` = 2104L, `2106` = 2106L, `2107` = 2107L,
`2109` = 2109L, `2111` = 2111L, `2114` = 2114L, `2126` = 2126L,
`2136` = 2136L, `2142` = 2142L, `2144` = 2144L, `2146` = 2146L,
`2148` = 2148L, `2153` = 2153L, `2160` = 2160L, `2162` = 2162L,
`2165` = 2165L, `2170` = 2170L, `2172` = 2172L, `2173` = 2173L,
`2175` = 2175L, `2180` = 2180L, `2182` = 2182L, `2184` = 2184L,
`2186` = 2186L, `2189` = 2189L, `2191` = 2191L, `2193` = 2193L,
`2198` = 2198L, `2200` = 2200L, `2202` = 2202L, `2205` = 2205L,
`2207` = 2207L, `2210` = 2210L, `2212` = 2212L, `2214` = 2214L,
`2215` = 2215L, `2217` = 2217L, `2220` = 2220L, `2221` = 2221L,
`2223` = 2223L, `2225` = 2225L, `2229` = 2229L, `2232` = 2232L,
`2234` = 2234L, `2236` = 2236L, `2238` = 2238L, `2239` = 2239L,
`2241` = 2241L, `2245` = 2245L, `2247` = 2247L, `2249` = 2249L,
`2251` = 2251L, `2253` = 2253L, `2262` = 2262L, `2264` = 2264L,
`2268` = 2268L, `2270` = 2270L, `2274` = 2274L, `2279` = 2279L,
`2288` = 2288L, `2292` = 2292L, `2296` = 2296L, `2298` = 2298L,
`2304` = 2304L, `2308` = 2308L, `2310` = 2310L, `2313` = 2313L,
`2321` = 2321L, `2324` = 2324L, `2327` = 2327L, `2329` = 2329L,
`2331` = 2331L, `2334` = 2334L, `2336` = 2336L, `2339` = 2339L,
`2342` = 2342L, `2344` = 2344L, `2346` = 2346L, `2348` = 2348L,
`2350` = 2350L, `2352` = 2352L, `2353` = 2353L, `2355` = 2355L,
`2358` = 2358L, `2361` = 2361L, `2365` = 2365L, `2367` = 2367L,
`2373` = 2373L, `2375` = 2375L, `2381` = 2381L, `2384` = 2384L,
`2387` = 2387L, `2391` = 2391L, `2393` = 2393L, `2395` = 2395L,
`2397` = 2397L, `2399` = 2399L, `2401` = 2401L, `2403` = 2403L,
`2405` = 2405L, `2410` = 2410L, `2412` = 2412L, `2413` = 2413L,
`2415` = 2415L, `2423` = 2423L, `2425` = 2425L, `2427` = 2427L,
`2429` = 2429L, `2435` = 2435L, `2437` = 2437L, `2439` = 2439L,
`2442` = 2442L, `2444` = 2444L, `2446` = 2446L, `2448` = 2448L,
`2449` = 2449L, `2451` = 2451L, `2455` = 2455L, `2457` = 2457L,
`2462` = 2462L, `2466` = 2466L, `2467` = 2467L, `2469` = 2469L,
`2474` = 2474L, `2479` = 2479L, `2481` = 2481L, `2483` = 2483L,
`2485` = 2485L, `2487` = 2487L, `2496` = 2496L, `2498` = 2498L,
`2500` = 2500L, `2502` = 2502L, `2507` = 2507L, `2509` = 2509L,
`2511` = 2511L, `2514` = 2514L, `2518` = 2518L, `2520` = 2520L,
`2522` = 2522L, `2524` = 2524L, `2526` = 2526L, `2528` = 2528L,
`2530` = 2530L, `2532` = 2532L, `2533` = 2533L, `2535` = 2535L
), class = "omit"))
What I have tried:
library(data.table)
library(lubridate)
dt <- data.table(dt)
dt[, c("date", "ficol") := .(ymd(date), as.numeric(ficol))]
# Sort by ID and visit
setorder(dt, Included.y, vis)
# Flag open appointment
dt[, open := ifelse(is.na(ficol) | ficol<60, TRUE, FALSE)]
# Select first open appointment and update ficol
out_dt <- dt[open==TRUE, .SD[1], by = c("Included.y")]
out_dt[, ficol := ifelse(is.na(ficol), 2, ficol+2)]
out_dt[, open := NULL]
out_dt
However, this solution would only work if each date would only be selected once. It does not accumulate the ficol count and therefor does not 'stop' after the ficol column has reached 60..
Submit an article or tip