New columns based on a column modalities and other columns

user_13392775 0
Viewed 77

I couldn't find the answer to this but maybe i looked with the wrong keywords, sorry in advance if that's the case. Basically i have a dataframe that looks like this:

  code class Var1 Var2
1   U1     A    1    3
2   U2     A    5   43
3   U1     B    6    3
4   U2     B    6   12
5   U1     C   87    7
6   U2     C    1    8

It can be reproduced using the following code:

df <- data.frame(code=c("U1","U2","U1","U2","U1","U2"),class = c("A","A","B","B","C","C"), Var1 = c(1,5,6,6,87,1), Var2 = c(3,43,3,12,7,8))

And i would like to make it look like this by creating new columns based on the "Class" column modalities and the other columns, while keeping the column "Code" unchanged:

  Code Var1.A Var2.A Var1.B Var2.B Var1.C Var2.C
1   U1      1      3      6      3     87      7
2   U2      5     43      6     12      1      8

This second dataframe can be reproduced using this line:

df2 <- data.frame(Code=c("U1","U2"),Var1.A = c(1,5), Var2.A = c(3,43), Var1.B = c(6,6),Var2.B=c(3,12),Var1.C=c(87,1),Var2.C=c(7,8))

Please note that my real dataframe is way longer than the one in this example, and i would probably need an automated solution

Thank you very much!

tidyr reshape dataframe r
3 Answers

You can use the following solution. In this case we use glue syntax to create custom column names. .value refers to column names I chose for values and class is the values previously stored in class column with a literal dot . between them as follows:

library(tidyr)

df %>%
  pivot_wider(names_from = "class", values_from = c("Var1", "Var2"), 
              names_glue = "{.value}.{class}")

# A tibble: 2 x 7
  code  Var1.A Var1.B Var1.C Var2.A Var2.B Var2.C
  <chr>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
1 U1         1      6     87      3      3      7
2 U2         5      6      1     43     12      8
user_143145200

Using dcast from data.table

library(data.table)
dcast(setDT(df), code ~ class, value.var = c("Var1", "Var2"), sep=".")
   code Var1.A Var1.B Var1.C Var2.A Var2.B Var2.C
1:   U1      1      6     87      3      3      7
2:   U2      5      6      1     43     12      8
user_37322710

A base R option with reshape can help you

reshape(
  df,
  direction = "wide",
  idvar = "code",
  timevar = "class"
)`

which gives

  code Var1.A Var2.A Var1.B Var2.B Var1.C Var2.C
1   U1      1      3      6      3     87      7
2   U2      5     43      6     12      1      8
user_121587570
Related