I have the following dataframe consisting of different variables (A, X, L) that were measured at two different times (t1 and t2). Of course, in the real data there are many more variables (just for the sake of the example)
n <- 100
df <- data.frame(A_t1 = runif(n, min = 1, max = 5),
A_t2 = runif(n, min = 1, max = 5),
X_t1 = runif(n, min = 1, max = 5),
X_t2 = runif(n, min = 1, max = 5),
L_t1 = runif(n, min = 1, max = 5),
L_t2 = runif(n, min = 1, max = 5)
) %>% pivot_longer(cols = starts_with(c("A", "X", "L")))
> df
# A tibble: 600 × 2
name value
<chr> <dbl>
1 A_t1 4.15
2 A_t2 2.20
3 X_t1 4.49
4 X_t2 3.84
5 L_t1 1.94
6 L_t2 1.75
7 A_t1 3.16
8 A_t2 1.71
9 X_t1 2.20
10 X_t2 4.47
What's the easiest way to compute the difference (A_delta, X_delta, L_delta) between t2 and t1 for each variable? (It can be either a long or in a wide format, but not absolute differences). I have been thinking about using group_by() and then lag(), but this seems not to be a very elegant solution.
Thanks for help!
CodePudding user response：
Here is one way without pivoting:
library(dplyr)
library(stringr)
df %>%
mutate(across(ends_with('_t2'), ~ . -
get(str_replace(cur_column(), "t2$", "t1")), .names = "diff_{.col}")) %>%
rename_at(vars(starts_with('diff')), ~ str_remove(., "\\.x"))
A_t1 A_t2 X_t1 X_t2 L_t1 L_t2 diff_A_t2 diff_X_t2 diff_L_t2
1 1.874540 3.074320 2.809577 2.000548 4.250013 2.916864 1.19977955 -0.8090287 -1.33314925
2 1.666149 3.325096 4.925863 4.700479 1.589953 3.059602 1.65894633 -0.2253848 1.46964884
3 2.507464 2.845826 2.877151 1.123485 4.272384 2.632520 0.33836177 -1.7536663 -1.63986374
4 2.918205 3.483349 3.043116 2.853709 2.137141 3.662551 0.56514385 -0.1894073 1.52540950
5 1.584534 4.763194 3.819624 2.548507 3.521542 3.625293 3.17865997 -1.2711166 0.10375089
6 3.489614 3.947787 3.582125 4.320157 1.651846 3.668169 0.45817253 0.7380325 2.01632261
7 1.095135 2.667855 3.906208 4.962086 3.080116 2.081373 1.57271981 1.0558787 -0.99874333
8 3.582729 1.326156 1.547543 2.251823 4.375497 4.470540 -2.25657237 0.7042800 0.09504281
9 3.052125 4.370710 3.390268 1.714186 2.981911 4.693147 1.31858491 -1.6760823 1.71123554
10 1.167758 3.833476 4.922670 2.153172 1.741627 4.001324 2.66571756 -2.7694977 2.25969665
11 4.139607 1.863655 1.854453 2.286554 2.233476 3.163566 -2.27595208 0.4321011 0.93008973
12 4.964073 1.120543 2.929241 4.919893 1.736991 4.147704 -3.84352938 1.9906515 2.41071309
13 3.129148 2.041903 3.307728 3.061521 1.430422 4.819154 -1.08724504 -0.2462069 3.38873248
14 2.335440 4.252497 1.124363 2.829957 2.291107 3.844773 1.91705710 1.7055932 1.55366613
15 3.751504 3.880441 2.354099 4.951746 2.117195 3.861720 0.12893729 2.5976468 1.74452404
16 4.337609 3.472808 2.399587 3.868535 1.057768 4.472129 -0.86480095 1.4689478 3.41436031
17 4.999311 1.301474 1.366422 3.087492 3.847841 1.571798 -3.69783724 1.7210708 -2.27604325
18 3.531337 3.238671 3.346619 2.232999 3.628785 3.338364 -0.29266618 -1.1136199 -0.29042090
19 3.825083 2.561724 1.443649 1.180004 3.690201 4.863383 -1.26335846 -0.2636448 1.17318201
20 4.663794 1.292553 2.054126 1.384345 3.017501 1.623819 -3.37124156 -0.6697805 -1.39368256
21 4.801823 3.111480 1.715078 1.038130 1.208945 1.958026 -1.69034296 -0.6769488 0.74908138
22 3.324566 2.636758 2.729465 3.820546 2.523494 3.913322 -0.68780821 1.0910812 1.38982868
23 4.406653 2.445914 3.949529 2.672628 4.721586 4.752134 -1.96073865 -1.2769012 0.03054821
24 3.295314 3.201848 3.426501 2.626555 2.708426 3.268756 -0.09346610 -0.7999460 0.56033023
25 3.155240 1.777966 1.324444 2.020998 4.207876 4.448476 -1.37727420 0.6965537 0.24060038
26 1.067699 4.472845 4.108465 3.431266 3.946934 3.887675 3.40514589 -0.6771996 -0.05925894
27 3.493764 4.669479 1.934555 2.883440 2.773150 1.366518 1.17571514 0.9488849 -1.40663142
28 3.633989 4.646854 1.602691 4.180919 3.796351 4.879231 1.01286510 2.5782279 1.08287997
29 3.885950 3.653506 3.864009 1.383172 2.379900 2.964982 -0.23244340 -2.4808363 0.58508200
30 2.196123 2.058188 4.085805 2.093173 1.419842 4.493087 -0.13793493 -1.9926323 3.07324484
31 1.872997 3.384298 1.837991 1.373438 1.114418 2.465665 1.51130162 -0.4645534 1.35124694
32 3.628474 1.411555 4.673352 3.327973 2.960074 1.994996 -2.21691895 -1.3453788 -0.96507799
33 3.874743 2.367229 1.427780 1.069699 4.384514 2.528831 -1.50751332 -0.3580808 -1.85568320
34 4.852265 3.941433 1.345281 2.286677 4.938528 1.356440 -0.91083133 0.9413964 -3.58208817
35 3.277322 3.444477 2.268521 4.352339 4.086609 4.904624 0.16715496 2.0838183 0.81801502
36 4.183448 4.233629 1.847334 3.882866 2.724131 3.341027 0.05018155 2.0355318 0.61689506
37 3.826329 4.465756 3.014041 2.670895 4.168045 4.547172 0.63942743 -0.3431462 0.37912782
38 4.461004 4.791000 2.763098 2.092032 2.446065 1.617298 0.32999629 -0.6710664 -0.82876686
39 3.421037 1.198067 2.807645 2.946869 1.503155 4.973224 -2.22296940 0.1392233 3.47006875
40 4.722681 3.996151 2.165523 4.529120 2.783005 2.793148 -0.72652963 2.3635973 0.01014308
41 2.427351 1.397317 2.579700 1.357503 2.497989 2.799101 -1.03003391 -1.2221971 0.30111264
42 1.777554 4.186032 4.916223 3.750554 4.967269 4.374811 2.40847821 -1.1656691 -0.59245796
43 4.725610 4.226431 2.451729 1.166103 4.019221 4.132578 -0.49917942 -1.2856260 0.11335687
44 1.816401 3.619565 3.236727 2.327158 4.135175 1.771374 1.80316385 -0.9095691 -2.36380115
45 4.915365 2.979968 2.815308 4.436481 4.180649 2.445409 -1.93539616 1.6211729 -1.73523975
46 3.747201 4.508869 1.590191 1.271776 4.452354 1.092799 0.76166794 -0.3184146 -3.35955415
47 3.697294 4.001566 2.207558 4.676382 3.850758 3.048620 0.30427220 2.4688236 -0.80213769
48 2.534249 3.281691 1.823145 2.029053 4.546019 4.071197 0.74744214 0.2059081 -0.47482231
49 3.712844 1.726144 4.933841 4.570082 4.552944 1.032102 -1.98670010 -0.3637598 -3.52084234
50 2.343897 4.396924 1.373742 4.193989 3.853471 2.846715 2.05302667 2.8202466 -1.00675613
51 4.472083 2.449359 2.091336 3.169622 2.411612 4.263220 -2.02272399 1.0782853 1.85160797
52 4.040028 4.860052 3.098910 2.027178 4.633496 2.985976 0.82002468 -1.0717322 -1.64751910
53 2.996307 3.125739 4.635223 3.022648 3.104225 2.303797 0.12943242 -1.6125755 -0.80042797
54 3.524626 2.470070 3.880150 4.876186 2.844186 3.619054 -1.05455674 0.9960354 0.77486834
55 3.609807 2.427554 1.308225 1.874094 3.855581 3.231264 -1.18225311 0.5658694 -0.62431700
56 2.504065 2.832678 1.539329 1.949566 3.283089 4.291989 0.32861272 0.4102365 1.00889967
57 4.741309 4.513216 2.443781 1.485824 4.396459 1.178986 -0.22809287 -0.9579574 -3.21747334
58 3.962107 4.278112 3.378555 2.216497 3.852946 1.539353 0.31600500 -1.1620577 -2.31359308
59 3.795888 4.255163 3.733871 2.648748 2.712651 2.145149 0.45927551 -1.0851230 -0.56750266
60 3.446140 2.261078 4.741577 3.730746 2.044745 2.487931 -1.18506181 -1.0108319 0.44318589
61 2.423901 4.935408 3.003743 4.114519 3.203373 2.392129 2.51150668 1.1107759 -0.81124403
62 1.623392 4.464951 3.929314 1.692418 3.886739 1.370086 2.84155927 -2.2368960 -2.51665300
63 4.022309 3.458249 1.390272 4.603026 3.538099 1.316647 -0.56405979 3.2127539 -2.22145141
64 3.482749 4.490465 1.839511 4.035164 4.867904 3.724412 1.00771650 2.1956534 -1.14349217
65 1.895340 3.144618 1.118995 2.769068 3.299120 3.928235 1.24927797 1.6500726 0.62911496
66 1.742253 1.185515 4.011884 3.320883 2.593371 4.955493 -0.55673753 -0.6910007 2.36212192
67 4.935101 3.937632 4.252034 4.680081 4.592170 4.467190 -0.99746866 0.4280478 -0.12497947
68 2.429383 2.115193 4.698501 4.375461 2.684046 3.248452 -0.31419058 -0.3230404 0.56440561
69 1.595154 3.398683 2.567348 1.293824 3.918406 4.565305 1.80352838 -1.2735241 0.64689944
70 4.485915 2.627804 2.355568 2.092511 2.546384 4.981388 -1.85811025 -0.2630571 2.43500436
71 2.811593 1.371381 4.246834 2.627824 4.211511 3.560476 -1.44021138 -1.6190103 -0.65103583
72 3.382056 1.772067 4.717573 1.759712 1.044826 2.433184 -1.60998923 -2.9578609 1.38835738
73 3.291063 1.155917 3.106823 4.981417 4.418390 2.452495 -2.13514660 1.8745941 -1.96589508
74 4.021734 1.728039 4.912924 3.444405 4.519199 4.787987 -2.29369561 -1.4685192 0.26878772
75 1.739764 4.773927 3.216202 2.304863 4.415315 4.943468 3.03416323 -0.9113393 0.52815299
76 2.159070 2.877491 4.149016 1.284349 1.795728 2.978980 0.71842154 -2.8646668 1.18325224
77 2.384631 1.344435 4.024129 4.935890 2.523142 3.262614 -1.04019583 0.9117612 0.73947145
78 3.937383 1.234039 3.704074 3.481392 1.004911 3.923579 -2.70334366 -0.2226811 2.91866808
79 2.202084 3.534844 3.945656 2.708099 2.332737 1.048700 1.33276052 -1.2375565 -1.28403755
80 2.905218 1.880165 4.330358 3.448888 1.716179 1.249481 -1.02505321 -0.8814707 -0.46669824
81 1.722445 2.255539 2.059880 4.184615 1.915228 2.341018 0.53309364 2.1247355 0.42579064
82 3.051844 4.563118 4.353552 4.990230 4.238897 4.703893 1.51127477 0.6366778 0.46499647
83 2.000529 3.814307 1.367176 3.095031 4.618708 1.552575 1.81377765 1.7278541 -3.06613283
84 4.918606 4.257360 3.254456 4.740708 1.703425 3.008954 -0.66124637 1.4862525 1.30552850
85 4.607643 2.105648 3.759696 2.226089 4.976536 3.857512 -2.50199458 -1.5336071 -1.11902412
86 4.057085 1.996307 1.715783 4.030057 4.870936 1.128541 -2.06077788 2.3142738 -3.74239496
87 1.691240 3.829030 1.088323 3.943195 2.919479 2.814279 2.13779051 2.8548719 -0.10519996
88 1.709932 4.594249 3.277362 3.646037 2.801262 4.873592 2.88431709 0.3686748 2.07233009
89 3.522149 2.431008 3.332327 3.644446 3.929332 4.703221 -1.09114052 0.3121190 0.77388942
90 1.121490 3.133493 1.437141 3.273135 1.125386 4.552146 2.01200272 1.8359937 3.42676004
91 1.826040 1.780780 2.091669 4.031958 2.412636 4.341652 -0.04526029 1.9402888 1.92901544
92 3.718355 2.721667 2.378158 3.982240 4.865020 1.304825 -0.99668761 1.6040820 -3.56019542
93 1.526076 3.374309 3.700949 4.754219 4.441379 3.543821 1.84823275 1.0532699 -0.89755800
94 2.748481 1.076543 1.986201 1.487104 4.624976 3.027307 -1.67193753 -0.4990962 -1.59766924
95 3.747308 3.266206 4.236352 2.341871 1.832230 4.498552 -0.48110175 -1.8944806 2.66632151
96 3.039255 4.324106 4.472066 1.716115 4.913111 3.430885 1.28485113 -2.7559507 -1.48222618
97 3.254269 1.949849 4.261411 3.318126 1.009344 2.767513 -1.30442081 -0.9432852 1.75816917
98 2.199629 4.408485 4.751623 3.027443 3.544182 2.860582 2.20885635 -1.7241799 -0.68360079
99 2.593184 3.957531 2.482957 1.558236 3.622533 2.461507 1.36434779 -0.9247204 -1.16102554
100 4.291329 4.362089 4.186318 2.253436 2.337046 4.118349 0.07076018 -1.9328821 1.78130330
CodePudding user response：
Adapting this answer from @Mael to your case one option would be to use a bit of glue and rlang::parse_exprs() like so:
n <- 10
set.seed(123)
library(dplyr, warn =FALSE)
library(glue)
cols <- c("A", "X", "L")
exprs <- glue("{cols}_t2 - {cols}_t1")
names(exprs) <- glue("{cols}_delta")
df |>
mutate(!!!rlang::parse_exprs(exprs))
#> A_t1 A_t2 X_t1 X_t2 L_t1 L_t2 A_delta X_delta
#> 1 2.150310 4.827333 4.558157 4.852097 1.571200 1.183325 2.6770233 0.2939397
#> 2 4.153221 2.813337 3.771214 4.609196 2.658185 2.768800 -1.3398839 0.8379826
#> 3 2.635908 3.710283 3.562027 3.762821 2.654897 4.195699 1.0743749 0.2007939
#> 4 4.532070 3.290534 4.977079 4.181870 2.475382 1.487597 -1.2415360 -0.7952094
#> 5 4.761869 1.411699 3.622823 1.098455 1.609779 3.243792 -3.3501704 -2.5243685
#> 6 1.182226 4.599300 3.834122 2.911184 1.555224 1.826126 3.4170739 -0.9229380
#> 7 3.112422 1.984351 3.176264 4.033838 1.932136 1.510127 -1.1280710 0.8575741
#> 8 4.569676 1.168238 3.376568 1.865632 2.863850 4.013231 -3.4014380 -1.5109363
#> 9 3.205740 2.311683 2.156639 2.272724 2.063891 4.580181 -0.8940572 0.1160851
#> 10 2.826459 4.818015 1.588455 1.926503 4.431311 2.497851 1.9915557 0.3380486
#> L_delta
#> 1 -0.3878754
#> 2 0.1106150
#> 3 1.5408021
#> 4 -0.9877848
#> 5 1.6340129
#> 6 0.2709013
#> 7 -0.4220098
#> 8 1.1493817
#> 9 2.5162909
#> 10 -1.9334598
CodePudding user response：
Using base, get the alternating columns and subtract:
cbind(df, df[, c(FALSE, TRUE)] - df[, c(TRUE, FALSE)])
A safer method, in case there are other columns:
#get column names
t1 <- grep("*_t1$", colnames(df), value = TRUE)
t2 <- grep("*_t2$", colnames(df), value = TRUE)
#substract
delta <- df[, t2] - df[, t1]
#pretty column names, and bind to original dataframe
colnames(delta) <- gsub("t.*", "delta", colnames(delta))
res <- cbind(df, delta)