Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Reshape data.frame with two columns into multiple columns with data (R)

A trivial question but I cant find the answer as of yet.

I want to split the dataframe column 'year' into a set of new columns with each year the column name and subsequent data below it:

    Year     FQ
   1975  3.156
   1975  8.980
   1977 10.304
   1977  7.861
   1979  4.729
   1979  7.216
   1981  4.856
   1981  3.438
   1983  9.887
   1983  3.850

desired output:

1975    1977    1979   1981    1983 

3.156   10.304  4.729  4.856   9.887
8.980   7.861   7.216  3.438   3.850

sample data:

d<-structure(list(Year = structure(1:10, .Label = c("1975", "1975", 
"1977", "1977", "1979", "1979", "1981", "1981", "1983", "1983", 
"1985", "1985", "1987", "1987", "1988", "1988", "1991", "1991", 
"1993", "1993", "1995", "1995", "1997", "1997", "2000", "2000", 
"2001", "2001", "2003", "2003", "2005", "2005", "2007", "2007", 
"2009", "2009", "2011", "2011"), class = "factor"), FQ = c(3.156, 
8.98, 10.304, 7.861, 4.729, 7.216, 4.856, 3.438, 9.887, 3.85)), .Names = c("Year", 
"FQ"), class = "data.frame", row.names = c(1L, 62L, 123L, 184L, 
245L, 306L, 367L, 428L, 489L, 550L))

I have tried melting the data:

melt(d, id.vars = "Year")

and then using cast:

cast(d, Year~value) 

and reshape

d1<-reshape(d, idvar="Year", timevar="FQ", direction="wide")

but to no avail

like image 290
Salmo salar Avatar asked Dec 04 '13 11:12

Salmo salar


People also ask

How do I split data into columns in R?

To split a column into multiple columns in the R Language, we use the separator() function of the dplyr package library. The separate() function separates a character column into multiple columns with a regular expression or numeric locations.

How do I combine two columns of data in R?

How do I concatenate two columns in R? To concatenate two columns you can use the <code>paste()</code> function. For example, if you want to combine the two columns A and B in the dataframe df you can use the following code: <code>df['AB'] <- paste(df$A, df$B)</code>.

How do you spread data in R?

To use spread() , pass it the name of a data frame, then the name of the key column in the data frame, and then the name of the value column. Pass the column names as they are; do not use quotes. To tidy table2 , you would pass spread() the key column and then the value column.

How do you reshape data frames?

melt() function is used to reshape a DataFrame from a wide to a long format. It is useful to get a DataFrame where one or more columns are identifier variables, and the other columns are unpivoted to the row axis leaving only two non-identifier columns named variable and value by default.


1 Answers

You don't really have an "ID" variable, so you need to create one. It will be easier if Year was a character variable, so I've done that transformation below, in addition to adding an "ID" variable:

d <- within(d, {
  Year <- as.character(Year)
  ID <- ave(Year, Year, FUN=seq_along)
})

From here, it is easy to use dcast directly...

library(reshape2)
dcast(d, ID ~ Year, value.var="FQ")
#   ID  1975   1977  1979  1981  1983
# 1  1 3.156 10.304 4.729 4.856 9.887
# 2  2 8.980  7.861 7.216 3.438 3.850

... or reshape.

reshape(d, direction  = "wide", idvar="ID", timevar="Year")
#    ID FQ.1975 FQ.1977 FQ.1979 FQ.1981 FQ.1983
# 1   1   3.156  10.304   4.729   4.856   9.887
# 62  2   8.980   7.861   7.216   3.438   3.850
like image 74
A5C1D2H2I1M1N2O1R2T1 Avatar answered Sep 22 '22 05:09

A5C1D2H2I1M1N2O1R2T1