Re-configuring a data set

Question

I'm working on a class assignment currently and have created a pretty large data frame from some odd 500 jumbled text files. What I have currently in my large data frame is a column for Date, Latitude, Longitude, and a bunch of different weather readings.

I am given this .dat file which contains elevation data for a few Latitude and Longitude points, though the format is not how I need it in order to bind the relevant info to my data frame. The Longitude is in the column headers (when I use read.table()) and the latitude is in the "rownames" column.

read.table(.dat)

rownames 113.75 111.25 108.75
36.25   1526.75 1759.56 1948.38
33.75   612.94  1079.12 1979.31
31.25   328.62  1228.88 1483.69

Desired data frame

Longitude Latitude Elevation
113.75    36.25    1526.75
113.75    33.75    612.94
113.75    31.25    328.62
111.25    36.25    1759.56
111.25    33.75    1079.12
111.25    31.25    1228.88
108.75    36.25    1948.38
108.75    33.75    1979.31
108.75    31.25    1483.69

I want to change this into a data frame with columns for Latitude, Longitude, and Elevation reading so that I may add the corresponding elevation data into my larger data frame. I'm having trouble finding a function, or writing a regular expression that accomplishes this. Please let me know how I should proceed!

MrFlick MrFlick · Accepted Answer · 2015-10-20T05:38:37

You can use the reshape2 pacakge to help melt your data. For example

dat<-read.table(text="rownames 113.75 111.25 108.75
36.25   1526.75 1759.56 1948.38
33.75   612.94  1079.12 1979.31
31.25   328.62  1228.88 1483.69", header=T, check.names=F)

long <- reshape2::melt(dat, "rownames")
long$variable<-as.numeric(as.character(long$variable))
long <- setNames(long, c("Latitute","Longitude","Elevation"))

long
#   Latitute Longitude Elevation
# 1    36.25    113.75   1526.75
# 2    33.75    113.75    612.94
# 3    31.25    113.75    328.62
# 4    36.25    111.25   1759.56
# 5    33.75    111.25   1079.12
# 6    31.25    111.25   1228.88
# 7    36.25    108.75   1948.38
# 8    33.75    108.75   1979.31
# 9    31.25    108.75   1483.69

str(long)
# 'data.frame': 9 obs. of  3 variables:
#  $ Latitute : num  36.2 33.8 31.2 36.2 33.8 ...
#  $ Longitude: num  114 114 114 111 111 ...
#  $ Elevation: num  1527 613 329 1760 1079 ...

Re-configuring a data set

1 Answers