CODEWITHSUNDEEP
rloopstidyversedplyr
Luis
Luis

Reputation: 1584

iterative computation ./ mutate of the mean of a large dataset within dplyr functions

Let's say this is my dataset

dataset

I would like to compute a new variable (mutate) considering always the same pattern (i.e.g, the lf_aparc_volume and the rh_aparc_volune; then the lh_bankssts and rh_bankssts). Therefore, the new variable needs to be the mean of one column with the column 2 spaces ahead, and so on. [In the real dataset, we have 30 columns between the two columns I want to take the mean].

In excel, after selecting the two variables, it will be a "drag and drop" to the right. Thus, when the result becomes "missing", the algorithm should stop.

new dataset

I would like to remain in the tidyverse environment. Any suggestions?

Edit with the answer (thanks to Ian Campbell) If someone is facing the same situation, please see the following code:

ds %>% #get the dataset
  pivot_longer(-identificacao, names_to = "variable", values_to = "values") %>% #re-arrange the way we see the ds
  separate(variable, into = c("group","variable"),
           sep = "_", extra = "merge") %>%  #fix names
  pivot_wider(id_cols = c("identificacao","group"),
              names_from = "variable", values_from = "values") %>% #wide format
  group_by(identificacao) %>% #now I'll group the take the means
  mutate(mean_aparc = mean(aparc_volume)) %>% 
  mutate(mean_bankssts = mean(bankssts_volume)) %>% 
  distinct(identificacao, .keep_all = TRUE) #keep only one identification per row

Code:

ds <-structure(list(identificacao = c("3004U", "77584X", "25917G", 
                                      "39895C", "20597Y", "64085M", "51573F", "42221E", "58658E", "8983C", 
                                      "18516K", "27050E"), lh_aparc_volume = c(2112, 2081, 2050, 2350, 
                                                                               2250, 1730, 1874, 1821, 2004, 1928, 1844, 2900), lh_bankssts_volume = c(1750, 
                                                                                                                                                       1654, 1344, 1876, 1366, 1424, 1416, 1521, 1231, 2415, 938, 1356
                                                                               ), rh_aparc_volume = c(1797, 1895, 1386, 1875, 2123, 1457, 1754, 
                                                                                                      2478, 1670, 1613, 1702, 1873), rh_bankssts_volume = c(1951, 1991, 
                                                                                                                                                            1774, 2539, 1830, 2564, 2433, 1092, 1803, 2009, 1609, 1787)), row.names = c(NA, 
                                                                                                                                                                                                                                        -12L), class = c("tbl_df", "tbl", "data.frame"))
ds

Upvotes: 0

Views: 57

Answers (2)

Ian Campbell
Ian Campbell

Reputation: 24878

Here's an approach with bind_cols and map2:

library(dplyr)
library(purrr)
cols.ahead <- 2
ds %>%
bind_cols(., map2(seq(2,ceiling(ncol(.)/2)),seq(2,ceiling(ncol(.)/2)) + cols.ahead,
                  ~ setNames((ds[,.x]+ds[,.y])/2,
                             paste0(gsub(".+_(\\w+)_.+","\\1",names(ds)[.x]),"_mean"))))
# A tibble: 12 x 7
   identificacao lh_aparc_volume lh_bankssts_volume rh_aparc_volume rh_bankssts_volume aparc_mean bankssts_mean
   <chr>                   <dbl>              <dbl>           <dbl>              <dbl>      <dbl>         <dbl>
 1 3004U                    2112               1750            1797               1951      1954.         1850.
 2 77584X                   2081               1654            1895               1991      1988          1822.
 3 25917G                   2050               1344            1386               1774      1718          1559 
 4 39895C                   2350               1876            1875               2539      2112.         2208.
 5 20597Y                   2250               1366            2123               1830      2186.         1598 
 6 64085M                   1730               1424            1457               2564      1594.         1994 
 7 51573F                   1874               1416            1754               2433      1814          1924.
 8 42221E                   1821               1521            2478               1092      2150.         1306.
 9 58658E                   2004               1231            1670               1803      1837          1517 
10 8983C                    1928               2415            1613               2009      1770.         2212 
11 18516K                   1844                938            1702               1609      1773          1274.
12 27050E                   2900               1356            1873               1787      2386.         1572.

Another "tidyverse" approach would be tidyr:pivot_longer:

library(dplyr)
library(tidyr)
ds %>%
  pivot_longer(-identificacao, names_to = "variable", values_to = "values") %>%
  separate(variable, into = c("group","variable"),
           sep = "_", extra = "drop") %>%
  pivot_wider(id_cols = c("identificacao","variable"),
              names_from = "group", values_from = "values") %>%
  mutate(mean = (lh + rh)/2) %>%
  pivot_wider(id_cols = "identificacao",
              names_from = "variable", 
              values_from = c("lh","rh","mean"))

# A tibble: 12 x 7
   identificacao lh_aparc lh_bankssts rh_aparc rh_bankssts mean_aparc mean_bankssts
   <chr>            <dbl>       <dbl>    <dbl>       <dbl>      <dbl>         <dbl>
 1 3004U             2112        1750     1797        1951      1954.         1850.
 2 77584X            2081        1654     1895        1991      1988          1822.
 3 25917G            2050        1344     1386        1774      1718          1559 
 4 39895C            2350        1876     1875        2539      2112.         2208.
 5 20597Y            2250        1366     2123        1830      2186.         1598 
 6 64085M            1730        1424     1457        2564      1594.         1994 
 7 51573F            1874        1416     1754        2433      1814          1924.
 8 42221E            1821        1521     2478        1092      2150.         1306.
 9 58658E            2004        1231     1670        1803      1837          1517 
10 8983C             1928        2415     1613        2009      1770.         2212 
11 18516K            1844         938     1702        1609      1773          1274.
12 27050E            2900        1356     1873        1787      2386.         1572.

Obviously this moves whatever lh and rh are to the end of the column name. If this is a dealbreaker, you could use rename_at.

Upvotes: 1

Enoch
Enoch

Reputation: 33

What is the problem with the simple mutate?

View(ds %>% mutate(col1 = (lh_aparc_volume + rh_aparc_volume) /2 ,col2 = (lh_bankssts_volume + rh_bankssts_volume)/2))

Upvotes: 0

Related Questions