[R] Transforming data for nice output table

[Jeff Newmiller]

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 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
>>
>>         [[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.
>
> ______________________________________________
> 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.
>

---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil using 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