Hi R users, I have a question about manipulating data. For example, I have DF1 as the following, how to transform it to a gridded dataset DF2? In DF2, each value Precip is an attribute of the corresponding grid cell. So DF2 is like a spatial surface, and can be imported to ArcGIS. Thanks for your help. DF1 latitude longitude Precip 45.5 110.5 3.2 45.5 111 5.0 45.5 111.5 1.8 45.5 112 2.0 46 110.5 6.1 46 111 4.5 46 111.5 7.8 46 112 5.5 ... DF2 6.1 4.5 7.8 5.5 3.2 5.0 1.8 2.0 ... [[alternative HTML version deleted]]
Hi lily,
Something like this should work:
DF1<-read.table(text"latitude longitude Precip
45.5 110.5 3.2
45.5 111 5.0
45.5 111.5 1.8
45.5 112 2.0
46 110.5 6.1
46 111 4.5
46 111.5 7.8
46 112 5.5",
header=TRUE)
lats<-sort(unique(DF1$latitude),decreasing=TRUE)
lons<-sort(unique(DF1$longitude))
DF2<-matrix(NA,nrow=length(lats),ncol=length(lons))
rownames(DF2)<-lats
colnames(DF2)<-lons
nval<-dim(DF1)[1]
for(val in 1:nval) {
row<-which(lats == DF1$latitude[val])
col<-which(lons == DF1$longitude[val])
DF2[row,col]<-DF1$Precip[val]
}
Jim
On Tue, Nov 13, 2018 at 6:22 PM lily li <chocold12 at gmail.com>
wrote:>
> Hi R users,
>
> I have a question about manipulating data. For example, I have DF1 as the
> following, how to transform it to a gridded dataset DF2? In DF2, each value
> Precip is an attribute of the corresponding grid cell. So DF2 is like a
> spatial surface, and can be imported to ArcGIS. Thanks for your help.
>
> DF1
> latitude longitude Precip
> 45.5 110.5 3.2
> 45.5 111 5.0
> 45.5 111.5 1.8
> 45.5 112 2.0
> 46 110.5 6.1
> 46 111 4.5
> 46 111.5 7.8
> 46 112 5.5
> ...
>
>
> DF2
> 6.1 4.5 7.8 5.5
> 3.2 5.0 1.8 2.0
> ...
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
You might take a look at the reshape package, which switches from 'long' to 'wide' formats and vice versa in a fairly flexible way. S Ellison> -----Original Message----- > From: R-help [mailto:r-help-bounces at r-project.org] On Behalf Of lily li > Sent: 13 November 2018 07:22 > To: R mailing list > Subject: [R] How to create gridded data > > Hi R users, > > I have a question about manipulating data. For example, I have DF1 as the > following, how to transform it to a gridded dataset DF2? In DF2, each value > Precip is an attribute of the corresponding grid cell. So DF2 is like a > spatial surface, and can be imported to ArcGIS. Thanks for your help. > > DF1 > latitude longitude Precip > 45.5 110.5 3.2 > 45.5 111 5.0 > 45.5 111.5 1.8 > 45.5 112 2.0 > 46 110.5 6.1 > 46 111 4.5 > 46 111.5 7.8 > 46 112 5.5 > ... > > > DF2 > 6.1 4.5 7.8 5.5 > 3.2 5.0 1.8 2.0 > ... > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting- > guide.html > and provide commented, minimal, self-contained, reproducible code.******************************************************************* This email and any attachments are confidential. Any use...{{dropped:8}}
If you want an actual spatial dataset, the best place to ask is R-sig-geo R has substantial capabilities for dealing with gridded spatial data, including in the sp, raster, and sf packages. Here's one approach, creating a SpatialGridDataFrame, which can be exported in any standard raster format using the rgdal package. DF2 <- DF1 coordinates(DF2) <- ~longitude + latitude gridded(DF2) <- TRUE fullgrid(DF2) <- TRUE I recommend Roger Bivand's excellent book: https://www.springer.com/us/book/9781461476177 and there are abundant web tutorials. Sarah On Tue, Nov 13, 2018 at 2:22 AM lily li <chocold12 at gmail.com> wrote:> > Hi R users, > > I have a question about manipulating data. For example, I have DF1 as the > following, how to transform it to a gridded dataset DF2? In DF2, each value > Precip is an attribute of the corresponding grid cell. So DF2 is like a > spatial surface, and can be imported to ArcGIS. Thanks for your help. > > DF1 > latitude longitude Precip > 45.5 110.5 3.2 > 45.5 111 5.0 > 45.5 111.5 1.8 > 45.5 112 2.0 > 46 110.5 6.1 > 46 111 4.5 > 46 111.5 7.8 > 46 112 5.5 > ... > > > DF2 > 6.1 4.5 7.8 5.5 > 3.2 5.0 1.8 2.0 > ... >-- Sarah Goslee (she/her) http://www.numberwright.com
Sarah's answer is probably better depending on what you want to do with the resulting data, but here's a way to go from your original DF1 to DF2:> DF1 <- structure(list(latitude = c(45.5, 45.5, 45.5, 45.5, 46, 46, 46,+ 46), longitude = c(110.5, 111, 111.5, 112, 110.5, 111, 111.5, + 112), Precip = c(3.2, 5, 1.8, 2, 6.1, 4.5, 7.8, 5.5)), + class = "data.frame", row.names = c(NA, -8L))># Convert to table with xtabs()> DF2 <- xtabs(Precip~latitude+longitude, DF1) ># Reverse the order of the latitudes> DF2 <- DF2[rev(rownames(DF2)), ] > DF2longitude latitude 110.5 111 111.5 112 46 6.1 4.5 7.8 5.5 45.5 3.2 5.0 1.8 2.0 # Convert to a data frame> DF2 <- as.data.frame.matrix(DF2) > DF2110.5 111 111.5 112 46 6.1 4.5 7.8 5.5 45.5 3.2 5.0 1.8 2.0 ---------------------------------------- David L Carlson Department of Anthropology Texas A&M University College Station, TX 77843-4352 -----Original Message----- From: R-help <r-help-bounces at r-project.org> On Behalf Of Sarah Goslee Sent: Tuesday, November 13, 2018 8:16 AM To: lily li <chocold12 at gmail.com> Cc: r-help <r-help at r-project.org> Subject: Re: [R] How to create gridded data If you want an actual spatial dataset, the best place to ask is R-sig-geo R has substantial capabilities for dealing with gridded spatial data, including in the sp, raster, and sf packages. Here's one approach, creating a SpatialGridDataFrame, which can be exported in any standard raster format using the rgdal package. DF2 <- DF1 coordinates(DF2) <- ~longitude + latitude gridded(DF2) <- TRUE fullgrid(DF2) <- TRUE I recommend Roger Bivand's excellent book: https://www.springer.com/us/book/9781461476177 and there are abundant web tutorials. Sarah On Tue, Nov 13, 2018 at 2:22 AM lily li <chocold12 at gmail.com> wrote:> > Hi R users, > > I have a question about manipulating data. For example, I have DF1 as the > following, how to transform it to a gridded dataset DF2? In DF2, each value > Precip is an attribute of the corresponding grid cell. So DF2 is like a > spatial surface, and can be imported to ArcGIS. Thanks for your help. > > DF1 > latitude longitude Precip > 45.5 110.5 3.2 > 45.5 111 5.0 > 45.5 111.5 1.8 > 45.5 112 2.0 > 46 110.5 6.1 > 46 111 4.5 > 46 111.5 7.8 > 46 112 5.5 > ... > > > DF2 > 6.1 4.5 7.8 5.5 > 3.2 5.0 1.8 2.0 > ... >-- Sarah Goslee (she/her) http://www.numberwright.com ______________________________________________ R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.