Include third variable in table

Question

I have made an edit after realising my code was insufficient in order to explain to problem - appologies.

I have a data frame including four columns

purchaseId <- c("abc","xyz","def","ghi")
product <- c("a","b","c","a")
quantity <- c(1,2,2,1)
revenue <- c(500,1000,300,500)
t <- data.frame(purchaseId,product, quantity, revenue)

table(t$product,t$quantity)

Running this query

table(t$product,t$quantity)

returns a table indicating how many times each combination occurs

What I would like to do is plot both product and quantity as rows and columns (as shown above) but with the revenue as an actual value.

The result should look like this:

This would allow me to create a table that I could export as a csv.

Could anyone help me any further?

edit - the code suggested below throws the following error on the actual data set of 140K rows:

Error: dims [product 21525] do not match the length of object [147805]

Other ideas?

Of course the example code above is a simplified version of the actual data I'm using, but the idea is the same.

Thank you advance, Kind regards.

@etienne's solution is very cool, also what would work is the recast function from `data.table` or `reshape2`. `reshape2::recast(t,product ~ quantity, measure.var = "revenue", fill=0)` — Chris, Nov 27 '15 at 15:31

score 0 · Answer 1 · answered Nov 27 '15 at 15:30

0

table(t$product,t$quantity)*t$revenue

answered Nov 27 '15 at 15:30

pseudo_teetotaler

1,485
1
15
35

1

can u mention the error ? – pseudo_teetotaler Nov 27 '15 at 15:32
Runs without problems on my machine. However, you might consider using another variable name as `t` is a base-function in R and having variables named the same as functions is bad practice. – Heroka Nov 27 '15 at 15:36
@Heroka It isn’t really in R. – Konrad Rudolph Nov 27 '15 at 15:43
@Konrad I know, but I've helped solve someone a problem today which was partly caused by using 't' as a variable-name (and because it's a function, the error was not 'object not found' making debugging difficult). It's at least something to be aware of, imho. – Heroka Nov 27 '15 at 15:47
@KonradRudolph: what do you mean by "It isn't really in R" ? – etienne Nov 27 '15 at 15:48
2

@etienne It’s not bad practice (neither in R nor elsewhere, really). Name conflicts *will* happen in any moderately large code base, and choosing convoluted names to avoid this doesn’t fix any actual problem; if anything, it exacerbates it. R is unique in that it (a) has no proper concept of name isolation, which is a problem in its own right; and (b) allows you to refer to functions even if their name is shadowed by a locally declared non-function variable, which doesn’t really make sense. Yet neither of these two points changes the general fact that name shadowing isn’t inherently bad. – Konrad Rudolph Nov 27 '15 at 16:03
@KonradRudolph: great, thanks for the explanation ! – etienne Nov 27 '15 at 16:05
The following error occurs (updated version of the question) Warning message: In table(t$product, t$quantity) * t$revenue : longer object length is not a multiple of shorter object length In my actual set the following error shows Error: dims [product 42025] do not match the length of object [147805] It's actually fairly simple to do through a pivot table in Excel. Can't imagine it could be that hard in R... – glnvdl Nov 27 '15 at 21:32

score 0 · Answer 2 · answered Dec 07 '15 at 14:43

Using library(reshape2) or library(data.table)

dcast(t,product ~ quantity, value.var = "revenue", fun = sum)

it is fairly simple syntax:

Set the data frame you are recasting
Set the "formula" of the resulting data frame. LHS of ~ is the row-wise pivot, RHS is the column-wise.
value.var tells you what column we want to place in the cells, and using fun we want to aggregate with the sum function

As you mentioned in your comments familiarity with Excel Pivot tables, its worth noting that dcast is a fairly comprehensive replacement, with additional flexibility.

Include third variable in table

2 Answers2