transpose n rows in dataframe with many columns in R

Question

I have a data frame with 6 variables and 973 observations. The fifth column has four unqiue character entries and the last column has a corresponding numeric value. Below is an example of the data:

year    month    gear    area    code    value
2018    1        YH      678      GEF     0.8
2018    1        YH      678      KID     1.7
2018    2        YH      678      KID     1.2
2017    3        JK      678      LKA     1.4
2017    3        JK      678      DJN     0.9
2017    3        JK      679      DJN     0.5
2018    7        JK      689      GEF     0.3

I would like to make the codes in the fifth column into column headers and have their corresponding values in each row, to rearrange as follows:

year    month    gear    area    GEF    KID    LKA    DJN
2018    1        YH      678     0.8    1.7
2018    2        YH      678            1.2
2017    3        JK      678                   1.4    0.9
2017    3        JK      679                          0.5
2018    7        JK      680     0.3

I have not been able to find a way to do this do keep the format of the first four columns.

score 1 · Accepted Answer · answered Jun 20 '18 at 06:45

1

You need to go from "long" to "wide" format:

library(tidyverse)    
df_wide <- spread(df, code, value)

answered Jun 20 '18 at 06:45

Omry Atia

2,411
2
14
27

1

`library(tidyr)` should be sufficient for this. +1 – Mankind_008 Jun 20 '18 at 07:12

Lennyy · Answer 2 · 2018-06-20T06:54:06.903

df <- read.table(text = "
year    month    gear    area    code    value
2018    1        YH      678      GEF     0.8
2018    1        YH      678      KID     1.7
2018    2        YH      678      KID     1.2
2017    3        JK      678      LKA     1.4
2017    3        JK      678      DJN     0.9
2017    3        JK      679      DJN     0.5
2018    7        JK      689      GEF     0.3", header = T)

reshape(df, v.names = "value", timevar = "code", direction = "wide", idvar = c("year", "month", "gear", "area"))

  year month gear area value.GEF value.KID value.LKA value.DJN
1 2018     1   YH  678       0.8       1.7        NA        NA
3 2018     2   YH  678        NA       1.2        NA        NA
4 2017     3   JK  678        NA        NA       1.4       0.9
6 2017     3   JK  679        NA        NA        NA       0.5
7 2018     7   JK  689       0.3        NA        NA        NA

transpose n rows in dataframe with many columns in R

2 Answers2