Difference between revisions of "Temperature and population in Europe"

Revision as of 09:47, 2 May 2011

Boxes with dashed borders contain brief guidance text
for completing the entries for new variables.

Scope

How to join/merge two datasets? What are the temperature and population variation in Europe? D↷

Definition

Data

Two datasets, one of population of Europe and one of temperature in Europe. Both will be found from N:\YMTO\PROJECTS\CLAIH\CLAIH data\GIS_data\data

Population data

CCS_pop_data.xls; sheet called "rate m".

Population data is from EMEP and contains i and j coordinates; country id; years 2010, 2020, 2030, 2040 and 2050 scenarios; and age groups of 0-14, 15-64, and 65+.

Population data is sliced by year to own file: N:\YMTO\PROJECTS\CLAIH\CLAIH data\GIS_data\data\pop_vuosi_pilkotut.

Temperature data

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

List of upstream variables.
The variables can be listed used descriptive (free-format) names or unambiguous identifiers
(e.g. Analytica IDs).

Unit

Unit in which the result is expressed.

Formula

So, purpose is to merge two different datasets with spatial join. That is possible with many different programmes (I think). 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. At first we need to read temperature data with R

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=";")

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.

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

Here is not cleaned (=dirty) rcode for that:

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

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")) #mergering population data ans temeperature data based on coordinates

data = data[complete.cases(data),] #take of empty rowswhere is not population data 
data = data[order(data[,"DAY_"]),] #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[,5] <- data[,5] - 273.15 #Calculating kelvins to celsius

rownames(data) = c(1:nrow(data))#names rows by numbers of the row
rownames(data) = rownames(data, prefix = "ID")#name "ID" for the new column

write.table(data,"N:/YMTO/PROJECTS/CLAIH/CLAIH data/GIS_data/Print/merged data/pop_tmax2000_huhti_merge.txt", sep=";")#This writes new data to file. 

#Next lines are the same as previous just for the rest of summer months.

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=";")

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"))

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),]

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_"]),]

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

rownames(data_t) = c(1:nrow(data_t))
rownames(data_t) = rownames(data_t, prefix = "ID")

rownames(data_k) = c(1:nrow(data_k))
rownames(data_k) = rownames(data_k, prefix = "ID")

rownames(data_h) = c(1:nrow(data_h))
rownames(data_h) = rownames(data_h, prefix = "ID")

rownames(data_e) = c(1:nrow(data_e))
rownames(data_e) = rownames(data_e, prefix = "ID")

rownames(data_s) = c(1:nrow(data_s))
rownames(data_s) = rownames(data_s, prefix = "ID")

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=";")

Now all monthly temperature data is merged to population based on one day that have been joined with GIS. Merge is based on coordinates.

Now the problem is that R prints data based on coordinates - not days. That is right but days in difficult order for GIS (if we need to produce maps) and rcode with organize all data with dates is needed.

--# : You mean you want to sort the data by day? You could probably use data <- data[order(data[,"MONTH"], data[,"DAY"]),], or similar. --Teemu R 11:01, 20 April 2011 (EEST)--# : Thanks Teemu! I managed to order data by day. Now Im trying to get new column with rownumbers (see code above). I think there is some easy way to do it, but what it is? I keep trying and finding. Like yous see I now how to make new column, but how I code that new line has rownumbers. Lets see if I manage to find out this before you will came back to work. --Pauliina 10:21, 28 April 2011 (EEST)--# : I managed to do that.. --Pauliina 12:47, 2 May 2011 (EEST)

Algebra or other explicit methods if possible
(e.g. Analytica code between the <anacode> </anacode> tags).

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 <ref> </ref> tags.
Additional references can also be listed here.

Related files

<mfanonymousfilelist></mfanonymousfilelist>

Temperature and population in Europe. Opasnet . [1]. Accessed 15 May 2024.