View source for Temperature and population in Europe

{{method|moderator=|stub=Yes}}
 '''Boxes with dashed borders''' contain brief guidance text
 for completing the entries for new variables.

== Scope ==
How to join population and temperature datasets by GIS and R?
{{disclink|Variable or method?}}

== Definition ==

=== Data ===

Two datasets, one of population of Europe and one of temperature in Europe. Both data is located in N:\YMTO\PROJECTS\CLAIH\CLAIH data\GIS_data\data

====Population data ====
*data name: CCS_pop_data.xls; sheet called "rate m".

EMEP based populaiton data includes 11 columns with i, j coordinates; country id; years 2010, 2020, 2030, 2040 and 2050 scenarios; and age groups of 0-14, 15-64, and 65+. 
All age groups and one year (2010) have been teaken to joined file (N:\YMTO\PROJECTS\CLAIH\CLAIH data\GIS_data\data\pop_vuosi_pilkotut). 

====Temperature data====
*data name: tmax2000sliced.xls

Data of temperature contains daily temperature data for the year 2000 in Europe. Coordinates are in latitude-longitude format. Temperature data is too large to open with excel (over 18 MB) and it was too large to merge with population data by R. 
Thus temperature data is sliced monthly and saved to own file: N:\YMTO\PROJECTS\CLAIH\CLAIH data\GIS_data\data\Tmax_kk_pilkotut.

=== Dependencies ===

*[[Ambient air temperature in Europe]]
*[[Population of Europe by Country]]
*[[CLAIH assessment]]

=== Unit ===

 Unit in which the result is expressed.

=== Formula ===

Purpose is to merge two different datasets with spatial join. That is possible with many different programmes. In this page joining have been done by ArcGIS. Some part of data changes have been made by R. Microsoft Access have used in data changes for GIS. There should be easier way to do this, but with these programmes this is the one that works.

====Data for GIS reading format====
At first we need to read temperature data with R 

<pre>tmax=read.table("N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/tmax2000sliced_t.txt", dec=",", header=T, sep=";")
write.table(tmax,"N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/tmax2000sliced_comma.txt", sep=";")
</pre>
tmax2000sliced_t is same data with tmax2000sliced.xls but in txt-format and slashed with ";". for ArcGIS we need to change ";" to dots ("."). the first reading R-code line will do that. Second one is writing it to files.

Secondly data should be modify to GIS readable format (.dbf). It didnt work with R (reason or another). 
*Open Access - open (Files of type:"All File") - Find data from file N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/tmax2000sliced_comma. - choose "Delimited" - Next - semicolon; First Row contains Field names - Field options Year:Do not import; all other fields: Double - Next - name for the file "Tmax2000sliced_acc - Finish.
*Mouse right click in file in Access window - Export - Save as type "dBase IV" Save in the same file as previous one. 

