Paste value from for loop into data frame R-CodePudding

I have two dataframes in R, recurrent and L1HS. I am trying to find a way to do this:

If a sequence in recurrent matches sequence in L1HS, paste a value from a column in recurrent into new column in L1HS.

The recurrent dataframe looks like this:

> head(recurrent)
    chr     start       end X  Y level                   unique
1: chr4  56707846  56708347 0 38    03   chr4_56707846_56708347
2: chr1  20252181  20252682 0 37    03   chr1_20252181_20252682
3: chr2 224560903 224561404 0 37    03 chr2_224560903_224561404
4: chr5 131849595 131850096 0 36    03 chr5_131849595_131850096
5: chr7  46361610  46362111 0 36    03   chr7_46361610_46362111
6: chr1  20251169  20251670 0 36    03   chr1_20251169_20251670

The L1HS dataset contains many columns containing genetic sequence basepairs and a column "Sequence" that should hopefully have some matches with "unique" in the recurrent data frame, like so:

> head(L1HS$Sequence)
"chr1_35031657_35037706" 
"chr1_67544575_67550598" 
"chr1_81404889_81410942" 
"chr1_84518073_84524089"
"chr1_87144764_87150794"

I know how to search for matches using

test <- recurrent$unique %in% L1HS$Sequence

to get the Booleans:

> head(test)
[1] FALSE FALSE FALSE FALSE FALSE FALSE

But I have a couple of problems from here. If the sequence is found, I want to copy the "level" value from the recurrent dataset to the L1HS dataset in a new column. For example, if the sequence "chr4_56707846_56708347" from the recurrent data was found in the full-length data, I'd like the full-length data frame to look like:

Sequence                level    other_columns
chr4_56707846_56708347   03     gggtttcatgaccc....

I was thinking of trying something like:

for (i in L1HS){
   if (recurrent$unique %in% L1HS$Sequence{
     L1HS$level <- paste(recurrent$level[i])}
}

but of course this isn't working and I can't figure it out.

I am wondering what the best approach is here! I'm wondering if merge/intersect/apply might be easier/better, or just what best practice might look like for a somewhat simple question like this. I've found some similar examples for Python/pandas, but am stuck here.

Thanks in advance!

CodePudding user response：

You can do a simple left_join to add level to L1HS with dplyr.

library(dplyr)

L1HS %>%
  left_join(., recurrent %>% select(unique, level), by = c("Sequence" = "unique"))

Or with merge:

merge(x=L1HS,y=recurrent[, c("unique", "level")], by.x = "Sequence", by.y = "unique",all.x=TRUE)

Output

                Sequence level
1 chr1_35031657_35037706     4
2 chr1_67544575_67550598     2
3 chr1_81404889_81410942    NA
4 chr1_84518073_84524089     3
5 chr1_87144764_87150794    NA

*Note: This will still retain all the columns in L1HS. I just didn't create any additional columns in the example data below.

Data

recurrent <- structure(list(chr = c("chr4", "chr1", "chr2", "chr5", "chr7", 
"chr1"), start = c(56707846L, 20252181L, 224560903L, 131849595L, 
46361610L, 20251169L), end = c(56708347L, 20252682L, 224561404L, 
131850096L, 46362111L, 20251670L), X = c(0L, 0L, 0L, 0L, 0L, 
0L), Y = c(38L, 37L, 37L, 36L, 36L, 36L), level = c(3L, 2L, 3L, 
3L, 3L, 4L), unique = c("chr4_56707846_56708347", "chr1_67544575_67550598", 
"chr2_224560903_224561404", "chr5_131849595_131850096", "chr1_84518073_84524089", 
"chr1_35031657_35037706")), class = "data.frame", row.names = c(NA, 
-6L))

L1HS <- structure(list(Sequence = c("chr1_35031657_35037706", "chr1_67544575_67550598", 
"chr1_81404889_81410942", "chr1_84518073_84524089", "chr1_87144764_87150794"
)), class = "data.frame", row.names = c(NA, -5L))