slicing rows and put back in as variables in R

Question

I want to slice a few rows and add the data back into the dataset as another variable. so my task goes like this... converting

location   year   value
aus       1990    1
aus       1991    2
aus       1992    2
usa       1990    1
usa       1991    3
usa       1992    2
uk        1990    3
uk        1991    2
uk        1992    2   
...     

into something like this

year   value_aus   value_usa   value_uk
1990      1            1          3
1991      2            3          2
1992      2            2          2
.
.
.

my data have 56 years for 36 different countries.

I tried as follows..

nations<-factor(data$LOCATION)
nationlist<-nations[!duplicated(nations)]


data_w<-data.frame(year=data$TIME[data$LOCATION==nationlist[1]])

for(loc in c(as.character(nationlist))){
  data_w<-data.frame(data_w[,], loc = data$Value[data$LOCATION==loc], check.rows=TRUE)
  }

but this didn't work and spits out "arguments imply differing number of rows: 56, 54" as some of the countries have different number of observations(years in this case) i guess.

Any helps would be greatly appreciated.

Jinseok

I think It isn't good idea to transform long format into wide format manually. `tidyr::spread(your_data, location, value)` would give what you want (see `help(spread, tidyr)`). — cuttlefish44, Nov 25 '17 at 14:23

www · Accepted Answer · 2017-11-25T15:10:52.103

A solution using dplyr and tidyr. The key is to use spread to convert data frame from long format to wide format. setNames(sub("location", "value", colnames(.))) is just to change the column names to be the same as the desired output.

library(dplyr)
library(tidyr)

dt2 <- dt %>%
  spread(location, value, sep = "_") %>%
  setNames(sub("location", "value", colnames(.)))
dt2
#   year value_aus value_uk value_usa
# 1 1990         1        3         1
# 2 1991         2        2         3
# 3 1992         2        2         2

DATA

dt <- read.table(text = "location   year   value
aus       1990    1
                 aus       1991    2
                 aus       1992    2
                 usa       1990    1
                 usa       1991    3
                 usa       1992    2
                 uk        1990    3
                 uk        1991    2
                 uk        1992    2   
                 ",
                 header = TRUE, stringsAsFactors = FALSE)

slicing rows and put back in as variables in R

1 Answers1