CODEWITHSUNDEEP
rdplyrtidyr
Aleph0
Aleph0

Reputation: 6084

Stack columns and introduce new factor type using gather

I have a very simple df1, which looks like this.

library(dplyr)
library(tidyr)

df1<-data.frame(
  G=c(1,1,1,1,1,1,1,2,2,2,2,2,2,2),
  I=c(1,2,3,4,5,6,7,1,2,3,4,5,6,7),
  X1=c( 0.043,  0.055,-0.060,  0.046,  0.140,  0.036,  0.045,  0.034,  0.032,  0.030,  0.029,  0.032,  0.032,  0.036),
  Y1=c(-0.422, -0.554,-0.426, -0.538, -0.446, -0.654, -0.437, -0.424, -0.416, -0.410, -0.411, -0.424, -0.397, -0.403),
  X2=c( 0.035 , 0.035, 0.023,  0.033,  0.040,  0.032,  0.036,  0.035,  0.035,  0.023,  0.033,  0.040,  0.032,  0.036),
  Y2=c(-0.420, -0.427,-0.409, -0.421, -0.422, -0.420, -0.402, -0.420, -0.427, -0.409, -0.421, -0.422, -0.420, -0.402)
)

I now wanted to use gather to transform this tibble, so that X2 and Y2 are stacked on top of X1 and Y1, but having BG=3. After the transformation it should have 3*7 rows and 4 columns named G,I,X_a,Y_a. It should look like the following (I just made it manually with a text editor). (X2 and Y2 do not vary among the factor BG).

G I    X_a    Y_a
1 1  0.043 -0.422
1 2  0.055 -0.554
1 3 -0.060 -0.426
1 4  0.046 -0.538
1 5  0.140 -0.446
1 6  0.036 -0.654
1 7  0.045 -0.437
2 1  0.034 -0.424
2 2  0.032 -0.416
2 3  0.030 -0.410
2 4  0.029 -0.411
2 5  0.032 -0.424
2 6  0.032 -0.397
2 7  0.036 -0.403
3 1  0.035 -0.420
3 2  0.035 -0.427
3 3  0.023 -0.409
3 4  0.033 -0.421
3 5  0.040 -0.422
3 6  0.032 -0.420
3 7  0.036 -0.402

My first attempt was to use the following:

df2 <- df1 %>%
  gather(X1, X2, key = TypeX, value = Xs) %>%
  gather(Y1, Y2, key = TypeY, value = Ys)

This comes close to what I wanted, but is actually not the thing I really want.

Can I achieve this behavior using just gather or do I need something else.

Upvotes: 2

Views: 50

Answers (1)

Kevin Arseneau
Kevin Arseneau

Reputation: 6264

Your problem can be solved through renaming and bind_rows using the dplyr package.

df %>%
  select(G, I, "X_a" = X1, "Y_a" = Y1) %>%
  bind_rows(
    df %>%
      filter(G == 1) %>%
      mutate(G = 3) %>%
      select(G, I, "X_a" = X2, "Y_a" = Y2)
  )

#    G I    X_a    Y_a
# 1  1 1  0.043 -0.422
# 2  1 2  0.055 -0.554
# 3  1 3 -0.060 -0.426
# 4  1 4  0.046 -0.538
# 5  1 5  0.140 -0.446
# 6  1 6  0.036 -0.654
# 7  1 7  0.045 -0.437
# 8  2 1  0.034 -0.424
# 9  2 2  0.032 -0.416
# 10 2 3  0.030 -0.410
# 11 2 4  0.029 -0.411
# 12 2 5  0.032 -0.424
# 13 2 6  0.032 -0.397
# 14 2 7  0.036 -0.403
# 15 3 1  0.035 -0.420
# 16 3 2  0.035 -0.427
# 17 3 3  0.023 -0.409
# 18 3 4  0.033 -0.421
# 19 3 5  0.040 -0.422
# 20 3 6  0.032 -0.420
# 21 3 7  0.036 -0.402

N.B. I've made the assumption that for G == 3 the results of from X2 and Y2 are duplicated for G == 1 & G == 2 from I == 1:7

Upvotes: 2

Related Questions