-2

I have two 'data.frame's : df1 has 2000 rows(ID) and 100 columns with values matching the names of columns in df2. df2 has 50 rows named q1 through q50 and 11 columns named 1 through 11.

Every two columns in df1 correspond to a row in df2. Column 1 and 2 in df1 correspond to row q1 in df2, column 3 and 4 correspond to row q2, and so on. See an example of the data frames here : df1 df2.

I want to create a third data.frame with dimensions the same as df1. The values in this frame df3 should replace the column name in d1 with the corresponding q row's column value in df2. Here is an example df3 based on the provided df1 and df2 : df3

How can I achieve this?

BattleTested_закалённый в бою
  • 4,037
  • 5
  • 26
  • 47
fateme
  • 33
  • 5
  • Posing images of data is not helpful. See [how to create a reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). – MrFlick Nov 07 '17 at 15:46
  • yes, you are right. I will try write better. it was difficult for me. – fateme Nov 08 '17 at 12:11

1 Answers1

0

Probably not the most efficient method, but should get the result you're after.

# Create df1 for testing
df1 <- data.frame("1" = c(5,1),
                  "2" = c(1,4),
                  "3" = c(1,2),
                  "4" = c(2,1),
                  "5" = c(1,1),
                  "6" = c(1,1),
                  "7" = c(2,2),
                  "8" = c(2,2))

colnames(df1) <- c(1:8)

# Create df2 for testing
df2 <- data.frame("1" = c(-.02635,-.00849,-.00039,.043179,.009653),
                  "2" = c(.027682,.007413,-.00003,-.00406,-.00194),
                  "3" = c(.030295,.010292,-.06765,-.04161,NA),
                  "4" = c(.023239,.031153,.137365,-.03764,NA),
                  "5" = c(.043868,NA,-.08496,-.0377,NA),
                  "6" = c(.045689,NA,NA,-.03547,NA))

colnames(df2) <- c(1:6)
row.names(df2) <- paste0("q",row.names(df2))


# Create df3 from df1 and df2
df3 <- as.data.frame(t(apply(df1, 1, function(x){
                                      q <- 1
                                      iter <- 1
                                      vals <- numeric()
                                        for(i in x){
                                          vals <- append(vals, ifelse(is.null(df2[paste0('q',q),i]), NA, df2[paste0('q',q),i]))
                                          q <- ifelse(iter %% 2 == 0, q + 1, q)
                                          iter <- iter + 1
                                        }
                                      return(vals)
                                      })))
Matt Jewett
  • 3,249
  • 1
  • 14
  • 21
  • Thank you for your guidance, support and sharing your wisdom with me. You understood my question completely and your program was excellent. But, when it run for my data some columns is filled with a new number. It is so strange. For example: In df3, among columns or rows there are new numbers (30, 20, 2, 3 …) they are not in df1 or df2. These new numbers do not assign to a special columns or rows. They are very sporadic and another columns or rows are correct, exactly. It is a question for me. – fateme Nov 08 '17 at 12:05
  • It would be nearly impossible to diagnose what might be causing that without the full data set. If you could provide the full data set or at least a portion where you know there is an issue, I might be able to help more. – Matt Jewett Nov 08 '17 at 21:21
  • Matt Jewett thanks for the quick response. your codes was correct,completely. this is exactly what i need. i understood one number in my real data (df2) was wrong and i corrected it. thanks again. – fateme Nov 10 '17 at 08:18