//
you're reading...
Uncategorized

Cross tabulation with manipulation of values

See here (http://stackoverflow.com/questions/9007741/how-can-i-get-xtabs-to-calculate-means-instead-of-sums-in-r) for R solutions below, basically using:

– xtabs & aggregate

xtabs(hp~cyl+gear,aggregate(hp~cyl+gear,mtcars,mean))
   gear
cyl        3        4        5
  4  97.0000  76.0000 102.0000
  6 107.5000 116.5000 175.0000
  8 194.1667   0.0000 299.5000

– ddply

ddply(dataframe, .(year), summarise, mean(age), max(height), sd(weight), etc...)

– tapply

tapply(dfrm$age, dfrm$year, FUN=mean)
with(mtcars, tapply(hp, list(cyl, gear), mean))
 tapply(mtcars$hp, list(mtcars$cyl,mtcars$gear), mean)
         3     4     5
4  97.0000  76.0 102.0
6 107.5000 116.5 175.0
8 194.1667    NA 299.5

See here for ANSI-SQL solution http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=25


SELECT 
    SUM(CASE WHEN purchase_date BETWEEN '2004-08-01' and   '2004-08-31' THEN amount ELSE 0 END) As m2004_08, 
    SUM(CASE WHEN purchase_date BETWEEN '2004-09-01' and   '2004-09-30'  THEN amount ELSE 0 END) As m2004_09,
    SUM(CASE WHEN purchase_date BETWEEN '2004-10-01' and   '2004-10-31' THEN amount ELSE 0 END) As m2004_10, 
SUM(amount) As Total
FROM purchases WHERE purchase_date BETWEEN '2004-08-01' AND '2004-10-31'
Advertisements

Discussion

One thought on “Cross tabulation with manipulation of values

  1. It’s actually a nice and helpful piece of info. I’m glad
    that you simply shared this helpful info with us. Please stay us informed like this.
    Thanks for sharing.

    Posted by mobile apps | August 7, 2013, 8:52 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

mathbabe

Exploring and venting about quantitative issues

The Stone and the Shell

Using large digital libraries to advance literary history

Hi. I'm Hilary Mason.

Zoom out, zoom in, zoom out.

Introduction to Data Science, Columbia University

Blog to document and reflect on Columbia Data Science Class

statMethods blog

A Quick-R Companion

the Tarzan

[R] + applied economics.

4D Pie Charts

Scientific computing, data viz and general geekery, with examples in R and MATLAB.