Creating a data frame from a data framework

Question

I would like to create a dataframe from a previously created data frame. my first data framework:

    Sample motif chromosome
    1      CT-G.A    1
    1      TA-C.C    1
    1      TC-G.C    2
    2      CG-A.T    2
    2      CA-G.T    2

Then I want to create a dataframe, as shown below, for all (96 * 24-motifs * chromosomes -):

    Sample CT-G.A,chr1 TA-C.C,chr1 TC-G.C,chr1 CG-A.T,ch1 CA-G.T,ch1 CT-G.A,chr2 TA-C.C,chr2 TC-G.C,chr2 CG-A.T,ch2 CA-G.T,ch2 
    1       1             1           0           0            0        0          0     1    0     0      0      0
    2       0             0           0           0            0        0          0     0    0     0      1      1

Answer 1

Here is a possible solution using dplyrand tidyr.

value, , , data.frame, , motif-chromosome-Sample, 0 . key , . , data.frame (. ), . , !

---

df = read.table(text="Sample motif chromosome
1      CT-G.A    1
                1      TA-C.C    1
                1      TC-G.C    2
                2      CG-A.T    2
                2      CA-G.T    2
                2      CA-G.T    2",header=T)


library(tidyr)
library(dplyr)

df  %>% mutate(value=1) %>% complete(motif,chromosome,Sample,fill=list(value=0)) %>%
  mutate(key=paste0(motif,',chr',chromosome)) %>%
  group_by(Sample,key) %>%
  summarize(value = sum(value)) %>%
  spread(key,value) %>% 
  as.data.frame

:

  Sample CA-G.T,chr1 CA-G.T,chr2 CG-A.T,chr1 CG-A.T,chr2 CT-G.A,chr1 CT-G.A,chr2 TA-C.C,chr1 TA-C.C,chr2 TC-G.C,chr1 TC-G.C,chr2
1      1           0           0           0           0           1           0           1           0           0           1
2      2           0           2           0           1           0           0           0           0           0           0

Answer 2

, -, , factor , ( dcast , ).

@Florian, :

library(data.table)
cols <- c("motif", "chromosome")
setDT(df)[, (cols) := lapply(.SD, factor), .SDcols = cols][
  , dcast(unique(.SD)[, value := 1L], 
          Sample ~ motif + chromosome, value.var = "value", 
          fill = 0L, drop = FALSE)]
#   Sample CA-G.T_1 CA-G.T_2 CG-A.T_1 CG-A.T_2 CT-G.A_1 CT-G.A_2 TA-C.C_1 TA-C.C_2 TC-G.C_1 TC-G.C_2
# 1      1        0        0        0        0        1        0        1        0        0        1
# 2      2        0        1        0        1        0        0        0        0        0        0

"cols" myfun() , .

---

"tidyverse", @Florian, , - :

library(tidyverse)
df %>%
  mutate_at(c("motif", "chromosome"), factor) %>%
  mutate(value = 1) %>%
  distinct() %>%
  mutate(key = interaction(motif, chromosome)) %>%
  select(-motif, -chromosome) %>%
  spread(key, value, fill = 0, drop = FALSE)

---

@Florian Gist.

10 000 20 :

Answer 3

This will work for you. I used the package tidyrand dplyr. In fact, I preferred to use uniteand expand.gridfrom base rto achieve, finally usingspread

df <- read.table(text = "Sample motif chromosome
    1      CT-G.A    1
           1      TA-C.C    1
           1      TC-G.C    2
           2      CG-A.T    2
           2      CA-G.T    2", header = TRUE)

#add a column to represent presence of chromosome    
df$val <- 1
library(tidyr)
library(dplyr)

#Complete missing rows
df_complete <- left_join(
          expand.grid(Sample=unique(df$Sample), motif=unique(df$motif), 
                         chromosome=unique(df$chromosome)),
             df, by = c("Sample", "motif", "chromosome"), copy = TRUE)

#Additional rows should have val = 0
df_complete$val[is.na(df_complete$val)] <- 0

df_complete %>%
    unite(motif, c("motif", "chromosome"), sep = ",chr" ) %>% 
    spread(motif, val)

#Result
  Sample CA-G.T,chr1 CA-G.T,chr2 CG-A.T,chr1 CG-A.T,chr2 CT-G.A,chr1 CT-G.A,chr2 TA-C.C,chr1 TA-C.C,chr2 TC-G.C,chr1 TC-G.C,chr2
1      1           0           0           0           0           1           0           1           0           0           1
2      2           0           1           0           1           0           0           0           0           0           0