Creating variable from 2 columns

Question

I am trying to create a new variable called dCt where I get values depending on a factor column (the time measurement). When the time == 1 I want de dCt to come from value at time == 1 minus endogenous_ct_01 (measure at time 1). Consequently the same for values at time ==3. The grouping variables need to remain

Syntax kind of:

#1st try
sample<- sample%>% dplyr::group_by(id, gen) %>% dplyr::mutate(dCt = (value[time==3] & endogenous_ct_03[time==3]))

# The problem with this syntax is the column dCt is filled with values corresponding to either time ==1 or time ==3. What I need is to match the results of from time levels with the values in **dCt**

# 2nd try
sample<- sample%>% dplyr::group_by(id, gen) %>%  ifelse(time ==3,  dplyr::mutate(dCt = value - endogenous_ct_03, NA)) && ifelse(time ==1,  dplyr::mutate(dCt = value - endogenous_ct_01, NA))

The database example

sample <- structure(list(id = structure(c(50203032, 120715027, 130102010, 
130105045, 70111023, 60901029, 70201047, 60901030, 120715012, 
111201019, 50203020, 60901021, 50203010, 140103026, 130108009, 
111202007, 140103030, 70204056, 140103028, 111201006, 70111024, 
120715029, 120715025, 120715030, 120715034, 120715030, 70111021, 
140103026, 111201026, 110113006, 111201026, 130108009, 50203031, 
110104030, 70201047, 140103029, 50203014, 50203031, 50203011, 
110113007, 60901030, 71801002, 70802013, 120715011, 50508027, 
110104030, 111201007, 111201014, 50203031, 70713001, 60901028, 
140103029, 120715035, 60901031, 130102010, 130108009, 140103027, 
70111022, 50203019, 140102090, 50203027, 111201017, 50203010, 
60901021, 140101096, 50109026, 71801001, 70802012, 70204055, 
60901031, 71801003, 71801002, 50203006, 111201014, 130106037, 
110104019, 70111024, 111201006, 50203017, 140103033, 50109018, 
111202007, 120715011, 50203026, 130102013, 60901038, 60901029, 
60901038, 71801001, 130108009, 140102089, 60901024, 110113005, 
120715030, 110104028, 120715035, 70204055, 71801001, 120715012, 
50705001, 140102089, 70204056, 140103038, 130106034, 140103038, 
60901027, 50203009, 111202015, 130106037, 130102012, 110104028, 
120715033, 50203016, 70802011, 130102013, 70204056, 130102009, 
70710002, 130105044, 140101097, 60901034, 130105044, 50508026, 
70802013, 50203011, 50203032, 111201017, 140101091, 50203012, 
60901036, 111202009, 130105044, 50203019, 140101097, 60901031, 
130102014, 50527001, 70204056, 140102090, 130108008, 120715035, 
120715020, 50203032, 140102089, 50203014, 70802012, 50203026, 
140102088, 110113001, 120715019, 50203017, 110104029, 50203020, 
71801002, 140101099, 70710002, 120715025, 50203032, 50109026, 
50508027, 50705001, 140101088, 110104023, 130102010, 50203026, 
120715034, 140101097, 50203006, 140102087, 71801002, 130105044, 
60901034, 50109025, 70111023, 50203031, 110104023, 110104028, 
111202015, 50601001, 70111021, 111201014, 70111021, 50203020, 
130105045, 111202009, 130106034, 140101099, 130102008, 130108008, 
130108009, 140103026, 140102089, 60901023, 50705001, 110113007, 
50203017, 60901029, 120715033, 50203013, 120715025, 70713002, 
120715022, 120715032, 120715022, 110104019, 110104019, 140102087, 
111202007, 50203027, 50203019, 140103033, 70204055, 50203022, 
50521001, 110113008, 50203031, 50203011, 130108009, 50118001, 
50202099, 70802012, 70111022, 110113008, 50203032, 130102014, 
120715035, 120715030, 140103029, 110104017, 130106038, 130102012, 
50521001, 120715033, 50203027, 130102010, 120715025, 120715029, 
140101088, 60901034, 50203027, 70111024, 111201017, 50508026, 
140102092, 111201017, 60901028, 70204056, 111201014, 50203013, 
111202015, 70713001, 70802015, 120715021, 140101099, 140101088, 
130102008, 50203019, 110113001, 140102092, 110113003, 140101097, 
110606056, 60901028, 130106034, 50508026, 50203027, 130108009, 
50203012, 70802011, 120715032, 130105045, 60901034, 50203017, 
111202015, 120715026, 130104004, 120715035, 111201019, 50521001, 
50202099, 50203026, 60901034, 130105045, 110113005, 71801002, 
140103033, 50202099, 140103036, 130105045, 70204055, 50203019, 
50118001, 60901035, 120715027, 50203008, 120715032, 70211014, 
130102013, 70713002, 140103026), label = "Identificador", format.spss = "F10.0", display_width = 10L), 
    gen = c("s18", "abcg1", "il8rb", "ccl3", "ldlr", "abca1", 
    "cav1", "rxrb", "cd40l", "pparg", "nr1h2", "scarb1", "tgfb2", 
    "nlrp1", "il1b", "lrp1", "nr1h2", "s18", "pparg", "nr1h2", 
    "rxrb", "mttp", "cxcl2", "rxrb", "rxra", "nfe2l2", "msr1", 
    "ptgs1", "ptgs2", "ccl3", "abca1", "cd86", "pik3cb", "cav1", 
    "il8rb", "abcg1", "pparg", "il10", "pcsk9", "cav1", "lrp1", 
    "abca1", "cd36", "ppard", "pik3cb", "msr1", "ido", "nfe2l2", 
    "nlrp1", "dusp1", "cav1", "abcg4", "cyp27a1", "scarb1", "il8ra", 
    "mttp", "b2m", "s18", "lrp1", "cd86", "slc2a3_glut3_", "cxcl8_il8_", 
    "lrp1", "nr1h3", "tnf", "abca1", "olr1", "nfkb1", "cxcl2", 
    "b2m", "msr1", "il8rb", "b2m", "pparg", "tnf", "icam1", "cd40l", 
    "mcp1", "mttp", "scarb1", "cd86", "vcam1", "s18", "cxcl8_il8_", 
    "abcg1", "cd86", "cyp27a1", "cd40l", "vcam1", "il8rb", "ifng", 
    "cav1", "lag3", "s1pr3", "rxrb", "cdkn2a", "tgfb2", "icam1", 
    "pik3cb", "mttp", "lag3", "gapd", "nampt", "pparg", "pparg", 
    "slc2a3_glut3_", "cdkn2a", "abca1", "icam1", "il8ra", "gapd", 
    "gapd", "s18", "ido", "lrp1", "abcg4", "cd68", "il8ra", "nfkb1", 
    "pparg", "icam1", "mttp", "gapd", "vcam1", "chuk", "cd86", 
    "cd68", "cd86", "ifng", "cd36", "rxrb", "s1pr3", "s1pr3", 
    "mcp1", "dusp1", "nampt", "slc2a3_glut3_", "nlrp3", "adrb2", 
    "scarb1", "vcam1", "rxra", "ido", "ppard", "il23a", "cxcl8_il8_", 
    "ido", "abcg1", "adrb2", "cxcl2", "olr1", "nr1h2", "rxra", 
    "pparg", "ifng", "il1b", "nampt", "cyp27a1", "gapd", "rxrb", 
    "lrp1", "gapd", "cav1", "mcp1", "scarb1", "ccl3", "il10", 
    "s1pr3", "nampt", "rxra", "olr1", "pik3cb", "vcam1", "olr1", 
    "lrp1", "vcam1", "s18", "lrp1", "lrp1", "slc2a3_glut3_", 
    "il1b", "rxra", "pparg", "cd68", "lrp1", "gapd", "vcam1", 
    "icam1", "il10", "olr1", "nr1h3", "cav1", "ppara", "nfe2l2", 
    "scarb1", "il8rb", "cav1", "cd68", "rxrb", "cxcl8_il8_", 
    "nfe2l2", "b2m", "adrb2", "ptgs1", "scarb1", "nr1h3", "abcg4", 
    "il8ra", "nr1h2", "cd68", "il1b", "b2m", "pcsk9", "s1pr3", 
    "pparg", "mttp", "chuk", "nfe2l2", "lag3", "pparg", "msr1", 
    "il8rb", "olr1", "ptgs1", "s18", "dusp1", "rxra", "abcg4", 
    "nfkb1", "olr1", "pparg", "scarb1", "vcam1", "pparg", "dusp1", 
    "pcsk9", "vcam1", "chuk", "cxcl8_il8_", "cd86", "olr1", "cav1", 
    "abcg4", "mcp1", "ldlr", "mttp", "s1pr3", "ifng", "pparg", 
    "lag3", "rxra", "slc2a3_glut3_", "cav1", "ptgs1", "cd40l", 
    "nfkb1", "abca1", "nr1h2", "abcg4", "il8ra", "ppard", "nampt", 
    "abcg4", "adrb2", "gapd", "lag3", "cxcl2", "cxcl8_il8_", 
    "ido", "ido", "ptgs1", "cdkn2a", "vcam1", "cdkn2a", "il1b", 
    "dusp1", "msr1", "il10", "lrp1", "slc2a3_glut3_", "lrp1", 
    "dusp1", "rxra", "abcg1", "slc2a3_glut3_", "cav1", "abcg4", 
    "nr1h3", "olr1", "lrp1", "gapd", "cav1", "ppard", "ppara", 
    "vcam1", "tnf", "il8ra", "cd36", "b2m", "cd40l"), time = c("3", 
    "3", "3", "1", "3", "3", "3", "3", "1", "3", "1", "3", "3", 
    "3", "3", "1", "3", "3", "3", "3", "3", "1", "3", "3", "3", 
    "3", "3", "1", "3", "3", "3", "3", "1", "3", "3", "3", "3", 
    "3", "1", "1", "3", "3", "1", "3", "1", "3", "1", "3", "1", 
    "3", "1", "3", "3", "3", "1", "3", "3", "3", "3", "1", "1", 
    "3", "3", "1", "3", "3", "3", "3", "3", "3", "3", "1", "3", 
    "3", "3", "3", "3", "1", "3", "3", "1", "3", "3", "3", "3", 
    "3", "3", "1", "3", "3", "1", "3", "3", "3", "3", "3", "3", 
    "3", "3", "1", "3", "3", "3", "3", "3", "3", "3", "3", "3", 
    "3", "1", "3", "3", "3", "3", "3", "3", "3", "1", "3", "3", 
    "3", "3", "3", "1", "3", "3", "1", "1", "3", "3", "1", "1", 
    "3", "3", "1", "1", "3", "3", "3", "3", "3", "3", "1", "3", 
    "3", "3", "3", "3", "3", "3", "3", "3", "3", "3", "3", "1", 
    "3", "1", "3", "3", "1", "1", "3", "3", "3", "3", "3", "3", 
    "3", "3", "1", "3", "1", "3", "3", "3", "1", "1", "1", "3", 
    "1", "3", "3", "3", "3", "3", "3", "3", "3", "3", "3", "3", 
    "1", "3", "3", "3", "3", "3", "1", "3", "3", "3", "3", "3", 
    "1", "3", "3", "3", "3", "3", "3", "3", "3", "1", "3", "3", 
    "3", "3", "1", "3", "3", "3", "1", "3", "3", "3", "3", "3", 
    "3", "3", "3", "3", "3", "3", "3", "1", "3", "1", "3", "1", 
    "3", "1", "1", "3", "3", "3", "3", "1", "3", "3", "3", "3", 
    "3", "3", "3", "3", "3", "3", "1", "3", "3", "3", "3", "3", 
    "3", "3", "3", "1", "1", "3", "3", "3", "3", "3", "1", "3", 
    "3", "3", "3", "3", "3", "1", "3", "3", "3", "3", "3", "3", 
    "3", "3", "3", "3", "1", "3", "1", "3", "3", "3", "3"), endogenous_ct_01 = c(NA, 
    NA, NA, 12.252, NA, NA, NA, NA, 11.114, NA, 12.329, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, 12.391, NA, NA, NA, NA, NA, 
    10.77, NA, NA, NA, NA, 12.688, NA, NA, NA, NA, NA, 12.035, 
    12.672, NA, NA, 12.288, NA, 14.186, NA, 11.827, NA, 12.688, 
    NA, 12.421, NA, NA, NA, 11.595, NA, NA, NA, NA, 12.99, 12.018, 
    NA, NA, 12.108, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, 11.987, NA, NA, 12.612, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, 13.125, NA, NA, NA, NA, NA, NA, NA, NA, 11.64, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, 11.862, NA, NA, NA, NA, 
    NA, NA, NA, 12.625, NA, NA, NA, NA, NA, 12.035, NA, NA, 11.754, 
    11.812, NA, NA, 12.625, 12.049, NA, NA, 11.05, 12.29, NA, 
    NA, NA, NA, NA, NA, 13.125, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, 10.714, NA, 12.503, NA, NA, 11.948, 10.302, 
    NA, NA, NA, NA, NA, NA, NA, NA, 12.288, NA, 11.717, NA, NA, 
    NA, 11.907, NA, 11.356, NA, 11.356, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, 11.64, NA, NA, NA, NA, NA, 10.714, NA, 
    NA, NA, NA, NA, 12.169, NA, NA, NA, NA, NA, NA, NA, NA, 12.138, 
    NA, NA, NA, NA, 12.031, NA, NA, NA, 10.874, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, 12.391, NA, 12.288, NA, 10.937, 
    NA, 12.406, 12.071, NA, NA, NA, NA, 12.492, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, 11.581, NA, NA, NA, NA, NA, NA, NA, 
    NA, 9.922, 11.874, NA, NA, NA, NA, NA, 13.734, NA, NA, NA, 
    NA, NA, NA, 12.252, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    11.302, NA, 11.874, NA, NA, NA, NA), endogenous_ct_03 = c(12.449, 
    11.775, 11.604, NA, 12.021, 11.92, 11.434, 11.402, NA, 10.77, 
    NA, 11.726, 13.669, 11.679, 11.45, NA, 13.1, 13.629, 10.645, 
    11.325, 12.734, NA, 11.429, 11.968, 13.313, 11.968, 12.647, 
    NA, 11.461, 9.786, 11.461, 11.45, NA, 11.667, 11.434, 11.937, 
    12.095, 12.402, NA, NA, 11.402, 12.442, NA, 12.338, NA, 11.667, 
    NA, 11.865, NA, 13.863, NA, 11.937, 11.347, 12.817, NA, 11.45, 
    11.938, 12.498, 12.028, NA, NA, 11.197, 13.669, NA, 11.937, 
    12.376, 13.2, 12.242, 14.644, 12.817, 13.869, NA, 11.125, 
    11.865, 12.715, 11.594, 12.734, NA, 11.816, 13.107, NA, 12.435, 
    12.338, 12.645, 12.322, 12.043, 11.92, NA, 13.2, 11.45, NA, 
    11.453, 12.338, 11.968, 11.866, 11.347, 14.644, 13.2, 11.687, 
    NA, 11.416, 13.629, 11.138, 13.139, 11.138, 12.439, 11.841, 
    11.833, 12.715, 11.988, NA, 12.353, 12.314, 12.132, 12.322, 
    13.629, 11.565, 11.734, NA, 12.577, 11.57, 11.495, 12.126, 
    12.715, NA, 12.449, 11.197, NA, NA, 11.453, 11.728, NA, NA, 
    12.577, 12.817, NA, NA, 13.629, 13.979, 11.367, 11.347, 12.115, 
    12.449, NA, 12.095, 12.242, 12.645, 12.171, 11.949, 11.857, 
    11.816, 11.069, 12.739, 12.442, 14.443, 11.734, NA, 12.449, 
    NA, 11.99, 11.21, NA, NA, 11.604, 12.645, 13.313, 12.577, 
    11.125, 12.225, 12.442, 11.495, NA, 13.182, NA, 12.402, 12.475, 
    11.866, NA, NA, NA, 11.865, NA, 12.739, 11.011, 11.728, 13.139, 
    14.443, 11.581, 11.367, 11.45, 11.679, 11.416, 12.925, NA, 
    11.457, 11.816, 11.92, 12.353, 11.322, NA, 12.278, 11.148, 
    12.484, 11.148, 11.594, NA, 12.225, 12.435, 12.488, 12.028, 
    13.107, 14.644, 12.569, 12.863, NA, 12.402, 12.471, 11.45, 
    12.376, NA, 12.242, 12.498, 12.709, NA, 12.186, 11.347, 11.968, 
    11.937, 12.111, 12.971, 11.988, 12.863, 12.353, 12.488, 11.604, 
    11.429, NA, 12.372, NA, 12.488, NA, 11.197, NA, NA, 11.197, 
    11.485, 13.629, 11.865, NA, 11.833, 13.863, 13.139, 11.179, 
    14.443, 12.372, 11.581, 12.028, 11.949, 11.836, NA, 12.577, 
    12.093, 11.485, 13.139, 12.126, 12.488, 11.45, 11.46, NA, 
    NA, 11.011, 11.57, 11.816, 11.833, 10.797, NA, 11.347, 10.77, 
    12.863, 12.164, 12.645, 11.57, NA, 12.338, 12.442, 13.107, 
    12.164, 12.641, 11.011, 14.644, 12.028, 12.376, 12.015, NA, 
    11.871, NA, 12.415, 12.322, 12.278, 11.679), value = c(10.915, 
    17.719, 13.072, 22.402, 19.491, 16.762, 22.821, 16.135, 16.517, 
    24.84, 18.986, 19.906, 25.731, 15.732, 16.78, 18.112, 18.652, 
    12.32, 23.362, 17.584, 17.704, NA, 22.752, 16.69, 14.623, 
    16.772, 20.847, 15.537, 17.004, 21.232, 17.727, 17.458, 18.54, 
    21.429, 14.348, 19.298, 23.302, 24.115, NA, 23.305, 16.896, 
    18.073, NA, 16.764, 19.828, 19.026, 20.468, 17.059, 18.075, 
    14.276, 21.838, NA, 15.787, 21.201, 14.576, NA, 9.501, 11.275, 
    18.555, 18.744, 12.807, 16.55, 19.16, 22.599, 18.093, 18.602, 
    26.755, 15.939, 26.424, 10.13, 21.144, NA, 7.688, 24.049, 
    19.564, 18.082, 19.002, 24.508, NA, 20.999, 18.351, 25.776, 
    11.235, 18.536, 18.856, 17.934, 17.199, 19.555, 26.73, 13.877, 
    24.754, 25.361, 21.074, 19.312, 16.668, 20.531, 26.855, 19.03, 
    16.839, NA, 19.823, 18.105, 11.348, 27.936, 23.144, 13.857, 
    20.296, 16.997, 19.494, 16.204, 15.884, 16.054, 11.349, 21.403, 
    17.865, NA, 13.513, 14.241, 17.065, 25.099, 18.13, NA, 16.79, 
    24.409, 18.035, 17.891, 13.399, 17.483, 22.601, 15.781, 17.145, 
    20.776, 19.82, 24.38, 13.646, 11.626, 14.208, 19.584, 16.171, 
    19.717, 22.974, 13.247, 20.849, 18.085, 20.933, 17.542, 20.361, 
    18.704, 15.781, 22.77, 23.416, 17.196, 15.656, 23.836, 25.836, 
    15.615, 11.353, 17.634, 17.13, 16.745, 16.368, 15.931, 23.749, 
    25.237, 20.268, 22.122, 25.818, 19.754, 12.995, 14.165, 22.688, 
    17.731, 26.775, 23.055, 17.775, 26.728, 10.804, 18.139, 18.634, 
    13.435, 17.029, 13.592, 24.095, 12.745, 17.648, 18.786, 27.639, 
    18.516, 23.574, NA, 20.679, 23.516, NA, 16.355, 19.052, 14.767, 
    24.064, 14.204, 17.752, 16.145, 16.925, 8.739, 15.903, 15.503, 
    19.776, 22.054, NA, 14.805, 18.568, 14.773, 17.878, 11.279, 
    NA, 20.503, 24.536, 27.208, 17.937, 16.596, 20.464, 23.564, 
    21.648, 15.447, 22.903, 15.793, 10.767, 12.412, 13.471, NA, 
    16.671, 22.578, 24.455, 20.54, 22.764, 24.374, 12.967, NA, 
    24.9, 18.259, 17.226, 17.688, 20.792, 22.452, NA, 24.577, 
    18.182, NA, 21.598, 22.283, 23.958, 19.453, 14.822, 14.678, 
    22.821, 19.946, 18.755, 16.038, 18.45, 18.163, NA, 15.072, 
    17.571, 12.923, NA, 17.387, 16.79, 21.4, 23.662, 17.725, 
    21.319, 19.29, 15.371, 19.75, 25.497, 20.51, 15.783, 14.723, 
    19.865, 25.621, 17.889, 13.302, 17.55, 13.553, 14.777, 18.598, 
    13.729, 24.806, NA, 21.482, 22.898, 21.186, 16.627, 23.327, 
    17.737, 20.032, 26.569, 18.745, 15.769, 16.76, 9.468, 17.326
    )), row.names = c(NA, -300L), class = "data.frame")

