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
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 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>.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With