thr3ads.net - R help - [R] Help with rearranging data [Jul 2008]

If this information is useful, please help other people find it:
Share via:

William Pepe

2008-Jul-22 17:59 UTC

[R] Help with rearranging data

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]]

Henrik Bengtsson

2008-Jul-22 18:33 UTC

head link

[R] Help with rearranging data

Hi,

is your question how you do this in R?  Note that this is a mailing
list for R.  There are probably other lists/groups better suited for
Excel-specific problems.

Cheers

Henrik

On Tue, Jul 22, 2008 at 10:59 AM, 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

2008-Jul-22 18:49 UTC

head link

[R] Help with rearranging data

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?

Reasonably Related Threads

Search for more apparently analagous threads

R help - Jul 2008 - Help with rearranging data

[R] Help with rearranging data

[R] Help with rearranging data

[R] Help with rearranging data

Reasonably Related Threads