Mass Spec Merge Script by JJ

gistfile1.txt

library(mzR)
library(msdata)
library(jsonlite)
library("RSQLite")
z <- openMSfile("iTRAQ_Erwinia.mzML")
x <- openIDfile("iTRAQ_Erwinia.mzid")
p <- psms(x)
m <- modifications(x)
s <- score(x)
## add a column for the scanNum by parsing
psm <- data.frame(scanNum=sub("^.*=(\\d+)$","\\1",p[,1]),p)
scanl <- 1:runInfo(z)$scanCount
moz <- sapply(scanl,function(y) toJSON(peaks(z,y)[,1]))
intensity <- sapply(scanl,function(y) toJSON(peaks(z,y)[,2]))
pkdf <- data.frame(scanNum = scanl, moz = moz, intensity = intensity)

## mzR to RSQLite
sqlite <- dbDriver("SQLite")
dbconn <- dbConnect(sqlite,"iTRAQ_Erwinia.sqlite")
dbWriteTable(dbconn,"psm",psm)
dbWriteTable(dbconn,"scan",header(z))
dbWriteTable(dbconn,"score",s)
dbWriteTable(dbconn,"modification",m)
dbWriteTable(dbconn,"peaks",pkdf)


The resulting DB:
sqlite> .schema
CREATE TABLE modification
( "spectrumID" TEXT,
        "sequence" TEXT,
        "name" TEXT,
        "mass" REAL,
        "location" INTEGER
);
CREATE TABLE peaks
( "scanNum" INTEGER,
        "moz" TEXT,
        "intensity" TEXT
);
CREATE TABLE psm
( "scanNum" TEXT,
        "spectrumID" TEXT,
        "chargeState" INTEGER,
        "rank" INTEGER,
        "passThreshold" INTEGER,
        "experimentalMassToCharge" REAL,
        "calculatedMassToCharge" REAL,
        "sequence" TEXT,
        "modNum" INTEGER,
        "isDecoy" INTEGER,
        "post" TEXT,
        "pre" TEXT,
        "start" INTEGER,
        "end" INTEGER,
        "DatabaseAccess" TEXT,
        "DatabaseSeq" TEXT,
        "DatabaseDescription" TEXT
);
CREATE TABLE scan
( "seqNum" INTEGER,
        "acquisitionNum" INTEGER,
        "msLevel" INTEGER,
        "polarity" INTEGER,
        "peaksCount" INTEGER,
        "totIonCurrent" REAL,
        "retentionTime" REAL,
        "basePeakMZ" REAL,
        "basePeakIntensity" REAL,
        "collisionEnergy" REAL,
        "ionisationEnergy" REAL,
        "lowMZ" REAL,
        "highMZ" REAL,
        "precursorScanNum" INTEGER,
        "precursorMZ" REAL,
        "precursorCharge" INTEGER,
        "precursorIntensity" REAL,
        "mergedScan" INTEGER,
        "mergedResultScanNum" INTEGER,
        "mergedResultStartScanNum" INTEGER,
        "mergedResultEndScanNum" INTEGER
);
CREATE TABLE score
( "spectrumID" TEXT,
        "MS_GF_RawScore" REAL,
        "MS_GF_DeNovoScore" REAL,
        "MS_GF_SpecEValue" REAL,
        "MS_GF_EValue" REAL
);