On Mon, Aug 20, 2018 at 2:17 PM David Doyle <kydaviddo...@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
Dear 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@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.
______________________________________________
R-help@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.
