It would depend on the format of the gridded data. Assuming it is a data frame like DF2 in my earlier answer, you just reverse the steps:> 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> DF3 <- data.frame(as.table(as.matrix(DF2)))Var1 Var2 Freq 1 46 110.5 6.1 2 45.5 110.5 3.2 3 46 111 4.5 4 45.5 111 5.0 5 46 111.5 7.8 6 45.5 111.5 1.8 7 46 112 5.5 8 45.5 112 2.0 But the latitude and longitude get converted to factors and we lose the column names:> DF3 <- data.frame(as.table(as.matrix(DF2))) > colnames(DF3) <- c("latitude", "longitude", "Precip") > DF3$latitude <- as.numeric(as.character(DF3$latitude)) > DF3$longitude <- as.numeric(as.character(DF3$longitude))---------------------------------------- David L Carlson Department of Anthropology Texas A&M University College Station, TX 77843-4352 From: lily li <chocold12 at gmail.com> Sent: Tuesday, November 13, 2018 10:50 PM To: David L Carlson <dcarlson at tamu.edu> Cc: Sarah Goslee <sarah.goslee at gmail.com>; R mailing list <r-help at r-project.org> Subject: Re: [R] How to create gridded data Thanks, Sarah's answer helps the question. Now how to change the gridded data back to DF1 format? I don't know how to name the format, thanks. On Tue, Nov 13, 2018 at 10:56 PM David L Carlson <mailto:dcarlson at tamu.edu> wrote: 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)), ] > DF2? ? ? ? longitude 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) > DF2? ? ?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 ---------------------------------------- David L Carlson Department of Anthropology Texas A&M University College Station, TX 77843-4352 -----Original Message----- From: R-help <mailto:r-help-bounces at r-project.org> On Behalf Of Sarah Goslee Sent: Tuesday, November 13, 2018 8:16 AM To: lily li <mailto:chocold12 at gmail.com> Cc: r-help <mailto: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://urldefense.proofpoint.com/v2/url?u=https-3A__www.springer.com_us_book_9781461476177&d=DwMFaQ&c=ODFT-G5SujMiGrKuoJJjVg&r=veMGHMCNZShld-KX-bIj4jRE_tP9ojUvB_Lqp0ieSdk&m=vZqNKoDe8N1TzBzeK12g2oa0cBS8VD6NDCs-hUhvt5o&s=B73PwZQrdKUmM1ML2Y5zjaEz7xqkHlzBDCrhluogK2U&e and there are abundant web tutorials. Sarah On Tue, Nov 13, 2018 at 2:22 AM lily li <mailto: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) https://urldefense.proofpoint.com/v2/url?u=http-3A__www.numberwright.com&d=DwMFaQ&c=ODFT-G5SujMiGrKuoJJjVg&r=veMGHMCNZShld-KX-bIj4jRE_tP9ojUvB_Lqp0ieSdk&m=vZqNKoDe8N1TzBzeK12g2oa0cBS8VD6NDCs-hUhvt5o&s=qSosThG59aeSFYzVFf1e-YQGbuBKVbvgVi1z9nFm884&e ______________________________________________ mailto:R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see https://urldefense.proofpoint.com/v2/url?u=https-3A__stat.ethz.ch_mailman_listinfo_r-2Dhelp&d=DwMFaQ&c=ODFT-G5SujMiGrKuoJJjVg&r=veMGHMCNZShld-KX-bIj4jRE_tP9ojUvB_Lqp0ieSdk&m=vZqNKoDe8N1TzBzeK12g2oa0cBS8VD6NDCs-hUhvt5o&s=2pS9yFu5bpcRyCi1vX_OEDD2Ie8ZihvOQrkDQSNu8RM&ePLEASE do read the posting guide https://urldefense.proofpoint.com/v2/url?u=http-3A__www.R-2Dproject.org_posting-2Dguide.html&d=DwMFaQ&c=ODFT-G5SujMiGrKuoJJjVg&r=veMGHMCNZShld-KX-bIj4jRE_tP9ojUvB_Lqp0ieSdk&m=vZqNKoDe8N1TzBzeK12g2oa0cBS8VD6NDCs-hUhvt5o&s=1brzEOjZ4EUr_llqwyO274DfJfsOpRBI-pmd-hp0WAQ&eand provide commented, minimal, self-contained, reproducible code.
Now I have new question about this post. If the grid coordinates in DF1 are not complete, i.e. there are missing coordinates, how to fill these with -99 in the exported DF2? Thanks. On Thu, Nov 15, 2018 at 10:57 PM David L Carlson <dcarlson at tamu.edu> wrote:> It would depend on the format of the gridded data. Assuming it is a data > frame like DF2 in my earlier answer, you just reverse the steps: > > > DF2 > 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 > > > DF3 <- data.frame(as.table(as.matrix(DF2))) > Var1 Var2 Freq > 1 46 110.5 6.1 > 2 45.5 110.5 3.2 > 3 46 111 4.5 > 4 45.5 111 5.0 > 5 46 111.5 7.8 > 6 45.5 111.5 1.8 > 7 46 112 5.5 > 8 45.5 112 2.0 > > But the latitude and longitude get converted to factors and we lose the > column names: > > > DF3 <- data.frame(as.table(as.matrix(DF2))) > > colnames(DF3) <- c("latitude", "longitude", "Precip") > > DF3$latitude <- as.numeric(as.character(DF3$latitude)) > > DF3$longitude <- as.numeric(as.character(DF3$longitude)) > > ---------------------------------------- > David L Carlson > Department of Anthropology > Texas A&M University > College Station, TX 77843-4352 > > From: lily li <chocold12 at gmail.com> > Sent: Tuesday, November 13, 2018 10:50 PM > To: David L Carlson <dcarlson at tamu.edu> > Cc: Sarah Goslee <sarah.goslee at gmail.com>; R mailing list < > r-help at r-project.org> > Subject: Re: [R] How to create gridded data > > Thanks, Sarah's answer helps the question. Now how to change the gridded > data back to DF1 format? I don't know how to name the format, thanks. > > On Tue, Nov 13, 2018 at 10:56 PM David L Carlson <mailto:dcarlson at tamu.edu> > wrote: > 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)), ] > > DF2 > longitude > 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) > > DF2 > 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 > > ---------------------------------------- > David L Carlson > Department of Anthropology > Texas A&M University > College Station, TX 77843-4352 > > > -----Original Message----- > From: R-help <mailto:r-help-bounces at r-project.org> On Behalf Of Sarah > Goslee > Sent: Tuesday, November 13, 2018 8:16 AM > To: lily li <mailto:chocold12 at gmail.com> > Cc: r-help <mailto: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://urldefense.proofpoint.com/v2/url?u=https-3A__www.springer.com_us_book_9781461476177&d=DwMFaQ&c=ODFT-G5SujMiGrKuoJJjVg&r=veMGHMCNZShld-KX-bIj4jRE_tP9ojUvB_Lqp0ieSdk&m=vZqNKoDe8N1TzBzeK12g2oa0cBS8VD6NDCs-hUhvt5o&s=B73PwZQrdKUmM1ML2Y5zjaEz7xqkHlzBDCrhluogK2U&e> > and there are abundant web tutorials. > > Sarah > On Tue, Nov 13, 2018 at 2:22 AM lily li <mailto: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) > > https://urldefense.proofpoint.com/v2/url?u=http-3A__www.numberwright.com&d=DwMFaQ&c=ODFT-G5SujMiGrKuoJJjVg&r=veMGHMCNZShld-KX-bIj4jRE_tP9ojUvB_Lqp0ieSdk&m=vZqNKoDe8N1TzBzeK12g2oa0cBS8VD6NDCs-hUhvt5o&s=qSosThG59aeSFYzVFf1e-YQGbuBKVbvgVi1z9nFm884&e> > ______________________________________________ > mailto:R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > > https://urldefense.proofpoint.com/v2/url?u=https-3A__stat.ethz.ch_mailman_listinfo_r-2Dhelp&d=DwMFaQ&c=ODFT-G5SujMiGrKuoJJjVg&r=veMGHMCNZShld-KX-bIj4jRE_tP9ojUvB_Lqp0ieSdk&m=vZqNKoDe8N1TzBzeK12g2oa0cBS8VD6NDCs-hUhvt5o&s=2pS9yFu5bpcRyCi1vX_OEDD2Ie8ZihvOQrkDQSNu8RM&e> PLEASE do read the posting guide > https://urldefense.proofpoint.com/v2/url?u=http-3A__www.R-2Dproject.org_posting-2Dguide.html&d=DwMFaQ&c=ODFT-G5SujMiGrKuoJJjVg&r=veMGHMCNZShld-KX-bIj4jRE_tP9ojUvB_Lqp0ieSdk&m=vZqNKoDe8N1TzBzeK12g2oa0cBS8VD6NDCs-hUhvt5o&s=1brzEOjZ4EUr_llqwyO274DfJfsOpRBI-pmd-hp0WAQ&e> and provide commented, minimal, self-contained, reproducible code. >[[alternative HTML version deleted]]
If the grid is not full you need to identify the missing cells. As an example we can remove rows 3 and 6 from DF1:> DF1miss <- DF1[-c(3, 6), ] > DF2miss <- xtabs(Precip~latitude+longitude, DF1miss) > DF2misslongitude latitude 110.5 111 111.5 112 45.5 3.2 5.0 0.0 2.0 46 6.1 0.0 7.8 5.5 The table command inserts 0 for empty cells, but for your data zero is a valid value so we need to identify the missing values and replace them with NA:> DF2mod <- xtabs(~latitude+longitude, DF1miss) < 1 > DF2modlongitude latitude 110.5 111 111.5 112 45.5 FALSE FALSE TRUE FALSE 46 FALSE TRUE FALSE FALSE> DF2miss[DF2mod] <- NA > DF2miss # Print table with blanks for missing values:longitude latitude 110.5 111 111.5 112 45.5 3.2 5.0 2.0 46 6.1 7.8 5.5> print(DF2miss, na.print=NA) # Print table with <NA> for missing values:longitude latitude 110.5 111 111.5 112 45.5 3.2 5.0 <NA> 2.0 46 6.1 <NA> 7.8 5.5 ---------------------------------------- David L Carlson Department of Anthropology Texas A&M University College Station, TX 77843-4352 From: lily li <chocold12 at gmail.com> Sent: Monday, March 25, 2019 1:18 AM To: David L Carlson <dcarlson at tamu.edu> Cc: r-help <r-help at r-project.org> Subject: Re: [R] How to create gridded data Now I have new question about this post. If the grid coordinates in DF1 are not complete, i.e. there are missing coordinates, how to fill these with -99 in the exported DF2? Thanks. On Thu, Nov 15, 2018 at 10:57 PM David L Carlson <mailto:dcarlson at tamu.edu> wrote: It would depend on the format of the gridded data. Assuming it is a data frame like DF2 in my earlier answer, you just reverse the steps:> DF2? ? ?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> DF3 <- data.frame(as.table(as.matrix(DF2)))? Var1? Var2 Freq 1? ?46 110.5? 6.1 2 45.5 110.5? 3.2 3? ?46? ?111? 4.5 4 45.5? ?111? 5.0 5? ?46 111.5? 7.8 6 45.5 111.5? 1.8 7? ?46? ?112? 5.5 8 45.5? ?112? 2.0 But the latitude and longitude get converted to factors and we lose the column names:> DF3 <- data.frame(as.table(as.matrix(DF2))) > colnames(DF3) <- c("latitude", "longitude", "Precip") > DF3$latitude <- as.numeric(as.character(DF3$latitude)) > DF3$longitude <- as.numeric(as.character(DF3$longitude))---------------------------------------- David L Carlson Department of Anthropology Texas A&M University College Station, TX 77843-4352 From: lily li <mailto:chocold12 at gmail.com> Sent: Tuesday, November 13, 2018 10:50 PM To: David L Carlson <mailto:dcarlson at tamu.edu> Cc: Sarah Goslee <mailto:sarah.goslee at gmail.com>; R mailing list <mailto:r-help at r-project.org> Subject: Re: [R] How to create gridded data Thanks, Sarah's answer helps the question. Now how to change the gridded data back to DF1 format? I don't know how to name the format, thanks. On Tue, Nov 13, 2018 at 10:56 PM David L Carlson <mailto:mailto:dcarlson at tamu.edu> wrote: 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)), ] > DF2? ? ? ? longitude 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) > DF2? ? ?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 ---------------------------------------- David L Carlson Department of Anthropology Texas A&M University College Station, TX 77843-4352 -----Original Message----- From: R-help <mailto:mailto:r-help-bounces at r-project.org> On Behalf Of Sarah Goslee Sent: Tuesday, November 13, 2018 8:16 AM To: lily li <mailto:mailto:chocold12 at gmail.com> Cc: r-help <mailto:mailto: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://urldefense.proofpoint.com/v2/url?u=https-3A__www.springer.com_us_book_9781461476177&d=DwMFaQ&c=ODFT-G5SujMiGrKuoJJjVg&r=veMGHMCNZShld-KX-bIj4jRE_tP9ojUvB_Lqp0ieSdk&m=vZqNKoDe8N1TzBzeK12g2oa0cBS8VD6NDCs-hUhvt5o&s=B73PwZQrdKUmM1ML2Y5zjaEz7xqkHlzBDCrhluogK2U&e and there are abundant web tutorials. Sarah On Tue, Nov 13, 2018 at 2:22 AM lily li <mailto:mailto: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) https://urldefense.proofpoint.com/v2/url?u=http-3A__www.numberwright.com&d=DwMFaQ&c=ODFT-G5SujMiGrKuoJJjVg&r=veMGHMCNZShld-KX-bIj4jRE_tP9ojUvB_Lqp0ieSdk&m=vZqNKoDe8N1TzBzeK12g2oa0cBS8VD6NDCs-hUhvt5o&s=qSosThG59aeSFYzVFf1e-YQGbuBKVbvgVi1z9nFm884&e ______________________________________________ mailto:mailto:R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see https://urldefense.proofpoint.com/v2/url?u=https-3A__stat.ethz.ch_mailman_listinfo_r-2Dhelp&d=DwMFaQ&c=ODFT-G5SujMiGrKuoJJjVg&r=veMGHMCNZShld-KX-bIj4jRE_tP9ojUvB_Lqp0ieSdk&m=vZqNKoDe8N1TzBzeK12g2oa0cBS8VD6NDCs-hUhvt5o&s=2pS9yFu5bpcRyCi1vX_OEDD2Ie8ZihvOQrkDQSNu8RM&ePLEASE do read the posting guide https://urldefense.proofpoint.com/v2/url?u=http-3A__www.R-2Dproject.org_posting-2Dguide.html&d=DwMFaQ&c=ODFT-G5SujMiGrKuoJJjVg&r=veMGHMCNZShld-KX-bIj4jRE_tP9ojUvB_Lqp0ieSdk&m=vZqNKoDe8N1TzBzeK12g2oa0cBS8VD6NDCs-hUhvt5o&s=1brzEOjZ4EUr_llqwyO274DfJfsOpRBI-pmd-hp0WAQ&eand provide commented, minimal, self-contained, reproducible code.