Help Finding Differences Between Many Columns in a Dataframe

Hello,

I am a beginner in R and am having some trouble performing operations on my data. I have a dataframe whose columns contain three types of data, "applcn_ic", "admssn_ic", and "enrlt_ic", for each year for 12 years. (All values are integers, and I have other unrelated columns in the dataframe as well.) So I have columns "applcn_ic2001" through "applcn_ic2013", "admssn_ic2001" through "admssn_ic2013", and "enrlt_ic2001" through "enrlt_ic2013". For each type of data, I would like to find the difference between consecutive years and save that as a new variable (e.g. save "applcn_ic2005" minus "applcn_ic2004" as "applcn_diff_2005"). How do I do that?

Thanks!

The package dplyr makes this a cinch! Assuming your dataframe is named 'df1' the following code should do it.

df1 <- df1 %>%
  mutate(., applcn_diff_2005 = applcn_ic2005 - applcn_ic2004,
  applcn_diff_2006 = applcn_ic2006  - applcn_ic2005,
. . . etc)
2 Likes

Thanks for your answer, cmeuli07! I have a lot of differences to calculate and was wondering if there is an easier way of doing this, say with for loops, apply functions, or maps instead?

I'm sure there will be if you produce a reproducible example:

as martin.R said, it'd be easier to do if you provided data for a reprex, but here's a simplified version. Replace the variable a# with your variable names and make sure that i is in first year:(most_recent_year -1)

This is base R, and not necessarily pretty, but it gets the job done:

df1 <- data.frame(a1 = rep(1, 9),
                  a2 = rep(3, 9),
                  a3 = rep(13, 9),
                  a4 = rep(54, 9),
                  a5 = rep(85, 9),
                  a6 = rep(16, 9),
                  a7 = rep(77, 9),
                  a8 = rep(88, 9),
                  a9 = rep(9, 9))

for(i in 1:8){
  df1[[paste0("a",i+1,"_diff")]] <- df1[[paste0("a", i+1)]] - df1[[paste0("a", i)]] 
}

Thanks, that's very helpful! Here is most of my code (the last enrollment column didn't fit):

data <- read.csv("~/Downloads/joint_table.csv")

