tibble shaping to spread a factor across multiple variables to factor out rows

Question

Note, this differs from the question "How to reshape data from long to wide format?" in 2 ways:

There are a multiplicity of destination columns to be qualified by the single factor column.
The title of this question specified "tibble", implying tidyverse solutions are preferred.

Executing:

set.seed(14)
df <- data.frame(geo = LETTERS[1:2], time = c(2000,2000,2001,2001),
                     X = rnorm(4, 0, 1),
                     Y = rnorm(4, 0, 2))
head(df)

produces something like:

  geo time          X           Y
1   A 2000 -0.6618498 -0.07228116
2   B 2000  1.7189542  2.46389035
3   A 2001  2.1216670 -0.12976154
4   B 2001  1.4971537  2.13798746

What I want to do is reshape to spread time and end up with two rows with values for geo: A and B.

This would produce something like:

geo          X.2000           Y.2000          X.2001           Y.2001
  A      -0.6618498      -0.07228116       2.1216670      -0.12976154
  B       1.7189542       2.46389035       1.4971537       2.13798746

In base R, `reshape(df, direction = "wide", idvar = "geo", timevar = "time")`. With the `tidyverse`, you'll have to `gather`, `unite`, and then `spread`. `dcast` from "data.table" on a `data.table` can also do this without first `melt`ing the data. — A5C1D2H2I1M1N2O1R2T1, Dec 14 '17 at 05:06
Possible duplicate of [How to reshape data from long to wide format?](https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format) — Ronak Shah, Dec 14 '17 at 05:16
Not worth an answer, but for completeness, `data.table::dcast(as.data.table(df), geo ~ time, value.var = c("X", "Y"))`. — A5C1D2H2I1M1N2O1R2T1, Dec 14 '17 at 05:19

Kevin Arseneau · Accepted Answer · 2017-12-14T05:20:05.567

2

The tidy approach is:

library(tidyr)
library(dplyr)

df %>%
  gather(key, value, -c(geo, time)) %>%
  unite(key, c(key, time), sep = ".") %>%
  spread(key, value)

#   geo     X.2000   X.2001      Y.2000     Y.2001
# 1   A -0.6618498 2.121667 -0.07228116 -0.1297615
# 2   B  1.7189542 1.497154  2.46389035  2.1379875

edited Dec 14 '17 at 05:20

answered Dec 14 '17 at 05:16

Kevin Arseneau

6,186
1
21
40

As I specified 'tibble' in the title of the question, `tidyverse` is preferred. – user3673 Dec 14 '17 at 05:45
Unfortunately, this solution changes all the spread column classes to "character" if any of them don't match "numeric". – user3673 Dec 16 '17 at 03:48

score 0 · Answer 2 · answered Dec 14 '17 at 05:14

We can use reshape function

reshape(df, direction = "wide", idvar = "geo", timevar = "time")

and it gives us the results that you wish

geo     X.2000      Y.2000   X.2001     Y.2001
1   A -0.6618498 -0.07228116 2.121667 -0.1297615
2   B  1.7189542  2.46389035 1.497154  2.1379875

tibble shaping to spread a factor across multiple variables to factor out rows

2 Answers2