How to replace columns with respect to column names?

user_11178936 0
Viewed 56

Let's consider two data frames:

df1 <- data.frame("A" = c(1, 1, 2), 
                  "B" = 2:4,
                  "C" = 3:5,
                  "D" = 1:3)

> df1
  A B C D
1 1 2 3 1
2 1 3 4 2
3 2 4 5 3

df2 <- data.frame("A_log" = log(c(1, 1, 2)),
                  "C_diff" = diff(c(2,4,1,7)),
                  "C_log" = log(3:5))

> df2
       A_log C_diff    C_log
1 0.0000000      2 1.098612
2 0.0000000     -3 1.386294
3 0.6931472      6 1.609438

I want to replace columns of df1 with columns from df2 in the way following - column names of df2 are transformed column names of df1 (always column name of df2 is in format df1.colname_something). So I want to replace columns from df1 by columns from df2 which column names are modified. For example: Column "A" should be replaced by A_log (same column name and added _log). Now we want to replace column "C" in df1, and since we have two columns starting from "C" in df2 then we will replace one column "C" in df1 with two columns from df2. We don't replace column "B" and "D" because there is no variable starting from those letters in df2

i.e. I want to have an output:

       Alog B C_diff    C_log D
1 0.0000000 2      2 1.098612 1
2 0.0000000 3     -3 1.386294 2
3 0.6931472 4      6 1.609438 3

My work so far

for (i in seq_len(ncol(df1))) {
  # Obtain next column names of df1
  df1.colname <- colnames(df1)[i]
  # Compare patterns of df1.colname with df2 column names and look for similarities.
  which_match <- which(grepl(df1.colname, colnames(df2)))
  if (length(which_match) >= 1) {
    df3 <- cbind(df3, df2[which_match])
  }
  else {
    df3 <- cbind(df3, df1[i])
  }
}

> df3[, -1]
       A_log B C_diff    C_log D
1 0.0000000 2      2 1.098612 1
2 0.0000000 3     -3 1.386294 2
3 0.6931472 4      6 1.609438 3

And this solution is quite good in my opinion - for sure it's working. The big drawback of it is that it's using loop which is may not be necessary in this case. Do you think that maybe there is some other better approach to do that, or maybe the loop is avoidable?

dataframe r loops string
2 Answers

You can use df2 as it is and select columns from df1 whose subset is not present in df2.

cbind(df2, df1[setdiff(names(df1), sub('_.*', '', names(df2)))])

#      A_log C_diff    C_log B D
#1 0.0000000      2 1.098612 2 1
#2 0.0000000     -3 1.386294 3 2
#3 0.6931472      6 1.609438 4 3

sub('_.*', '', names(df2)) part might be specific to the column names in your actual dataset. In the example, the pattern that you have is ColumnNameFromdf1_somethingelse so I remove _somethingelse from the column name keeping only the part which is available in df1 hence here it returns.

sub('_.*', '', names(df2))
#[1] "A" "C" "C"
user_39629140

You can try going over each name of df1, search for the name in df2 using grep and if found return those form df2 otherwise the one form df1.

do.call(cbind, lapply(names(df1), function(x) {
  i <- grep(paste0("^", x, "_"), names(df2))
  if(length(i) > 0) df2[i]
  else df1[x]
}))
#      A_log B C_diff    C_log D
#1 0.0000000 2      2 1.098612 1
#2 0.0000000 3     -3 1.386294 2
#3 0.6931472 4      6 1.609438 3
user_104885040
Related