Split column variables into new columns

Question

A dataset has found its way to me that is set up with each sample occupying a number of a rows, each of those rows for a certain variable (Type) that looks like this:

ID Lat Lon Type %
01 yy1 xx1 A    30    
01 yy1 xx1 B    45
01 yy1 xx1 C    25
02 yy2 xx2 A    10
02 yy2 xx2 B    60
02 yy2 xx2 C    30
03 yy3 xx3 A    15
...

I need to rearrange it like this, creating new columns for each variable in column Type and dropping out the % column, using it's entries to populate the new columns:

ID Lat Lon A  B  C
01 yy1 xx1 30 45 25
02 yy2 xx2 10 60 30
03 yy3 xx3 15
...

Do any of you R wizards know how to do this? Many thanks for any help!

score 0 · Answer 1 · answered Feb 10 '20 at 22:41

You can use:

reshape(df,dir="wide",timevar = "Type",idvar = c("ID","Lat","Lon"))
  ID Lat Lon val.A val.B val.C
1  1 yy1 xx1   30   45   25
4  2 yy2 xx2   10   60   30
7  3 yy3 xx3   15   NA   NA

or using pivot_wider from tidyr you could do:

library(tidyr)
 pivot_wider(df,id_cols = c("ID","Lat","Lon"),names_from = "Type",values_from = "val")
# A tibble: 3 x 6
     ID Lat   Lon       A     B     C
  <int> <fct> <fct> <int> <int> <int>
1     1 yy1   xx1      30    45    25
2     2 yy2   xx2      10    60    30
3     3 yy3   xx3      15    NA    NA

df:

df
  ID Lat Lon Type val
1  1 yy1 xx1    A 30
2  1 yy1 xx1    B 45
3  1 yy1 xx1    C 25
4  2 yy2 xx2    A 10
5  2 yy2 xx2    B 60
6  2 yy2 xx2    C 30
7  3 yy3 xx3    A 15

Split column variables into new columns

1 Answers1