Creating the right table from lapply list

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Creating the right table from lapply list

R help mailing list-2
Hello,
  I have no previous experience with R, but had to learn on the fly in the past couple of weeks. Basically, what I am trying to do is read a certain variable from a series of files and save it as csv-table. The variable has an hourly value for each month in a year for the past 20 years and has to be read for different geographical locations. So there will be 12 files per year (1 for each month) and the values for the variable from each file will be 696 to 744 (depending on how many days x 24 hours there were in the month).What I achieved is to to read the values from all 12 files stored in directory with a function and add them as vectors to a lapply-list:



Myfunction <- function(filename) {
 nc <- nc_open(filename)
 lon <- ncvar_get(nc, "lon")
 lat <- ncvar_get(nc, "lat")
 RW <- ncvar_get(nc, "X")
 HW <- ncvar_get(nc, "Y")
 pt.geo <- c(8.6810 , 50.1143)
 dist <- sqrt( (lon - pt.geo[1])^2 + (lat - pt.geo[2])^2 )
 ind <- which(dist==min(dist, na.rm=TRUE),arr.ind=TRUE)
 sis <- ncvar_get(nc, "SIS", start=c(ind[1],ind[2],1), count=c(1,1,-1))
 vec <- c(sis)
}

filenames <- list.files(path = "C:/Users/Desktop/VD/Solardaten/NC", pattern = "nc", full.names = TRUE)
 output <- lapply(filenames, Myfunction)



And here start my problems with saving "output" as a csv table. Output would contain 12 vectors of different lenght.I want to have them as 12 columns (1x per month) in Excel and each column should have as many row-entries as there are values for this month.Whatever I tried with write.table I was not able to achieve this (tried converting the output to a matrix, also no successes).Please help! Or should I be trying to have the 12 elements as data frames and not vectors?
This is how I want the table for each year to look - 12 columns and all the respective values in the rows (column names I can add by myself):
Best regardsOrlin


______________________________________________
[hidden email] 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.

