SURVO 98 edit field: 201 2000 300 (32 bit version) 0001|*SAVE DATAMOV3 / Tiedonsiirtojuttuja 0002|*/TOI 0003|*FONT 9 0004|*REDIM 3000,300 0005|*RESIZE 56,72 0006|*RESIZE 60,90 0007|*/LMAX 0008|*WIN POS 700,0 0011|*Tiedonsiirtoja 0013|*.SVO <-> R data.frame() 0015|*Excel? 0016|*SQL 0018|* - _Aineistonhaku_ / 0019|* - _vaihe x... 0020|* - _Tietojen yhdistely_ / 0021|* - _Kuvailua_ / 0023|*http://www.survo.fi/muste/forum/ 0025|*http://data.worldbank.org/ 0028|*................. 0029|*Pari taulukkoa: 0032|*GET http://api.worldbank.org/datafiles/AG.LND.FRST.ZS_Indicator_MetaData_en_EXCEL.xls 0033|*GET http://api.worldbank.org/datafiles/AG.LND.ARBL.ZS_Indicator_MetaData_en_EXCEL.xls 0034|*GET http://api.worldbank.org/datafiles/EN.ATM.CO2E.PC_Indicator_MetaData_en_EXCEL.xls 0036|*............... 0037|*#INDEX *Indicator_*.xls,CUR+1 0038|*/OPEN AG.LND.ARBL.ZS_Indicator_MetaData_en_EXCEL.xls / 226304 24.04.13 13:20 0039|*/OPEN AG.LND.FRST.ZS_Indicator_MetaData_en_EXCEL.xls / 52736 24.04.13 13:17 0040|*/OPEN EN.ATM.CO2E.PC_Indicator_MetaData_en_EXCEL.xls / 213504 24.04.13 13:24 0041|*################## 0043|*>DIR /B *Indicator_*.xls > lst1 0044|*SHOW lst1 0045|*................... 0046|*RBUF a,A 0047|*R a,A 0048|a#### XLConnect -package must be installed 0050|*### t„„ tarvitaan... 0051|*#### R EXCEL-READ FUNCTION via XLConnect / ###### 0052|*# _ /OPEN http://cran.r-project.org/web/packages/XLConnect/XLConnect.pdf 0053|*### reads .xls(x) file into R data.frame 0054|*#### XLConnect -package must be installed 0055|*"f_ReadSheetfromExcelData" <- function(excel, sheet, 0056|* FIRST = 0, LAST=0, # rows 0057|* startCol=0, endCol=0, # columns 0058|* region=NULL, # not used by now... 0059|* dateTimeFormat=NULL, # not used (getOtion...) 0060|* header=TRUE){ 0064|*require(XLConnect); # this R-package must be installed 0066|*##getOption("XLConnect.dateTimeFormat") 0067|*## default: "%Y-%m-%d %H:%M:%S" 0069|*# 1. Load workbook 0070|*wb <- loadWorkbook(excel, create = TRUE); 0072|*cat("Workbook:\n"); wb; 0074|*#sheets <- getSheets(wb); sheets; 0075|*# 2. Read 0076|*lv_1<- readWorksheet(wb, sheet = sheet, 0077|* header = header, 0078|* startRow = FIRST, endRow = LAST, 0079|* startCol=startCol, endCol=endCol, 0083|* ); 0084|*lv_1; 0086|* }; 0088|*#### f_ReadSheetfromExcelData ###### 0089|*######################################################## 0090|*######################################################### 0094|*lst1 <- readLines("lst1") 0095|*lst1 0096|*i <- 0; 0097|*for(f in lst1) { 0098|*i <- i +1; 0099|* D <- f_ReadSheetfromExcelData(excel=f, sheet=1); #, 0100|* # muita optioita 0101|* # FIRST = 0, LAST=0, # rows 0102|* # startCol=0, endCol=0, # columns 0103|* # region=NULL, # not used by now... 0104|* # dateTimeFormat=NULL, # not used (getOtion...) 0105|* # header=TRUE){ 0106|*str(D); 0107|*names(D) <- gsub("Country.*[.]","C_", names(D)); # korvataan . muuttujien nimiss„ 0108|*names(D); 0110|*M <- f_ReadSheetfromExcelData(excel=f, sheet=2); #, 0111|*str(M) 0114|*attributes(D)$Meta <- M 0115|*attributes(D) 0116|*D$VAR <- attributes(D)$Meta$INDICATOR_NAME 0117|*eval(parse(text=paste("D", i, " <- D", sep=""))) 0119|* }; 0121|*# str(D1) 0122|A################### 0123|*?write.svo 0125|*?download.file() 0127|*................. 0128|*FILE SHOW D1 0129|*Siirto .SVOksi 0131|*/ACTIVATE # 0133|*/F2ESC 0134|*FILE SAVE R>D1 TO NEW D1 0135|*FILE SAVE R>D2 TO NEW D2 0136|*FILE SAVE R>D3 TO NEW D3 0138|*................ 0140|*FILE SHOW D1 0141|*FILE SHOW D2 0142|*FILE SHOW D3 0146|*............... 0147|*#FILE LOAD +D1 / IND=ORDER,1 VARS=VAR 0148|* VAR 0149|* Arable land (% of land area) 0151|*########################### 0154|*#FILE LOAD +D2 / IND=ORDER,1 VARS=VAR 0155|* VAR 0156|* Forest area (% of land area) 0158|*########################### 0159|* FILE SHOW D2 0160|* FILE SHOW D3 0161|*#FILE LOAD +D3 / IND=ORDER,1 VARS=VAR 0162|* VAR 0163|* CO2 emissions (metric tons per capita) 0165|*########################### 0167|*FILE SHOW D1 0169|*.............. 0171|* - _Tietojen yhdistely_ / 0173|*....................... 0174|*Musteen virta-paketin asentaminen ja siihen liittyv„t "rojut": 0175|*R>install.packages("virta",contriburl="http://www.survo.fi/muste") 0176|*R>install.packages(c("RSQLite","RSQLite.extfuns","DBI")) 0178|*......................... 0181|*FILE SQL KYSELY TO NEW WB 0182|*QUERY KYSELY 0183|* select C_Name,X1961 0184|* from MUSTE.D1 0185|* where X1961>15 0186|* order by X1961; 0187|*END 0190|*FILE SHOW WB 0192|*................... 0193|*FILE SQL COMB1 TO NEW WB 0194|*QUERY COMB1 0195|* SELECT d1.C_Name, d1.C_Code, 0196|* d1.X2011 AS ARABLE, 0197|* d2.X2011 AS FOREST, 0198|* d3.X2009 AS CO2 0199|* FROM MUSTE.D1 d1, MUSTE.D2 d2, MUSTE.D3 d3 0201|* WHERE d1.C_Code = d2.C_Code AND 0202|* d1.C_Code = d3.C_Code 0203|* ORDER BY d1.C_Name; 0205|*END 0210|*........................ 0211|*#FILE STATUS WB 0212|* Copied from R data frame resu 0213|*FIELDS: (active) 0214|* 1 SA_ 46 C_Name 0215|* 2 SA_ 3 C_Code 0216|* 3 NA_ 8 ARABLE 0217|* 4 NA_ 8 FOREST 0218|* 5 NA_ 8 CO2 0219|*END 0220|*Survo data file WB: record=111 bytes, M1=10 L=64 M=5 N=246 0222|*#################### 0224|*......................... 0225|* WSIZE=WX,WY 0226|*RPLOT WB,ARABLE,FOREST TO KOE.R / POINT=[Times(7)],C_Code FRAME=1 XLABEL= YLABEL= 0227|*GPLOT WB,ARABLE,FOREST / POINT=[Times(7)],C_Code FRAME=1 XLABEL= YLABEL= 0229|*SHOW KOE.R 0230|*XSCALE=*log(x),0.1,10,100 0231|*YSCALE=*log(y),0.1,10,100 0233|*FILE 0234|*................... 0237|*FILE SHOW WB 0239|*FILE LOAD WB TO R>B 0241|*str(B) 0244|*str(B) 0246|*R b,B 0248|b 0249|*#hist(B$CO2) 0251|*attributes(B) 0252|*FONT 8 0253|*RESIZE 54,72 0255|*B$DIST1 <- with(B,sqrt(ARABLE^2 + FOREST^2)); 0258|* plot(B$ARABLE, B$FOREST, 0259|* xlab=attributes(D1)$Meta$INDICATOR_NAME, 0260|* ylab=attributes(D2)$Meta$INDICATOR_NAME, 0261|* cex=.5, pch="" 0262|* ); 0264|*varit <- c("green", "blue", "red", "black"); # heat.colors(3, alpha = 1); 0266|*#apu <- B[B$C_Code=="FIN",] 0267|*#with(apu, text(ARABLE, FOREST, labels=C_Code, cex=1, col="blue")); # 0269|*fit<-loess(FOREST~ARABLE, data=B, span=.3) 0270|*#?predict() 0272|*nd <- data.frame(ARABLE=seq(1,60, by=1)); 0273|*nd 0274|*nd2 <- data.frame(predict(fit, newdata=nd), nd) 0275|*nd2 0276|*lines(nd2[,2], nd2[,1] , col="blue") 0278|*m1 <- lm(FOREST ~ 1 + ARABLE, data=B); 0279|*abline(a=m1$coef[1], b=m1$coef[2], col="pink", lwd=2); 0281|*## 0283|*with(B[B$C_Code=="FIN", ],points(ARABLE,FOREST, pch=1, 0284|* col="dark grey", lty=2 , cex=2.5)); 0286|*k <- c(5,10,20); 0287|*with(B[B$CO2 < k[1] ,], 0288|* text(ARABLE, FOREST, labels=C_Code, col=varit[1], cex=.5)); 0290|*with(B[B$CO2 >= k[1] & B$CO2 < k[2] ,], 0291|* text(ARABLE, FOREST, labels=C_Code, col=varit[2], cex=.5)); 0293|*with(B[B$CO2 >= k[2] & B$CO2 < k[3] ,], 0294|* text(ARABLE, FOREST, labels=C_Code, col=varit[3], cex=.5)); 0296|*with(B[B$CO2 >= k[3] ,], 0297|* text(ARABLE, FOREST, labels=C_Code, col=varit[4], cex=.5)); 0299|*# 0300|*apu1 <- B[B$DIST1 < 10, c("C_Name", "C_Code", "ARABLE", "FOREST", "CO2")] 0301|*apu1 <- apu1[order(-apu1$CO2), ] 0303|*mtext(paste(attributes(D3)$Meta$INDICATOR_NAME, "upp. limit"), adj=1, cex=.8) 0304|*#?mtext() 0305|*leg <- paste(c(k,"*")); 0307|*legend("topright", legend=leg, col=varit, pch=19, cex=.8) 0309|*title(main="Environmental indicators (countries)"); 0313|B################### 0314|*.......................... 0316|*?plot() 0318|*abline(a = NULL, b = NULL, h = NULL, v = NULL, reg = NULL, 0319|* coef = NULL, untf = FALSE, ...) 0322|*?loess() 0324|*?abline() 0326|*FILE SAVE R>apu1 TO NEW MAAT2 / SELECT=!C1 C1=C_Code:NA 0327|*................. 0328|* SELECT=!C1 C1=C_Code:NA 0329|*#FILE LOAD +MAAT2,CUR+1 / VARS=C_Code,ARABLE,FOREST,CO2,C_Name 0330|* C_C ARABLE FOREST CO2 C_Name 0331|* QAT 1.206 0.000 44.027 Qatar 0332|* KWT 0.617 0.359 30.308 Kuwait 0333|* ARE 0.605 3.808 22.601 United Arab Emirates 0334|* BHR 1.789 0.711 20.709 Bahrain 0335|* SAU 1.447 0.454 16.143 Saudi Arabia 0336|* OMN 0.103 0.006 15.170 Oman 0337|* FRO 2.149 0.072 14.555 Faeroe Islands 0338|* KAZ 8.903 1.224 14.031 Kazakhstan 0339|* NOC 1.438 2.510 13.985 High income: nonOECD 0340|* GRL 0.002 0.001 10.222 Greenland 0341|* LBY 0.995 0.123 10.040 Libya 0342|* TKM 4.043 8.782 9.672 Turkmenistan 0343|* SGP 0.900 3.286 6.395 Singapore 0344|* ISL 1.227 0.307 6.367 Iceland 0345|* MEA 4.814 2.010 5.855 Middle East & North Africa (all income levels) 0346|* MNG 0.394 6.962 5.348 Mongolia 0347|* ARB 3.368 1.625 4.531 Arab World 0348|* MNA 5.851 2.445 4.061 Middle East & North Africa (developing only) 0349|* JOR 1.978 1.098 3.812 Jordan 0350|* IRQ 9.210 1.900 3.507 Iraq 0351|* DZA 3.153 0.623 3.471 Algeria 0352|* EGY 2.883 0.071 2.711 Egypt, Arab Rep. 0353|* NAM 0.972 8.765 1.600 Namibia 0354|* KGZ 6.652 5.061 1.249 Kyrgyz Republic 0355|* YEM 2.199 1.040 1.029 Yemen, Rep. 0356|* MRT 0.437 0.230 0.614 Mauritania 0357|* DJI 0.086 0.242 0.610 Djibouti 0358|* PSE 7.392 1.523 0.584 West Bank and Gaza 0359|* TJK 6.073 2.929 0.418 Tajikistan 0360|* TCD 3.891 9.090 0.038 Chad 0362|*############################# 0364|*............................