CODEWITHSUNDEEP
r
user113156
user113156

Reputation: 7107

restructuring data based on matching condition of column and year

I have some data which looks like:

# A tibble: 200 x 10
   status LastYear Var_1_LastYear Var_1_LastYear_… Var_1_LastYear_… Var_1_LastYear_…
    <int>    <int>          <int>            <int>            <int>            <int>
 1      0     2016         410161           356697           326280           352698
 2      0     2015         286384           452049           644232          1090170
 3      0     2016         112513            97947           100756            65112
 4      0     2013          63577            56222            29615            30678

I am trying to organise it into a long format but with some additional conditions.

The LastYear column is the most recent data I have in the data set. This column aligns up with the Var_1_LastYear column, so in row 1 for 2016 the data for the Var_1_LastYear is 410161, then the data for this observation for 2015 is the data in column Var_1_LastYear_but_1 which is 356697, the data for this observation for 2014 is in column Var_1_LastYear_but_2 which is 326280, finally the data for 2013 for this observation is in Var_1_LastYear_but_3 and that is all the data I have on this observation.

The same thing applies to row 4 in the data for 2013. The most recent data I have is 2013 and located in Var_1_LastYear 63577, the data for 2012 is in Var_1_LastYear_but_1, the data for 2011 is Var_1_LastYear_but_2 and finally the data for 2010 is Var_1_LastYear_but_3.

Each row is a unique observation in the data.

I want to structure the data such that the years align with the correct columns.

Data:

