Collapsing Table in R

Question

I'm trying to summarise a table in R and unsure the best way to approach it.

I've attempted this with the dplyr summarise but it does not seem to be very efficient, nor obvious what I need to do.

Starting Data

table <- data.frame(Company1 = c("A","A","C","C"), Company2 = c("B", "B", 
"D", "D"), target = c("sales", "turnover", "sales", "turnover"), result = 
c(200, 5000, 300, 7500))


     Company1 Company2   target result
 1        A        B    sales    200
 2        A        B turnover   5000
 3        C        D    sales    300
 4        C        D turnover   7500

Expected

     Company1    Company2       Sales        Turnover
 1        A         B         sales - 700  turnover - 5000
 2        C         D         sales - 300  turnover - 7500

score 2 · Accepted Answer · answered Sep 01 '19 at 13:29

2

We can use tidyr's unite to combine result and target column and then spread to get the data in wide format.

library(tidyr)

table %>%
  unite(result, target, result, sep = "-", remove = FALSE) %>%
  spread(target, result)

#  Company1 Company2     sales      turnover
#1        A        B sales-200 turnover-5000
#2        C        D sales-300 turnover-7500

answered Sep 01 '19 at 13:29

Ronak Shah

377,200
20
156
213

That's brilliant thanks. Not too familiar with tidyr other than the fill function. Didn't realise how much it had to offer. Thanks again! – Brad Sep 01 '19 at 13:42

score 0 · Answer 2 · answered Sep 01 '19 at 16:50

We can use dcast from data.table

library(data.table)
dcast(setDT(table)[, new := paste(target, result, sep = ' - ')], 
      Company1 + Company2 ~ target, value.var = 'new')
#  Company1 Company2       sales        turnover
#1:        A        B sales - 200 turnover - 5000
#2:        C        D sales - 300 turnover - 7500

Collapsing Table in R

2 Answers2