################################################################################
#                                                                              
# recoding of TRY database                                                  
#                                                                              
# written by Maik Billing                          
#                                                                 
#                                                                              
################################################################################

library(data.table)

rm(list=ls(all=TRUE))
dev.off()

### path to data ###

path.in <- 'D:/Maik/Documents/TRY/'
setwd(path.in)
path.lookup<-paste(path.in,"TRY_Categorical_Traits_Lookup_Table_2011_10_12_JK_csv.csv",sep="")
path.coding<-paste(path.in,"Coding.csv",sep="")
path.TRY<-paste(path.in,"3501_newTRY_data.txt",sep="")

lookup<-read.csv2(path.lookup)
coding<-read.csv2(path.coding)
TRY<-fread(path.TRY)

#TRY_all<-TRY
#TRY<-TRY_all
#TRY<-TRY[1:200000,]

TRYnew<-as.data.frame(matrix(NA,nrow=nrow(TRY),ncol=10))


### match species ID to get the index in lookup file
#to test: pmatch( c("a","a","b","b","c","c","c"),c("c", "b","a"),duplicates.ok = T)
index.speciesID<-pmatch(TRY$AccSpeciesName,lookup$AccSpeciesName,duplicates.ok = T)

#check?
TRY$AccSpeciesName[1]
lookup$AccSpeciesName[20155]
### get species ID from lookup table

TRYnew$V1<- lookup$AccSpeciesID[index.speciesID]

### override standard stuff

TRYnew$V2<-TRY$ObservationID
TRYnew$V3<-TRY$ObsDataID
TRYnew$V4<-TRY$OrigValue
TRYnew$V5<-TRY$StdValue
TRYnew$V6<-TRY$DataID

### match with coding table

#plant groth form
TRYnew$V7<-pmatch(lookup$PlantGrowthForm[index.speciesID],
                  coding$Plant.Growth.Form,duplicates.ok=T)
#leaf type
TRYnew$V8<-pmatch(lookup$LeafType[index.speciesID],
                  coding$Leaf.Type,duplicates.ok=T) 
#leaf phenology
TRYnew$V9<-pmatch(lookup$LeafPhenology[index.speciesID],
                  coding$Leaf.Phenology,duplicates.ok=T) 
#Photosynthetic pathway
TRYnew$V10<-pmatch(lookup$PhotosyntheticPathway[index.speciesID],
                  coding$Photosynthetic.Pathway,duplicates.ok=T) 

write.table(TRYnew, file = "TRY_Maik_new.csv",
          row.names=F, 
          col.names=F,
          sep=",")

#TRYnew<-read.table(file = "TRY_Maik_test.csv")

### create columns for lat and lon
TRYnew$V11<-NA
TRYnew$V12<-NA

#Latitude DataID:59
index<-which(TRYnew$V6==59)
df<-data.frame(x=TRYnew$V2[index],y=TRYnew$V5[index])
df_part<-data.frame(x=TRYnew$V2)
total <- merge(df_part,df,by="x",all.x = T)
TRYnew$V11<-total$y

#Longitude DataID:60
index<-which(TRYnew$V6==60)
df<-data.frame(x=TRYnew$V2[index],y=TRYnew$V5[index])
df_part<-data.frame(x=TRYnew$V2)
total <- merge(df_part,df,by="x",all.x = T)
TRYnew$V12<-total$y

write.table(TRYnew, file = "TRY_Maik_latlon_new.csv",
            row.names=F, 
            col.names=F,
            sep=",")
TRYraw<-TRY
TRY<-TRYnew
save(file="TRYdata.Rdata",TRY)