use spread but keep two variables per column?

Question

Imagine the following df:

   quar              metier    kweffort trips
   <int>               <chr>       <dbl> <int>
1      1 OTB_DEF_100-119_0_0  12866.9022     6
2      1   OTB_DEF_70-99_0_0  13546.3839    11
3      1   OTB_MCD_70-99_0_0   2052.9117     2
4      1 TBB_DEF_100-119_0_0   4496.7492     1
5      1   TBB_DEF_70-99_0_0 384167.3136    69
6      1    TBB_DEF_G120_0_0   7260.1340     1
7      2 OTB_DEF_100-119_0_0  78069.8550    19
8      2   OTB_DEF_70-99_0_0  14333.0972    12
9      2    OTB_DEF_G120_0_0    859.0463     1
10     2   OTB_MCD_70-99_0_0  22528.3388    19

How can I use spread to get a table that has quar in the columns and both kweffort and trips as a value?

It would hopefully look like this:

metier                 quar 1                      quar 2    quar 3    quar 4
OTB_DEF_100-119_0_0    trip value kweffort value   ...       ...       ...

Would you mind being more precise about your desired output. It's not clear to me what you expect. — Steven Beaupré, Oct 25 '16 at 15:15

akrun · Answer 1 · 2016-10-25T17:01:39.653

1

We can unite the 'kweffort', and 'trips' to a single column, then spread to 'wide' format.

library(tidyr)
df1 %>%
    unite(kweffort_trips, kweffort, trips, sep=", ") %>% 
    spread(quar, kweffort_trips, sep="")

edited Oct 25 '16 at 17:01

answered Oct 25 '16 at 15:35

akrun

874,273
37
540
662

1

Maybe use `... sep = ''` in `spread()` so that `quar` appears in the column name. – Steven Beaupré Oct 25 '16 at 16:16

score 0 · Answer 2 · edited Oct 25 '16 at 18:54

0

Not with base R:

library(reshape2)
df$trips.kweffort <- paste('trips', df$trips, 'kweffort', df$kweffort)
dcast(df, metier~quar, value.var = 'trips.kweffort')

edited Oct 25 '16 at 18:54

Pierre L

28,203
6
47
69

answered Oct 25 '16 at 17:40

Sandipan Dey

21,482
2
51
63

use spread but keep two variables per column?

2 Answers2