Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #First create a vector with all my dates
- #Create a frame to hold the data
- testdf = data.frame(sdci=rep("",1),stringsAsFactors=FALSE)
- #needs library(lubridate) for month/year functions
- library(lubridate)
- #Loop and Add data to the df
- for (i in 1:lenght(dates2){testdf[i, ] = c(dbGetQuery(con,paste0(' SELECT
- sdci_',year(dates2[i]),'_',sprintf("%02d",month(dates2[i])),'_mean from
- gr_sea_outlets_tier2 order by area_km2'))}
- data$"2000/8/1" = dbGetQuery(db,"SELECT sdci_2000_08_mean as '2000/08/01' from gr_sea_outlets_tier2 order by area_km2 desc limit 1;")
- for year in $(seq 2000 2010); do
- for month in $(seq -w 05 09); do
- data$"$year"/"$month"/01" = dbGetQuery(db,"SELECT sdci_$year_$month_mean as '$year/$month/01' from gr_sea_outlets_tier2 order by area_km2 desc limit 1;");
- done;
- done
- for year in $(seq 2000 2010); do for month in $(seq -w 05 09); do echo data$'"$year"-"$month"-"01"' <- dbConnect(db,"SELECT sdci_"$year"_"$month"_mean from gr_sea_outlets_tier2 order by area_km2 desc limit 1;") >> r.cmd.data ; done; done
- # Get the inputs we want
- years <- 2000:2010
- months <- sprintf("%02d", 5:9)
- dat <- expand.grid(months = months, years = years)
- # Construct a function that gets the query given the month and year
- getVal <- function(month, year){
- query <- paste0("SELECT sdci_",
- year, "_", month, "_mean as '",
- year, "/", month,
- "/01' from gr_sea_outlets_tier2 order by",
- " area_km2 desc limit 1;")
- #dbGetQuery(db, query)
- query
- }
- # Actually apply the function to each month/year combo of interest
- out <- mapply(getVal, dat$months, dat$years)
- dat <- list()
- for(month in months){
- for(year in years){
- id <- paste(year, month, 1, sep = "/")
- dat[[id]] <- getVal(month, year)
- }
- }
Add Comment
Please, Sign In to add comment