Select Git revision
loadDB.R 7.03 KiB
#####
#
# Import SIMO Results (SQLite-Databases) in R environment
# and save a dataframe in folder /output
# CB
# 2019-07-12
#
#####
## load libraries
library(RSQLite)
library(tidyr)
library(dplyr)
### Load management regimes
# ---------------------------
# Management regimes and their abbreviation, they are merged to the data by the branching group (script loadDB.R)
regime <- read.csv(paste0(path, "/params/regimes.csv"),
sep = ",",
stringsAsFactors = FALSE)
# Variant 1: create a single dataframe for each SQL Database (selected columns of table UNIT)
# Dataframes are given the names of the DB and column "test" is added with the name of the DB
for (name in db_names){
# name = "simulated_without_MV_Korsnas_Wind_three_286-300"
#db <- dbConnect(dbDriver("SQLite"), dbname = paste0(path,"input_data/simulated_", sim_variant, "_", name, "_rsu.db"))
db <- dbConnect(dbDriver("SQLite"),
dbname = paste(inputFolder,
paste0(name, ".db"), sep = "/"))
#db <- dbConnect(dbDriver("SQLite"),
# dbname = paste0(path, "input_data/simulated_without_MV_Korsnas_Wind_NO_1-15.db"))
rsl <- dbGetQuery( db, paste0("select ", columns, " from UNIT"))
# Create output name, Remove the 'simulated_'
outName <- gsub("simulated_", "", name)
rsl$gpkg <- outName
# Disconnect database
dbDisconnect(db)
### Add the abbreviation of the regimes
rsl <- rsl %>%
left_join(regime, by = "branching_group", all.x = TRUE)
### Filter those stands that caused errors during the simulation with SIMO
# import csv file that contains the error stands
# !!! no needed now???
error_stands <- read.csv(paste0(path, "/params/errors_watersheds.csv"), sep = ",", header = TRUE, stringsAsFactors = FALSE)
error_stands$id <- as.character(error_stands$id)
rsl <- rsl %>%
anti_join(error_stands, by = c("id", "gpkg"))
### Rename set aside scenario if it considers deadwood extraction
# Maybe now we can remove this????
#if(sim_variant %in% all.variants) {
#rsl <- rsl %>% mutate(regime = ifelse(regime %in% "SA", "SA_DWextract", regime))
#}
# Export the final table
print(paste0("writing csv for ", outName))
write.table(rsl,
paste0(outputFolder, "/", outName, ".csv" ),
sep = ";",
row.names = F,
col.names = TRUE)
#assign( paste("rsl", name, sep="_"), rsl)
#rm(db, rsl)
#print("csv exported")
}
# Variant 2: create one huge dataframe combining all SQL databases (selected columns of table UNIT)
# Different DBs are indicated by an additional column "test"
# rslt <- NULL
#
# for (name in db_names){
# #name = "MV_Korsnas"
# db <- dbConnect(dbDriver("SQLite"),
# dbname = paste0(inputFolder, name, ".db"))
# #dbname = paste0(path, "input_data/simulated_without_MV_Korsnas_Wind_NO_1-15.db"))
# #dbname = paste0(path,"input_data/simulated_", sim_variant, "_" , name, "_rsu.db"))
# rsl <- dbGetQuery( db, paste0("select ", columns, " from UNIT"))
# rsl$gpkg <- name
# dbDisconnect(db)
# rslt <- rbind(rslt, rsl)
# rm(db, rsl)
# }
#
# ### Add the abbreviation of the regimes
# rslt <- rslt %>%
# left_join(regime, by= "branching_group", all.X = TRUE)
#
#
# ### Rename set aside scenario if it considers deadwood extraction
# if(sim_variant %in% all.variants) {
#
# rslt <- rslt %>% mutate(regime = ifelse(regime %in% "SA", "SA_DWextract", regime))
#
# }
#
#
### Filter those stands that caused errors during the simulation with SIMO
# import csv file that contains the error stands
# error_stands <- read.csv(paste0(path, "params/errors_watersheds.csv"), sep = ",", header = TRUE, stringsAsFactors = FALSE)
# error_stands$id <- as.character(error_stands$id)
#
# rslt <- rslt %>%
# anti_join(error_stands, by = c("id", "gpkg"))
#
# ### write table
# write.table(rslt, paste0(path, "output/rslt_", sim_variant, "_all.csv" ), sep = ";", row.names = F, col.names = TRUE)
# write.table(rsl,
# paste0(path, "/", name, ".csv" ),
# sep = ";",
# row.names = F,
# col.names = TRUE)
##################################################################
##################################################################
# # First extraction of wind simulated Korsnas data for MSc
#
# sim_variant <- "without"
# db_names <- c("MV_Pori_Wind_1", "MV_Pori_Wind_2", "MV_Pori_Wind_3") #
#
#
# columns <- paste0("id,
# year,
# branch,
# branch_desc,
# branching_group,
# Age,
# area,
# cash_flow,
# V_total_deadwood,
# BA,
# V,
# N,
# H_dom,
# D_gm,
# Harvested_V,
# Biomass,
# income_biomass,
# CARBON_STORAGE,
# Carb_flux_nat_wd_nrg,
# Carbon_flux_natural_rm_wind")
#
#
# # Carb_flux_nat_wd_nrg,
# # Carbon_flux_natural_rm_wind"
# # Harvested_V_log,
# # Harvested_V_log_under_bark,
# # Harvested_V_pulp,
# # Harvested_V_pulp_under_bark
# # V_log,
# # H_gm,
# # SINCE_DRAINAGE,
# # DRAINAGE_STATUS,
# # regen_age,
# # SINCE_DRAINAGE_ORIG,
# # V_pulp,
# # SOIL_CLASS
#
#
#
# rslt <- NULL
#
# for (name in db_names){
# db <- dbConnect(dbDriver("SQLite"), dbname = paste0(path,"input_data/withoutCC_wind/simulated_", sim_variant, "_" , name, ".db"))
# rsl <- dbGetQuery( db, paste0("select ", columns, " from UNIT"))
# rsl$gpkg <- name
# dbDisconnect(db)
# rslt <- rbind(rslt, rsl)
# rm(db, rsl)
# }
#
# rslt <- rslt %>%
# left_join(regime, by= "branching_group", all.X = TRUE)
#
# ### write table
# write.table(rslt, paste0(path, "output/test/rslt_", sim_variant, "CC_WIND_Pori_all.csv" ), sep = ";", row.names = F, col.names = TRUE)
#
# rslt <- read.csv(paste0(path, "output/test/rslt_", sim_variant, "_all.csv" ), sep = ";", header = TRUE, stringsAsFactors = FALSE)
#
# ##################################################################
# ##################################################################
#
#
# ##### Getting all Colnames from a SQL querry
#
#
# # was used to create List of SIMO output -> What indicators can be calculate for ES
#
# db <- dbConnect(dbDriver("SQLite"), dbname = paste0(path,"input_data/test/simulated_without_rsu_example2.db"))
# rsl <- dbGetQuery(db, 'select * from UNIT')
# dbDisconnect(db)
#
# colnames_without <- colnames(rsl)
# noquote(colnames_without)
# write.table(colnames_FBE, paste0(path,"temp/colnames_FBE.csv"), row.names = FALSE)
#
#
# onlyinCC <- colnames_CC45[!colnames_CC45 %in% colnames_without]