Reshaping data with multiple columns

Question

How would I reshape this kind of data? This is just a short exercpt of my data, the real dataset is much longer. So an automated solution for any kind of lengh would be very appreciated.

data <- data.frame(id = c(1,2,3),
                   volume_1 = c(0.33, 0.58, 0.2),
                   name_1 = c("a", "b","c"),
                   volume_2 = c(0.3, 0.4, 0.5),
                   name_2 = c("x", "y", "z")
)
data

 id volume_1 name_1 volume_2 name_2
1  1     0.33      a      0.3      x
2  2     0.58      b      0.4      y
3  3     0.20      c      0.5      z

to this:

foo <- data.frame(id = c(1,2,3),
                  a = c(0.33, 0, 0),
                  b = c(0, 0.58, 0),
                  c = c(0, 0, 0.2),
                  x = c(0.3, 0, 0),
                  y = c(0, 0.4, 0),
                  z = c(0, 0, 0.5)
)
foo

 id    a    b   c   x   y   z
1  1 0.33 0.00 0.0 0.3 0.0 0.0
2  2 0.00 0.58 0.0 0.0 0.4 0.0
3  3 0.00 0.00 0.2 0.0 0.0 0.5

I´m aware of pivot_longer() or pivot_wider() and also of the reshape package, but I´m not sure how to accomplish this with any kind of length in the dataset.

Duck · Accepted Answer · 2021-01-22 15:28:29Z

1

Try this:

library(dplyr)
library(tidyr)
#Code
new <- data %>%
  pivot_wider(names_from = starts_with('name'),values_from=starts_with('volume'),
              values_fill = 0)

Output:

# A tibble: 3 x 7
     id volume_1_a_x volume_1_b_y volume_1_c_z volume_2_a_x volume_2_b_y volume_2_c_z
  <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>
1     1         0.33        0              0            0.3          0            0  
2     2         0           0.580          0            0            0.4          0  
3     3         0           0              0.2          0            0            0.5

answered Jan 22, 2021 at 15:28

Duck

39.6k14 gold badges47 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jay.sf · Accepted Answer · 2021-01-22 17:11:27Z

1

What you want is a double reshape, clean names using gsub, and set NAs to zero.

r <- reshape(reshape(data, idvar="id", varying=list(c(2, 4), c(3, 5)), direction="long"),
             idvar="id", timevar="name_1", drop="time", direction="wide")
names(r) <- gsub("volume_1.", "", names(r))
r[is.na(r)] <- 0
r
#     id    a    b   c   x   y   z
# 1.1  1 0.33 0.00 0.0 0.3 0.0 0.0
# 2.1  2 0.00 0.58 0.0 0.0 0.4 0.0
# 3.1  3 0.00 0.00 0.2 0.0 0.0 0.5

answered Jan 22, 2021 at 17:11

jay.sf

76.3k8 gold badges66 silver badges132 bronze badges

Collectives™ on Stack Overflow

Reshaping data with multiple columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related