Averaging selected columns with head number name

user_11084433 0
Viewed 48

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")])...
dplyr r
3 Answers

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
user_39629140

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  
>
user_107227520

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)
user_37322710
Related