Search code examples
rdcast

Reshape data in R using dcast

I have the following data 

library(reshape2)

d <- data_frame(
            Quart = c("Q1", "Q2", "Q3", "Q4", "Q4", "Q1","Q4","Q1", "Q2", "Q3"),
            var1 = c(1,0,1,1,1,0,2,3,0,1),
            var2= c(1,0,1,1,1,0,1,0,1,0),
            location= c("loc1", "loc1", "loc2", "loc2", "loc2", "loc2", "loc2",
                        "loc1", "loc2","loc1"))

I would like to reshape these data to create a table to look like the below.

The cell that is starred should be var1 multiplied by the number of loc1 where Quart=Q1. So in this scenario it would be '3', loc2 would be '0' as there aren't any rows that have both Q1 and loc2, loc3 would be '1', and loc4 would be '0'.

enter image description here

I've tried to reshape the data using d cast:

  d%>%
  reshape2::dcast(formula = Quart * var1 ~ location
              ,fun.aggregate = length       
translates to counts
              ,margins = c("Quart", "var1")
  ) -> d

However this doesn't quite give me what I want. Any ideas?

Thanks

Solution

  • An option is to first use tidyr::gather to convert var1 and var2 in long format and then apply dcast as:

    library(reshape2)
library(tidyverse)
gather(d, key, value, -Quart, -location) %>%
  dcast(Quart+key ~ location, fun.aggregate = sum, value.var = "value")

#   Quart  key loc1 loc2
# 1    Q1 var1    4    0
# 2    Q1 var2    1    0
# 3    Q2 var1    0    0
# 4    Q2 var2    0    1
# 5    Q3 var1    1    1
# 6    Q3 var2    0    1
# 7    Q4 var1    0    4
# 8    Q4 var2    0    3