CODEWITHSUNDEEP
rdata.table
geneorama
geneorama

Reputation: 3720

Compute data on one column based on aggregate results from another column

I would like to use data.table to calculate a summary statistic, and then based on that result, calculate a statistic on a second column.

Here is an example using the Air Quality data.

Set up the data

(pretend it came this way)

library(data.table)
dt = as.data.table(airquality)
dt[ , Season:=ifelse(Month>7, 'Fall', 'Summer')]

Some months have high wind

## The range of monthly Wind values
dt[ , list(MinWind=min(Wind), MaxWind=max(Wind)), 
        by=c('Season', 'Month')]

---- R OUTPUT:
   Season Month MinWind MaxWind
1: Summer     5     5.7    20.1
2: Summer     6     1.7    20.7
3: Summer     7     4.1    14.9
4:   Fall     8     2.3    15.5
5:   Fall     9     2.8    16.6
>

Goal: Calculate the average seasonal Solar Radiation grouped by months that had Wind greater than or less than 20.

Can I do this in one step?

## Add a column to indicate if it was a high wind month
dt[, HighWind:=any(Wind>20), by=Month]
## Aggregate based on both HighWind and Season
dt[, list(AveSolarR=mean(Solar.R, na.rm=TRUE)), by=c("HighWind","Season")]

---- R OUTPUT:
   HighWind season AveSolarR
1:     TRUE Summer  185.9649
2:    FALSE Summer  216.4839
3:    FALSE   Fall  169.5690

Upvotes: 1

Views: 205

Answers (1)

James
James

Reputation: 66834

Why not combine both into one list?

dt[,list(HighWind=any(Wind>20),AveSolarR=mean(Solar.R,na.rm=T)),by=Month]
   Month HighWind AveSolarR
1:     5     TRUE  181.2963
2:     6     TRUE  190.1667
3:     7    FALSE  216.4839
4:     8    FALSE  171.8571
5:     9    FALSE  167.4333

For the modified problem, you need to do the HighWind calculation in the by statement, but I think it makes it more convoluted.

dt[,list(AveSolarR=mean(Solar.R,na.rm=T)),
  by=list(HighWind=Month%in%Month[Wind>20],Season)]
   HighWind Season AveSolarR
1:     TRUE Summer  185.9649
2:    FALSE Summer  216.4839
3:    FALSE   Fall  169.5690

Upvotes: 5

Related Questions