An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20080722/c009743a/attachment.pl>
Help with rearranging data
4 messages · William Pepe, Henrik Bengtsson, jim holtman
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.
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?
An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20080722/89508563/attachment.pl>