CODEWITHSUNDEEP
rdata.tabletidyverse
qnp1521
qnp1521

Reputation: 896

R long to wide with pair of columns

I am trying to convert a dataset from long to wide format.

My input data is below:

df <- data.frame(year = c(2011:2013),
             T1 = c("a", "b", "c"),
             T2 = c("b", "c", "d"),
             V1 = rep(1, 3),
             V2 = rep(2, 3))

where the desired output is

df2 <- data.frame(year = c(2011:2013),
              a = c(1, 0, 0),
              b = c(2, 1, 0),
              c = c(0, 2, 1),
              d = c(0, 0, 2))

I think what makes this more challenging (at least for me) is the fact that column T1 and V1 are "pairs" and the same is true for T2 and V2. Tried to gather column T1 and T2 then spread (or dcast) it, but doesn't seem to work.

Here's what I've tried.

require(data.table)
dt <- setDT(df)
tmp <- melt(dt, measure.vars = c("T1", "T2"))
dcast(tmp, year ~ value, value.var = c("V1", "V2"))

When I dcast it, V1_a and V2_a takes value 1 and 2, respectively, not allowing me to keep the "pair" structure.

Any help would be greatly appreciated! Thank you!

Upvotes: 3

Views: 326

Answers (2)

akrun
akrun

Reputation: 887058

Here, we can use measure with patterns in the data.table syntax

library(data.table)
dcast(melt(setDT(df), measure = patterns("^T", "^V")), 
      year ~ value1, value.var = 'value2', fill = 0)
#   year a b c d
#1: 2011 1 2 0 0
#2: 2012 0 1 2 0
#3: 2013 0 0 1 2

Upvotes: 3

Ronak Shah
Ronak Shah

Reputation: 388962

You can use pivot_longer first to get data in long format with T and V pairs separately and then use pivot_wider to move it back to wide format.

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(cols = -year, names_to = c(".value", "cols"), 
               names_pattern = "([A-Z])(\\d)") %>%
  select(-cols) %>%
  pivot_wider(names_from = T, values_from = V, values_fill = list(V = 0))

# A tibble: 3 x 5
#   year     a     b     c     d
#  <int> <dbl> <dbl> <dbl> <dbl>
#1  2011     1     2     0     0
#2  2012     0     1     2     0
#3  2013     0     0     1     2

Upvotes: 2

Related Questions