Grouping and Sorting in R

Question

I am an Excel user attempting to transition some of my routine data reporting tasks to R. One of the most powerful features in Excel is the ability to pivot and sort data. I know these functions are features in R as well but I am not sure how to write the code to solve this particular grouping and sorting problem.

Basically, using R I would like to take a data set that looks like this:

Simple Data Set

And transform it to look like this:

Pivoted Data Set

Notice that the final table groups points by team and then sorts the summary data by the number of points scored. Learning how to do this in R would be a huge time saver.

Thanks in advance for you help!

SO is not a "gimme the codez" site. Attempt something and come back with a question on what you've written. — CAbbott, Jun 08 '12 at 21:37
Aside from other comments (which I agree with), that's a really horrible format for the final table... — Tim P, Jun 09 '12 at 07:48

score 3 · Accepted Answer · edited May 23 '17 at 12:26

The two functions you will need ... once you have an example ... are xtabs and ftable. First we convert an existing table to a dataframe and then note that the counts are now in a variable named "Freq". Then we re-tabulate to a smaller number of dimensions:

> Tdf <- as.data.frame(Titanic, stringsAsFactors=FALSE)
> str(Tdf)
'data.frame':   32 obs. of  6 variables:
 $ Class   : chr  "1st" "2nd" "3rd" "Crew" ...
 $ Sex     : chr  "Male" "Male" "Male" "Male" ...
 $ Age     : chr  "Child" "Child" "Child" "Child" ...
 $ Survived: chr  "No" "No" "No" "No" ...
 $ Freq    : num  0 0 35 0 0 0 17 0 118 154 ...

> ftable( xtabs(Freq ~ Class+Sex, Tdf) , row.vars=c("Class", "Sex") )
Class Sex        
1st   Female  145
      Male    180
2nd   Female  106
      Male    179
3rd   Female  196
      Male    510
Crew  Female   23
      Male    862

I haven't gotten very many points for it but I did at one time demonstrate on SO how to do sub-totals and grand totals:

R: calculating column sums & row sums as an aggregation from a dataframe

score 2 · Answer 2 · edited May 23 '17 at 10:30

First, welcome to R and SO. You'll find that you get better answers if you follow the guidelines here for posting questions:

How to make a great R reproducible example?

You can accomplish what you are asking for in a number of ways.

First, you need to use the sum function over each grouping created by c(Team,Player). There are several ways of doing this:

by in base R, plus as.data.frame.by in the taRifx package
ddply in the plyr package

Then take the resulting output and sort it (again, lots of ways to do that):

sort in the taRifx package
arrange in the plyr package

Since it sounds like you'll be doing this a lot, I'd strongly suggest creating your own function which performs the two steps in just the way you'd like automatically. You can then put that function in your .Rprofile or in a package so it's always available.

Grouping and Sorting in R

2 Answers2

Linked