Looping through the data frame and match the values from another file in R

Question

I need some help with r.

I have a data frame:

ant <- data.frame(n_scale = c(0.62, 0.29, -0.9), 
                       aa = c('A','B','C'))

It looks like this:

0.62 A
0.29 B
-0.90 C

Then I read a file with a dataframe2 which looks like:

-1 0 1 2
C  B A A

I want to achieve this:

   -1     0     1     2
    C     B     A     A
   -0.9   0.29  0.62  0.62

How can I loop through the dataframe2 to get values from the ant data frame?

Thank you very much for your help! :)

Could you please provide the output from `dput` for each of your frames? Two reasons: (1) `-1` is a non-standard column name, usually R changes it to something like `X.1`; (2) letters and numbers in the same "column" means everything is a string. — r2evans, Sep 28 '20 at 06:29
I have a feeling this is "just a merge/join" (https://stackoverflow.com/q/1299871/3358272 and https://stackoverflow.com/a/6188334/3358272), but somehow dataframe2 is is shown in a transposed state. Perhaps it would be more useful to use it in a columnar format, similar to `ant`? — r2evans, Sep 28 '20 at 06:37
You are not going to succeed with a merge unless you first transpose one of the dataframes. Would be easier to use the match function. — IRTFM, Sep 28 '20 at 06:52
Thank you for your inputs. I amended the input of dataframe2 as suggested. It is now in a format of what the dput(df) shows above. — student24, Sep 28 '20 at 08:03
jay.sf thank you for your suggestion. It works quite good. I get this output: aa n_scale hyd 1 A 0.62 1 2 A 0.62 2 3 B 0.29 0 4 C -0.90 -1 Is there a way to keep the original order of -1 0 1 2 while merging the dataframes? — student24, Sep 28 '20 at 08:17
What you have shared is an image which is of dataframe and not the `dput`. We need something that we can copy/paste into our R session. So image is not helpful here. Read about [how to ask a good question](http://stackoverflow.com/help/how-to-ask) and how to give a [reproducible example](http://stackoverflow.com/questions/5963269). — Ronak Shah, Sep 28 '20 at 08:44

score 0 · Accepted Answer · answered Sep 28 '20 at 08:48

Using merge. After that you can match hyd of result with that of df2.

res <- merge(ant, df2)
res <- res[match(df2$hyd, res$hyd), ]
res
#   aa n_scale hyd
# 4  C   -0.90  -1
# 3  B    0.29   0
# 1  A    0.62   1
# 2  A    0.62   2

Please next time when asking, provide your data as I do below.

Data:

ant <- data.frame(n_scale = c(0.62, 0.29, -0.9), 
                  aa = c('A','B','C'))

df2 <- data.frame(hyd=c(-1, 0, 1, 2),
                  aa=c("C",  "B", "A", "A"))

Looping through the data frame and match the values from another file in R

1 Answers1