Hello all,
I would appreciate your thoughts on a seemingly simple problem. I have a
database, where each row represent a single record. I want to aggregate this
database so I use the aggregate command :
D<-read.csv("C:\\Users\\test.csv")
attach(D)
by1<-factor(Class)
by2<-factor(X)
W<-aggregate(x=Count,by=list(by1,by2),FUN="sum")
The results I get following the form:
>W
Group.1 Group.2 x
1 1 0.1 4
2 2 0.1 7
3 3 0.1 1
4 1 0.2 3
5 3 0.2 4
6 3 0.3 4
However, what I really want is an aggregation which includes the zero
values, i.e.:
>W
Group.1 Group.2 x
1 1 0.1 4
2 2 0.1 7
3 3 0.1 1
4 1 0.2 3
2 0.2 0
5 3 0.2 4
1 0.3 0
2 0.3 0
6 3 0.3 4
How can I achieve what I want?
Best regards,
Ioanna
What zero values? And are they acutall zeros or are the NA's, that is, missing values? The code looks okay but without some sample data it is difficult to know exactly what you are doing. The easiest way to supply data is to use the dput() function. Example with your file named "testfile": dput(testfile) Then copy the output and paste into your email. For large data sets, you can just supply a representative sample. Usually, dput(head(testfile, 100)) will be sufficient. http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example Please supply some sample data. John Kane Kingston ON Canada> -----Original Message----- > From: ii54250 at msn.com > Sent: Fri, 15 Mar 2013 12:40:54 +0000 > To: r-help at r-project.org > Subject: [R] Data manipulation > > Hello all, > > > > I would appreciate your thoughts on a seemingly simple problem. I have a > database, where each row represent a single record. I want to aggregate > this > database so I use the aggregate command : > > > > D<-read.csv("C:\\Users\\test.csv") > > > > attach(D) > > > > by1<-factor(Class) > > by2<-factor(X) > > W<-aggregate(x=Count,by=list(by1,by2),FUN="sum") > > > > The results I get following the form: > > > > >W > > Group.1 Group.2 x > > 1 1 0.1 4 > > 2 2 0.1 7 > > 3 3 0.1 1 > > 4 1 0.2 3 > > 5 3 0.2 4 > > 6 3 0.3 4 > > > > > > However, what I really want is an aggregation which includes the zero > values, i.e.: > > > > >W > > Group.1 Group.2 x > > 1 1 0.1 4 > > 2 2 0.1 7 > > 3 3 0.1 1 > > 4 1 0.2 3 > > 2 0.2 0 > > 5 3 0.2 4 > > 1 0.3 0 > > 2 0.3 0 > > 6 3 0.3 4 > > > > > > How can I achieve what I want? > > > > Best regards, > > Ioanna > > ______________________________________________ > 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.____________________________________________________________ FREE ONLINE PHOTOSHARING - Share your photos online with your friends and family! Visit http://www.inbox.com/photosharing to find out more!
Is this what you want to do?
D2 <- expand.grid(Class=unique(D$Class), X=unique(D$X))
D2 <- merge(D2, D, all=TRUE)
D2$Count[is.na(D2$Count)] <- 0
W <- aggregate(D2$Count, list(D2$Class, D2$X), "sum")
W
Best,
Nello
-----Original Message-----
From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org]
On Behalf Of IOANNA
Sent: Freitag, 15. M?rz 2013 13:41
To: r-help at r-project.org
Subject: [R] Data manipulation
Hello all,
I would appreciate your thoughts on a seemingly simple problem. I have a
database, where each row represent a single record. I want to aggregate this
database so I use the aggregate command :
D<-read.csv("C:\\Users\\test.csv")
attach(D)
by1<-factor(Class)
by2<-factor(X)
W<-aggregate(x=Count,by=list(by1,by2),FUN="sum")
The results I get following the form:
>W
Group.1 Group.2 x
1 1 0.1 4
2 2 0.1 7
3 3 0.1 1
4 1 0.2 3
5 3 0.2 4
6 3 0.3 4
However, what I really want is an aggregation which includes the zero values,
i.e.:
>W
Group.1 Group.2 x
1 1 0.1 4
2 2 0.1 7
3 3 0.1 1
4 1 0.2 3
2 0.2 0
5 3 0.2 4
1 0.3 0
2 0.3 0
6 3 0.3 4
How can I achieve what I want?
Best regards,
Ioanna
Hi IOANNA,
I got the data but it is missing a value in Count (length 22 vs length 23 in
the other two variable so I stuck in an extra 1. I hope this is correct.
There also was an attachement called winmail.dat that appears to be some kind of
MicroSoft Mail note that is pure gibberish to me--I'm on a Linux box.
For some reason in neither posting does your example of the output you want come
through. Are you posting in html ? R-help strips any html so is there a change
it stripped out a table?
If i do this
table(Class, X)
X
Class 0.1 0.2 0.3
1 4 3 0
2 7 0 0
3 1 4 4
I see that you have two combinations of Class and X with no entries. Is this
what you wanted to show in W? If so, it is not immediately apparent how to go
about this.
John Kane
Kingston ON Canada
> -----Original Message-----
> From: ii54250 at msn.com
> Sent: Fri, 15 Mar 2013 13:11:48 +0000
> To: jrkrideau at inbox.com, r-help at r-project.org
> Subject: RE: [R] Data manipulation
>
>
> Hello John,
>
>
> I thought I attached the file. So here we go:
> Class=c(1,1,1,1, 1,1,1,2, 2,2,2,2, 2,2,3,3,
> 3,3,3,3, 3,3,3)
> X=c(0.1,0.1,0.1, 0.1,0.2, 0.2,0.2, 0.1,0.1,
> 0.1,0.1,0.1,0.1,0.1,0.1,0.2,0.2,0.2,0.2,0.3,0.3,0.3, 0.3)
> Count=c(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
>
> by1<-factor(Class)
> by2<-factor(X)
> W<-aggregate(x=Count,by=list(by1,by2),FUN="sum")
>
>
>
> However, what I want is a table that also include lines for the Group.1
> and
> Group.2 values for which there are no records. In other words something
> like
> this:
>
>
>
> Thanks again. I hope its clearer now.
> Ioanna
>
>
> -----Original Message-----
> From: John Kane [mailto:jrkrideau at inbox.com]
> Sent: 15 March 2013 12:51
> To: IOANNA; r-help at r-project.org
> Subject: RE: [R] Data manipulation
>
> What zero values? And are they acutall zeros or are the NA's, that is,
> missing values?
>
> The code looks okay but without some sample data it is difficult to know
> exactly what you are doing.
>
> The easiest way to supply data is to use the dput() function. Example
> with
> your file named "testfile":
> dput(testfile)
> Then copy the output and paste into your email. For large data sets, you
> can just supply a representative sample. Usually,
> dput(head(testfile, 100)) will be sufficient.
>
>
>
http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducibl
> e-example
>
> Please supply some sample data.
>
>
> John Kane
> Kingston ON Canada
>
>
>> -----Original Message-----
>> From: ii54250 at msn.com
>> Sent: Fri, 15 Mar 2013 12:40:54 +0000
>> To: r-help at r-project.org
>> Subject: [R] Data manipulation
>>
>> Hello all,
>>
>>
>>
>> I would appreciate your thoughts on a seemingly simple problem. I have
>> a database, where each row represent a single record. I want to
>> aggregate this database so I use the aggregate command :
>>
>>
>>
>> D<-read.csv("C:\\Users\\test.csv")
>>
>>
>>
>> attach(D)
>>
>>
>>
>> by1<-factor(Class)
>>
>> by2<-factor(X)
>>
>> W<-aggregate(x=Count,by=list(by1,by2),FUN="sum")
>>
>>
>>
>> The results I get following the form:
>>
>>
>>
>> >W
>>
>> Group.1 Group.2 x
>>
>> 1 1 0.1 4
>>
>> 2 2 0.1 7
>>
>> 3 3 0.1 1
>>
>> 4 1 0.2 3
>>
>> 5 3 0.2 4
>>
>> 6 3 0.3 4
>>
>>
>>
>>
>>
>> However, what I really want is an aggregation which includes the zero
>> values, i.e.:
>>
>>
>>
>> >W
>>
>> Group.1 Group.2 x
>>
>> 1 1 0.1 4
>>
>> 2 2 0.1 7
>>
>> 3 3 0.1 1
>>
>> 4 1 0.2 3
>>
>> 2 0.2 0
>>
>> 5 3 0.2 4
>>
>> 1 0.3 0
>>
>> 2 0.3 0
>>
>> 6 3 0.3 4
>>
>>
>>
>>
>>
>> How can I achieve what I want?
>>
>>
>>
>> Best regards,
>>
>> Ioanna
>>
>> ______________________________________________
>> 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.
>
> ____________________________________________________________
> FREE ONLINE PHOTOSHARING - Share your photos online with your friends and
> family!
> Visit http://www.inbox.com/photosharing to find out more!
____________________________________________________________
FREE 3D EARTH SCREENSAVER - Watch the Earth right on your desktop!
Nice. That does look like it. IOANNA? John Kane Kingston ON Canada> -----Original Message----- > From: nblaser at ispm.unibe.ch > Sent: Fri, 15 Mar 2013 14:27:03 +0100 > To: ii54250 at msn.com, r-help at r-project.org > Subject: Re: [R] Data manipulation > > Is this what you want to do? > > D2 <- expand.grid(Class=unique(D$Class), X=unique(D$X)) > D2 <- merge(D2, D, all=TRUE) > D2$Count[is.na(D2$Count)] <- 0 > > W <- aggregate(D2$Count, list(D2$Class, D2$X), "sum") > W > > Best, > Nello > > > -----Original Message----- > From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] > On Behalf Of IOANNA > Sent: Freitag, 15. M?rz 2013 13:41 > To: r-help at r-project.org > Subject: [R] Data manipulation > > Hello all, > > > > I would appreciate your thoughts on a seemingly simple problem. I have a > database, where each row represent a single record. I want to aggregate > this database so I use the aggregate command : > > > > D<-read.csv("C:\\Users\\test.csv") > > > > attach(D) > > > > by1<-factor(Class) > > by2<-factor(X) > > W<-aggregate(x=Count,by=list(by1,by2),FUN="sum") > > > > The results I get following the form: > > > > >W > > Group.1 Group.2 x > > 1 1 0.1 4 > > 2 2 0.1 7 > > 3 3 0.1 1 > > 4 1 0.2 3 > > 5 3 0.2 4 > > 6 3 0.3 4 > > > > > > However, what I really want is an aggregation which includes the zero > values, i.e.: > > > > >W > > Group.1 Group.2 x > > 1 1 0.1 4 > > 2 2 0.1 7 > > 3 3 0.1 1 > > 4 1 0.2 3 > > 2 0.2 0 > > 5 3 0.2 4 > > 1 0.3 0 > > 2 0.3 0 > > 6 3 0.3 4 > > > > > > How can I achieve what I want? > > > > Best regards, > > Ioanna > > ______________________________________________ > 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.____________________________________________________________ GET FREE SMILEYS FOR YOUR IM & EMAIL - Learn more at http://www.inbox.com/smileys Works with AIM?, MSN? Messenger, Yahoo!? Messenger, ICQ?, Google Talk? and most webmails
Thanks a lot! -----Original Message----- From: John Kane [mailto:jrkrideau at inbox.com] Sent: 15 March 2013 13:41 To: Blaser Nello; IOANNA; r-help at r-project.org Subject: Re: [R] Data manipulation Nice. That does look like it. IOANNA? John Kane Kingston ON Canada> -----Original Message----- > From: nblaser at ispm.unibe.ch > Sent: Fri, 15 Mar 2013 14:27:03 +0100 > To: ii54250 at msn.com, r-help at r-project.org > Subject: Re: [R] Data manipulation > > Is this what you want to do? > > D2 <- expand.grid(Class=unique(D$Class), X=unique(D$X)) > D2 <- merge(D2, D, all=TRUE) > D2$Count[is.na(D2$Count)] <- 0 > > W <- aggregate(D2$Count, list(D2$Class, D2$X), "sum") W > > Best, > Nello > > > -----Original Message----- > From: r-help-bounces at r-project.org > [mailto:r-help-bounces at r-project.org] > On Behalf Of IOANNA > Sent: Freitag, 15. M?rz 2013 13:41 > To: r-help at r-project.org > Subject: [R] Data manipulation > > Hello all, > > > > I would appreciate your thoughts on a seemingly simple problem. I have > a database, where each row represent a single record. I want to > aggregate this database so I use the aggregate command : > > > > D<-read.csv("C:\\Users\\test.csv") > > > > attach(D) > > > > by1<-factor(Class) > > by2<-factor(X) > > W<-aggregate(x=Count,by=list(by1,by2),FUN="sum") > > > > The results I get following the form: > > > > >W > > Group.1 Group.2 x > > 1 1 0.1 4 > > 2 2 0.1 7 > > 3 3 0.1 1 > > 4 1 0.2 3 > > 5 3 0.2 4 > > 6 3 0.3 4 > > > > > > However, what I really want is an aggregation which includes the zero > values, i.e.: > > > > >W > > Group.1 Group.2 x > > 1 1 0.1 4 > > 2 2 0.1 7 > > 3 3 0.1 1 > > 4 1 0.2 3 > > 2 0.2 0 > > 5 3 0.2 4 > > 1 0.3 0 > > 2 0.3 0 > > 6 3 0.3 4 > > > > > > How can I achieve what I want? > > > > Best regards, > > Ioanna > > ______________________________________________ > 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.____________________________________________________________ GET FREE SMILEYS FOR YOUR IM & EMAIL - Learn more at webmails