Expanding rows of a data frame into multiple rows

[Peter Meilstrup]

The setup: I have a data frame where one column is in list mode, and
each entry contains a vector of varying length.
I want to expand this into a data frame with one row for each member
of the list-mode column (the other values being replicated)

For example, an example input and the desired output would be:

  input <- data.frame(site = 1:6,
                      sector = factor(c("north", "south", "east",
"west", "east", "south")),
                      observations =
I(list(c(1,2,3),c(4,3),c(),c(14,12,53,2,4),c(3),c(23))))

  desired.output <-
    data.frame(site = c(1,1,1,2,2,4,4,4,4,5,6),
               sector = factor(c(2,2,2,3,3,4,4,4,4,4,1,3),
                               labels = c("east", "north", "south", "west")),
               observations = c(1,2,3,4,3,14,12,53,2,4,3,23))

There seems like there ought to be a good (simple, fast) way to do
this, but I've been struggling. Any ideas?

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

[Weidong Gu-2]

This may work

obs.l<-sapply(input$observations,length)

desire.output<-data.frame(site=rep(1:6,obs.l),obs=unlist(input$observations))

Weidong Gu

On Sat, Oct 22, 2011 at 7:51 PM, Peter Meilstrup
<[hidden email]> wrote:

> The setup: I have a data frame where one column is in list mode, and
> each entry contains a vector of varying length.
> I want to expand this into a data frame with one row for each member
> of the list-mode column (the other values being replicated)
>
> For example, an example input and the desired output would be:
>
>  input <- data.frame(site = 1:6,
>                      sector = factor(c("north", "south", "east",
> "west", "east", "south")),
>                      observations =
> I(list(c(1,2,3),c(4,3),c(),c(14,12,53,2,4),c(3),c(23))))
>
>  desired.output <-
>    data.frame(site = c(1,1,1,2,2,4,4,4,4,5,6),
>               sector = factor(c(2,2,2,3,3,4,4,4,4,4,1,3),
>                               labels = c("east", "north", "south", "west")),
>               observations = c(1,2,3,4,3,14,12,53,2,4,3,23))
>
> There seems like there ought to be a good (simple, fast) way to do
> this, but I've been struggling. Any ideas?
>
> ______________________________________________
> [hidden email] 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.
>

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

[trinker]

To further  Weidong Gu's suggestion merge would give you what you want but not the numeric column corresponding to the sector.  Adding that would be easy enough via transform if you really desired it.
 
Tyler

 
obs.l<-sapply(input$observations,length)
desire.output<-data.frame(site=rep(1:6,obs.l),obs=unlist(input$observations))
merge(input[,-3], desire.output, all.x=TRUE)
----------------------------------------

> Date: Sat, 22 Oct 2011 20:34:04 -0400
> From: [hidden email]
> To: [hidden email]
> CC: [hidden email]; [hidden email]
> Subject: Re: [R] Expanding rows of a data frame into multiple rows
>
> This may work
>
> obs.l<-sapply(input$observations,length)
>
> desire.output<-data.frame(site=rep(1:6,obs.l),obs=unlist(input$observations))
>
> Weidong Gu
>
> On Sat, Oct 22, 2011 at 7:51 PM, Peter Meilstrup
> <[hidden email]> wrote:
> > The setup: I have a data frame where one column is in list mode, and
> > each entry contains a vector of varying length.
> > I want to expand this into a data frame with one row for each member
> > of the list-mode column (the other values being replicated)
> >
> > For example, an example input and the desired output would be:
> >
> > input <- data.frame(site = 1:6,
> > sector = factor(c("north", "south", "east",
> > "west", "east", "south")),
> > observations =
> > I(list(c(1,2,3),c(4,3),c(),c(14,12,53,2,4),c(3),c(23))))
> >
> > desired.output <-
> > data.frame(site = c(1,1,1,2,2,4,4,4,4,5,6),
> > sector = factor(c(2,2,2,3,3,4,4,4,4,4,1,3),
> > labels = c("east", "north", "south", "west")),
> > observations = c(1,2,3,4,3,14,12,53,2,4,3,23))
> >
> > There seems like there ought to be a good (simple, fast) way to do
> > this, but I've been struggling. Any ideas?
> >
> > ______________________________________________
> > [hidden email] 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.
> >
>
> ______________________________________________
> [hidden email] 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.    

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

[djmuseR]

Here's another approach using the plyr package:

# Function to process each row of input:
g <- function(d) {
    y <- unlist(d$observations)
    if(length(y) > 0)
      data.frame(site = d$site, sector = d$sector, y = y) else NULL
  }

library('plyr')
> ddply(input, .(site), g)
   site sector  y
1     1  north  1
2     1  north  2
3     1  north  3
4     2  south  4
5     2  south  3
6     4   west 14
7     4   west 12
8     4   west 53
9     4   west  2
10    4   west  4
11    5   east  3
12    6  south 23

HTH,
Dennis

On Sat, Oct 22, 2011 at 4:51 PM, Peter Meilstrup
<[hidden email]> wrote:

> The setup: I have a data frame where one column is in list mode, and
> each entry contains a vector of varying length.
> I want to expand this into a data frame with one row for each member
> of the list-mode column (the other values being replicated)
>
> For example, an example input and the desired output would be:
>
>  input <- data.frame(site = 1:6,
>                      sector = factor(c("north", "south", "east",
> "west", "east", "south")),
>                      observations =
> I(list(c(1,2,3),c(4,3),c(),c(14,12,53,2,4),c(3),c(23))))
>
>  desired.output <-
>    data.frame(site = c(1,1,1,2,2,4,4,4,4,5,6),
>               sector = factor(c(2,2,2,3,3,4,4,4,4,4,1,3),
>                               labels = c("east", "north", "south", "west")),
>               observations = c(1,2,3,4,3,14,12,53,2,4,3,23))
>
> There seems like there ought to be a good (simple, fast) way to do
> this, but I've been struggling. Any ideas?
>
> --
> You received this message because you are subscribed to the Google Groups "manipulatr" group.
> To post to this group, send email to [hidden email].
> To unsubscribe from this group, send email to [hidden email].
> For more options, visit this group at http://groups.google.com/group/manipulatr?hl=en.
>
>

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