[Echobase-commits] r765 - R-EchoBase

Author: mdoray Date: 2012-10-30 12:43:24 +0100 (Tue, 30 Oct 2012) New Revision: 765 Url: http://forge.codelutin.com/repositories/revision/echobase/765 Log: Added: R-EchoBase/Baracouda2Echobase.r R-EchoBase/R-echobaseLegacyCheck.r R-EchoBase/R-echobase_functions.r Added: R-EchoBase/Baracouda2Echobase.r =================================================================== --- R-EchoBase/Baracouda2Echobase.r (rev 0) +++ R-EchoBase/Baracouda2Echobase.r 2012-10-30 11:43:24 UTC (rev 765) @@ -0,0 +1,217 @@ +#All sA per echotype per ESDUs from old barac +#------------------------- +path='Q:/Projects/Acoustic biomass assessment/Data/ALL/barac2R/ESDU.txt' + +esduall=read.table(path,sep=';',header=TRUE) +names(esduall) +aggregate(esduall$ENERGIE,list(esduall$CAMPAGNE),summary) + +#------------------------- +#All sA per echotype per ESDUs from new barac +#------------------------- +path='Z:/Campagnes/PELGAS/Data/barac2R/resEsduEchotype.txt' + +resduall=read.table(path,sep=';',header=TRUE) +names(resduall) +aggregate(resduall$ENERGIE,list(resduall$CAMPAGNE),summary) + +#All ENERGIES converted to sA in this case... + +#Select PELGAS cruises +#------------------------- +resduall.PELGAS=resduall[substr(resduall$CAMPAGNE,1,6)=='PELGAS',] + +# #Select PELGAS2000 and 2001 for sa correction +# resduall.PELGAS0001=resduall.PELGAS[resduall.PELGAS$CAMPAGNE%in% +# c('PELGAS2000','PELGAS2001'),] +# names(resduall.PELGAS0001) +# +# #sA<->En correspondence +# #(after Diner, 2005. EVALUATION DE STOCK PAR ECHO-INTEGRATION +# #(r?actualisation de la note au CIEM en 1983 - revu en d?cembre 98 puis juillet 2005) +# # sA=0.02327*En/De where De=distance in nautical miles +# #convert En to NASC +# #-------------------------- +# resduall.PELGAS0001$ENERGIE=resduall.PELGAS0001$ENERGIE*0.02327 +# +# #binds series +# #-------------------------- +# dim(resduall.PELGAS) +# resduall.PELGAS=rbind(resduall.PELGAS[!resduall.PELGAS$CAMPAGNE%in% +# c('PELGAS2000','PELGAS2001'),],resduall.PELGAS0001) +# dim(resduall.PELGAS) +# +# aggregate(resduall.PELGAS$ENERGIE,list(resduall.PELGAS$CAMPAGNE),summary) +# +#************************************************************************* +# #Impossible-> En have already been converted into Sa in baracouda... +#************************************************************************* + +#Rename columns +#-------------------------- +names(resduall.PELGAS)=c('Voyage','name','echotype','NASC', + 'ReferenceStationCatch','Formula','Flag') + +#remove (or not?) null NASC +#-------------------------- +dim(resduall.PELGAS) +resduall.PELGAS.wo0=resduall.PELGAS[resduall.PELGAS$NASC!=0,] +dim(resduall.PELGAS.wo0) + + #Export sA and hauls per esdu and echotype + #----------------- + path.export='Z:/Campagnes/PELGAS/Data/barac2R/' + write.table(resduall.PELGAS, + paste(path.export,'PELGAS0010resEsduEchotype4Echobase.csv', + sep=''),sep=';',row.names=FALSE) + +#Biometries +path='C:/Users/mdoray.IFR/Documents/Data/Echobase/export-SampleData.csv' +sampleData=read.table(path,sep=';',header=TRUE) +names(sampleData) +aggregate(resduall$ENERGIE,list(resduall$CAMPAGNE),summary) +rm(sampleData) + +#---------------------------------------------------- +#missing operations: 1 depthstratum problem and "unsorted" samples problems +#---------------------------------------------------- +#Fishing data to be added to legacy data in Echobase format +#---------------------------------------------------- +#import missing baracouda fishing data +path='~/.gvfs/donnees2 sur nantes/Campagnes/bd/Echobase/EchobaseLegacyCheck/BaracoudaExports/PECHE_verifImportEchobase.txt' +missing.fbara=read.table(path,sep=';',header=TRUE) +missing.fbara$PTRI=missing.fbara$PT +missing.fbaraH=missing.fbara[missing.fbara$SIGNEP=='H',] +head(missing.fbara) +dim(missing.fbara) +unique(missingPechei4Echobase$operationId) + +missingPechei4Echobase[missingPechei4Echobase$operationId=='N0408',] + +missingPechei4Echobase=missing.fbara[,c('NOSTA','GENR_ESP','SIGNEP','PT','NT', + 'LM','PM','MOULE','PTRI')] +names(missingPechei4Echobase)=c("operationId","baracoudaCode","sizeCategory", + "sampleWeight","numberSampled","meanLength", + "meanWeight","noPerKg","sortedWeight") +head(missingPechei4Echobase) +path.export='~/.gvfs/donnees2 sur nantes/Campagnes/bd/Echobase/EchobaseLegacyCheck/' +write.table(missingPechei4Echobase,paste(path.export,'missing_TotalSamplesR4Echobase.csv',sep=''),sep=';', + row.names=FALSE) + +#Biological measurements data to be added to legacy data in Echobase format +#---------------------------------------------------- +#import missing baracouda fishing data +path='~/.gvfs/donnees2 sur nantes/Campagnes/bd/Echobase/EchobaseLegacyCheck/BaracoudaExports/MENS_checkEchobase.txt' +missing.mbara=read.table(path,sep=';',header=TRUE) +head(missing.mbara) +missing.mbara$Lcm=missing.mbara$TAILLE +missing.mbara$CATEG=missing.mbara$SIGNEP +missing.mbara$SEXE='N' +missing.mbara$UNITE=0 +missing.mbara$INC=5 +dim(missing.mbara) +missing.mbara[is.na(missing.mbara$POIDSTAILLE),'POIDSTAILLE']=missing.mbara[is.na(missing.mbara$POIDSTAILLE),"POIDSECHANT"]/ + missing.mbara[is.na(missing.mbara$POIDSTAILLE),'NBIND'] + +missing.mens4Echobase=missing.mbara[,c('NOSTA','GENR_ESP','CATEG','SEXE', + 'POIDSECHANT','NECHANT','Lcm','NBIND', + 'POIDSTAILLE','UNITE','INC')] +names(missing.mens4Echobase)=c('operationId','baracoudaCode', + 'sizeCategory','sexCategory', + 'sampleWeight','numberSampled', + 'lengthClass','numberAtLength', + 'weightAtLength','units','round') +head(missing.mens4Echobase) +path.export='~/.gvfs/donnees2 sur nantes/Campagnes/bd/Echobase/EchobaseLegacyCheck/' +write.table(missing.mens4Echobase,paste(path.export,'missing_subSamplesR4Echobase.csv',sep=''),sep=';', + row.names=FALSE) + +#Creates operations to be added to legacy data in Echobase format +#---------------------------------------------------- +path='W:/Campagnes/bd/Echobase/EchobaseLegacyCheck/BaracoudaExports/missing_Operations4Echobase.txt' +path='~/.gvfs/donnees2 sur nantes/Campagnes/bd/Echobase/EchobaseLegacyCheck/BaracoudaExports/missing_Operations4Echobase.txt' + +baracmope=read.table(path,sep=';',header=TRUE) +head(baracmope) +names(baracmope) + +Operations=data.frame(vesselName=baracmope$NAVIRE,operationId=baracmope$NOSTA, + depthStratumId=baracmope$STA_IMAGES, + gearShootingStartTime=baracmope$DHTUDEB, + midHaulLatitude=baracmope$LATDD, + midHaulLongitude=baracmope$LGDD, + gearShootingStartLatitude=baracmope$LATDD, + gearShootingStartLongitude=baracmope$LGDD, + gearShootingEndTime=NA, + gearShootingEndLatitude=NA, + gearShootingEndLongitude=NA, + gearCode=baracmope$ENGIN) + +Operations$gearShootingStartTime=paste(format(strptime( + Operations$gearShootingStartTime,"%d/%m/%Y %H:%M:%S")),'.0000',sep='') + +dim(Operations) +unique(Operations$vesselName) +unique(Operations$gearCode) +head(Operations) + +#------------------ +#Export OperationMetadataValue from baracouda to echobase +#------------------ +OperationMetadataValue=rbind(data.frame(vesselName=baracmope$NAVIRE, + operationId=baracmope$NOSTA,metadataType='MeanWaterDepth', + operationMetadataValue=baracmope$SONDEDEB), + data.frame(vesselName=baracmope$NAVIRE, + operationId=baracmope$NOSTA,metadataType='WaterDepthShoot', + operationMetadataValue=as.numeric(baracmope$SONDEDEB)), + data.frame(vesselName=baracmope$NAVIRE, + operationId=baracmope$NOSTA,metadataType='WaterDepthHaul', + operationMetadataValue=as.numeric(baracmope$SONDEFIN))) +head(OperationMetadataValue) +dim(OperationMetadataValue) +#remove rows with NAs +OperationMetadataValue=OperationMetadataValue[complete.cases(OperationMetadataValue),] +dim(OperationMetadataValue) +#------------------ +#GearMetadataValue +#------------------ +names(baracmope) + +GearMetadataValue=rbind(data.frame(vesselName=baracmope$NAVIRE, + operationId=baracmope$NOSTA, + gearCode=baracmope$ENGIN, + metadataType='CableLength', + gearMetadataValue=as.numeric(baracmope$LFUNES)), + data.frame(vesselName=baracmope$NAVIRE, + operationId=baracmope$NOSTA, + gearCode=baracmope$ENGIN, + metadataType='MinSpeed', + gearMetadataValue=as.numeric(baracmope$VMIN)), + data.frame(vesselName=baracmope$NAVIRE, + operationId=baracmope$NOSTA, + gearCode=baracmope$ENGIN, + metadataType='MaxSpeed', + gearMetadataValue=as.numeric(baracmope$VMAX))) +head(GearMetadataValue) + +dim(GearMetadataValue) +#remove rows with NAs +GearMetadataValue=GearMetadataValue[complete.cases(GearMetadataValue),] +dim(GearMetadataValue) + +table(GearMetadataValue$metadataType) +table(as.character(GearMetadataValue$operationId)) + +#Export Operation data in Echobase format +path.export='~/.gvfs/donnees2 sur nantes/Campagnes/bd/Echobase/EchobaseLegacyCheck/' + +write.table(Operations,paste(path.export,'missing_Operations4Echobase.csv',sep=''), + row.names=FALSE,sep=';') +write.table(OperationMetadataValue, + paste(path.export,'missing_OperationMetadataValue4Echobase.csv',sep=''), + row.names=FALSE,sep=';') +write.table(GearMetadataValue,paste(path.export, + 'missing_GearMetadataValue4Echobase.csv',sep=''),row.names=FALSE, + sep=';') + + Added: R-EchoBase/R-echobaseLegacyCheck.r =================================================================== --- R-EchoBase/R-echobaseLegacyCheck.r (rev 0) +++ R-EchoBase/R-echobaseLegacyCheck.r 2012-10-30 11:43:24 UTC (rev 765) @@ -0,0 +1,474 @@ +library(RPostgreSQL) +drv <- dbDriver("PostgreSQL") +summary(drv) +dbListConnections(drv) +dbGetInfo(drv) + +con <- dbConnect(drv,host='acoustica', + dbname="echobase-pelgas-2000-2010",user='echobase', + password='echobase',port=5432) + +# con <- dbConnect(drv,host='localhost', +# dbname="echobase-legacy",user='echobase', +# password='echobase',port=5432) + +# res <- dbSendQuery(con, "SELECT * from mission") +# data <- fetch(res, n = -1) +# alternatively, use dbGetQuery + +#-------------------------- +#Import all fishing subsamples +#-------------------------- +sqlSubSamples="SELECT voyage.name AS voyage_name, operation.id AS operation_id, sampletype.name AS sampletype, species.genusspecies, sample.sampleweight, sample.numbersampled, sampledata.datalabel, sampledata.datavalue, sampledatatype.name, sampledatatype.units, sampledatatype.precision, sampledatatype.meaningen, sampledatatype.raptribiometrydatatype FROM public.voyage AS voyage, public.mission AS mission, public.transit AS transit, public.transect AS transect, public.operation AS operation, public.sample AS sample, public.sampledata AS sampledata, public.sampledatatype AS sampledatatype, public.sampletype AS sampletype, public.speciescategory AS speciescategory, public.species AS species, public.sizecategory AS sizecategory WHERE voyage.mission = mission.topiaid AND transit.voyage = voyage.topiaid AND transect.transit = transit.topiaid AND operation.transect = transect.topiaid AND sample.operation = operation.topiaid AND sampledata.sample = sample.topiaid AND sampledata.sampledatatype = sampledatatype.topiaid AND sample.sampletype = sampletype.topiaid AND sample.speciescategory = speciescategory.topiaid AND speciescategory.species = species.topiaid AND speciescategory.sizecategory = sizecategory.topiaid AND sampletype.name = 'Subsample'" +Allmens <- dbGetQuery(con,sqlSubSamples) +table(Allmens$voyage_name) +dim(Allmens) +head(Allmens) +head(mens) +mens.range.check(mens) +path='~/.gvfs/q sur nantes/Projects/Acoustic biomass assessment/Methods/Database/Echobase/Baracouda2Echobase/RequetesPeche/Subsample_SampleData.txt' +subs.bara=read.table(path,sep=';',header=TRUE) +dim(Allmens) +dim(subs.bara) +head(subs.bara) +table(subs.bara$voyage_name) + +#-------------------------- +#Import all fishing samples +#-------------------------- +#From echobase +sqlPeche="SELECT voyage.name AS voyage_name, vessel.name AS vessel_name, +operation.id AS operation_id, depthstratum.id AS depthstratum, +operation.gearshootingstarttime, operation.midhaullatitude, +operation.midhaullongitude, sampletype.name AS sample_type, species.genusspecies, +species.baracoudacode, sizecategory.name AS size_cat, sample.sampleweight, +sample.numbersampled, sampledata.datavalue,sampledatatype.name AS data_type, +sampledatatype.meaningen, sampledatatype.raptribiometrydatatype FROM +public.voyage AS voyage, public.mission AS mission, public.transit AS transit, +public.transect AS transect, public.operation AS operation, +public.sample AS sample, public.sampledata AS sampledata, +public.sampledatatype AS sampledatatype, public.sampletype AS sampletype, +public.speciescategory AS speciescategory, public.species AS species, +public.sizecategory AS sizecategory, public.depthstratum AS depthstratum, +public.vessel AS vessel WHERE voyage.mission = mission.topiaid AND transit.voyage = voyage.topiaid AND transect.transit = transit.topiaid AND operation.transect = transect.topiaid AND sample.operation = operation.topiaid AND sampledata.sample = sample.topiaid AND sampledata.sampledatatype = sampledatatype.topiaid AND sample.sampletype = sampletype.topiaid AND sample.speciescategory = speciescategory.topiaid AND speciescategory.species = species.topiaid AND speciescategory.sizecategory = sizecategory.topiaid AND operation.depthstratum = depthstratum.topiaid AND transect.vessel = vessel.topiaid AND sampletype.name = 'Total' OR sampletype.name = 'Unsorted'" +AllPeche <- dbGetQuery(con,sqlPeche) +table(AllPeche$voyage_name) +plot(AllPeche$midhaullongitude,AllPeche$midhaullatitude) +identify(AllPeche$midhaullongitude,AllPeche$midhaullatitude) +#pb with: +AllPeche[12480,] +head(AllPeche) +AllPeche[AllPeche$operation_id=='N0408',] +unique(AllPeche$operation_id) + +unique(AllPeche[AllPeche$voyage_name=='PELGAS2009','vessel_name']) +unique(AllPeche[,'vessel_name']) +AllPeche[AllPeche$operation_id=='N5381',] + +#From baracouda +path='/home/mathieubuntu/Documents/Echobase/EchobaseLegacyCheck/BaracoudaExports/PECHE.txt' +peche.bara=read.table(path,sep=';',header=TRUE) +head(peche.bara) + +#No of operations +op.bara=unique(peche.bara[,c('CAMPAGNE','NOSTA')]) +op.bara$opid=paste(op.bara$CAMPAGNE,op.bara$NOSTA) +unique(peche.bara$ETAT) +op.bara.reel=unique(peche.bara[peche.bara$ETAT!='FICTIF',c('CAMPAGNE','NOSTA')]) +op.bara.reel$opid=paste(op.bara.reel$CAMPAGNE,op.bara.reel$NOSTA) +op.bara.fictif=unique(peche.bara[peche.bara$ETAT=='FICTIF',c('CAMPAGNE','NOSTA')]) +op.EB=unique(AllPeche[,c('voyage_name','operation_id')]) +op.EB$opid=paste(op.EB$voyage_name,op.EB$operation_id) +table(peche.bara$CAMPAGNE) + +NOSTA.comp=merge(data.frame(table(op.bara$CAMPAGNE)), + data.frame(table(op.bara.reel$CAMPAGNE)),by.x='Var1', + by.y='Var1') +names(NOSTA.comp)=c('Voyage','Nop.bara','Nop.bara.reel') +NOSTA.comp=merge(NOSTA.comp, + data.frame(table(op.bara.fictif$CAMPAGNE)),by.x='Voyage', + by.y='Var1') +names(NOSTA.comp)=c('Voyage','Nop.bara','Nop.bara.reel','Nop.bara.fictif') +NOSTA.comp=merge(NOSTA.comp, + data.frame(table(op.EB$voyage_name)),by.x='Voyage', + by.y='Var1') +names(NOSTA.comp)=c('Voyage','Nop.bara','Nop.bara.reel','Nop.bara.fictif','Nop.EB') + +NOSTA.comp$diff=NOSTA.comp$Nop.bara-NOSTA.comp$Nop.bara.fictif-NOSTA.comp$Nop.EB + +missing.op=op.bara.reel[!is.element(op.bara.reel$opid,op.EB$opid),] + +#-------------------------- +#Import all echotypes +#-------------------------- +sqlEchotypes="SELECT mission.name AS mission_name, voyage.name AS voyage_name, echotype.name AS echotype_name, echotype.meaning, species.genusspecies FROM public.voyage AS voyage, public.mission AS mission, public.echotype_species AS echotype_species, public.echotype AS echotype, public.echotype_voyage AS echotype_voyage, public.species AS species WHERE voyage.mission = mission.topiaid AND echotype_species.echotype = echotype.topiaid AND echotype_voyage.echotype = echotype.topiaid AND echotype_voyage.voyage = voyage.topiaid AND echotype_species.species = species.topiaid" +AllEchotypes <- dbGetQuery(con,sqlEchotypes) +head(AllEchotypes) +rowSums(table(AllEchotypes$voyage_name,AllEchotypes$echotype_name)) + +path='~/.gvfs/q sur nantes/Projects/Acoustic biomass assessment/Methods/Database/Echobase/Baracouda2Echobase/RequetesAcoustics/resultats/VoyageEchotype.txt' +echotypes.bara=read.table(path,sep=';',header=TRUE) +head(echotypes.bara) +dim(unique(echotypes.bara[,-6])) + +#Import all voyages +#-------------------------- +sqlVoyages="SELECT voyage.name, voyage.startdate, voyage.enddate FROM +public.voyage AS voyage, public.mission AS mission +WHERE voyage.mission = mission.topiaid" +Voyages <- dbGetQuery(con,sqlVoyages) +head(Voyages) + +#Import all esdu cells +#-------------------------- +sqlCellsIndirect="SELECT voyage.name AS voyage_name, vessel.name AS vessel_name, +acousticinstrument.transceivermodel, acousticinstrument.frequency, +dataprocessing.processingtemplate, dataprocessing.processingdescription, +dataprocessing.sounderconstant, dataprocessing.eithresholdlow, +dataprocessing.acousticdensityunit, cell.name AS cell_name, +celltype.name AS celltype, celltype.id AS cell_id, cell.topiaid, +cell.cell FROM public.voyage AS voyage, public.mission AS mission, +public.transit AS transit, public.transect AS transect, +public.dataacquisition AS dataacquisition, +public.dataprocessing AS dataprocessing, public.cell AS cell, +public.vessel AS vessel, public.acousticinstrument AS acousticinstrument, +public.celltype AS celltype WHERE voyage.mission = mission.topiaid AND transit.voyage = voyage.topiaid AND transect.transit = transit.topiaid AND dataacquisition.transect = transect.topiaid AND dataprocessing.dataacquisition = dataacquisition.topiaid AND cell.dataprocessing = dataprocessing.topiaid AND transect.vessel = vessel.topiaid AND dataacquisition.acousticinstrument = acousticinstrument.topiaid AND cell.celltype = celltype.topiaid AND celltype.id = 'Esdu'" + +EsduCellsIndirect <- dbGetQuery(con,sqlCellsIndirect) +head(EsduCellsIndirect) +(NesduCells=dim(EsduCellsIndirect)[1]) +table(EsduCellsIndirect$voyage_name) +#EsduCellsIndirect[EsduCellsIndirect$voyage_name=='PELGAS2010','cell_name'] + +#Import all data acquisition/processing +#-------------------------- +sqlDataAcqProc="SELECT voyage.name, dataprocessing.processingtemplate, dataprocessing.digitthreshold, dataprocessing.eithresholdlow, dataprocessing.eithresholdhigh, dataprocessing.transceiverprocessinggain, dataprocessing.processingsoftwareversion, dataprocessing.transceiverprocessingsacorrection, dataprocessing.transceiverprocessingabsorption, dataprocessing.transceiverprocessingabsorptiondescription, dataprocessing.transducerprocessingbeamangleathwartship, dataprocessing.transducerprocessingpsi, dataprocessing.transducerprocessingbeamanglealongship FROM public.voyage AS voyage, public.mission AS mission, public.transit AS transit, public.transect AS transect, public.dataacquisition AS dataacquisition, public.dataprocessing AS dataprocessing WHERE voyage.mission = mission.topiaid AND transit.voyage = voyage.topiaid AND transect.transit = transit.topiaid AND dataacquisition.transect = transect.topiaid AND dataprocessing.dataacquisition = dataacquisition.topiaid" +DataAcqProc <- dbGetQuery(con,sqlDataAcqProc) +head(DataAcqProc) + +#Import all esdu +#-------------------------- +sqlEsduCells="SELECT voyage.name AS voyage_name, vessel.name AS vessel_name, +acousticinstrument.transceivermodel, acousticinstrument.frequency, +dataprocessing.processingtemplate, dataprocessing.processingdescription, +dataprocessing.sounderconstant, dataprocessing.eithresholdlow, +dataprocessing.acousticdensityunit, cell.name AS cell_name, +celltype.id AS cell_type +FROM public.voyage AS voyage, +public.mission AS mission, public.transit AS transit, public.transect AS transect, +public.dataacquisition AS dataacquisition, public.dataprocessing AS dataprocessing, +public.cell AS cell, public.vessel AS vessel, +public.acousticinstrument AS acousticinstrument, public.celltype AS celltype +WHERE voyage.mission = mission.topiaid AND transit.voyage = voyage.topiaid AND +transect.transit = transit.topiaid AND dataacquisition.transect = transect.topiaid +AND dataprocessing.dataacquisition = dataacquisition.topiaid AND +cell.dataprocessing = dataprocessing.topiaid AND transect.vessel = vessel.topiaid +AND dataacquisition.acousticinstrument = acousticinstrument.topiaid AND +cell.celltype = celltype.topiaid AND +celltype.id = 'Esdu'" +EsduCells <- dbGetQuery(con,sqlEsduCells) +head(EsduCells) +dim(EsduCells) +length(unique(EsduCells$cell_name)) + +#Import all esdu cells data +#-------------------------- +sqlEsduCellsData="SELECT voyage.name AS voyage_name, vessel.name AS vessel_name, +acousticinstrument.transceivermodel, acousticinstrument.frequency, +dataprocessing.processingtemplate, dataprocessing.processingdescription, +dataprocessing.sounderconstant, dataprocessing.eithresholdlow, +dataprocessing.acousticdensityunit, cell.name AS cell_name, +celltype.id AS cell_type, +datametadata.name AS data_type, data.datavalue AS data_value FROM public.voyage AS voyage, +public.mission AS mission, public.transit AS transit, public.transect AS transect, public.dataacquisition AS dataacquisition, public.dataprocessing AS dataprocessing, public.cell AS cell, public.vessel AS vessel, public.acousticinstrument AS acousticinstrument, public.celltype AS celltype, public.data AS data, public.datametadata AS datametadata WHERE voyage.mission = mission.topiaid AND transit.voyage = voyage.topiaid AND transect.transit = transit.topiaid AND dataacquisition.transect = transect.topiaid AND dataprocessing.dataacquisition = dataacquisition.topiaid AND cell.dataprocessing = dataprocessing.topiaid AND transect.vessel = vessel.topiaid AND dataacquisition.acousticinstrument = acousticinstrument.topiaid AND cell.celltype = celltype.topiaid AND data.cell = cell.topiaid AND data.datametadata = datametadata.topiaid AND celltype.id = 'Esdu'" +EsduCellsData <- dbGetQuery(con,sqlEsduCellsData) +head(EsduCellsData) +length(unique(EsduCellsData$cell_name)) + +#same as esdu no. +unique(EsduCellsData$cell_type) +stesdus=EsduCellsData[EsduCellsData$data_type=='TimeStart','data_value'] +head(stesdus) +#date time conversion +#turn off locale-specific days of week for date import +Sys.setlocale("LC_TIME", "C") +tesdus <- strptime(paste(substr(stesdus,1,10),substr(stesdus,26,29), + substr(stesdus,12,19)),format="%a %b %d %Y %H:%M:%S") +tesdus <- strptime(paste(substr(stesdus,1,19)),format="%Y-%m-%d %H:%M:%S") +class(tesdus) +plot(tesdus,EsduCellsData[EsduCellsData$data_type=='TimeStart','data_value']) +esdus=cells4humans(df=EsduCellsData,idvar='cell_name') +head(esdus) +dim(esdus) +esdus$esdu.id=paste(esdus$voyage_name,esdus$LongitudeStart,esdus$LatitudeStart) + +(NesduCellsData=dim(EsduCellsData)[1]) + +#duplicated cell names? yes, for same PM/AM hour... +unique(table(EsduCellsData$cell_name)) +duplicated.EsduCellData=names(table(EsduCellsData$cell_name))[table(EsduCellsData$cell_name)==10] +decd=EsduCellsData[EsduCellsData$cell_name%in%duplicated.EsduCellData,] +decd[decd$cell_name=='1983-04-24 07:03:00Z',] + +#import baracouda EI data +path='~/.gvfs/q sur nantes/Projects/Acoustic biomass assessment/Methods/Database/Echobase/Baracouda2Echobase/RequetesAcoustics/resultats/VoyageEchotype.txt' +path='/home/mathieubuntu/Documents/Echobase/EchobaseLegacyCheck/BaracoudaExports/EIlay.txt' +path.EIbara='~/.gvfs/donnees2 sur nantes/Campagnes/bd/Echobase/EchobaseLegacyCheck/BaracoudaExports/EIlay.txt' +EI.bara=read.table(path.EIbara,sep=';',header=TRUE) +head(EI.bara) +dim(EI.bara) +dim(EI.bara[EI.bara$LIBELLE_TRANCHE=='TOTAL',]) +EI.bara$LGDMC=correct.positions( + df=EI.bara$LGDMC,xname="LGDMC",asNewColumn=FALSE) +EI.bara$LATDMC=correct.positions( + df=EI.bara$LATDMC,yname="LATDMC",asNewColumn=FALSE) + +esdus.bara=EI.bara[EI.bara$LIBELLE_TRANCHE=='TOTAL',] +esdus.bara$esdu.id=paste(esdus.bara$CAMPAGNE,esdus.bara$LGDMC,esdus.bara$LATDMC) +head(esdus.bara) +#date time conversion +baratime=esdus.bara$DHTU_ESDU + +bara.tesdus <- strptime(baratime,format="%d/%m/%Y %H:%M:%S") +table(is.element(bara.tesdus,tesdus)) +missing.esdus=esdus.bara[!is.element(bara.tesdus,tesdus),] +dim(esdus.bara) +dim(missing.esdus) +head(missing.esdus) +path.missing='/home/mathieubuntu/Documents/Echobase/EchobaseLegacyCheck/BaracoudaExports/missingEsdus.txt' +write.table(missing.esdus,path.missing,sep=';',row.names=FALSE) + + + +x11() +plot(esdus.bara$LGDMC,esdus.bara$LATDMC,pch=16) +points(missing.esdus$LGDMC,missing.esdus$LATDMC,pch=16,col=2) +points(esdus$LongitudeStart,esdus$LatitudeStart,pch=16,col=3) +coast() +legend('bottomleft',legend=c('esdus in barac','esdus not in Echobase', + 'esdus in Echobase'),pch=16,col=seq(3)) +# identify(esdus.bara$LGDMC,esdus.bara$LATDMC,labels=esdus.bara$DHTU_ESDU) +# identify(esdus$LongitudeStart,esdus$LatitudeStart,labels=esdus$esdu.id) +x11() +plot(missing.esdus$LGDMC,missing.esdus$LATDMC) +coast() +#identify(esdus.bara$LGDMC,esdus.bara$LATDMC,labels=esdus.bara$DHTU_ESDU) + +#plots per cruise +#DAAG1989 has only esdus data, no EI data +lcamp=unique(esdus.bara$CAMPAGNE) +lcamp.eb=unique(esdus$voyage_name) +for (i in seq(length(lcamp))){ + dfi=esdus.bara[esdus.bara$CAMPAGNE==lcamp[i],] + dfi2=esdus[esdus$voyage_name==lcamp[i],] + dfi3=missing.esdus[missing.esdus$CAMPAGNE==lcamp[i],] + x11() + plot(dfi$LGDMC,dfi$LATDMC,main=lcamp[i]) + points(dfi2$LongitudeStart,dfi2$LatitudeStart,pch=16,col=3) + points(dfi3$LGDMC,dfi3$LATDMC,pch=16,col=2) + coast() + legend('bottomleft',legend=c('esdus in barac','esdus not in Echobase', + 'esdus in Echobase'),pch=16,col=seq(3)) +} + +graphics.off() + +unique(missing.esdus[missing.esdus$CAMPAGNE=='PELGAS2008','DHTU_ESDU']) +unique(esdus.bara[esdus.bara$CAMPAGNE=='PELGAS2008','DHTU_ESDU']) +dfs=missing.esdus[substr(missing.esdus$DHTU_ESDU,1,10)%in%'06/07/2008',] +dfs=missing.esdus[substr(missing.esdus$DHTU_ESDU,4,5)%in%'07',] +dim(dfs) +dfs=esdus.bara[substr(esdus.bara$DHTU_ESDU,4,5)%in%'07',] +#***EI data from July in Pelgas2008 and 2009... removed*** +missing.esdus2=missing.esdus[!substr(missing.esdus$DHTU_ESDU,4,5)%in%'07',] +plot(dfs$LGDMC,dfs$LATDMC) +coast() +plot(missing.esdus2$LGDMC,missing.esdus2$LATDMC) +coast() + +#missing EI data selection +missing.EI=EI.bara[EI.bara$DHTU_ESDU%in%missing.esdus2$DHTU_ESDU,] +head(missing.EI) + +#Surface layers sum vs. esdu total in missing esdus +#----------- +ei0=missing.EI[substr(missing.EI$LIBELLE_TRANCHE,1,1)=='S',] +ei0a=aggregate(ei0[,'ENERGIE'],list(ei0[,'DHTU_ESDU']),sum) +names(ei0a)=c('st','sa') +ei0a=merge(ei0a,missing.esdus2,by.x='st',by.y='DHTU_ESDU') +dim(ei0a);dim(missing.esdus2) +ei0a$dSa=ei0a$sa-ei0a$ENERGIE +summary(ei0a$dSa) +hist(Mcsv.base0a$sa-Mcsv.base0a[,'MOVIES_EILayer\\eilayer\\sa']) + +#convert to Echobase format +#Import columns to be filled +#------------------------------------------------------------ +path='Q:/Projects/Acoustic biomass assessment/Methods/Database/Echobase/Newdata2Echobase/MoviesFiles/colonneMovies.txt' +path='~/.gvfs/q sur nantes/R/scripts/AcouBiom/FileFormats/colonneMovies.txt' +nmi=read.table(path,header=TRUE,sep=',') +nmi=as.character(nmi[,1]) +nmi=gsub('/','\\',nmi,fixed=TRUE) +nmi +#Import example file +#------------------------------------------------------------ +path='~/.gvfs/q sur nantes/R/scripts/AcouBiom/FileFormats/results_20120207_161208_lay.csv' +csvex=read.table(path,header=TRUE,sep=';') +head(csvex) +head(missing.esdus) +Mcsv.base=matrix(rep(NA,dim(missing.EI)[1]*dim(csvex)[2]),ncol=dim(csvex)[2]) +Mcsv.base=data.frame(Mcsv.base) +names(Mcsv.base)=gsub('.','\\',as.character(names(csvex)),fixed=TRUE) +#Fill DataAcquisition +Mcsv.base[,'MOVIES_EILayer\\sndset\\channelName']=38000 +Mcsv.base[,'MOVIES_EILayer\\sndset\\absorptionCoef']=8.47 +Mcsv.base[,'MOVIES_EILayer\\sndset\\pulseduration']=1.024 +Mcsv.base[,'MOVIES_EILayer\\sndset\\soundcelerity']='variable' +Mcsv.base[,'MOVIES_EILayer\\sndset\\softChannelId']=47 +Mcsv.base[,'MOVIES_EILayer\\sndset\\transmissionPower']=2000 +#Fill DataProcessing +Mcsv.base['MOVIES_EILayer']=format(Sys.time(), "%Y/%m/%d %H:%M:%OS3") +Mcsv.base[,'MOVIES_EILayer\\cellset\\thresholdlow']=-60 +Mcsv.base['MOVIES_EILayer\\cellset\\thresholdup']=0 +Mcsv.base[,'MOVIES_EILayer\\sndset\\soundcelerity']='variable' +#Fill Esdu/data +Mcsv.base[,'MOVIES_EILayer\\cellset\\lat']=missing.EI$LATDMC +Mcsv.base[,'MOVIES_EILayer\\cellset\\long']=missing.EI$LGDMC +Mcsv.base[,'MOVIES_EILayer\\shipnav\\depth']=missing.EI$SONDE +Mcsv.base[,'MOVIES_EILayer\\cellset\\depthstart']=missing.EI$HMAX_TRANCHE +Mcsv.base[,'MOVIES_EILayer\\cellset\\depthend']=missing.EI$HMIN_TRANCHE +dend=c(format(missing.EI$DHTU_ESDU[-1]),'06/06/1998 15:22:47') +missing.EI$DHTU_ESDU[length(missing.EI$DHTU_ESDU)] +Mcsv.base[,'MOVIES_EILayer\\cellset\\dateend']=paste(dend,'.0000',sep='') +Mcsv.base[,'MOVIES_EILayer\\cellset\\datestart']=paste(format(missing.EI$DHTU_ESDU), + '.0000',sep='') +Mcsv.base[,'MOVIES_EILayer\\eilayer\\sa']=missing.EI$ENERGIE +unique(missing.EI$Nb_Echant_Integre) +unique(missing.EI$Nb_Ecant_Total) +#Mcsv.base[,'MOVIES_EILayer\\eilayer\\ni']=missing.EI$Nb_Echant_Integre +#Mcsv.base[,'MOVIES_EILayer\\eilayer\\nt']=missing.EI$Nb_Ecant_Total +lt=c(paste('S',seq(10),sep=''),paste('F',seq(4),sep=''),'TOTAL') +ebl=seq(0,(length(lt)-1),1) +tlt=data.frame(bara=lt,echo=ebl) +names(Mcsv.base) +ntl2=merge(missing.EI[,c('DHTU_ESDU','LIBELLE_TRANCHE')],tlt,by.x='LIBELLE_TRANCHE', + by.y='bara',sort=FALSE) +ntl2$t=paste(format(ntl2$DHTU_ESDU),'.0000',sep='') +names(ntl2) +names(Mcsv.base) +Mcsv.base[,'MOVIES_EILayer\\cellset\\celltype']=0 +Mcsv.base[missing.EI$LIBELLE_TRANCHE=='TOTAL', + 'MOVIES_EILayer\\cellset\\celltype']=4 +Mcsv.base[substr(missing.EI$LIBELLE_TRANCHE,1,1)=='F', + 'MOVIES_EILayer\\cellset\\celltype']=1 +Mcsv.base[,'MOVIES_EILayer\\cellset']=missing.EI$LIBELLE_TRANCHE +dim(Mcsv.base) +names(Mcsv.base) +Mcsv.base=merge(Mcsv.base,ntl2[,c('t','LIBELLE_TRANCHE','echo')], + by.x=c('MOVIES_EILayer\\cellset\\datestart', + 'MOVIES_EILayer\\cellset'), + by.y=c('t','LIBELLE_TRANCHE')) +dim(Mcsv.base) +names(Mcsv.base) +Mcsv.base[,'MOVIES_EILayer\\cellset\\cellnum']=Mcsv.base[,'echo'] +Mcsv.base[,'MOVIES_EILayer\\cellset\\area']=1 +Mcsv.base$dataQuality=1 +#Mcsv.base[,c('MOVIES_EILayer\\cellset','MOVIES_EILayer\\cellset\\cellnum')] +Mcsv.base[,'MOVIES_EILayer\\cellset']=NA + +#Select columns not entirely padded with NA +nNAcols=colSums(apply(Mcsv.base,2,is.na)) +Mcsv.bases=Mcsv.base[,nNAcols!=dim(Mcsv.base)[1]] +names(Mcsv.base) +Mcsv.base=Mcsv.base[,names(Mcsv.base)!='echo'] +names(Mcsv.base) + +#check export file +#----------------------- +#All esdus cells +#----------- +Mcsv.base4=Mcsv.base[Mcsv.base[,'MOVIES_EILayer\\cellset\\celltype']==4,] +plot(Mcsv.base4[,'MOVIES_EILayer\\cellset\\long'], + Mcsv.base4[,'MOVIES_EILayer\\cellset\\lat'], + cex=log(Mcsv.base4[,'MOVIES_EILayer\\eilayer\\sa']+1)/10,pch=16) +coast() + +#All EI cells +#----------- +plot(Mcsv.base[,'MOVIES_EILayer\\cellset\\long'], + Mcsv.base[,'MOVIES_EILayer\\cellset\\lat'], + cex=log(Mcsv.base[,'MOVIES_EILayer\\eilayer\\sa']+1)/10,pch=16) +coast() + +#All esdus unique? +dim(Mcsv.base4) +length(unique(Mcsv.base4[,'MOVIES_EILayer\\cellset\\datestart'])) + +#Surface layers sum vs. esdu total +#----------- +Mcsv.base0=Mcsv.base[Mcsv.base[,'MOVIES_EILayer\\cellset\\celltype']==0,] + +Mcsv.base0a=aggregate(Mcsv.base0[,'MOVIES_EILayer\\eilayer\\sa'], + list(Mcsv.base0[,'MOVIES_EILayer\\cellset\\datestart']),sum) +names(Mcsv.base0a)=c('st','sa') +Mcsv.base0a=merge(Mcsv.base0a,Mcsv.base4, + by.x='st', + by.y='MOVIES_EILayer\\cellset\\datestart') +dim(Mcsv.base0a);dim(Mcsv.base4) +dSa=Mcsv.base0a$sa-Mcsv.base0a[,'MOVIES_EILayer\\eilayer\\sa'] +summary(Mcsv.base0a$sa-Mcsv.base0a[,'MOVIES_EILayer\\eilayer\\sa']) +hist(Mcsv.base0a$sa-Mcsv.base0a[,'MOVIES_EILayer\\eilayer\\sa']) + +#Export file +#---------------- +path.export='~/.gvfs/donnees2 sur nantes/Campagnes/bd/Echobase/EchobaseLegacyCheck/' +write.table(Mcsv.base, + paste(path.export,'BaracMissing_lay4Echobase.csv',sep=''),sep=';', + row.names=FALSE) + + + + +lcamp=unique(esdus.bara$CAMPAGNE) +for (i in seq(length(lcamp))){ + dfi=esdus.bara[esdus.bara$CAMPAGNE==lcamp[i],] + dfi2=esdus[esdus$voyage_name==lcamp[i],] + dfi3=missing.esdus[missing.esdus$CAMPAGNE==lcamp[i],] + x11() + plot(dfi$LGDMC,dfi$LATDMC,main=lcamp[i]) + points(dfi2$LongitudeStart,dfi2$LatitudeStart,pch=16,col=3) + points(dfi3$LGDMC,dfi3$LATDMC,pch=16,col=2) +} +graphics.off() + +Nesdus.PELGAS2010=EsduCellsData[EsduCellsData$voyage_name=='PELGAS2010'& + EsduCellsData$data_type=='TimeStart','data_value'] + +path='/media/3F6B3FF516350E78/Echobase/EchobaseLegacyCheck/duplicated_esduCell_names.txt' +write.table(decd,path,sep=';',row.names=FALSE) + +#Import all elementary cells +#-------------------------- + + + + + +dbDisconnect(con) + + + + + + +#Connection to .odb file with ODB package +library(ODB) +connection <- odb.open("/home/mathieubuntu/Documents/Echobase/Echobase-acoustica.odb") + +connection <- odb.open("/home/mathieubuntu/Documents/Echobase/test.odb") + +data <- odb.read(connection, "SELECT * FROM table WHERE id < 15") +odb.write(connection, "UPDATE table SET field='peach' WHERE id = 5") +odb.close(connection) + + Added: R-EchoBase/R-echobase_functions.r =================================================================== --- R-EchoBase/R-echobase_functions.r (rev 0) +++ R-EchoBase/R-echobase_functions.r 2012-10-30 11:43:24 UTC (rev 765) @@ -0,0 +1,23 @@ +#This function reshape cells data from EchoBase into wide format +#with proper numeric fields + +cells4humans=function(df,v.names="data_value",idvar="EIcell_id", + timevar="data_type",snames=c("voyage_name","esdu_name", + "cell_type","cell_name","esdu_data_type",idvar)){ + #Cells in wide format + #----------------------------------------- + df.wide=reshape(df,v.names =v.names , idvar = idvar, + timevar =timevar , direction = "wide") + head(df.wide) + names(df.wide)=gsub(paste(v.names,'.',sep=''),'',names(df.wide)) + #Position correction + df.wide$LongitudeStart=correct.positions( + df=df.wide$LongitudeStart,xname="LongitudeStart",asNewColumn=FALSE) + df.wide$LatitudeStart=correct.positions( + df=df.wide$LatitudeStart,yname="LatitudeStart",asNewColumn=FALSE) + #Set data column format to numeric + #----------------------------------------- + df.wide[,!names(df.wide)%in%snames]= + apply(df.wide[,!names(df.wide)%in%snames],2,as.numeric) + df.wide +} \ No newline at end of file