Create DataFrame from two different DataFrame with specific condition

Question

I'd like to create a DataFrame from two different type of DataFrame with a condition as well as keep extra column. My first DataFrame is:

    sample_id      motif    chromosome position   
        1         CT-G.A      chr1        7300        
        1         TA-C.C      chr1        1000        
        1         TC-G.C      chr2        1200        
        1         TC-G.C      chr2        3000        
        2         CG-A.T      chr2        12898       
        2         CA-G.T      chr2        234235

and the second DataFrame is:

geneID    chromosome   start     end       
  E1          chr1      100      10300            
  E2          chr1      1100     20122                   
  E3          chr2      1200     2000                         
  E4          chr2      400      234236              
  E5          chr2      12000    20000

then I want to create a DataFrame with this condition that:

if (first$chromosome == second$chromosome & second$start<= first$position <= second$end)

then I have a motif in that gene. Hence I want to create this DataFrame:

sample_id     E1,CT-G.A   E1,TA-C.C   E1,TC-G.C   E1,TC-G.C   E1,CG-A.T   E1,CA-G.T     E2,CT-G.A   E2,TA-C.C   E2,TC-G.C   E2,CG-A.T   E2,CA-G.T     E3,CT-G.A   E3,TA-C.C   E3,TC-G.C   E3,CG-A.T   E3,CA-G.T     E4,CT-G.A   E4,TA-C.C   E4,TC-G.C   E4,CG-A.T   E4,CA-G.T     E5,CT-G.A   E5,TA-C.C   E5,TC-G.C   E5,CG-A.T   E5,CA-G.T     E6,CT-G.A   E6,TA-C.C   E6,TC-G.C   E6,CG-A.T   E6,CA-G.T
    1            1         1            0             0           0           0          1            0            0            0            0            0            0            1            0            0            0            0      2            0            0            0            0            0            0            0            0          0            0          0      0
    2            0         0            0             0           0           0          0            0            0            0            0            0            0            0            0            0            0            0      0            1            1            0            0            0            1            0            0          0            0          0      0

`inner_join` doesn't output the way you have posted your desired output. Number of columns shown in sample output can come only when you perform "Cartesian join". — Prem, Feb 12 '18 at 11:25

score 1 · Accepted Answer · answered Feb 12 '18 at 09:32

This will work. But you might want to think about your column headers if you do it like this.

library(dplyr)
library(tidyr)

df1 %>% inner_join(df2, "chromosome") %>% 
  mutate(geneID_motif = paste(geneID, motif, sep = ","),
         n = if_else(position >= start & position <= end, 1, 0)) %>% 
  select(sample_id, geneID_motif, n) %>%
  group_by(sample_id, geneID_motif) %>% 
  summarise(n = sum(n)) %>%
  spread(key = geneID_motif, value = n, fill = 0)

# A tibble: 2 x 14
# Groups:   sample_id [2]
  sample_id `E1,CT-G.A` `E1,TA-C.C` `E2,CT-G.A` `E2,TA-C.C` `E3,CA-G.T` `E3,CG-A.T` `E3,TC-G.C` `E4,CA-G.T` `E4,CG-A.T` `E4,TC-G.C`
      <int>       <dbl>       <dbl>       <dbl>       <dbl>       <dbl>       <dbl>       <dbl>       <dbl>       <dbl>       <dbl>
1         1        1.00        1.00        1.00           0           0           0        1.00        0           0           2.00
2         2        0           0           0              0           0           0        0           1.00        1.00        0   
# ... with 3 more variables: `E5,CA-G.T` <dbl>, `E5,CG-A.T` <dbl>, `E5,TC-G.C` <dbl>

data:

  df1 <-
  structure(
    list(
      sample_id = c(1L, 1L, 1L, 1L, 2L, 2L),
      motif = c("CT-G.A", "TA-C.C", "TC-G.C", "TC-G.C", "CG-A.T", "CA-G.T"),
      chromosome = c("chr1", "chr1", "chr2", "chr2", "chr2", "chr2"),
      position = c(7300L, 1000L, 1200L, 3000L, 12898L, 234235L)
    ),
    .Names = c("sample_id", "motif", "chromosome", "position"),
    class = "data.frame",
    row.names = c(NA,-6L)
  )
