How to assign values in one column to other columns in wide data using R

user_8255512 0
Viewed 244

There is a wide data set, a simple example is

df<-data.frame("id"=c(1:6),
           "ax"=c(1,2,2,3,4,4),
           "bx"=c(7,8,8,9,10,10),
           "cx"=c(11,12,12,13,14,14))

I'm looking for a way to assign the values in "ax" to column "bx" and "cx". Here, imagine we have thousands of columns we intend to replace with "ax", so I want this to be done in an automated approach using R. The expected output look like

df<-data.frame("id"=c(1:6),
           "ax"=c(1,2,2,3,4,4),
           "bx"=c(1,2,2,3,4,4),
           "cx"=c(1,2,2,3,4,4))

I've thought of, and tried using mutate_at and ends_with, but this has not work for me. For example, I tried

df %>% 
 mutate_at(vars(ends_with("x")), labels = "ax")

and this prints an error. Not sure what's wrong or what's to be added to get this working, so I would like to request your help on this. Thank you very much!

dplyr dataframe r
3 Answers

A simple way using base R would be :

change_cols <- grep('x$', names(df))
df[change_cols] <- df$ax
df

#  id ax bx cx
#1  1  1  1  1
#2  2  2  2  2
#3  3  2  2  2
#4  4  3  3  3
#5  5  4  4  4
#6  6  4  4  4
user_39629140

I would suggest this tidyverse approach using across() to select the range of variables you want:

library(tidyverse)
#Data
df<-data.frame("id"=c(1:6),
               "ax"=c(1,2,2,3,4,4),
               "bx"=c(7,8,8,9,10,10),
               "cx"=c(11,12,12,13,14,14))
#Mutate
df %>% mutate(across(c(bx:cx), ~ ax))

Output:

  id ax bx cx
1  1  1  1  1
2  2  2  2  2
3  3  2  2  2
4  4  3  3  3
5  5  4  4  4
6  6  4  4  4
user_20808480

Another option with mutate_at()

df %>% 
  mutate_at(vars(matches("x$")), ~ax)
#   id ax bx cx
# 1  1  1  1  1
# 2  2  2  2  2
# 3  3  2  2  2
# 4  4  3  3  3
# 5  5  4  4  4
# 6  6  4  4  4
user_123363050
Related