data <- structure(list(status = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), 
    LastYear = c(2016L, 2016L, 2016L, 2015L, 2016L, 2016L, 2016L, 
    2016L, 2016L, 2016L, 2014L, 2015L, 2016L, 2016L, 2016L, 2016L, 
    2016L, 2016L, 2016L, 2016L, 2016L, 2013L, 2015L, 2016L, 2016L, 
    2016L, 2016L, 2016L, 2016L, 2016L, 2014L, 2016L, 2016L, 2016L, 
    2016L, 2016L, 2013L, 2013L, 2016L, 2015L, 2016L, 2015L, 2016L, 
    2016L, 2016L, 2016L, 2014L, 2016L, 2016L, 2015L, 2016L, 2016L, 
    2013L, 2016L, 2016L, 2016L, 2015L, 2016L, 2016L, 2016L, 2016L, 
    2015L, 2015L, 2015L, 2014L, 2014L, 2013L, 2016L, 2016L, 2014L, 
    2016L, 2016L, 2016L, 2016L, 2015L, 2016L, 2016L, 2016L, 2016L, 
    2016L, 2016L, 2016L, 2016L, 2015L, 2016L, 2016L, 2016L, 2016L, 
    2015L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 
    2016L, 2016L, 2015L, 2016L, 2016L, 2016L, 2015L, 2016L, 2014L, 
    2015L, 2016L, 2016L, 2014L, 2016L, 2015L, 2016L, 2015L, 2016L, 
    2016L, 2013L, 2015L, 2016L, 2016L, 2016L, 2016L, 2015L, 2016L, 
    2016L, 2016L, 2016L, 2016L, 2015L, 2016L, 2016L, 2013L, 2016L, 
    2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2015L, 
    2013L, 2014L, 2015L, 2016L, 2015L, 2016L, 2013L, 2016L, 2016L, 
    2016L, 2016L, 2016L, 2016L, 2015L, 2016L, 2016L, 2014L, 2016L, 
    2016L, 2016L, 2015L, 2015L, 2015L, 2016L, 2016L, 2016L, 2014L, 
    2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 
    2014L, 2016L, 2015L, 2016L, 2016L, 2015L, 2016L, 2015L, 2015L, 
    2016L, 2015L, 2013L, 2015L, 2015L, 2016L, 2016L, 2015L, 2016L, 
    2016L, 2016L, 2016L, 2016L), Var_1_LastYear = c(199257L, 
    301210L, 170900L, 2239772L, 52910L, 953789L, 67052L, 267943L, 
    461103L, 8456077L, 208388L, 406534L, 317003L, 4379428L, 243391L, 
    16892427L, 416119L, 319789L, 85996L, 270020L, 55331L, 70543L, 
    35698L, 139125L, 61551L, 244786L, 77387L, 617342L, 186215L, 
    126518L, 42840L, 723980L, 317351L, 112513L, 563607L, 307839L, 
    71254L, 1416394L, 205934L, 286384L, 27757L, 2854697L, 410161L, 
    58574L, 8369054L, 48553L, 159829L, 13826974L, 119123L, 427598L, 
    917315L, 1062584L, 68595L, 521085L, 31336L, 36785L, 133582L, 
    221685L, 49721L, 771010L, 26293L, 462787L, 159521L, 66911L, 
    4941803L, 159410L, 557002L, 11688545L, 1637426L, 10315734L, 
    1149211L, 558498L, 122581L, 2437304L, 298769L, 1411080L, 
    163325L, 782719L, 286461L, 590687L, 1037064L, 1078523L, 48064L, 
    1367594L, 11860680L, 638107L, 514337L, 438645L, 1500607L, 
    43157L, 385815L, 46662L, 54966L, 771953L, 705613L, 1306056L, 
    1499402L, 225737L, 2890535L, 1395717L, 77690L, 381913L, 433165L, 
    16789988L, 170126L, 113108L, 94165L, 402913L, 121485L, 33308L, 
    65202L, 508432L, 599898L, 528011L, 1205410L, 1722197L, 63577L, 
    526347L, 1267673L, 143947L, 185402L, 433910L, 222679L, 42464L, 
    56606L, 126645L, 103834L, 517620L, 389908L, 249135L, 99257L, 
    3107395L, 204033L, 2223711L, 848002L, 324405L, 42884L, 34501L, 
    885989L, 81108L, 428102L, 120099L, 1366935L, 63683L, 1098268L, 
    284951L, 73020L, 262421L, 80642L, 3871102L, 3103860L, 55295L, 
    103391L, 54979L, 76878L, 77682L, 865256L, 195667L, 55260L, 
    1531978L, 52022L, 381324L, 172470L, 1334880L, 217369L, 221978L, 
    805525L, 2921348L, 661650L, 1752117L, 170261L, 7045756L, 
    30098L, 963802L, 847141L, 200031L, 575447L, 4959868L, 28020L, 
    3879228L, 469158L, 2199150L, 3477661L, 100622L, 7621237L, 
    2955868L, 58467L, 220618L, 1005937L, 464702L, 7768360L, 37100L, 
    124077L, 132463L, 1459294L, 31676L, 854780L, 62208L, 109772L, 
    8121963L), Var_1_LastYear_but_1 = c(211087L, 317156L, 196438L, 
    2724029L, 182671L, 895412L, NA, 208708L, 445366L, 9097717L, 
    185920L, 443193L, 262895L, 4216913L, 248345L, 15909158L, 
    423692L, 627790L, 110242L, 316563L, 48692L, 69201L, 16867L, 
    135680L, 57609L, 243398L, 49985L, NA, 214015L, 70284L, 472116L, 
    794404L, 297404L, 97947L, 388316L, NA, NA, 1430720L, 119737L, 
    452049L, 34663L, 2384518L, 356697L, 62240L, 8420230L, 42650L, 
    3000L, 11645279L, 125727L, 418228L, 928146L, 1060722L, 66400L, 
    349173L, 23321L, 38516L, 137455L, 100945L, 34328L, 688301L, 
    31080L, 425577L, 174960L, 68900L, 4666608L, 154915L, 633215L, 
    12295182L, 1152700L, 11336396L, 1164880L, 535447L, 67738L, 
    1908535L, NA, 1193756L, 174725L, 747794L, 211082L, 518026L, 
    1016343L, 968785L, 48422L, 1631038L, 16794137L, 676570L, 
    364069L, 357901L, 1471924L, 41694L, 352001L, 46664L, 42000L, 
    NA, 878970L, 1337205L, 1062705L, 236650L, 2914499L, 1261530L, 
    258249L, 345978L, 378896L, 18282153L, 163564L, 137695L, 94309L, 
    434244L, 3000L, 67813L, 78429L, 149478L, 905368L, 630653L, 
    1285011L, 1818847L, 56222L, 531539L, 1204754L, 146776L, 131956L, 
    536344L, 337611L, 170439L, 38087L, 124170L, 101035L, 268465L, 
    389908L, 298218L, 101496L, 22143496L, 222316L, 2666536L, 
    885633L, 520221L, 46227L, 32651L, 838993L, 82239L, 433475L, 
    814L, 2093817L, 51975L, 1130301L, 308679L, 73020L, 294416L, 
    94070L, 3187237L, 3193600L, 58912L, 108025L, NA, 27195L, 
    74981L, 707043L, 200562L, 98870L, 1335141L, 79137L, 432448L, 
    164301L, 1262004L, 203458L, NA, 755577L, 1542414L, 469671L, 
    1558813L, 104902L, 6927942L, 33859L, 814309L, 850912L, 178851L, 
    633667L, 4682055L, NA, 3753631L, 3100L, 2129230L, 3511106L, 
    85566L, 4326128L, 3290101L, NA, 183701L, 1068508L, 452242L, 
    7876556L, 57917L, 195715L, 132810L, 1449845L, 28613L, 658685L, 
    93363L, 98689L, 8122549L), Var_1_LastYear_but_2 = c(198939L, 
    310412L, 216471L, 180511L, 98335L, 804071L, NA, 197319L, 
    431614L, 9333272L, NA, 440676L, 251069L, 4140545L, 258777L, 
    14901779L, 236142L, 878651L, 105532L, 411366L, 45075L, 56258L, 
    NA, 152836L, 52971L, 222274L, 34686L, NA, 204642L, 59504L, 
    543096L, 675344L, 318440L, 100756L, 320164L, NA, NA, 1459473L, 
    69352L, 644232L, 40221L, 2416042L, 326280L, NA, 4940784L, 
    41578L, NA, 10684773L, 155485L, 542699L, 914252L, 1239645L, 
    66417L, 115228L, NA, 30493L, 148389L, 75821L, 22638L, 675043L, 
    29703L, 165362L, 165156L, 104908L, 3612454L, 96359L, 717843L, 
    12310693L, 1049113L, 12364506L, 1063303L, 565873L, 80678L, 
    1535509L, NA, 939357L, 166241L, 748607L, 96392L, 383708L, 
    1256475L, 823791L, 40511L, 1779818L, 18557147L, 713896L, 
    336811L, 337781L, 1383211L, 38726L, 391802L, 99332L, NA, 
    NA, 1081890L, 1401853L, 1106101L, 168082L, 2979207L, 876554L, 
    245802L, 312388L, 341693L, 19068937L, 160643L, 158033L, 94808L, 
    NA, NA, 149114L, 68892L, NA, NA, 490798L, 1298992L, 1690013L, 
    29615L, 554397L, 1183978L, 100472L, 3005L, 528728L, 226825L, 
    22611L, 118253L, 109319L, 102298L, 266590L, 389908L, 329276L, 
    97398L, 16634946L, 116214L, 1990332L, 863484L, 765124L, 51019L, 
    29700L, 719787L, 44989L, 374710L, 1178L, 2211102L, 115620L, 
    1145265L, 302550L, 73020L, 285094L, 104497L, 3057043L, 3145602L, 
    87951L, 112181L, NA, 18141L, 70805L, 658336L, 115908L, 97652L, 
    1319692L, 72702L, 467805L, 162343L, 1393888L, NA, NA, 710956L, 
    1536328L, 353983L, 1563529L, 90936L, 6833348L, 117571L, 830328L, 
    844702L, 183553L, 648204L, 4567672L, NA, 3688229L, NA, 2132990L, 
    3546924L, 43652L, 2040732L, 3735372L, NA, 176666L, 1115924L, 
    388868L, 7548700L, 160260L, 196646L, 134385L, 1603156L, 23451L, 
    792151L, 43510L, 71248L, 8122691L), Var_1_LastYear_but_3 = c(189440L, 
    356483L, 236108L, 470191L, 71903L, 1060099L, NA, 126850L, 
    371714L, 8754070L, NA, 375667L, 247230L, 4014510L, 267019L, 
    12451191L, 202228L, 1774761L, 91724L, 367312L, 39855L, 40185L, 
    NA, 123030L, 58147L, 207968L, NA, NA, 201076L, NA, 520240L, 
    839859L, 610033L, 65112L, NA, NA, NA, 1440484L, 42381L, 1090170L, 
    24459L, 2356778L, 352698L, NA, 5193031L, 46551L, NA, 9276946L, 
    165252L, 523792L, 920436L, 1280899L, 73835L, 36158L, NA, 
    17214L, 139789L, 64258L, 30287L, 612223L, 28987L, 104377L, 
    176904L, 81415L, 3405983L, 163800L, 867292L, 11108846L, 1499367L, 
    12802758L, 1081600L, 499539L, 105082L, 1913615L, NA, 569541L, 
    178553L, 793745L, 325610L, NA, 1364817L, 764961L, 37733L, 
    1977591L, 29661637L, 744053L, 234814L, 272723L, 1156259L, 
    40597L, 345588L, 127077L, NA, NA, 1581767L, 1497332L, 978981L, 
    1849L, 3149148L, 913866L, 238679L, 325297L, 327323L, NA, 
    175950L, 161687L, 294834L, NA, NA, 226188L, 43863L, NA, NA, 
    630984L, 1362443L, 1719703L, 30678L, 650570L, 919316L, 114914L, 
    NA, 477524L, 220807L, NA, 129904L, 102754L, 102809L, 249521L, 
    389908L, 249519L, 98512L, 9920877L, 77684L, 1853507L, 861956L, 
    966070L, 47428L, 35393L, 647899L, 45113L, 349630L, 1290L, 
    2527773L, 142131L, 1154903L, 357104L, 73020L, 293766L, 96540L, 
    3283709L, 3162046L, 54776L, 117010L, NA, NA, 79690L, 631184L, 
    101125L, 100118L, 1286678L, 89823L, 426553L, 860L, 1099116L, 
    NA, NA, 680844L, 1524862L, 265850L, 1660273L, 129240L, 7307019L, 
    62900L, 721437L, 839573L, 202847L, 639819L, 4555511L, NA, 
    3762434L, NA, 2027136L, 4520073L, 46824L, 2026769L, 3310178L, 
    NA, NA, 1123716L, 356413L, 7135340L, 151674L, 295405L, NA, 
    1703376L, 18354L, 827198L, 69649L, 38651L, 8122079L), Var_2_LastYear = c(49646L, 
    128433L, 9865L, 67318L, -79144L, 448904L, 3000L, 173186L, 
    385545L, 430198L, -108783L, 109720L, 32716L, 1276693L, 104658L, 
    12290151L, 43244L, 37196L, -14816L, 143850L, -21564L, 10050L, 
    -7102L, -35772L, 50741L, 115876L, 35416L, 9342L, 118473L, 
    13508L, -377548L, -31704L, 97534L, 67185L, 19403L, -134569L, 
    1254L, 985856L, 23700L, 265418L, -67457L, 912184L, 2295L, 
    -11570L, 3277340L, -9250L, 4593L, 4784553L, -31076L, 92667L, 
    113356L, -47770L, 47103L, 306303L, 2950L, 17114L, -3057L, 
    -3628L, 35341L, 739782L, 23699L, -21296L, -31412L, -17596L, 
    163170L, 57668L, -187349L, 4604485L, 286093L, 4380082L, 715947L, 
    440036L, 8052L, 888755L, 5134L, -142197L, 47996L, 781690L, 
    93575L, 380374L, 572574L, 564355L, 47941L, 642593L, 7432691L, 
    203696L, 341478L, 272314L, 962008L, 23525L, 156283L, -210976L, 
    46667L, 431407L, 269826L, 1159844L, 691914L, -515780L, -156117L, 
    280504L, -80700L, -50035L, 151352L, 12686708L, 81145L, 103031L, 
    71997L, 141768L, 48587L, 33308L, 32785L, -83341L, 387280L, 
    153305L, 1092158L, 1151317L, -24286L, 37749L, 404024L, 65597L, 
    41130L, 330090L, 64399L, -20704L, 4649L, 1536L, -5125L, 23733L, 
    176322L, 178525L, 22901L, 329402L, 123543L, 651761L, 654218L, 
    170449L, -13418L, 12935L, 578437L, -3907L, 53006L, 84394L, 
    193843L, -63106L, 387456L, 191425L, 56520L, 74978L, 2949L, 
    1614682L, 3068099L, -31043L, 41523L, -380L, 5981L, -51001L, 
    469377L, -335128L, -15710L, 1361539L, 31458L, 150582L, 15747L, 
    940690L, 30267L, 50246L, 218948L, 1523363L, 195883L, 950678L, 
    124491L, 389884L, 7413L, 699554L, 611315L, 62183L, 565239L, 
    4408778L, -2498L, 3424425L, -15167L, 1776826L, 2173977L, 
    7714L, 1303927L, 511051L, 9872L, 116048L, 271848L, 122751L, 
    5582408L, -70993L, -29643L, 128152L, 913716L, 26204L, 442360L, 
    4979L, -12781L, 3145222L), Var_2_LastYear_but_1 = c(45648L, 
    156133L, 33365L, 66813L, -183861L, 331702L, NA, 146753L, 
    398854L, 405303L, -74987L, 97180L, 9101L, 1078235L, 82939L, 
    10137230L, 44251L, 67558L, -3566L, 154033L, -21746L, -160L, 
    3619L, -14615L, 43178L, 112398L, 26322L, NA, 113383L, 11221L, 
    -217277L, -48388L, 94694L, 66716L, 12140L, NA, NA, 997325L, 
    20194L, 403241L, -24236L, 889393L, 222L, -1607L, 3319046L, 
    -20422L, 3000L, 4821359L, -32426L, 80839L, 144138L, -56121L, 
    47711L, 317529L, 2811L, 11409L, -3821L, -4028L, 20345L, 670288L, 
    7881L, -30545L, -34797L, 5536L, 186201L, 46015L, -155108L, 
    4432967L, 334588L, 4733758L, 697193L, 446852L, 5339L, 625498L, 
    NA, -78394L, 52055L, 747794L, 85540L, 305429L, 554324L, 470236L, 
    48299L, 1029952L, 8406421L, 194121L, 264060L, 80562L, 924821L, 
    23464L, 154136L, -220679L, 42000L, NA, 253206L, 1181118L, 
    552336L, -425948L, -137678L, 230640L, -4762L, -59113L, 144068L, 
    13522161L, 77068L, 104438L, 72078L, 160011L, 3000L, 66392L, 
    31631L, -43961L, 605703L, 247496L, 1086241L, 1010364L, -24447L, 
    98386L, 404028L, 51365L, 20331L, 380118L, 76171L, 15584L, 
    5121L, 1791L, -17591L, -207352L, 176322L, 173395L, 22222L, 
    79208L, 111782L, 605989L, 684252L, 237846L, -4881L, 11840L, 
    524639L, -3067L, 49197L, -1726L, 181583L, -92496L, 405019L, 
    197510L, 56520L, 103544L, 2399L, 1506043L, 3078618L, -29239L, 
    40015L, NA, -7590L, -31940L, 406129L, -256601L, 32538L, 1221181L, 
    49559L, 293726L, 14351L, 880716L, 16333L, NA, 181027L, 1523363L, 
    185618L, 871356L, 59251L, 320291L, 7556L, 695549L, 614428L, 
    52392L, 628198L, 4269860L, NA, 3319670L, 3100L, 1737952L, 
    2202070L, -1365L, 926675L, 711371L, NA, 113784L, 328476L, 
    47430L, 5573294L, -46194L, -47188L, 124740L, 1003347L, 23848L, 
    461043L, 2674L, -3393L, 3145838L), Var_2_LastYear_but_2 = c(44890L, 
    172580L, 24924L, 75188L, -227111L, 282569L, NA, 93163L, 396835L, 
    791506L, NA, 85990L, -7451L, 1054404L, -79750L, 10515140L, 
    40249L, 96821L, 7104L, 220060L, -17231L, -3474L, NA, 4912L, 
    41868L, 62691L, 3852L, NA, 109940L, 6783L, -199238L, -100474L, 
    94283L, 66515L, 122L, NA, NA, 1005490L, 8106L, 546470L, -6590L, 
    871118L, 3535L, NA, 3711842L, -24510L, NA, 4340533L, -7978L, 
    87012L, 144416L, -60904L, 49598L, 106541L, NA, 8691L, -233L, 
    -4664L, 14813L, 647803L, 6323L, -48178L, -27191L, 3992L, 
    210169L, 28737L, -104907L, 4157279L, 320791L, 4842772L, 668432L, 
    472300L, 3489L, 535942L, NA, -145130L, 51646L, 747760L, 33724L, 
    285500L, 488586L, 384804L, 10714L, 1355881L, 62003L, 178490L, 
    245211L, 75354L, 813224L, 23513L, 150761L, -165854L, NA, 
    NA, 360161L, 1274505L, 593487L, -332120L, -15798L, -137985L, 
    35210L, -57334L, 141486L, 13660005L, 74220L, 103780L, 72545L, 
    NA, NA, 132512L, 13326L, NA, NA, 213980L, 1090801L, 885777L, 
    -28352L, 103021L, 154423L, 23772L, 3005L, 430368L, 69265L, 
    14835L, 75334L, 2136L, -14032L, -228632L, 176322L, 146620L, 
    13331L, 71855L, 79016L, 564273L, 673481L, 207784L, -5008L, 
    9230L, 454197L, 17176L, 46277L, -35609L, 430928L, -46740L, 
    413999L, 193942L, 56520L, 103486L, 2051L, 1438119L, 3061827L, 
    -32672L, 39104L, NA, 3579L, -16713L, 389473L, -253558L, 40662L, 
    1222272L, 47294L, 218917L, 13552L, 838724L, NA, NA, 168592L, 
    1523845L, 169364L, 793303L, 50339L, 276138L, 8486L, 683470L, 
    608330L, 53204L, 643103L, 4211806L, NA, 3205295L, NA, 1631983L, 
    2188790L, 1922L, 390013L, 564932L, NA, 108697L, 338917L, 
    83823L, 5557917L, 39344L, -50592L, 121330L, 1136722L, 19168L, 
    463508L, -42308L, 9235L, 3146312L), Var_2_LastYear_but_3 = c(44564L, 
    165347L, 16001L, 44878L, -89668L, 350719L, NA, 57283L, 286586L, 
    515336L, NA, 78219L, -22066L, 1072074L, -56310L, 9967100L, 
    36781L, 118878L, 5221L, 177792L, -18858L, -5352L, NA, 3387L, 
    -4776L, 59587L, NA, NA, 106186L, NA, -183684L, -11036L, 391935L, 
    33360L, NA, NA, NA, 1009992L, 5973L, 503047L, -10607L, 848134L, 
    2877L, NA, 4371942L, -27543L, NA, 4503522L, -19411L, 103081L, 
    172825L, 3378L, 45190L, 5702L, NA, 6576L, 3844L, -3550L, 
    13727L, 597423L, 5272L, -50490L, -14111L, 1615L, 248531L, 
    32215L, -15384L, 3943388L, 291299L, 4794914L, 649755L, 410680L, 
    1953L, 437106L, NA, -378817L, 50984L, 792667L, 136849L, NA, 
    423414L, 346300L, 5275L, 1758743L, -481552L, 164183L, 137740L, 
    51846L, 645266L, 24459L, 146437L, -162076L, NA, NA, 467848L, 
    1364565L, 582905L, -313615L, 408723L, -86206L, 108227L, -45731L, 
    139706L, NA, 73831L, 110152L, 84709L, NA, NA, 127276L, -11452L, 
    NA, NA, 235620L, 1039951L, 717239L, -17008L, 101981L, 79327L, 
    42473L, NA, 371713L, 67008L, NA, 97570L, 2570L, -4354L, -257425L, 
    176322L, 117792L, 11268L, 64475L, 36503L, 525353L, 666825L, 
    235894L, -5180L, 9689L, 369367L, 16572L, 43892L, -35609L, 
    389306L, 15957L, 436397L, 235494L, 56520L, 97448L, 1712L, 
    1364555L, 3063415L, -57488L, 37541L, NA, NA, 14364L, 375278L, 
    -255212L, 42172L, 1179363L, 75535L, 226539L, 860L, 665703L, 
    NA, NA, 164817L, 1517672L, 144825L, 871473L, 65822L, 235132L, 
    6787L, 677731L, 603364L, 113402L, 635736L, 4184338L, NA, 
    3154865L, NA, 1518754L, 2192465L, 10492L, 451723L, 325724L, 
    NA, NA, 321792L, 131421L, 5526775L, 50613L, -47657L, NA, 
    1149931L, 14193L, 466855L, -16235L, 7652L, 3152323L)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -200L))

