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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: grafik.png
Type: image/png
Size: 120645 bytes
Desc: not available
URL:
<https://stat.ethz.ch/pipermail/r-help/attachments/20180328/424b655c/attachment.png>
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
<r-help at r-project.org> 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
>
>
> ______________________________________________
> 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.
>
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"
<r-help-bounces at r-project.org on behalf of r-help at r-project.org>
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
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 <macqueen1 at llnl.gov> 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" < > r-help-bounces at r-project.org on behalf of r-help at r-project.org> 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 > > > > ______________________________________________ > 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. >[[alternative HTML version deleted]]
Apparently Analagous Threads
- Creating the right table from lapply list
- Extracting subset from netCDF file using lat/lon and converting into .csv in R
- Unexpected values obtained when reading in data using ncdf and ncdf4
- Extracting subset from netCDF file using lat/lon and converting into .csv in R
- Unexpected values obtained when reading in data using ncdf and ncdf4