Click here to Skip to main content
15,070,173 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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..
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900