Pivot Table Help: A 'VALUE' Filters object has matched 10 row(s). Should have matched at most one row

Question

[cthangav]

I have a datasheet like this:

 TF TG  Score   FDR REs
 Pou5f1 L1td1   1.09E+06    9.24E-06    chr4_98727908_98728497
 Pou5f1 Cd109   580062  9.24E-06    chr9_78623220_78624062;chr9_78615332_78616035
 Sox2   Insm1   467781  9.24E-06    chr2_146299937_146300524;chr2_146218029_146218779;chr2_146290863_146291290;chr2_146257123_146257330;chr2_146221410_146222199;chr2_146302245_146302635;chr2_146062353_146063082;chr2_145958441_145958744;chr2_146834367_146834661
 Sox2   L1td1   428168  9.24E-06    chr4_98727908_98728497;chr4_98726641_98726938
 Pou5f1 Insm1   424962  9.24E-06    chr2_146218029_146218779;chr2_146257123_146257330;chr2_146221410_146222199;chr2_146302245_146302635;chr2_146062353_146063082;chr2_145958441_145958744;chr2_146834367_146834661
 Sox2   Cd109   334763  9.24E-06    chr9_78623220_78624062;chr9_78615332_78616035

And I'm trying to create a pivot table where the rows are TF-TG pairs, and the columns are REs (the genomic regions). The cell values would be the score.

              chr4_98727908_98728497   chr9_78623220_78624062   chr9_78615332_78616035
Pou5f1-L1td1  1.09E+06
Pou5f1-Cd109  0                        580062                   580062

It's involves concatinating the TF/TG columns together, and separating out the RE column.

I tried to do this with Pivottabler:

ListerPivot <- qpvt(ListerMEF, rows = "TFTG", columns = "REs", calculations = "Score")

But I keep getting this error message:

Error: PivotTable$getSummaryValueFromBatch:  A 'VALUE' Filters object has matched 10 row(s).  Should have matched at most one row

Answer 1

[zx8754]

Using data.table, split on delimiter add it as a new row, then reshape long-to-wide using dcast

library(data.table)

d <- fread("TF TG  Score   FDR REs
 Pou5f1 L1td1   1.09E+06    9.24E-06    chr4_98727908_98728497
 Pou5f1 Cd109   580062  9.24E-06    chr9_78623220_78624062;chr9_78615332_78616035
 Sox2   Insm1   467781  9.24E-06    chr2_146299937_146300524;chr2_146218029_146218779;chr2_146290863_146291290;chr2_146257123_146257330;chr2_146221410_146222199;chr2_146302245_146302635;chr2_146062353_146063082;chr2_145958441_145958744;chr2_146834367_146834661
 Sox2   L1td1   428168  9.24E-06    chr4_98727908_98728497;chr4_98726641_98726938
 Pou5f1 Insm1   424962  9.24E-06    chr2_146218029_146218779;chr2_146257123_146257330;chr2_146221410_146222199;chr2_146302245_146302635;chr2_146062353_146063082;chr2_145958441_145958744;chr2_146834367_146834661
 Sox2   Cd109   334763  9.24E-06    chr9_78623220_78624062;chr9_78615332_78616035")

d[, .(x = unlist(strsplit(REs, split = ";"))), by = .(TF, TG, Score)
  ][, dcast(.SD, TF + TG ~ x, value.var = "Score")]

#        TF    TG chr2_145958441_145958744 chr2_146062353_146063082
# 1: Pou5f1 Cd109                       NA                       NA
# 2: Pou5f1 Insm1                   424962                   424962
# 3: Pou5f1 L1td1                       NA                       NA
# 4:   Sox2 Cd109                       NA                       NA
# 5:   Sox2 Insm1                   467781                   467781
# 6:   Sox2 L1td1                       NA                       NA
#    chr2_146218029_146218779 chr2_146221410_146222199 chr2_146257123_146257330
# 1:                       NA                       NA                       NA
# 2:                   424962                   424962                   424962
# 3:                       NA                       NA                       NA
# 4:                       NA                       NA                       NA
# 5:                   467781                   467781                   467781
# 6:                       NA                       NA                       NA
#    chr2_146290863_146291290 chr2_146299937_146300524 chr2_146302245_146302635
# 1:                       NA                       NA                       NA
# 2:                       NA                       NA                   424962
# 3:                       NA                       NA                       NA
# 4:                       NA                       NA                       NA
# 5:                   467781                   467781                   467781
# 6:                       NA                       NA                       NA
# chr2_146834367_146834661 chr4_98726641_98726938 chr4_98727908_98728497
# 1:                       NA                     NA                     NA
# 2:                   424962                     NA                     NA
# 3:                       NA                     NA                1090000
# 4:                       NA                     NA                     NA
# 5:                   467781                     NA                     NA
# 6:                       NA                 428168                 428168
#    chr9_78615332_78616035 chr9_78623220_78624062
# 1:                 580062                 580062
# 2:                     NA                     NA
# 3:                     NA                     NA
# 4:                 334763                 334763
# 5:                     NA                     NA
# 6:                     NA                     NA