[R] Transforming data for nice output table

[Paul Johnson]

On Mon, Aug 20, 2018 at 2:17 PM David Doyle <kydaviddoyle using 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 using 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.