Grouping and Averaging in Table

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Grouping and Averaging in Table

Prasanna BALAPRAKASH
Dear Rs

I have a single table with three columns in the following form:

1 100 150
1 45 32
1 99 100
2 150 33
2 22 87
2 71 31
....
....
1000 64 32
1 100 150
1 45 32
1 99 100
2 22 89
2 31 44
2 88 11
....
....
1200 64 32
1 100 150
1 45 32
1 99 100
2 150 33
2 22 87
2 71 31
...
...
1100 31 34

Totally 1000+1200+1100 rows.  Now, I need to group by first column  
and average then second and third column to get a table as follows as  
follows:

1 Avg. of all second col. values whose first col value is 1 Avg. of  
all third col. values whose first col value is 1
2 Avg. of all second col. values whose first col value is 2 Avg. of  
all third col. values whose first col value is 2
..
..
1200 Avg. of all second col. values whose first col value is 1200
Avg. of all third col. values whose first col value is 1200


Right now, I have a dirty implementation with a lot of "for" loops  
and "if" conditions. However, I am looking for some built in  
functions and lib. to make the code faster and easier.


Thanks
Prasanna

______________________________________________
[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
Reply | Threaded
Open this post in threaded view
|

Re: Grouping and Averaging in Table

Carlos Ortega
Hello

Use something like this:

res.dat<-split(yourTable[,c(2,3)], group=yourTable$firstColum)

That will generate a list grouped by the firstColum.
To get means, or whatever you need you will have to use any of the list
functions "apply", "sapply", etc.

Regards,
Carlos.
P.S:
- yourTable: It is the name of your table or data.frame.
- firstColum: It is the name of the first column of your table, data.frame.



On 2/17/06, Prasanna BALAPRAKASH <[hidden email]> wrote:

>
> Dear Rs
>
> I have a single table with three columns in the following form:
>
> 1       100     150
> 1       45      32
> 1       99      100
> 2       150     33
> 2       22      87
> 2       71      31
> ....
> ....
> 1000    64      32
> 1       100     150
> 1       45      32
> 1       99      100
> 2       22      89
> 2       31      44
> 2       88      11
> ....
> ....
> 1200    64      32
> 1       100     150
> 1       45      32
> 1       99      100
> 2       150     33
> 2       22      87
> 2       71      31
> ...
> ...
> 1100    31      34
>
> Totally 1000+1200+1100 rows.  Now, I need to group by first column
> and average then second and third column to get a table as follows as
> follows:
>
> 1       Avg. of all second col. values whose first col value is 1
> Avg. of
> all third col. values whose first col value is 1
> 2       Avg. of all second col. values whose first col value is 2
> Avg. of
> all third col. values whose first col value is 2
> ..
> ..
> 1200    Avg. of all second col. values whose first col value is 1200
> Avg. of all third col. values whose first col value is 1200
>
>
> Right now, I have a dirty implementation with a lot of "for" loops
> and "if" conditions. However, I am looking for some built in
> functions and lib. to make the code faster and easier.
>
>
> Thanks
> Prasanna
>
> ______________________________________________
> [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
>

        [[alternative HTML version deleted]]

______________________________________________
[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
Reply | Threaded
Open this post in threaded view
|

Re: Grouping and Averaging in Table

jholtman
In reply to this post by Prasanna BALAPRAKASH
?aggregate

> x <- read.table('clipboard')
> x
     V1  V2  V3
1     1 100 150
2     1  45  32
3     1  99 100
4     2 150  33
5     2  22  87
6     2  71  31
7  1000  64  32
8     1 100 150
9     1  45  32
10    1  99 100
11    2  22  89
12    2  31  44
13    2  88  11
14 1200  64  32
15    1 100 150
16    1  45  32
17    1  99 100
18    2 150  33
19    2  22  87
20    2  71  31
21 1100  31  34
> aggregate(x[,2:3], list(x[,1]), mean)
  Group.1       V2       V3
1       1 81.33333 94.00000
2       2 69.66667 49.55556
3    1000 64.00000 32.00000
4    1100 31.00000 34.00000
5    1200 64.00000 32.00000
>



On 2/17/06, Prasanna BALAPRAKASH <[hidden email]> wrote:

>
> Dear Rs
>
> I have a single table with three columns in the following form:
>
> 1       100     150
> 1       45      32
> 1       99      100
> 2       150     33
> 2       22      87
> 2       71      31
> ....
> ....
> 1000    64      32
> 1       100     150
> 1       45      32
> 1       99      100
> 2       22      89
> 2       31      44
> 2       88      11
> ....
> ....
> 1200    64      32
> 1       100     150
> 1       45      32
> 1       99      100
> 2       150     33
> 2       22      87
> 2       71      31
> ...
> ...
> 1100    31      34
>
> Totally 1000+1200+1100 rows.  Now, I need to group by first column
> and average then second and third column to get a table as follows as
> follows:
>
> 1       Avg. of all second col. values whose first col value is 1
> Avg. of
> all third col. values whose first col value is 1
> 2       Avg. of all second col. values whose first col value is 2
> Avg. of
> all third col. values whose first col value is 2
> ..
> ..
> 1200    Avg. of all second col. values whose first col value is 1200
> Avg. of all third col. values whose first col value is 1200
>
>
> Right now, I have a dirty implementation with a lot of "for" loops
> and "if" conditions. However, I am looking for some built in
> functions and lib. to make the code faster and easier.
>
>
> Thanks
> Prasanna
>
> ______________________________________________
> [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
>



--
Jim Holtman
Cincinnati, OH
+1 513 646 9390

What the problem you are trying to solve?

        [[alternative HTML version deleted]]

______________________________________________
[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