Reshape data in R using dcast

Question

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'.

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

score 1 · Accepted Answer · answered Jun 18 '18 at 14:33

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

Reshape data in R using dcast

1 Answers1