[R] Importing an Excel file that has merged cells

[stefan lhachimi]

Hi Jim,
it looks perfect!
Thanx a lot!
Have a nice weekend,
Stefan


On 6/30/07, jim holtman <jholtman at gmail.com> wrote:
> I think this does what you want using the 'na.locf' function is zoo:
>
> x <- read.csv('/tote.csv', header=FALSE, skip=4)
> # fill blanks in name column with NAs
> is.na(x$V2) <- x$V2 == ""
> # load the 'zoo' library for the function na.locf
> require(zoo)
> x$V1 <- na.locf(x$V1)  # key
> x$V2 <- na.locf(x$V2)  # name
> head(x,30)
>
>
>
> On 6/30/07, Lhachimi, Stefan <Lhachimi at demogr.mpg.de> wrote:
> >
> > Dear all,
> >
> > I have a problem with importing an excel file into R. I can open the
> > file easily (either saving it as a CSV or using RODBC). But the
> > original file is using merged cell in its first column, which gives
> > the name of the observation. (I am dealing with repeated measurements
> > for the same observation)
> > So when I open the dataframe in R it looks like this
> >
> > Col1    Col2 Col3
> > name1 val1 val2
> >      val3 val4
> > ....
> > name2  val5  val6
> >       val7  val8
> >
> > Everything is fine, except that the name of the observation is on the
> > first line and the following rows are empty. Until a new observation
> > starts, where a new name appears in the respective row and the
> > following rows are empty and so on.
> >
> > The number of rows is fixed for each observation. How can I fill the
> > column with the proper name (eg take the first string in this column
> > until you hit a row with a new string, then take this string and go on
> > until the next string).
> >
> > Or read the excel file in such a way, that when cells are merged that
> > when importing it to R all cells created from this merged cell should
> > have the same content.
> >
> > Unfortunately, I cannot change the lay-out of the original excel file
> > as it comes from an commercial data-base and I will have to use it
> > quite often.
> >
> > I have the gut feeling that there might be an easy solution out there
> > as it does not seem to be an uncommon problem.
> >
> > So if you have  hint or a solution I greatly appreciated it.
> > Thanks and a nice weekend,
> >
> > Stefan
> >
> >
> > ----------
> > This mail has been sent through the MPI for Demographic Rese...{{dropped}}
> >
> > ______________________________________________
> > R-help at stat.math.ethz.ch mailing list
> > 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.
> >
>
>
>
> --
> Jim Holtman
> Cincinnati, OH
> +1 513 646 9390
>
> What is the problem you are trying to solve?
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> 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.
>