Andy Baxter · Answer 1 · 2022-10-05T10:48:09.363

Using case_when here should give you what you're looking for in a readable way:

library(dplyr)


sample |> 
  mutate(dCt = case_when(
    time == 1 ~ value - endogenous_ct_01,
    time == 3 ~ value - endogenous_ct_03,
    TRUE ~ NA_real_
  ))


#> # A data frame: 300 × 7
#>           id gen   time  endogenous_ct_01 endogenous_ct_03 value   dCt
#>        <dbl> <chr> <chr>            <dbl>            <dbl> <dbl> <dbl>
#>  1  50203032 s18   3                 NA               12.4  10.9 -1.53
#>  2 120715027 abcg1 3                 NA               11.8  17.7  5.94
#>  3 130102010 il8rb 3                 NA               11.6  13.1  1.47
#>  4 130105045 ccl3  1                 12.3             NA    22.4 10.2 
#>  5  70111023 ldlr  3                 NA               12.0  19.5  7.47
#>  6  60901029 abca1 3                 NA               11.9  16.8  4.84
#>  7  70201047 cav1  3                 NA               11.4  22.8 11.4 
#>  8  60901030 rxrb  3                 NA               11.4  16.1  4.73
#>  9 120715012 cd40l 1                 11.1             NA    16.5  5.40
#> 10 111201019 pparg 3                 NA               10.8  24.8 14.1 
#> # … with 290 more rows

I am not sure why with the same database, I am having an error, becaouse of the argument TRUE ~ NA. It works perfectly removing it Error in names(message) <- `*vtmp*` : 'names' attribute [1] must be the same length as the vector [0] The database doesn't have NAs in time column but still I don't get why is giving error. It shouldn't be this way, should it? — Javier Hernando, Oct 05 '22 at 10:33
Try replacing `NA` with `NA_real_` - some others have discussed the same error [here](https://stackoverflow.com/questions/71199096/dplyrcase-when-inexplicably-returns-namesmessage-vtmp-error) — Andy Baxter, Oct 05 '22 at 10:47

Creating variable from 2 columns

1 Answers1