Reframing magic on data.frame

Question

Accepted Answer

The answers so far work to a certain degree, but don't fully answer your question. In particular, they don't address the issue of a case in which there are no shops which sold a particular product. From your example input and desired output, there were no shops which sold "Product3". Indeed, "Product3" does not even appear in your source `data.frame`. Additionally, they do not address the possible situation of having more than one row for each Shop + Product combination.

Here's a modified version of your data and the two solutions so far. I've added another row for a combination of "Shop1" and "Product1". Notice that I have converted your products to a `factor` variable that includes the levels that the variable can take, even if none of the cases actually has that level.

```
mydf <- data.frame(
  Shop.Name = c("Shop1", "Shop1", "Shop2", "Shop3", "Shop3", "Shop1"),
  Items = c(2, 4, 3, 2, 1, 2),
  Product = factor(
    c("Product1", "Product2", "Product1", "Product1", "Product4", "Product1"),
    levels = c("Product1", "Product2", "Product3", "Product4")))
```

`dcast` from "reshape2"

```
library(reshape2)
dcast(mydf, formula = Shop.Name ~ Product, value="Items", fill=0)
# Using Product as value column: use value.var to override.
# Aggregation function missing: defaulting to length
# Error in .fun(.value[i], ...) : 
#   2 arguments passed to 'length' which requires 1
```

Wha? Suddenly does not work. Do this instead:

```
dcast(mydf, formula = Shop.Name ~ Product, 
      fill = 0, value.var = "Items", 
      fun.aggregate = sum, drop = FALSE)
#   Shop.Name Product1 Product2 Product3 Product4
# 1     Shop1        4        4        0        0
# 2     Shop2        3        0        0        0
# 3     Shop3        2        0        0        1
```

Let's be oldschool. `cast` from "reshape"

```
library(reshape)
cast(mydf, formula = Shop.Name ~ Product, value="Items", fill=0)
# Aggregation requires fun.aggregate: length used as default
#   Shop.Name Product1 Product2 Product4
# 1     Shop1        2        1        0
# 2     Shop2        1        0        0
# 3     Shop3        1        0        1
```

Eh. Not what you wanted again... Try this instead:

```
cast(mydf, formula = Shop.Name ~ Product, 
     value = "Items", fill = 0, 
     add.missing = TRUE, fun.aggregate = sum)
#   Shop.Name Product1 Product2 Product3 Product4
# 1     Shop1        4        4        0        0
# 2     Shop2        3        0        0        0
# 3     Shop3        2        0        0        1
```

Let's get back to basics. `xtabs` from base R

```
xtabs(Items ~ Shop.Name + Product, mydf)
#          Product
# Shop.Name Product1 Product2 Product3 Product4
#     Shop1        4        4        0        0
#     Shop2        3        0        0        0
#     Shop3        2        0        0        1
```

Or, if you prefer a `data.frame` (note that your "Shop.Name" variable has been converted to the `row.names` of the `data.frame`):

```
as.data.frame.matrix(xtabs(Items ~ Shop.Name + Product, mydf))
#       Product1 Product2 Product3 Product4
# Shop1        4        4        0        0
# Shop2        3        0        0        0
# Shop3        2        0        0        1
```

Reframing magic on data.frame

Solution

Problem

Related problems