Create new columns for df in for-loop with name of existing columns + string

Question

My dataframe is structured like the following:

ID   A_L   A_R   B_L   B_R
1     7     5     6     3
2     3     2     3     1
3     6     3     4     5

The goal is to create a new column for each existing column (besides the first column ID) dividing the value of the existing column through its L/R counterpart. So A_L_ratio = A_L/A_R and A_R_ratio = A_R/A_L etc.

I've tried to create a for-loop, using if/elseto differentiate between odd and even indices.

for (col in 2:length(df)) {
  if( (col%%2) == 0){
    a <- df[,col] / df[,col+1]}
    else{
    a <- df[,col] / df[,col-1]}
df[colnames(df[col])"_ratio"] <- a
}

But I seem to fail at R's syntax when it comes to naming the columns. Name should be the name of the column that is called in each loop df[,col] + the string _ratio. At the end I want to append that columne to df. Could someone tell me the right syntax to do this? Thanks a lot!

Thiago Jacomasso · Accepted Answer · 2021-03-22 15:59:42Z

1

You need to paste the colnames to the string "_ratio". Something like this, maybe:

# Create the data.frame
df <- data.frame(
  ID = 1:3,
  A_L = c(7, 3, 6),
  A_R = c(5, 2, 3),
  B_L = c(6, 3, 4),
  B_R = c(3, 1, 5)
)

# create the cols with "_ratio" character appended
for (col in 2:length(df)) {
  if( (col%%2) == 0){
    a <- df[,col] / df[,col+1]
    } else {(a <- df[,col] / df[,col-1])}
df[paste(colnames(df[col]), "_ratio", sep = "")] <- a
}

There are easier and more efficient ways to do this using the dplyr package, though.

edited Mar 22, 2021 at 15:59

answered Mar 22, 2021 at 14:31

Thiago Jacomasso

515 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adrian Mak Over a year ago

Thanks, the paste was exactly what I was missing. Will take a look at dplyr, since I will probably encounter similar problems down the road. Thanks for the recommendation!

Adrian Mak Over a year ago

Ah, short addition that I had to use paste0, because paste would add a space between the column name and the "_ratio".

Thiago Jacomasso Over a year ago

Of course! My mistake. You could use the sep argument: paste(colnames(df[col]), "_ratio", sep = ""). I edited my answer with this

Sirius · Accepted Answer · 2021-03-22 14:40:31Z

1

Don't konw how important this is, but I got the imrpession you have many more columns than what is shown? If so better take it nice and slow so you don't get errors.

If safety checks are not needed, then disregard this.


df <- read.table( text="
ID   A_L   A_R   B_L   B_R
1     7     5     6     3
2     3     2     3     1
3     6     3     4     5
", header=TRUE )

var.names.L <- grep( "_L$", colnames(df) , value=TRUE )
var.names.R <- sub( "_L", "_R", var.names.L )

i.L.name.ok <- var.names.R %in% colnames(df)

ok.L.names <- var.names.L[i.has.R.name]
ok.R.names <- var.names.R[i.has.R.name]

new.columns.1 <- df[, ok.L.names ] / df[, ok.R.names ]
colnames(new.columns.1) <- paste0( colnames(new.columns.1), "_ratio" )
new.columns.2 <- df[, ok.R.names ] / df[, ok.L.names ]
colnames(new.columns.2) <- paste0( colnames(new.columns.2), "_ratio" )


cbind.data.frame(
    df,
    new.columns.1,
    new.columns.2
)

The above code nice and neatly checks that for every _L column there is a coresponding _R column, and then it performs the divition with only those columns.

Output:


  ID A_L A_R B_L B_R A_L_ratio B_L_ratio A_R_ratio B_R_ratio
1  1   7   5   6   3       1.4       2.0 0.7142857 0.5000000
2  2   3   2   3   1       1.5       3.0 0.6666667 0.3333333
3  3   6   3   4   5       2.0       0.8 0.5000000 1.2500000

answered Mar 22, 2021 at 14:40

Sirius

5,4492 gold badges16 silver badges27 bronze badges

1 Comment

Adrian Mak Over a year ago

Yeah, you are absolutely correct, I have many more columns. Thanks for providing this code, it will definitely be useful down the line. In this particular case I'm certain I have corresponding L/R columns. Thanks for you time/effort!

Collectives™ on Stack Overflow

Create new columns for df in for-loop with name of existing columns + string

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related