CODEWITHSUNDEEP
rdata.table
user7863288
user7863288

Reputation: 309

R Data Table - adding grouped columns into selected data

I have data like so

dt <- data.table(group = rep(1:3,100),
             price = rep(1:10, 30),
             currency = rep(1:2,150)
            )

> dt
     group price currency
  1:     1     1        1
  2:     2     2        2
  3:     3     3        1
  4:     1     4        2
  5:     2     5        1
 ---                     
296:     2     6        2
297:     3     7        1
298:     1     8        2
299:     2     9        1
300:     3    10        2

Essentially for each group I have a number of items that are charged at a price. Some items are priced in currency 1 and some in currency 2. I can easily sum revenue for the group

dt[, .(varname="total revenue", 
   value = sum(price)
  ), 
  by = list(group)
]

   group       varname value
1:     1 total revenue   550
2:     2 total revenue   550
3:     3 total revenue   550

And I can also easily sum for the group and the currency

dt[, .(varname="total revenue", 
   value = sum(price)
  ), 
  by = list(group,currency)
  ]

   group currency       varname value
1:     1        1 total revenue   250
2:     2        2 total revenue   300
3:     3        1 total revenue   250
4:     1        2 total revenue   300
5:     2        1 total revenue   250
6:     3        2 total revenue   300

But what I'd really like is to have a data table that contains group, varname containing the currency name and the summed value. I can calculate what I want with 

dt[, .(varname=paste("total revenue",currency), 
       value = sum(price)
), 
by = list(group,currency)
]

   group currency         varname value
1:     1        1 total revenue 1   250
2:     2        2 total revenue 2   300
3:     3        1 total revenue 1   250
4:     1        2 total revenue 2   300
5:     2        1 total revenue 1   250
6:     3        2 total revenue 2   300

But ideally I want to get rid of the currency column as the value is now present in the name of the variable. I can achieve that with chaining like so

x <- dt[, .(varname=paste("total revenue",currency), 
       value = sum(price)
), 
  by = list(group,currency)
][, currency:=NULL]

> x
   group         varname value
1:     1 total revenue 1   250
2:     2 total revenue 2   300
3:     3 total revenue 1   250
4:     1 total revenue 2   300
5:     2 total revenue 1   250
6:     3 total revenue 2   300

But I'm not sure if this is the "correct" way to achieve this with a data table. I'd perhaps thought there might be a way to do it with a single command i.e. not use chaining. I'm not against chaining just wondering if there is an alternative using data.table syntax.

Any comments/suggestions appreciated

Upvotes: 2

Views: 61

Answers (2)

be_green
be_green

Reputation: 753

I see no issue with the chaining, but it looks to me like this works:

dt[, .(value = sum(price)
), 
by = list(group,varname = paste("total revenue",currency))
]

Hope that helps!

Upvotes: 2

MarkusN
MarkusN

Reputation: 3223

Tasks like this are easily accomplished with dplyr-package:

library(dplyr)
dt %>%
  group_by(group, currency) %>%
  summarise(total = sum(price))

If you insits on renaming the price-colum you can add another function:

 %>% mutate(currency = paste('total revenue', currency))

Upvotes: 0

Related Questions