Averaging selected columns with head number name

Question

I would like to merge columns 1:2, and 34:158 averaging their values and giving them a new column name, Column 190 would remind the same. I find the complexity with dplyr such as the headers of the columns are numbers, and that I need to specifically select certain columns, not all number columns such as here: Select multiple columns with dplyr::select() with numbers as names

df <- data.frame(A=c("A", "B", "C"), `1`=c("1.9", "6.8", "4.7"), `2`=c("1.9", "6.8", "4.7"), `34`=c("3.9", "0.3", "2.7"), `158`=c("2.9", "3", "45"),`190`=c("22.1", "7.4", "56"), check.names=FALSE)


df:
     1     2    34    158   190

A   1.9   1.9   3.9   2.9  22.1
B   6.8   6.8   0.3   3    7.4
C   4.7   4.7   2.7   45   56

Final df would be this:

      Column1 Column2 Column3
  A   Av1:2  Av34:158 22.5
  B   Av1:2  Av34:158  7.4
  C   Av1:2  Av34:158  56

I have started trying:

library(dplyr)

df2<- df %>% dplyr::select(`1`:`2`, `34`:`158`)

Or maybe generate new columns and remove old?

df$Column1 = rowMeans(df[,c("1", "2")])...

Ronak Shah · Accepted Answer · 2020-10-30T14:23:48.617

1

You can do :

library(dplyr)

df %>% 
  type.convert(as.is = TRUE) %>%
  mutate(Column1 = rowMeans(select(., `1`:`2`), na.rm = TRUE), 
         Column2 = rowMeans(select(., `34`:`158`), na.rm = TRUE)) %>%
  select(A, Column1, Column2, Column3 = `190`)

#  A Column1 Column2 Column3
#1 A     1.9    3.40    22.1
#2 B     6.8    1.65     7.4
#3 C     4.7   23.85    56.0

edited Oct 30 '20 at 14:23

answered Oct 30 '20 at 14:21

Ronak Shah

377,200
20
156
213

Thanks! But could the Column 190 also change name to Column 3? – Ecg Oct 30 '20 at 14:22

Karthik S · Answer 2 · 2020-10-30T14:26:02.597

1

Does this work:

> library(dplyr)
> df %>% mutate(across(`1`:`190`, as.numeric)) %>% rowwise() %>% 
+   mutate(col1 = mean(c_across(`1`:`2`), na.rm = T), col2 = mean(c_across(`34`:`158`), na.rm = T)) %>% rename(col3 = `190`) %>% 
+   select(A, col1, col2, col3)
# A tibble: 3 x 4
# Rowwise: 
  A      col1  col2  col3
  <chr> <dbl> <dbl> <dbl>
1 A       1.9  3.4   22.1
2 B       6.8  1.65   7.4
3 C       4.7 23.8   56  
>

edited Oct 30 '20 at 14:26

answered Oct 30 '20 at 14:23

Karthik S

11,348
2
11
25

Thanks! But could the Column 190 also change name to Column 3? – Ecg Oct 30 '20 at 14:24
I am unsure if there is a typo on your code? I get the following error: Error in (function (cond) : error in evaluating the argument 'x' in selecting a method for function 'mean': `c_across()` must only be used inside dplyr verbs. – Ecg Oct 30 '20 at 14:38
Which version of dplyr are you using? Mine is 1.0.0 – Karthik S Oct 30 '20 at 14:39
Mine 1.0.2, could be that! Got another anwer above similar to yours that work fine for me, so don't worry if it does work for you, thanks anyways :) – Ecg Oct 30 '20 at 14:42
1

Then it should work, and I've used c_across() in verb, mutate is a dplyr verb. I've posted the code verbatim. If Ronak's code works then fine. Thanks. – Karthik S Oct 30 '20 at 14:48
1

Not sure, as error keeps appearing. But thanks a lot for checking! – Ecg Oct 30 '20 at 14:56

score 1 · Answer 3 · answered Oct 30 '20 at 17:45

1

Using base R

df <- type.convert(df, as.is = TRUE)
df$Column1 <- rowMeans( df[c("1", "2")], na.rm = TRUE)
df$Column2 <- rowMeans(df[c("34", "158")], na.rm = TRUE)

answered Oct 30 '20 at 17:45

akrun

874,273
37
540
662

Averaging selected columns with head number name

3 Answers3