grafik.png (161K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Creating the right table from lapply list

Bert Gunter-2
If I understand correctly, this query is really about how to organize
your data, not how to use R code to do it, though that may come later.
Of course, the first question is why mess with Excel at all? But I
shall assume you have good reason to get your data into Excel and do
what you want to with it there rather than in R.  That being the case,
the next question is why mess with your data in R ?-- I assume Excel
has tools to extract data from files and organize it for analysis --
that, presumably, is its purpose!

However, as the kids say, whatever...

I assume the tables you describe come one per location. If I were
doing this in R, I would organize the data ("tidyverse" style to use
Hadley's phrase) for each location into a data frame of 3 columns:
Year  Month  Value  . I would then combine all columns into one data
frame with columns  Location  Year   Month Value which could then be
exported as a CSV if you like.

But this likely depends on what you wish/need to do with the data in
Excel and possibly also your Excel skills in creating/converting the
data structures you need there, about which, of course, you have given
no information. So you may need to provide further detail to get
useful help. Or wait for someone smarter to reply.

Cheers,
Bert






Bert Gunter

"The trouble with having an open mind is that people keep coming along
and sticking things into it."
-- Opus (aka Berkeley Breathed in his "Bloom County" comic strip )


On Wed, Mar 28, 2018 at 9:32 AM, orlin mitov via R-help
<[hidden email]> wrote:

> Hello,
>   I have no previous experience with R, but had to learn on the fly in the past couple of weeks. Basically, what I am trying to do is read a certain variable from a series of files and save it as csv-table. The variable has an hourly value for each month in a year for the past 20 years and has to be read for different geographical locations. So there will be 12 files per year (1 for each month) and the values for the variable from each file will be 696 to 744 (depending on how many days x 24 hours there were in the month).What I achieved is to to read the values from all 12 files stored in directory with a function and add them as vectors to a lapply-list:
>
>
>
> Myfunction <- function(filename) {
>  nc <- nc_open(filename)
>  lon <- ncvar_get(nc, "lon")
>  lat <- ncvar_get(nc, "lat")
>  RW <- ncvar_get(nc, "X")
>  HW <- ncvar_get(nc, "Y")
>  pt.geo <- c(8.6810 , 50.1143)
>  dist <- sqrt( (lon - pt.geo[1])^2 + (lat - pt.geo[2])^2 )
>  ind <- which(dist==min(dist, na.rm=TRUE),arr.ind=TRUE)
>  sis <- ncvar_get(nc, "SIS", start=c(ind[1],ind[2],1), count=c(1,1,-1))
>  vec <- c(sis)
> }
>
> filenames <- list.files(path = "C:/Users/Desktop/VD/Solardaten/NC", pattern = "nc", full.names = TRUE)
>  output <- lapply(filenames, Myfunction)
>
>
>
> And here start my problems with saving "output" as a csv table. Output would contain 12 vectors of different lenght.I want to have them as 12 columns (1x per month) in Excel and each column should have as many row-entries as there are values for this month.Whatever I tried with write.table I was not able to achieve this (tried converting the output to a matrix, also no successes).Please help! Or should I be trying to have the 12 elements as data frames and not vectors?
> This is how I want the table for each year to look - 12 columns and all the respective values in the rows (column names I can add by myself):
> Best regardsOrlin
>
>
> ______________________________________________
> [hidden email] 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.
>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Creating the right table from lapply list

MacQueen, Don
In reply to this post by R help mailing list-2
Perhaps this toy example will help:

## example data
output <- list(1:5, 1:7, 1:4)

lens <- lapply(output, length)
maxlen <- max(unlist(lens))
outputmod <- lapply(output, function(x, maxl) c(x, rep(NA, maxl-length(x))), maxl=maxlen)
outputmat <- do.call(cbind, outputmod)
write.csv(outputmat, na='')

The idea is to pad the shorter vectors with NA (missing) before converting to a matrix structure.

I don't really need to know where the data came from, or that it's ncdf data, or how many months or years, etc. But I do need to know the structure of your "output" list. I'm assuming each element is a simple vector of numbers, and that the vectors' lengths are not all the same. If that's correct, then my example may be what you need.

This uses only base R methods, which I generally prefer. And no doubt it can be done more cleverly, or in a way that needs fewer intermediate variables ... but I don't really care.

-Don

--
Don MacQueen
Lawrence Livermore National Laboratory
7000 East Ave., L-627
Livermore, CA 94550
925-423-1062
Lab cell 925-724-7509
 
 
On 3/28/18, 9:32 AM, "R-help on behalf of orlin mitov via R-help" <[hidden email] on behalf of [hidden email]> wrote:

    Hello,
      I have no previous experience with R, but had to learn on the fly in the past couple of weeks. Basically, what I am trying to do is read a certain variable from a series of files and save it as csv-table. The variable has an hourly value for each month in a year for the past 20 years and has to be read for different geographical locations. So there will be 12 files per year (1 for each month) and the values for the variable from each file will be 696 to 744 (depending on how many days x 24 hours there were in the month).What I achieved is to to read the values from all 12 files stored in directory with a function and add them as vectors to a lapply-list:
   
   
   
    Myfunction <- function(filename) {
     nc <- nc_open(filename)
     lon <- ncvar_get(nc, "lon")
     lat <- ncvar_get(nc, "lat")
     RW <- ncvar_get(nc, "X")
     HW <- ncvar_get(nc, "Y")
     pt.geo <- c(8.6810 , 50.1143)
     dist <- sqrt( (lon - pt.geo[1])^2 + (lat - pt.geo[2])^2 )
     ind <- which(dist==min(dist, na.rm=TRUE),arr.ind=TRUE)
     sis <- ncvar_get(nc, "SIS", start=c(ind[1],ind[2],1), count=c(1,1,-1))
     vec <- c(sis)
    }
   
    filenames <- list.files(path = "C:/Users/Desktop/VD/Solardaten/NC", pattern = "nc", full.names = TRUE)
     output <- lapply(filenames, Myfunction)
   
   
   
    And here start my problems with saving "output" as a csv table. Output would contain 12 vectors of different lenght.I want to have them as 12 columns (1x per month) in Excel and each column should have as many row-entries as there are values for this month.Whatever I tried with write.table I was not able to achieve this (tried converting the output to a matrix, also no successes).Please help! Or should I be trying to have the 12 elements as data frames and not vectors?
    This is how I want the table for each year to look - 12 columns and all the respective values in the rows (column names I can add by myself):
    Best regardsOrlin
   
   

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Creating the right table from lapply list

Eric Berger
I like Don's answer which is clean and clear. A frequently useful
alternative to lapply() is sapply().
Taking the sapply() route also avoids the need for do.call(). So a modified
version of Don's code would be:

## example data
output <- list(1:5, 1:7, 1:4)

maxlen <- max( sapply(output, length) )
outputmat <-  sapply(output, function(x, maxl) c(x, rep(NA,
maxl-length(x))), maxl=maxlen)
write.csv(outputmat, na='')


On Thu, Mar 29, 2018 at 2:18 AM, MacQueen, Don <[hidden email]> wrote:

> Perhaps this toy example will help:
>
> ## example data
> output <- list(1:5, 1:7, 1:4)
>
> lens <- lapply(output, length)
> maxlen <- max(unlist(lens))
> outputmod <- lapply(output, function(x, maxl) c(x, rep(NA,
> maxl-length(x))), maxl=maxlen)
> outputmat <- do.call(cbind, outputmod)
> write.csv(outputmat, na='')
>
> The idea is to pad the shorter vectors with NA (missing) before converting
> to a matrix structure.
>
> I don't really need to know where the data came from, or that it's ncdf
> data, or how many months or years, etc. But I do need to know the structure
> of your "output" list. I'm assuming each element is a simple vector of
> numbers, and that the vectors' lengths are not all the same. If that's
> correct, then my example may be what you need.
>
> This uses only base R methods, which I generally prefer. And no doubt it
> can be done more cleverly, or in a way that needs fewer intermediate
> variables ... but I don't really care.
>
> -Don
>
> --
> Don MacQueen
> Lawrence Livermore National Laboratory
> 7000 East Ave., L-627
> Livermore, CA 94550
> 925-423-1062
> Lab cell 925-724-7509
>
>
> On 3/28/18, 9:32 AM, "R-help on behalf of orlin mitov via R-help" <
> [hidden email] on behalf of [hidden email]> wrote:
>
>     Hello,
>       I have no previous experience with R, but had to learn on the fly in
> the past couple of weeks. Basically, what I am trying to do is read a
> certain variable from a series of files and save it as csv-table. The
> variable has an hourly value for each month in a year for the past 20 years
> and has to be read for different geographical locations. So there will be
> 12 files per year (1 for each month) and the values for the variable from
> each file will be 696 to 744 (depending on how many days x 24 hours there
> were in the month).What I achieved is to to read the values from all 12
> files stored in directory with a function and add them as vectors to a
> lapply-list:
>
>
>
>     Myfunction <- function(filename) {
>      nc <- nc_open(filename)
>      lon <- ncvar_get(nc, "lon")
>      lat <- ncvar_get(nc, "lat")
>      RW <- ncvar_get(nc, "X")
>      HW <- ncvar_get(nc, "Y")
>      pt.geo <- c(8.6810 , 50.1143)
>      dist <- sqrt( (lon - pt.geo[1])^2 + (lat - pt.geo[2])^2 )
>      ind <- which(dist==min(dist, na.rm=TRUE),arr.ind=TRUE)
>      sis <- ncvar_get(nc, "SIS", start=c(ind[1],ind[2],1), count=c(1,1,-1))
>      vec <- c(sis)
>     }
>
>     filenames <- list.files(path = "C:/Users/Desktop/VD/Solardaten/NC",
> pattern = "nc", full.names = TRUE)
>      output <- lapply(filenames, Myfunction)
>
>
>
>     And here start my problems with saving "output" as a csv table. Output
> would contain 12 vectors of different lenght.I want to have them as 12
> columns (1x per month) in Excel and each column should have as many
> row-entries as there are values for this month.Whatever I tried with
> write.table I was not able to achieve this (tried converting the output to
> a matrix, also no successes).Please help! Or should I be trying to have the
> 12 elements as data frames and not vectors?
>     This is how I want the table for each year to look - 12 columns and
> all the respective values in the rows (column names I can add by myself):
>     Best regardsOrlin
>
>
>
> ______________________________________________
> [hidden email] 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.
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.