Upvotes: 1

Views: 38

Answers (1)

dario
dario

Reputation: 6485

Solution using dplyr and tidyr:

library(dplyr)
library(tidyr)
df %>% 
  pivot_longer(cols = -c(status, LastYear),
               names_to = "TimeDiff",
               names_prefix = "Var_\\d_LastYear(_but_)*") %>% 
  mutate(year = LastYear - as.numeric(TimeDiff),
         year = ifelse(is.na(year), LastYear, year)) %>% 
  select(status, year, value)

Returns:

 # A tibble: 800 x 3
    status  year  value
     <int> <dbl>  <int>
 1      0  2016  49646
 2      0  2015  45648
 3      0  2014  44890
 4      0  2013  44564
 5      0  2016 128433
 6      0  2015 156133
 7      0  2014 172580
 8      0  2013 165347
 9      0  2016   9865
10      0  2015  33365

Explanation:

First we bring the data (except columns status and LastYear into long form and remove all the characters except the numbers indicating the "Time difference from LastYear".

Then we substract TimeDiff from LastYear to get the year the value belongs to. But there are going to be some NA values from rows where TimeDiff is "". We replace them with the value from LastYear.

Finally we select the columns we want to keep in the correct order.

Edit:

Solution that also accounts for multiple Varibles in column names:

library(dplyr)
library(tidyr)
df %>% 
  pivot_longer(cols = -c(status, LastYear),
               names_to = c("Var", "TimeDiff"),
               names_pattern = "(Var_\\d)_LastYear(.*)") %>% 
  mutate(TimeDiff = gsub("[^0-9]", "", TimeDiff)) %>% 
  mutate(year = LastYear - as.numeric(TimeDiff),
         year = ifelse(is.na(year), LastYear, year)) %>% 
  select(status, year, value)

Returns:

 # A tibble: 1,600 x 4
   status  year Var    value
    <int> <dbl> <chr>  <int>
 1      0  2016 Var_1 199257
 2      0  2015 Var_1 211087
 3      0  2014 Var_1 198939
 4      0  2013 Var_1 189440
 5      0  2016 Var_2  49646
 6      0  2015 Var_2  45648
 7      0  2014 Var_2  44890
 8      0  2013 Var_2  44564
 9      0  2016 Var_1 301210
10      0  2015 Var_1 317156
# … with 1,590 more rows

Upvotes: 2

Related Questions