Hello everyone,
I'm trying to generate tables of my data out of R for my report.
My data is setup in the format as follows and the example can be found at:
http://doylesdartden.com/R/ExampleData.csv
Location Date Year GW_Elevation
127(I) 5/14/2006 2006 752.46
119(I) 5/14/2006 2006 774.67
127(I) 6/11/2007 2007 752.06
119(I) 6/11/2007 2007 775.57
I would like to generate a table that showed
Location GW_Elevation 2006 GW_Elevation 2007 GW_Elevation xxx.....
119(I) 774.67 775.57
xxxx
127(I) 752.46 752.06
xxxx
XXXX XX XX
Any thoughts on how to transform the data so it would be in this format??
Thank you for your time
David Doyle
[[alternative HTML version deleted]]
Hello, This is a very frequent question. I could rewrite one or two answers taken from StackOverflow: https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format But there you will have more options. Hope this helps, Rui Barradas On 20/08/2018 20:17, David Doyle wrote:> Hello everyone, > > I'm trying to generate tables of my data out of R for my report. > > My data is setup in the format as follows and the example can be found at: > http://doylesdartden.com/R/ExampleData.csv > > Location Date Year GW_Elevation > 127(I) 5/14/2006 2006 752.46 > 119(I) 5/14/2006 2006 774.67 > 127(I) 6/11/2007 2007 752.06 > 119(I) 6/11/2007 2007 775.57 > > I would like to generate a table that showed > > Location GW_Elevation 2006 GW_Elevation 2007 GW_Elevation xxx..... > > 119(I) 774.67 775.57 > xxxx > 127(I) 752.46 752.06 > xxxx > XXXX XX XX > > Any thoughts on how to transform the data so it would be in this format?? > > Thank you for your time > > David Doyle > > [[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 has been checked for viruses by AVG. https://www.avg.com
Hi David,
As you want the _values_ of Year from the initial data frame appended
to the _names_ of GW_Elevation, you can't do it the easy way:
dddf<-read.table(text="Location Date Year
GW_Elevation
127(I) 5/14/2006 2006 752.46
119(I) 5/14/2006 2006 774.67
127(I) 6/11/2007 2007 752.06
119(I) 6/11/2007 2007 775.57",
header=TRUE)
library(prettyR)
# easy part
sdddf<-stretch_df(dddf[c(1,3,4)],"Location",c("Year","GW_Elevation"))
sdddf
This only works for a data frame with the structure and names of the initial one
# hard part
sdddf_dim<-dim(sdddf)
nyears<-(sdddf_dim[2] - 1)/2
fsdddf<-sdddf[,c(1,1:nyears+nyears+1)]
names(fsdddf)<-c("Location",paste("GW_Elevation",unique(dddf$Year),sep="_"))
fsdddf
I would strongly suggest being happy with the easy way, because if the
order of years isn't ascending, the hard way won't work.
Jim
On Tue, Aug 21, 2018 at 5:17 AM, David Doyle <kydaviddoyle at gmail.com>
wrote:> Hello everyone,
>
> I'm trying to generate tables of my data out of R for my report.
>
> My data is setup in the format as follows and the example can be found at:
> http://doylesdartden.com/R/ExampleData.csv
>
> Location Date Year GW_Elevation
> 127(I) 5/14/2006 2006 752.46
> 119(I) 5/14/2006 2006 774.67
> 127(I) 6/11/2007 2007 752.06
> 119(I) 6/11/2007 2007 775.57
>
> I would like to generate a table that showed
>
> Location GW_Elevation 2006 GW_Elevation 2007 GW_Elevation xxx.....
>
> 119(I) 774.67 775.57
> xxxx
> 127(I) 752.46 752.06
> xxxx
> XXXX XX XX
>
> Any thoughts on how to transform the data so it would be in this format??
>
> Thank you for your time
>
> David Doyle
>
> [[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.
If departing from base R into contributed territory, tidyr::spread is
well-suited to this.
library(dplyr)
library(tidyr)
dta <- read.csv( "http://doylesdartden.com/R/ExampleData.csv"
, header = TRUE
, as.is = TRUE
)
result <- ( dta # starting with your data...
# keep only relevant columns
%>% select( Location, Year, GW_Elevation )
# make the key column look like your desired column names
%>% mutate( Year = sprintf( "GW_Elevation %d", Year ) )
# spread the "long" data out "wide"
%>% spread( Year, GW_Elevation )
)
On Tue, 21 Aug 2018, Jim Lemon wrote:
> Hi David,
> As you want the _values_ of Year from the initial data frame appended
> to the _names_ of GW_Elevation, you can't do it the easy way:
>
> dddf<-read.table(text="Location Date Year
GW_Elevation
> 127(I) 5/14/2006 2006 752.46
> 119(I) 5/14/2006 2006 774.67
> 127(I) 6/11/2007 2007 752.06
> 119(I) 6/11/2007 2007 775.57",
> header=TRUE)
> library(prettyR)
> # easy part
>
sdddf<-stretch_df(dddf[c(1,3,4)],"Location",c("Year","GW_Elevation"))
> sdddf
>
> This only works for a data frame with the structure and names of the
initial one
>
> # hard part
> sdddf_dim<-dim(sdddf)
> nyears<-(sdddf_dim[2] - 1)/2
> fsdddf<-sdddf[,c(1,1:nyears+nyears+1)]
>
names(fsdddf)<-c("Location",paste("GW_Elevation",unique(dddf$Year),sep="_"))
> fsdddf
>
> I would strongly suggest being happy with the easy way, because if the
> order of years isn't ascending, the hard way won't work.
>
> Jim
>
> On Tue, Aug 21, 2018 at 5:17 AM, David Doyle <kydaviddoyle at
gmail.com> wrote:
>> Hello everyone,
>>
>> I'm trying to generate tables of my data out of R for my report.
>>
>> My data is setup in the format as follows and the example can be found
at:
>> http://doylesdartden.com/R/ExampleData.csv
>>
>> Location Date Year GW_Elevation
>> 127(I) 5/14/2006 2006 752.46
>> 119(I) 5/14/2006 2006 774.67
>> 127(I) 6/11/2007 2007 752.06
>> 119(I) 6/11/2007 2007 775.57
>>
>> I would like to generate a table that showed
>>
>> Location GW_Elevation 2006 GW_Elevation 2007 GW_Elevation
xxx.....
>>
>> 119(I) 774.67 775.57
>> xxxx
>> 127(I) 752.46 752.06
>> xxxx
>> XXXX XX XX
>>
>> Any thoughts on how to transform the data so it would be in this
format??
>>
>> Thank you for your time
>>
>> David Doyle
>>
>> [[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.
>
> ______________________________________________
> 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.
>
---------------------------------------------------------------------------
Jeff Newmiller The ..... ..... Go Live...
DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live
Go...
Live: OO#.. Dead: OO#.. Playing
Research Engineer (Solar/Batteries O.O#. #.O#. with
/Software/Embedded Controllers) .OO#. .OO#. rocks...1k
Hello,
One of those would be with package reshape2.
dta <- read.csv( "http://doylesdartden.com/R/ExampleData.csv")
subdta <- dta[, c("Location", "Year",
"GW_Elevation")]
res <- reshape2::dcast(subdta, Location ~ Year, value.var =
"GW_Elevation")
names(res)[-1] <- paste("GW_Elevation", names(res)[-1], sep =
"_")
head(res)
Hope this helps,
Rui Barradas
On 20/08/2018 21:37, Rui Barradas wrote:> Hello,
>
> This is a very frequent question.
> I could rewrite one or two answers taken from StackOverflow:
>
>
https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format
>
>
>
> But there you will have more options.
>
>
> Hope this helps,
>
> Rui Barradas
>
> On 20/08/2018 20:17, David Doyle wrote:
>> Hello everyone,
>>
>> I'm trying to generate tables of my data out of R for my report.
>>
>> My data is setup in the format as follows and the example can be found
>> at:
>> http://doylesdartden.com/R/ExampleData.csv
>>
>> Location??????? Date??????? Year????? GW_Elevation
>> 127(I)??????? 5/14/2006???? 2006?????? 752.46
>> 119(I)??????? 5/14/2006???? 2006?????? 774.67
>> 127(I)??????? 6/11/2007???? 2007?????? 752.06
>> 119(I)??????? 6/11/2007???? 2007?????? 775.57
>>
>> I would like to generate a table that showed
>>
>> Location??? GW_Elevation 2006??? GW_Elevation 2007??? GW_Elevation
>> xxx.....
>>
>> 119(I)??????????????????? 774.67????????????????????? 775.57
>> ?????????? xxxx
>> 127(I)??????????????????? 752.46????????????????????? 752.06
>> ?????????? xxxx
>> XXXX????????????????????????? XX?????????????????????????? XX
>>
>> ? Any thoughts on how to transform the data so it would be in this
>> format??
>>
>> Thank you for your time
>>
>> David Doyle
>>
>> ????[[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 has been checked for viruses by AVG.
> https://www.avg.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.
On Mon, Aug 20, 2018 at 2:17 PM David Doyle <kydaviddoyle at gmail.com> wrote:> > Hello everyone, > > I'm trying to generate tables of my data out of R for my report. > > My data is setup in the format as follows and the example can be found at: > http://doylesdartden.com/R/ExampleData.csv > > Location Date Year GW_Elevation > 127(I) 5/14/2006 2006 752.46 > 119(I) 5/14/2006 2006 774.67 > 127(I) 6/11/2007 2007 752.06 > 119(I) 6/11/2007 2007 775.57 > > I would like to generate a table that showed > > Location GW_Elevation 2006 GW_Elevation 2007 GW_Elevation xxx..... > > 119(I) 774.67 775.57 > xxxx > 127(I) 752.46 752.06 > xxxx > XXXX XX XX > > Any thoughts on how to transform the data so it would be in this format?? > > Thank you for your time > > David DoyleDear David I'd consider studying R's reshape function, it was intended exactly for this purpose. No reason to adventure into any user-contributed tidy places to get this done. dta <- read.csv("http://doylesdartden.com/R/ExampleData.csv") dta <- dta[c("Location", "Year", "GW_Elevation")] dta.wide <- reshape(dta, direction = "wide", idvar = "Location", v.names = "GW_Elevation", timevar = "Year") head(dta.wide) Location GW_Elevation.2006 GW_Elevation.2007 GW_Elevation.2008 1 127(I) 752.46 NA 757.50 2 119(S) 774.67 778.76 776.40 3 132(I) 759.45 761.68 764.27 4 132(S) 761.77 761.04 765.44 5 111(I) 753.52 763.24 764.24 6 111(S) 766.18 772.84 767.41 GW_Elevation.2009 GW_Elevation.2010 GW_Elevation.2011 GW_Elevation.2012 1 759.90 756.40 759.05 759.31 2 777.59 777.45 778.21 778.13 3 761.90 764.03 763.63 763.99 4 761.21 763.12 762.69 759.57 5 750.85 764.37 762.99 763.90 6 769.77 767.88 767.95 767.19 GW_Elevation.2013 GW_Elevation.2014 GW_Elevation.2015 GW_Elevation.2016 1 756.07 756.66 757.72 757.66 2 778.88 778.28 775.16 778.28 3 761.22 762.81 762.36 764.46 4 763.19 763.87 761.94 763.90 5 764.42 761.65 764.02 762.93 6 770.20 767.25 767.74 766.87 The main difference between this and your stated target is that your target column names have spaces in them, which are forbidden in column names of data frames. Here R used a period for joining strings. You can override that if you want to with the reshape function, but usually I'd let the periods happen. If you do want to replace period with spaces, it can be done, but you break the warranty on other uses of a data frame. (Could get rid of underscore after GW in same way) colnames(dta.wide) <- sub("Elevation.", "Elevation ", colnames(dta.wide), fixed = TRUE) I'd not try to use that wide frame for many other purposes because of the spaces, but it works well if you want to make a pleasant table out of it. For example, xtable is my favorite: library(xtable) xt <- xtable(dta.wide) print(xt) The latex from that prints out beautifully in a document. The print method for xtable has a file parameter if you want to save the file. Good Luck pj> > [[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.-- Paul E. Johnson http://pj.freefaculty.org Director, Center for Research Methods and Data Analysis http://crmda.ku.edu To write to me directly, please address me at pauljohn at ku.edu.