[Echobase-commits] [echobase] 01/01: fixes #5919: Migrates to git

This is an automated email from the git hooks/post-receive script. New commit to branch develop in repository echobase. See http://git.codelutin.com/echobase.git commit 7b410613680bbc430551163e274583e07b8cf654 Author: Tony CHEMIT <chemit@codelutin.com> Date: Fri Oct 3 21:43:54 2014 +0200 fixes #5919: Migrates to git --- R-EchoBase/Baracouda2Echobase.r | 217 +++++++++++++++ R-EchoBase/R-echobaseLegacyCheck.r | 534 +++++++++++++++++++++++++++++++++++++ R-EchoBase/R-echobase_functions.r | 26 ++ 3 files changed, 777 insertions(+) diff --git a/R-EchoBase/Baracouda2Echobase.r b/R-EchoBase/Baracouda2Echobase.r new file mode 100644 index 0000000..69c4e27 --- /dev/null +++ b/R-EchoBase/Baracouda2Echobase.r @@ -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=';') + + diff --git a/R-EchoBase/R-echobaseLegacyCheck.r b/R-EchoBase/R-echobaseLegacyCheck.r new file mode 100644 index 0000000..020eb32 --- /dev/null +++ b/R-EchoBase/R-echobaseLegacyCheck.r @@ -0,0 +1,534 @@ +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 operat [...] +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 = [...] +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 ec [...] +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 transits +#-------------------------- +sqlTransits="SELECT transit.startTime,transit.endTime,voyage.name, voyage.startdate, voyage.enddate FROM +public.voyage AS voyage, public.mission AS mission, public.transit AS transit +WHERE voyage.mission = mission.topiaid AND transit.voyage = voyage.topiaid" +Transits <- dbGetQuery(con,sqlTransits) + +#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.transducerproc [...] +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) +# 33942 esdus vs. 34524 esdus in original baracouda, minus 577 bad esdus = 34847 valid esdus +#duplicated Esdu names? +length(unique(EsduCells$cell_name)) +desdus=EsduCells$cell_name[duplicated(EsduCells$cell_name)] +desdus.year=substr(desdus,1,4) +table(desdus.year) +length(desdus) +# 630 duplicated esdus + +#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 = [...] +EsduCellsData <- dbGetQuery(con,sqlEsduCellsData) +head(EsduCellsData) +dim(EsduCellsData) +length(unique(EsduCellsData$cell_name)) +# 33312 esdus with data +length(unique(EsduCellsData$cell_name)) +# 33312 unique esdus with data +unique(EsduCellsData$cell_type) +#date time conversion +stesdus=EsduCellsData[EsduCellsData$data_type=='TimeStart','data_value'] +head(stesdus) +#turn off locale-specific days of week for date import: deprecated +#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(stesdus,format="%Y-%m-%d %H:%M:%OS") + +#esdus in wide format +esdus=cells4humans(df=EsduCellsData,idvar='cell_name') +head(esdus) +dim(esdus) +esdus$esdu.id=paste(esdus$voyage_name,esdus$LongitudeStart,esdus$LatitudeStart) +esdus$t <- strptime(esdus$TimeStart,format="%Y-%m-%d %H:%M:%OS") +# No. of esdus per survey +esdus.survey=table(esdus$voyage_name) + +(NesduCellsData=dim(EsduCellsData)[1]) + +# No duplicated cell names with data +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,] + +#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) + +#import baracouda esdus data +#-------------------------------- +path.esdubara='~/.gvfs/donnees2 sur nantes/Campagnes/bd/Echobase/EchobaseLegacyCheck/BaracoudaExports/EI_ESU.txt' +esdus.bara2=read.table(path.esdubara,sep=';',header=TRUE) +head(esdus.bara2) +dim(esdus.bara2) +esdus.bara2$tesdus <- strptime(esdus.bara2$DHTU_ESDU,format="%d/%m/%Y %H:%M:%S") +# 35524 esdus in baracouda +esdus.bara2$DHTU_ESDU[duplicated(esdus.bara2$DHTU_ESDU)] +#no duplicated esdus + +esdus.bara=EI.bara[EI.bara$LIBELLE_TRANCHE=='TOTAL',] +esdus.bara$esdu.id=paste(esdus.bara$CAMPAGNE,esdus.bara$LGDMC,esdus.bara$LATDMC) +dim(esdus.bara) +# 26448 esdus with EI data in baracouda + +dfs=esdus.bara[esdus.bara$CAMPAGNE=='PELGAS2004',] + +# Summary no. of esdus per cruise +#--------------------------------- +names(esdus.bara2) +tbaraEsdus=table(substr(esdus.bara2$DHTU_ESDU,7,10)) +tbaraEI=table(esdus.bara$CAMPAGNE) +esdus.survey=table(esdus$voyage_name) +tesdus2=esdus.survey[names(esdus.survey)%in%names(tbara)] +esdus.survey.comp=cbind(tesdus2,tbaraEI) +#duplicated esdus +esdus.bara$DHTU_ESDU[duplicated(esdus.bara$DHTU_ESDU)] + +esdus.bara$tesdus <- strptime(esdus.bara$DHTU_ESDU,format="%d/%m/%Y %H:%M:%S") +table(is.element(bara.tesdus,tesdus)) + +#Missing esdus in Echobase +#---------------------------- +plot(esdus.bara2$tesdus,esdus.bara2$TOTAL) +points(esdus$t,esdus$NASC,pch=16,col=2) +missing.esdusEI1=esdus.bara[!is.element(esdus.bara$tesdus,tesdus),] + +dim(missing.esdusEI1) +names(esdus.bara) +esdus.bara$year=substr(esdus.bara[,"DHTU_ESDU"],7,10) +esdus.bara[,"DHTU_ESDU"][duplicated(esdus.bara[,"DHTU_ESDU"])] +missing.esdusEI2=esdus.bara[!is.element(esdus.bara$tesdus,substr(esdus$cell_name,1,19)),] +dim(missing.esdusEI2) +missing.esdus2=esdus.bara2[!is.element(esdus.bara2$tesdus,substr(esdus$cell_name,1,19)),] +dim(missing.esdus2) +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) + +dfs=esdus[esdus$voyage_name=='PELGAS2004',] +names(dfs) +dim(dfs) +dfs2=esdus.bara[esdus.bara$CAMPAGNE=='PELGAS2004',] +dim(dfs2) +names(dfs2) +dfs3=dfs2[!dfs2$tesdus%in%substr(dfs$cell_name,1,19),] +dim(dfs3) + +plot(dfs2$LGDMC,dfs2$LATDMC) +points(dfs3$LGDMC,dfs3$LATDMC,pch=16) + +names(esdus.bara$DHTU_ESDU) + +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',] +#***577 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 for missing esdus +table(substr(Mcsv.base[,"MOVIES_EILayer\\cellset\\datestart"],7,10)) +dft=Mcsv.base +dft$year=substr(dft[,"MOVIES_EILayer\\cellset\\datestart"],7,10) +dftu=unique(dft[,c("MOVIES_EILayer\\cellset\\datestart","year")]) +correct1=table(dftu$year)[-1] +esdus.survey.comps=data.frame(esdus.survey.comp[-seq(8),]) +esdus.survey.comps=cbind(esdus.survey.comps,correct1) +esdus.survey.comps$missing=esdus.survey.comps[,2]-esdus.survey.comps[,1] +sum(esdus.survey.comps$missing) + +es=Mcsv.base[substr(Mcsv.base[,"MOVIES_EILayer\\cellset\\datestart"],7,10)=='2004',] +length(unique(es[,"MOVIES_EILayer\\cellset\\datestart"])) + +#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) + + diff --git a/R-EchoBase/R-echobase_functions.r b/R-EchoBase/R-echobase_functions.r new file mode 100644 index 0000000..ed8ffd9 --- /dev/null +++ b/R-EchoBase/R-echobase_functions.r @@ -0,0 +1,26 @@ +#This function reshape cells data from EchoBase into wide format +#with proper numeric fields + +cells4humans=function(df,v.names="data_value",idvar="EIcell_id",correcXY=FALSE, + timevar="data_type",snames=c("voyage_name","esdu_name", + "cell_type","cell_name","esdu_data_type",idvar, + "TimeStart","TimeEnd")){ + #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 + if (correcXY){ + 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 -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@codelutin.com>.