df2 <-
  structure(
    list(
      geneID = c("E1", "E2", "E3", "E4", "E5"),
      chromosome = c("chr1", "chr1", "chr2", "chr2", "chr2"),
      start = c(100L, 1100L, 1200L,400L, 12000L),
      end = c(10300L, 20122L, 2000L, 234236L, 20000L)
    ),
    .Names = c("geneID", "chromosome", "start", "end"),
    class = "data.frame",
    row.names = c(NA,-5L)
  )

My data created a big matrix. (e.g 238 * (52000*96)). So how can I store this file in a .csv file — user3585775, Feb 12 '18 at 21:48
@user3585775 Maybe you should try `write.csv(t(your_matrix), "path")` because of the limitation of columns in Excel. — Prem, Feb 14 '18 at 06:28

score 1 · Answer 2 · edited Nov 27 '21 at 15:57

library(dplyr)
library(tidyr)

df1 %>%
  crossing(df2) %>%
  mutate(geneID_motif = paste(geneID, motif, sep=","),
         flag=ifelse(start <= position & position <= end & chromosome1 == chromosome2, 1, 0)) %>%
  select(sample_id, geneID_motif, flag) %>%
  group_by(sample_id, geneID_motif) %>%
  summarise(flag=as.integer(sum(flag))) %>%
  spread(geneID_motif, flag) %>%
  replace(is.na(.),0) %>%
  data.frame(check.names=FALSE)

Output is:

  sample_id E1,CA-G.T E1,CG-A.T E1,CT-G.A E1,TA-C.C E1,TC-G.C E2,CA-G.T E2,CG-A.T E2,CT-G.A E2,TA-C.C E2,TC-G.C
1         1         0         0         1         1         0         0         0         1         0         0
2         2         0         0         0         0         0         0         0         0         0         0
  E3,CA-G.T E3,CG-A.T E3,CT-G.A E3,TA-C.C E3,TC-G.C E4,CA-G.T E4,CG-A.T E4,CT-G.A E4,TA-C.C E4,TC-G.C E5,CA-G.T
1         0         0         0         0         1         0         0         0         0         2         0
2         0         0         0         0         0         1         1         0         0         0         0
  E5,CG-A.T E5,CT-G.A E5,TA-C.C E5,TC-G.C
1         0         0         0         0
2         1         0         0         0

Sample data:

df1 <- structure(list(sample_id = c(1L, 1L, 1L, 1L, 2L, 2L), motif = c("CT-G.A", 
"TA-C.C", "TC-G.C", "TC-G.C", "CG-A.T", "CA-G.T"), chromosome1 = c("chr1", 
"chr1", "chr2", "chr2", "chr2", "chr2"), position = c(7300L, 
1000L, 1200L, 3000L, 12898L, 234235L)), .Names = c("sample_id", 
"motif", "chromosome1", "position"), class = "data.frame", row.names = c(NA, 
-6L))

df2 <- structure(list(geneID = c("E1", "E2", "E3", "E4", "E5"), chromosome2 = c("chr1", 
"chr1", "chr2", "chr2", "chr2"), start = c(100L, 1100L, 1200L, 
400L, 12000L), end = c(10300L, 20122L, 2000L, 234236L, 20000L
)), .Names = c("geneID", "chromosome2", "start", "end"), class = "data.frame", row.names = c(NA, 
-5L))

When I run it say: Error in make.unique(as.character(rows)) : long vectors not supported yet: unique.c:1575 — user3585775, Feb 12 '18 at 16:14
Can you paste the complete code which generated above error by [editing your original question](https://stackoverflow.com/posts/48742049/edit)? _FYI - In my answer I have renamed "chromosome" column as `chromosome1` & `chromosome2` but even if you don't rename your column this code shouldn't give an error (just change the condition inside `mutate` as `chromosome == chromosome1` in case you have same column name in both dataset)._ BTW while reading original data in `df1` and `df2`, option `stringsAsFactors=F` might help. — Prem, Feb 13 '18 at 04:48
I have renamed chromosome column in both df1 and df2, but it said that : rror in make.unique(as.character(rows)) : long vectors not supported yet: unique.c:1575. I think it happened because of the length of my DataFrame. — user3585775, Feb 14 '18 at 02:14
Seems so. I think [this link](https://stackoverflow.com/questions/24335692/large-matrices-in-r-long-vectors-not-supported-yet) might help in case you have not stumbled upon it yet. — Prem, Feb 14 '18 at 06:21

Create DataFrame from two different DataFrame with specific condition

2 Answers2