====Data join in GIS====
Open GIS - mouse right click with New data frame - Add Data - find file Tmax2000sliced_acc.dbf - mouse right click with table file - Display x y routes - coordinate system "Edit" - select - Geographic coordinate system - GCS_WGS_1984 - add - ok (make sure that x-Field is Lo and y-Field is La. 

Now temperature data is in GIS. Next population data will be moved to GIS.
For population data have been made same changes with access that have been made to temperature data. And again addedd data to GIS. Coordinat system is Projected coordinates and Emep_50_km_Grid. 

Joining with GIS is too difficult cause of too many variables in temperature data (every days has own level). Datas have to sliced more. Temperature data will be sliced for monthly from april to september. Population is sliced for every years. See data description more detailed and file of sliced data. Data was sliced with GIS (fast and furious): 
*Attribute table: options - "MONTH">=4 AND "MONTH<=9
*options - Export - selected records - output table GIS_data - OK
*N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/tmax2000sliced_summer

Joining with GIS have done with one day and then merged with R to all other days and months. 

Joining with GIS: 
* slice data in attribute table: options - "MONTH"=4 AND "DAY"=1
*options - Export - selected records - output table GIS_data - OK
*N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/tmax2000_4_1.txt (save also dbf-format and open directly to GIS)
*mouse right click table file in GIS - "Joins and relates" - Join.. - Join data from another layerbased on spatial location - 1. choose layer joined (in this case N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/data/pop_vuosi_pilkotut/Pop_2010 - 2. point with closest to it.. - 3. N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/Print/Pop2010_tmax2000_4_1_join.

====Mergering data====
Next step is to merge this "one day join" to another days and months with R. 

<pre>
#read the joined data with temperature of one day and population (joining have been done with ArcGIS)
data_join = read.table("N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/data/pop2010_Tmax2000_4_1.txt", header=T, sep=";")

#read the temperature data
data_huhti = read.table("N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/data/Tmax_kk_pilkotut/tmax2000_huhti.txt", header=T, sep=";") 
data_touko=read.table("N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/data/Tmax_kk_pilkotut/tmax2000_touko.txt", header=T, sep=";")
data_kesä=read.table("N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/data/Tmax_kk_pilkotut/tmax2000_kesä.txt", header=T, sep=";")
data_heinä=read.table("N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/data/Tmax_kk_pilkotut/tmax2000_heinä.txt", header=T, sep=";")
data_elo=read.table("N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/data/Tmax_kk_pilkotut/tmax2000_elo.txt", header=T, sep=";")
data_syys=read.table("N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/data/Tmax_kk_pilkotut/tmax2000_syys.txt", header=T, sep=";")

#merge population data and temperature data based on coordinates
data = merge(data_huhti[,c("MONTH_","DAY_","LATITUDE","LONGITUDE","TEMPERATUR")], data_join[,c("EMEP50_I","EMEP50_J","COUNTRYID","YEAR_","X_14","X5_64","X5PLUS","LATITUDE","LONGITUDE","Distance")], all=TRUE, by=c("LATITUDE", "LONGITUDE")) 
data_t = merge(data_touko[,c("MONTH_","DAY_","LATITUDE","LONGITUDE","TEMPERATUR")], data_join[,c("EMEP50_I","EMEP50_J","COUNTRYID","YEAR_","X_14","X5_64","X5PLUS","LATITUDE","LONGITUDE","Distance")], all=TRUE, by=c("LATITUDE", "LONGITUDE"))
data_k = merge(data_kesä[,c("MONTH_","DAY_","LATITUDE","LONGITUDE","TEMPERATUR")], data_join[,c("EMEP50_I","EMEP50_J","COUNTRYID","YEAR_","X_14","X5_64","X5PLUS","LATITUDE","LONGITUDE","Distance")], all=TRUE, by=c("LATITUDE", "LONGITUDE"))
data_h = merge(data_heinä[,c("MONTH_","DAY_","LATITUDE","LONGITUDE","TEMPERATUR")], data_join[,c("EMEP50_I","EMEP50_J","COUNTRYID","YEAR_","X_14","X5_64","X5PLUS","LATITUDE","LONGITUDE","Distance")], all=TRUE, by=c("LATITUDE", "LONGITUDE"))
data_e = merge(data_elo[,c("MONTH_","DAY_","LATITUDE","LONGITUDE","TEMPERATUR")], data_join[,c("EMEP50_I","EMEP50_J","COUNTRYID","YEAR_","X_14","X5_64","X5PLUS","LATITUDE","LONGITUDE","Distance")], all=TRUE, by=c("LATITUDE", "LONGITUDE"))
data_s = merge(data_syys[,c("MONTH_","DAY_","LATITUDE","LONGITUDE","TEMPERATUR")], data_join[,c("EMEP50_I","EMEP50_J","COUNTRYID","YEAR_","X_14","X5_64","X5PLUS","LATITUDE","LONGITUDE","Distance")], all=TRUE, by=c("LATITUDE", "LONGITUDE"))

#take of empty rows where is not population data 
data <- data[complete.cases(data),] 
data_t <- data_t[complete.cases(data_t),]
data_k <- data_k[complete.cases(data_k),]
data_h <- data_h[complete.cases(data_h),]
data_e <- data_e[complete.cases(data_e),]
data_s <- data_s[complete.cases(data_s),]

#order data by day (NOTE that in one txt file includes just one month - all months together was too big to handle with R)
data = data[order(data[,"DAY_"]),]
data_t = data_t[order(data_t[,"DAY_"]),]
data_k = data_k[order(data_k[,"DAY_"]),]
data_h = data_h[order(data_h[,"DAY_"]),]
data_e = data_e[order(data_e[,"DAY_"]),]
data_s = data_s[order(data_s[,"DAY_"]),]

#Calculating kelvins to celsius
data[,5] <- data[,5] - 273.15 
data_t[,5] <- data_t[,5] - 273.15
data_k[,5] <- data_k[,5] - 273.15
data_h[,5] <- data_h[,5] - 273.15
data_e[,5] <- data_e[,5] - 273.15
data_s[,5] <- data_s[,5] - 273.15
data_bind <- rbind(data, data_t, data_k, data_h, data_e, data_s) #combines all monthly data in one text file

#write new monthly data to file 
write.table(data,"N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/Print/merged data/pop_tmax2000_huhti_merge.txt", sep=";")
write.table(data_t,"N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/Print/merged data/pop2010_tmax2000_touko_merge.txt", sep=";")
write.table(data_k,"N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/Print/merged data/pop2010_tmax2000_kesä_merge.txt", sep=";")
write.table(data_h,"N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/Print/merged data/pop2010_tmax2000_heinä_merge.txt", sep=";")
write.table(data_e,"N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/Print/merged data/pop2010_tmax2000_elo_merge.txt", sep=";")
write.table(data_s,"N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/Print/merged data/pop2010_tmax2000_syys_merge.txt", sep=";")

#write binded text file
write.table(data_bind, "N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/Print/pop2010_tmax2000.txt", sep=";")
</pre>

*GIS opens monthly maps with Geographic Coordinate System -> Europe ->ETRF1989

== Result ==

 If possible, a numerical expression or distribution.

==See also==

 Links to relevant information that does not belong to ''Definition''.

==Keywords==

==References==

 ...will appear here automatically, if cited above using the ''<nowiki><ref> </ref></nowiki>'' tags.
 Additional references can also be listed here.

<references/>

==Related files==

{{mfiles}}

{{publication
| authors        = 
| title          = 
| explanation    = 
| publishingyear = 
| urn            = 
| reference      = 
}}