data
#>     X               instnm applcn_ic2013 admssn_ic2013 enrlt_ic2013
#> 1   1        Arizona State         21770         17465         7171
#> 2   2               Auburn         15745         13027         3726
#> 3   3               Baylor         29249         16809         3190
#> 4   4              Clemson         18500         10706         3463
#> 5   5                 Duke         30374          4077         1714
#> 6   6        Florida State         29579         16803         6048
#> 7   7         Georgia Tech         14645          8045         3044
#> 8   8              Indiana         37826         27300         7604
#> 9   9           Iowa State         16539         13648         5366
#> 10 10         Kansas State          9839          9437         3821
#> 11 11                  LSU         16169         12326         5725
#> 12 12       Michigan State         31479         21610         8061
#> 13 13    Mississippi State         11191          7254         3156
#> 14 14 North Carolina State         20700         10353         4398
#> 15 15         Northwestern         32060          4912         2037
#> 16 16           Ohio State         31359         17413         7130
#> 17 17       Oklahoma State         11064          8411         3872
#> 18 18         Oregon State         12414          9799         3516
#> 19 19           Penn State         47552         25772         7649
#> 20 20               Purdue         31083         18779         6422
#> 21 21             Stanford         38828          2208         1677
#> 22 22           Texas Tech         19170         12709         4785
#> 23 23              Alabama         30975         17515         6454
#> 24 24            Tennessee         14396         10435         4276
#> 25 25                Texas         38161         15335         7249
#> 26 26              Arizona         26329         20251         7401
#> 27 27             Arkansas         18908         11076         4339
#> 28 28           California         61717         11108         4162
#> 29 29                 UCLA         72676         15981         5620
#> 30 30              Florida         27107         12618         6373
#> 31 31              Georgia         18458         10352         4936
#> 32 32             Illinois         33203         20716         7329
#> 33 33                 Iowa         21642         17363         4460
#> 34 34               Kansas         12389         11433         3771
#> 35 35             Kentucky         19810         13592         4702
#> 36 36             Maryland         26247         12333         4020
#> 37 37             Michigan         46813         15570         6200
#> 38 38            Minnesota         43048         19121         5544
#> 39 39          Mississippi         14258         11484         3582
#> 40 40       North Carolina         28437          7847         3915
#> 41 41             Oklahoma         10991          8841         4052
#> 42 42               Oregon         21263         15770         4031
#> 43 43       South Carolina         23429         14199         4625
#> 44 44  Southern California         47358          9395         2922
#> 45 45             Virginia         28984          8691         3520
#> 46 46           Washington         30199         16679         6253
#> 47 47            Wisconsin         23324         15841         6279
#> 48 48           Vanderbilt         31099          3963         1613
#> 49 49          Wake Forest         11121          3915         1232
#> 50 50     Washington State         14887         12219         4163
#>    applcn_ic2012 admssn_ic2012 enrlt_ic2012 applcn_ic2011 admssn_ic2011
#> 1          29722         26425         9254         29771         25795
#> 2          17463         13486         3852         18323         12827
#> 3          27828         16879         3254         38960         15451
#> 4          17072         10803         2935         16865          9724
#> 5          28145          3938         1724         25462          4196
#> 6          30040         16124         5738         28313         16561
#> 7          14088          7210         2695         13495          6976
#> 8          35247         26228         7613         35218         25455
#> 9          14540         12541         5048         15066         12135
#> 10          9273          9180         3770          8292          8204
#> 11         14818         11789         5290         18214         13148
#> 12         30224         21327         8354         28416         20728
#> 13         10462          6502         2894          9862          6192
#> 14         20103         10577         4697         19753         10709
#> 15         30926          5575         2107         27528          6367
#> 16         25816         16521         7215         26100         16518
#> 17         12056          9351         4289          9914          8099
#> 18         12197          9471         3506         11428          9269
#> 19         45502         23855         7366         41545         22761
#> 20         31124         19127         6476         29721         20318
#> 21         36632          2423         1765         34348          2437
#> 22         18027         11593         4560         17569         11645
#> 23         26409         14019         6397         22136          9636
#> 24         14398          9693         4207         13769          9595
#> 25         35431         16563         8092         32589         15172
#> 26         26854         19172         7300         26629         20068
#> 27         16749         10630         4574         16633         10129
#> 28         52982         11450         4443         50374         10795
#> 29         61556         16689         5825         61545         15719
#> 30         27419         12092         6289         27295         11786
#> 31         17569         11062         5482         17408         10318
#> 32         31454         19924         6921         28751         19434
#> 33         19430         15240         4470         18939         15105
#> 34         10035          9306         3580         10157          9397
#> 35         15153         10362         4139         13537          9275
#> 36         25326         11889         3906         26372         11815
#> 37         42544         15551         6148         39584         16073
#> 38         38174         18900         5514         39720         18505
#> 39         13934          8507         3373         13321         10524
#> 40         22652          7469         4026         22288          7552
#> 41         11650          9220         4138         11456          9377
#> 42         23012         16790         4167         18515         14588
#> 43         21311         13451         4637         18485         12914
#> 44         46104          9187         3021         37210          8566
#> 45         27178          8031         3400         23583          7847
#> 46         24540         14340         5788         24537         14340
#> 47         21352         14651         5828         21689         14417
#> 48         28348          4034         1608         24837          4078
#> 49         11407          3875         1240          9869          3933
#> 50         14825         11269         4389         13094         10939
#>    enrlt_ic2011 applcn_ic2010 admssn_ic2010 enrlt_ic2010 applcn_ic2009
#> 1          9544         28304         25616         9344         27089
#> 2          4202         15784         12417         4204         14862
#> 3          3033         34145         16315         3259         31440
#> 4          3016         16282         10224         3383         14504
#> 5          1751         22280          4203         1723         18774
#> 6          6135         26037         15498         5952         23439
#> 7          2712         11432          6721         2660         10258
#> 8          7424         36719         25391         7020         33011
#> 9          4552         12536         10662         4356         12549
#> 10         3644          8268          8147         3561          8413
#> 11         5481         15917         11012         4789         15093
#> 12         7984         26907         18829         7375         25395
#> 13         2898          9300          5158         2707         11281
#> 14         4705         18992         10409         4772         17853
#> 15         2127         25369          6887         2128         25013
#> 16         7089         26764         16572         6672         21330
#> 17         3896          8696          7079         3554          7561
#> 18         3696         10068          8303         3506          8640
#> 19         7262         40714         21017         6560         39089
#> 20         6821         30707         19993         6513         27213
#> 21         1707         32022          2340         1672         30429
#> 22         4464         16349         11720         4858         16551
#> 23         5766         20112         10776         5563         18967
#> 24         4188         12555          9352         4214         12234
#> 25         7149         31022         14583         7275         31362
#> 26         7032         26629         20068         7032         24756
#> 27         4447         14019          8468         3810         12035
#> 28         4109         48682         10524         4356         48473
#> 29         4636         55694         12178         4472         55423
#> 30         6451         26512         11459         6381         25798
#> 31         4679         17776          9557         4684         17207
#> 32         7252         27310         18324         6929         26057
#> 33         4565         17220         14434         4557         15060
#> 34         3702         10653          9740         3942         10902
#> 35         4328         12195          8966         4153         11120
#> 36         3994         26147         11671         3933         28331
#> 37         6236         31613         16006         6481         29965
#> 38         5368         36853         17613         5323         33910
#> 39         3569         10909          8479         3088          8595
#> 40         3960         23225          7345         3960         21543
#> 41         4053          9996          8500         3726          9252
#> 42         3978         16780         13367         3839         15013
#> 43         4468         17695         11262         3917         17018
#> 44         2931         35794          8715         2972         35753
#> 45         3439         22124          7212         3246         21109
#> 46         5774         21268         12264         5338         20224
#> 47         5927         21390         14225         5680         22613
#> 48         1601         21811          3914         1600         19353
#> 49         1240         10566          4256         1225         10553
#> 50         4176         11604          8068         2980         11795
#>    admssn_ic2009 enrlt_ic2009 applcn_ic2008 admssn_ic2008 enrlt_ic2008
#> 1          24473         9707         24922         23504         9274
#> 2          11816         3918         17068         12085         3984
#> 3          15699         3098         25501         13096         3068
#> 4           8355         2982         14255          7154         2762
#> 5           4202         1703         17748          4077         1700
#> 6          14308         5967         25485         11901         5031
#> 7           6248         2640          9664          6122         2628
#> 8          23975         7327         31177         22039         7564
#> 9          10953         4546         11058          9832         4347
#> 10          8283         3687          9386          5249         3981
#> 11         11092         5141         11452          8332         4596
#> 12         18392         7416         25589         17919         7555
#> 13          6415         2450          7479          4273         3313
#> 14         10538         4804         16553          9985         4907
#> 15          6553         2078         21930          5872         1981
#> 16         13840         6739         20932         13041         6173
#> 17          6537         3148          6406          5702         3073
#> 18          7270         3191          8149          6971         3141
#> 19         20011         7241         39551         20156         6495
#> 20         19905         6225         29952         21423         7025
#> 21          2426         1694         25299          2400         1703
#> 22         11228         4585         16143         11643         4385
#> 23         11133         5148         18500         11172         5116
#> 24          8892         3717         12824          9136         4351
#> 25         14213         7243         29501         12843         6718
#> 26         19310         6966         22544         18158         6709
#> 27          6751         2919         12045          6945         3011
#> 28         10404         4261         44149         10251         4225
#> 29         12659         4735         50746         11960         4563
#> 30         11015         6253         26326         10916         6384
#> 31          9569         4851         16871          9242         4721
#> 32         17053         6984         23240         16043         7287
#> 33         12503         4063         15582         12827         4246
#> 34         10003         4483         10367          9554         4084
#> 35          8757         4110         10619          8172         3865
#> 36         11870         4202         28054         10888         3912
#> 37         14970         6079         29814         12567         5783
#> 38         16960         5400         29173         15324         5107
#> 39          6839         2576          7946          6630         2473
#> 40          7315         3865         20090          6999         3893
#> 41          8211         3760         10863          7958         3803
#> 42         12801         4260         11287          9813         3587
#> 43          9954         3967         14994          8908         3813
#> 44          8724         2869         35900          7875         2766
#> 45          6768         3250         18363          6735         3255
#> 46         12327         5579         19906         12094         5607
#> 47         13446         5773         22289         14004         5994
#> 48          3899         1599         16944          4292         1569
#> 49          3959         1201          9050          3473         1202
#> 50          8995         3372         11326          8179         3411
#>    applcn_ic2007 admssn_ic2007 enrlt_ic2007 applcn_ic2006 admssn_ic2006
#> 1          20702         19133         7894         20702         19133
#> 2          17688         12200         4191         15919         11538
#> 3          26514         11668         2732         21393          9097
#> 4          12784          6990         2866         12784          6990
#> 5          18159          4122         1683         16820          3992
#> 6          24343         13415         6133         23687         14027
#> 7           9259          5831         2626          9250          6163
#> 8          29059         20282         7197         26645         20873
#> 9           9634          8674         3983          9101          8216
#> 10          7479          6207         3104          9937          4788
#> 11         10135          7455         4508         10825          7927
#> 12         24436         18040         7541         23247         17046
#> 13          6098          3879         2281          5778          3982
#> 14         15640          9610         4693         13752          9170
#> 15         18385          5434         2060         16204          4818
#> 16         21508         12697         6168         19026         12443
#> 17          6415          5651         3209          6730          5853
#> 18          9077          8387         3009          6552          6078
#> 19         34813         20181         8039         29904         18423
#> 20         25929         20429         6914         24883         21042
#> 21         23958          2464         1723         22333          2444
#> 22         11961         10759         4496         13809          9691
#> 23         14313          9140         4538         12522          8770
#> 24         12606          9009         4351         12251          9060
#> 25         27237         13800         7478         27315         13307
#> 26         25449         19703         8482         16609         14293
#> 27         10135          6262         2899          8443          5770
#> 28         41796          9932         4157         37014          9817
#> 29         47317         12188         4810         42223         11361
#> 30         24126         10158         6443         22093         10652
#> 31         15924          9214         5064         12326          7982
#> 32         21645         15361         6940         18985         14324
#> 33         14678         12209         4287         14350         11880
#> 34         10240          7874         4153         10030          7435
#> 35         10024          8073         4190         10516          8124
#> 36         24172         11370         4236         23546         10470
#> 37         27474         13826         5992         25806         12246
#> 38         26096         14823         5280         24663         14165
#> 39          7921          6644         2570          6763          4942
#> 40         19728          6737         3807         18414          6736
#> 41          9428          7776         3883          8223          6814
#> 42         10821          9531         3423         10012          9048
#> 43         13946          8782         3697         13023          8812
#> 44         33760          8453         2963         33973          8634
#> 45         17800          6274         3245         16087          6020
#> 46         17808         11511         5325         15923         10681
#> 47         20436         13403         5643         20156         14734
#> 48         12911          4238         1673         11663          4115
#> 49          7177          3041         1124          7342          3128
#> 50         10853          8240         3477          9314          7177
#>    enrlt_ic2006 applcn_ic2005 admssn_ic2005 enrlt_ic2005 applcn_ic2004
#> 1          7894         19914         18126         7706         20789
#> 2          4092         14249         11616         4197         12827
#> 3          2783         15485         10170         3168         10917
#> 4          2813         10620          7287         3033         10620
#> 5          1724         18090          3995         1724         17749
#> 6          6222         22450         14016         6067         22127
#> 7          2837          9172          6191         2427          8568
#> 8          8183         21974         18602         6949         21172
#> 9          3769          9101          8216         3769          9172
#> 10         3309         10067          4806         3466         10066
#> 11         4970         11077          8660         5700         10147
#> 12         7440         21844         16686         7485         21834
#> 13         1966          5778          3982         1966          5245
#> 14         4375         14109          8336         3957         12978
#> 15         1952         15637          4684         1915         14137
#> 16         6280         17636         12957         5954         16954
#> 17         3236          6533          3331         3315          6633
#> 18         2925          6917          6224         2888          7410
#> 19         6496         30122         17551         5907         31264
#> 20         7537         24052         20432         7252         24003
#> 21         1648         20195          2426         1633         19172
#> 22         3918         12583          8927         3779         13323
#> 23         4378         10555          7776         3739          9106
#> 24         4265         10832          4793         4422         10832
#> 25         7417         23925         12207         6836         23008
#> 26         6009         17904         15701         5974         23725
#> 27         2784          6041          5283         2752          5819
#> 28         4101         36679          9041         3672         37001
#> 29         4423         43267          9949         3723         44981
#> 30         6702         21151         12100         7241         22458
#> 31         4712         13267          8197         4513         11813
#> 32         7580         21986         14955         7237         22269
#> 33         4289         13241         11122         3849         13874
#> 34         4201         10442          7228         4269          9700
#> 35         3835         10604          8353         3961          9418
#> 36         3962         22427         11001         4212         22292
#> 37         5386         23882         13610         6115         21293
#> 38         5439         20573         14309         5305         18537
#> 39         2192          6662          5410         2270          6490
#> 40         3751         18850          6741         3589         17591
#> 41         3342          7382          6325         3239          8165
#> 42         3207          9623          8749         3183         10193
#> 43         3734         12379          8344         3403         12004
#> 44         2763         31634          8418         2741         29792
#> 45         3091         15657          5898         3113         14824
#> 46         4924         15955         10694         4893         15773
#> 47         6141         19263         13723         5641         19342
#> 48         1622         11663          4115         1622         11173
#> 49         1125          6289          2945         1121          6289
#> 50         2856          9193          6793         2878          9463
#>    admssn_ic2004 enrlt_ic2004 applcn_ic2003 admssn_ic2003 enrlt_ic2003
#> 1          17899         7147         19785         17490         7126
#> 2          10796         3594         12439          9653         3706
#> 3           7924         2785          8931          7341         2678
#> 4           7287         3019         11419          6945         2769
#> 5           3804         1640         16315          3778         1619
#> 6          14307         6240         22438         14465         6097
#> 7           6008         2579          8573          5386         2237
#> 8          17572         6352         22178         17992         6784
#> 9           8273         3729          9035          8116         3897
#> 10          4806         3466          7952          4736         3439
#> 11          8171         5428         10376          7973         5262
#> 12         17343         7607         24973         17690         7122
#> 13          3568         1753          4646          3493         1688
#> 14          8048         3931         12293          7310         3732
#> 15          4702         1941         14283          4701         2005
#> 16         12822         6057         20122         14494         6390
#> 17          5881         3264          6706          5952         3484
#> 18          6529         2949          5811          4379         3058
#> 19         17174         6048         27604         15628         5929
#> 20         19259         6865         22977         18076         6505
#> 21          2486         1649         18628          2343         1640
#> 22          8939         3950         13755          9257         4445
#> 23          7021         3368          8298          7194         3077
#> 24          4793         4422          9514          6790         3579
#> 25         11788         6791         24519         11504         6544
#> 26         19788         5725         21185         18021         5958
#> 27          4567         2514          5491          4661         2357
#> 28          8833         3653         36445          8707         3653
#> 29         10577         4268         43436         10454         4257
#> 30         11928         6741         22973         12029         6596
#> 31          8885         5190         11813          8885         5190
#> 32         13939         6811         21484         12924         6366
#> 33         11545         4017         13337         10979         4083
#> 34          6600         4066          9573          6458         4074
#> 35          7603         3688          8879          7250         3718
#> 36         11499         4200         25028         10679         4063
#> 37         13304         6040         25943         13814         5553
#> 38         13707         5588         17180         13059         5186
#> 39          5251         2427          7603          5114         2257
#> 40          6441         3516         17141          6073         3460
#> 41          6723         3614          8140          6638         3808
#> 42          8602         2865          9889          8479         3317
#> 43          8442         3491         12016          8446         3561
#> 44          8037         2770         29278          8753         2976
#> 45          5760         3113         14627          5775         3102
#> 46         11156         4977         15773         11156         4977
#> 47         13447         5579         19737         12992         5514
#> 48          4280         1602          9836          4550         1579
#> 49          2945         1121          5752          2599         1007
#> 50          7125         3108          9213          7184         3032
#>    applcn_ic2002 admssn_ic2002 enrlt_ic2002 applcn_ic2001 admssn_ic2001
#> 1          18155         15385         6348         20861         15702
#> 2          13264         10994         4184         13645         10362
#> 3           7431          6028         2620          7986          6336
#> 4          11315          5864         2471         11432          5976
#> 5          13976          3673         1615             0             0
#> 6          18342         11444         5772         18342         11444
#> 7           8953          5270         2282          9476          5157
#> 8          21264         17267         7080         20228         16777
#> 9          10370          9232         4219         10658          9604
#> 10          8212          4752         3527          8077          5006
#> 11         10536          8336         5301          9789          7588
#> 12         25210         16977         7000         24391         15877
#> 13          4772          3469         1759          5463          1870
#> 14         11928          7866         3893         12168          7944
#> 15         13988          4780         1952         14725          4827
#> 16         19563         14414         5982         19968         14501
#> 17          5167          4712         3265          5548          5074
#> 18          6645          3402         3115          6906          6041
#> 19         28100         15966         6122         28862         13966
#> 20         22872         17292         6208         21760         16727
#> 21         18599          2368         1639         19052          2406
#> 22         13101          9039         4531         12008          8837
#> 23          7322          6196         2655          7864          6250
#> 24          9350          8241         3682          8263          5675
#> 25         22178         13473         7935         20954         13326
#> 26         19832         16973         5808         19719         16613
#> 27          5017          4320         2251          4280          3858
#> 28         36100          8912         3863         32963          8715
#> 29         40739         10953         4246         40739         10953
#> 30         20119         11728         6536         18625         11186
#> 31         12786          8340         4228         10953          8419
#> 32         19930         12351         6247         18805         12116
#> 33         13079         11038         4184         11836         10089
#> 34          9005          6157         4078          8479          5861
#> 35          8449          6914         3140          8312          6696
#> 36         23117         10046         3912         19647         10801
#> 37         25108         12315         5187         24141         12594
#> 38         14724         10967         5185         15436         11673
#> 39          7603          5114         2257          7176          5001
#> 40         15946          6339         3687         16569          6088
#> 41          7248          6425         3833          6943          6459
#> 42          8686          7819         3107          8107          7305
#> 43         11176          7788         3287         11176          7788
#> 44         28362          8620         2766         26351          8973
#> 45         14320          5588         2999         14739          5534
#> 46         15633         10787         4846         14664         11523
#> 47         19249         13683         6095         16874         12777
#> 48          9836          4550         1579          9730          4514
#> 49          5995          2454         1007          5271          2421
#> 50          8932          6885         2790          7958          6476

This is not copy/paste friendly, please read the reprex guide and try to make a proper reproducible example.