How can I copy certain data from one csv file to another csv file using R?

Question

I'm a newbie in R (and programming in general) and need some help:

I want to transfer certain values from one csv file to another. csv1 and csv2 both have 3 columns: "A", "B", "value" of which "A" and "B" are strings. For those rows in csv2 which have the same strings as in csv1 (so A+B in csv1 = A+B in csv2) I want to transfer the values from csv1, otherwise I want to have "na" as an output.

What I did so far (and it might make absolutely no sense):

if (csv1$A == csv2$A & csv1$B == csv2$B)  {

   print(csv1$value for csv2$value)  ### I obviously can't use "for"!

else 
print ("na")

}
  }

Here is an example of csv1

And one of csv2

Thank you very much for your help! (I apologize for my lack of programming skills...)

Please make a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). Especially show a few example data, so someone can start making a solution. — Martin Gal, Jul 23 '21 at 22:33
Where exactly are you stuck? Did you already read the csv-files into R? — Martin Gal, Jul 23 '21 at 22:35
Hi, Thanks for your response. Yes, I read in the csv files successfully. — Bridget, Jul 24 '21 at 08:16
Hi Bridget, for next time it's useful to have a reproducible example as Martin says, at the moment for instance your example csvs have the same number of rows whereas your requirement is actually something else. So at the moment the answers don't really match the question. I'd recommend having a look through the link if you haven't already. — JCran, Jul 24 '21 at 14:23
Of course, sorry about that! It was my first post and I didn't know that another code is needed for different numbers of rows at first... — Bridget, Jul 24 '21 at 15:07

score 0 · Answer 1 · answered Jul 24 '21 at 10:37

0

I believe this should work for you. You were thinking along the right lines, but you need to iterate over the rows of your csvs and store the output from your comparisons in csv2$value.

== is the logical operator you need for exact equivalence.

The for loop tells R to look through the CSV by row.

for(i in 1:nrow(csv1)) {
if (csv1$A[i] == csv2$A[i] & csv1$B[i] == csv2$B[i])  {

csv2$value[i] <- csv2$value[i]
} else {
csv2$value[i] <- NA
}
}

answered Jul 24 '21 at 10:37

JCran

375
2
14

N.b. I've assumed you want NA rather the "na" but easy to change if you did want a string. – JCran Jul 24 '21 at 10:40

score 0 · Answer 2 · answered Jul 24 '21 at 13:57

Thank you very much for your great help, @JCran!

As my csv2 file doesn't have the same nb of lines as my csv1 file, I also added a which function to your code - now it works!

    for (i in (1:nrow(csv2))){
  matching_row_in_csv1 <- which((as.character(csv1$A) == as.character(csv2$A[i]))&(as.character(csv1$B)  == as.character(csv2$B[i])))
  if (length(matching_row_in_csv1) == 1){
    csv2$value[i] <- csv1$value[matching_row_in_csv1]
  } else {
    csv2$value[i] <- NA
  }
}

Many thanks again! :)

score 0 · Answer 3 · answered Jul 25 '21 at 16:43

Here are two alternatives:

With base R you can merge both data.frames and rename the resulten columns

df_new <- merge(csv2, csv1, by=c("A", "B"), all.x = TRUE)

df_new$value <- df_new$value.y
df_new[,c("value.y", "value.x")] <- NULL

to get

> df_new
        A       B value
1    bird feather  0.32
2 feather     pen    NA
3    moon     sun    NA

Basically the same way using the dplyr-package:

library(dplyr)
csv2 %>% 
  left_join(csv1, by=c("A", "B")) %>% 
  select(A, B, value = value.y)

returns

# A tibble: 3 x 3
  A       B       value
  <chr>   <chr>   <dbl>
1 bird    feather  0.32
2 feather pen     NA   
3 moon    sun     NA

Both ways don't use a for-loop and you don't need to compare the elements in your data.frames per row. But, contrary to @JCRAN and your own answer, the matching is done, even if your data in csv1 and csv2 are sorted differently. Perhaps this behavior, which is often a big advantage, isn't wanted in this case.

Thanks a lot, Martin! In this case the matching should only be done if the data is not sorted differently, but this trick is very useful and good to know for the future. It will save me a lot of time! Many thanks! — Bridget, Jul 26 '21 at 08:43

How can I copy certain data from one csv file to another csv file using R?

3 Answers3