Re: [R] Help with rearranging data

From: jim holtman <jholtman_at_gmail.com>
Date: Tue, 22 Jul 2008 14:49:08 -0400

Here is a start that will get you a dataframe. You will have to decide how you want to convert the long to wide format since that did not clearly come through in the mail:

> x <- readLines('/tempxx.txt')
> # remove blank lines
> x <- x[x != ""]
> # create matrix
> y <- matrix(x[-(1:6)], ncol=6, byrow=TRUE)
> colnames(y) <- x[1:6]
> y.df <- as.data.frame(y)
> head(y.df)

     Month Seg  Prod  Biller Cycle Sales
1 1/1/2008   A Table Phyllis     1   500
2 1/1/2008   A Table Phyllis     2   600
3 1/1/2008   A Table Phyllis     3   650
4 1/1/2008   A Table  Doreen     1   345
5 1/1/2008   A Table  Doreen     2   451
6 1/1/2008   A Table  Doreen     6   550

>

On Tue, Jul 22, 2008 at 1:59 PM, William Pepe <sonicstats11_at_hotmail.com> wrote:
>
> In Excel, I have data that looks like this.
>
>
>
>
>
> Month
> Seg
> Prod
> Biller
> Cycle
> Sales
>
> 1/1/2008
> A
> Table
> Phyllis
> 1
> 500
>
> 1/1/2008
> A
> Table
> Phyllis
> 2
> 600
>
> 1/1/2008
> A
> Table
> Phyllis
> 3
> 650
>
> 1/1/2008
> A
> Table
> Doreen
> 1
> 345
>
> 1/1/2008
> A
> Table
> Doreen
> 2
> 451
>
> 1/1/2008
> A
> Table
> Doreen
> 6
> 550
>
> 1/1/2008
> A
> Table
> Doreen
> 9
> 590
>
> 1/1/2008
> A
> Table
> Doreen
> 11
> 860
>
> 1/1/2008
> A
> Chair
> Phyllis
> 1
> 300
>
> 1/1/2008
> A
> Chair
> Phyllis
> 2
> 350
>
> 1/1/2008
> B
> Table
> Phyllis
> 1
> 700
>
> 1/1/2008
> B
> Table
> Phyllis
> 4
> 750
>
> 1/1/2008
> B
> Table
> Phyllis
> 7
> 650
>
> 1/1/2008
> B
> Table
> Doreen
> 1
> 800
>
> 1/1/2008
> B
> Table
> Doreen
> 2
> 975
>
> 1/1/2008
> B
> Chair
> Phyllis
> 1
> 400
>
> 1/1/2008
> B
> Chair
> Phyllis
> 2
> 450
>
> 1/1/2008
> B
> Chair
> Doreen
> 3
> 560
>
> 1/1/2008
> B
> Chair
> Doreen
> 5
> 550
>
> 2/1/2008
> A
> Table
> Phyllis
> 2
> 900
>
> 2/1/2008
> A
> Table
> Phyllis
> 3
> 950
>
> 2/1/2008
> A
> Table
> Doreen
> 1
> 780
>
> 2/1/2008
> A
> Table
> Doreen
> 2
> 800
>
> 2/1/2008
> A
> Chair
> Phyllis
> 1
> 450
>
> 2/1/2008
> A
> Chair
> Phyllis
> 2
> 440
>
> 2/1/2008
> A
> Chair
> Doreen
> 2
> 425
>
> 2/1/2008
> A
> Chair
> Doreen
> 3
> 390
>
> 2/1/2008
> B
> Table
> Phyllis
> 1
> 875
>
> 2/1/2008
> B
> Table
> Phyllis
> 2
> 800
>
> 2/1/2008
> B
> Table
> Doreen
> 2
> 675
>
> 2/1/2008
> B
> Table
> Doreen
> 3
> 885
>
> 2/1/2008
> B
> Chair
> Phyllis
> 1
> 450
>
> 2/1/2008
> B
> Chair
> Doreen
> 2
>
> 435
>
> 2/1/2008
> B
> Chair
> Doreen
> 3
> 400
> I want to rearrange the data so that I have columns such as Seg Prod Biller Cycle Jan Sales Feb Sales A Table Phyllis 1 500 0 A Table Phyllis 2 600 900 A Table Phyllis 3 650 950 A Table Doreen 1 345 780 A Table Doreen 2 451 800 A Table Doreen 6 550 0 A Table Doreen 9 590 0 A Table Doreen 11 860 0and so on. Any ideas on how this can be done, I would be greatly appreciative. Thanks, Sonic
> _________________________________________________________________
>
>
> _WL_Refresh_messenger_video_072008
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help_at_r-project.org 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?

______________________________________________
R-help_at_r-project.org 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.
Received on Tue 22 Jul 2008 - 18:54:03 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Tue 22 Jul 2008 - 19:31:58 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive