A late friday afternoon coding question. I'm having a hard time thinking
of the correct search terms for what I want to do.
If I have a df like this:
a <-
data.frame(name=c(rep('a',10),rep('b',15)),year=c(1971:1980,1971:1985),amount=1:25)
name year amount
1 a 1971 1
2 a 1972 2
3 a 1973 3
4 a 1974 4
5 a 1975 5
6 a 1976 6
7 a 1977 7
8 a 1978 8
9 a 1979 9
10 a 1980 10
11 b 1971 11
12 b 1972 12
13 b 1973 13
14 b 1974 14
15 b 1975 15
16 b 1976 16
17 b 1977 17
18 b 1978 18
19 b 1979 19
20 b 1980 20
21 b 1981 21
22 b 1982 22
23 b 1983 23
24 b 1984 24
25 b 1985 25
and I'd like to reshape it so it is like this:
X.1971 X.1972 X.1973 X.1974 X.1975 X.1976 X.1977 X.1978 X.1979 X.1980
X.1981
a 1 2 3 4 5 6 7 8 9 10
NA
b 11 12 13 14 15 16 17 18 19 20
21
X.1982 X.1983 X.1984 X.1985
a NA NA NA NA
b 22 23 24 25
Thanks for the assist.
[[alternative HTML version deleted]]
You can use the reshape() in core R (the stats package)> reshape(a, timevar="year", idvar="name", direction="wide")name amount.1971 amount.1972 amount.1973 amount.1974 1 a 1 2 3 4 11 b 11 12 13 14 amount.1975 amount.1976 amount.1977 amount.1978 1 5 6 7 8 11 15 16 17 18 amount.1979 amount.1980 amount.1981 amount.1982 1 9 10 NA NA 11 19 20 21 22 amount.1983 amount.1984 amount.1985 1 NA NA NA 11 23 24 25 (Was naming the output columns "X.<year>" instead of "amount.<year>" important?) Bill Dunlap Spotfire, TIBCO Software wdunlap tibco.com> -----Original Message----- > From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf Of Trevor Davies > Sent: Friday, November 18, 2011 4:05 PM > To: r-help at r-project.org > Subject: [R] reshape data.frame > > A late friday afternoon coding question. I'm having a hard time thinking > of the correct search terms for what I want to do. > > If I have a df like this: > > a <- > data.frame(name=c(rep('a',10),rep('b',15)),year=c(1971:1980,1971:1985),amount=1:25) > name year amount > 1 a 1971 1 > 2 a 1972 2 > 3 a 1973 3 > 4 a 1974 4 > 5 a 1975 5 > 6 a 1976 6 > 7 a 1977 7 > 8 a 1978 8 > 9 a 1979 9 > 10 a 1980 10 > 11 b 1971 11 > 12 b 1972 12 > 13 b 1973 13 > 14 b 1974 14 > 15 b 1975 15 > 16 b 1976 16 > 17 b 1977 17 > 18 b 1978 18 > 19 b 1979 19 > 20 b 1980 20 > 21 b 1981 21 > 22 b 1982 22 > 23 b 1983 23 > 24 b 1984 24 > 25 b 1985 25 > > > and I'd like to reshape it so it is like this: > X.1971 X.1972 X.1973 X.1974 X.1975 X.1976 X.1977 X.1978 X.1979 X.1980 > X.1981 > a 1 2 3 4 5 6 7 8 9 10 > NA > b 11 12 13 14 15 16 17 18 19 20 > 21 > X.1982 X.1983 X.1984 X.1985 > a NA NA NA NA > b 22 23 24 25 > > Thanks for the assist. > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.
On Nov 18, 2011, at 7:04 PM, Trevor Davies wrote:> A late friday afternoon coding question. I'm having a hard time > thinking > of the correct search terms for what I want to do. > > If I have a df like this: > > a <- > data.frame(name=c(rep('a',10),rep('b', > 15)),year=c(1971:1980,1971:1985),amount=1:25) > name year amount > 1 a 1971 1 > 2 a 1972 2 > 3 a 1973 3 > 4 a 1974 4 > 5 a 1975 5 > 6 a 1976 6 > 7 a 1977 7 > 8 a 1978 8 > 9 a 1979 9 > 10 a 1980 10 > 11 b 1971 11 > 12 b 1972 12 > 13 b 1973 13 > 14 b 1974 14 > 15 b 1975 15 > 16 b 1976 16 > 17 b 1977 17 > 18 b 1978 18 > 19 b 1979 19 > 20 b 1980 20 > 21 b 1981 21 > 22 b 1982 22 > 23 b 1983 23 > 24 b 1984 24 > 25 b 1985 25wide.a <- reshape(a , direction="wide", idvar="name", timevar="year") names(wide.a) <- sub("amount", "x", names(wide.a) ) wide.a # ------------------- name x.1971 x.1972 x.1973 x.1974 x.1975 x.1976 x.1977 x.1978 x. 1979 x.1980 x.1981 x.1982 x.1983 x.1984 x.1985 1 a 1 2 3 4 5 6 7 8 9 10 NA NA NA NA NA 11 b 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 >> > > and I'd like to reshape it so it is like this: > X.1971 X.1972 X.1973 X.1974 X.1975 X.1976 X.1977 X.1978 X.1979 X.1980 > X.1981 > a 1 2 3 4 5 6 7 8 9 > 10 > NA > b 11 12 13 14 15 16 17 18 19 > 20 > 21 > X.1982 X.1983 X.1984 X.1985 > a NA NA NA NA > b 22 23 24 25 > > Thanks for the assist. > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.David Winsemius, MD West Hartford, CT
This is very straightforward using the reshape2 package:
library('reshape2')
dc <- dcast(a, name ~ year, value_var = 'amount')
name 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984
1 a 1 2 3 4 5 6 7 8 9 10 NA NA NA NA
2 b 11 12 13 14 15 16 17 18 19 20 21 22 23 24
1985
1 NA
2 25
dcast() returns a data frame; a companion function acast() returns a
matrix. If you want to change the names afterward, use
names(dc)[-1] <- paste('X', 1971:1985, sep = '.')
HTH,
Dennis
On Fri, Nov 18, 2011 at 4:04 PM, Trevor Davies <davies.trevor at
gmail.com> wrote:> A late friday afternoon coding question. ?I'm having a hard time
thinking
> of the correct search terms for what I want to do.
>
> If I have a df like this:
>
> ?a <-
>
data.frame(name=c(rep('a',10),rep('b',15)),year=c(1971:1980,1971:1985),amount=1:25)
> ? name year amount
> 1 ? ? a 1971 ? ? ?1
> 2 ? ? a 1972 ? ? ?2
> 3 ? ? a 1973 ? ? ?3
> 4 ? ? a 1974 ? ? ?4
> 5 ? ? a 1975 ? ? ?5
> 6 ? ? a 1976 ? ? ?6
> 7 ? ? a 1977 ? ? ?7
> 8 ? ? a 1978 ? ? ?8
> 9 ? ? a 1979 ? ? ?9
> 10 ? ?a 1980 ? ? 10
> 11 ? ?b 1971 ? ? 11
> 12 ? ?b 1972 ? ? 12
> 13 ? ?b 1973 ? ? 13
> 14 ? ?b 1974 ? ? 14
> 15 ? ?b 1975 ? ? 15
> 16 ? ?b 1976 ? ? 16
> 17 ? ?b 1977 ? ? 17
> 18 ? ?b 1978 ? ? 18
> 19 ? ?b 1979 ? ? 19
> 20 ? ?b 1980 ? ? 20
> 21 ? ?b 1981 ? ? 21
> 22 ? ?b 1982 ? ? 22
> 23 ? ?b 1983 ? ? 23
> 24 ? ?b 1984 ? ? 24
> 25 ? ?b 1985 ? ? 25
>
>
> and I'd like to reshape it so it is like this:
> ?X.1971 X.1972 X.1973 X.1974 X.1975 X.1976 X.1977 X.1978 X.1979 X.1980
> X.1981
> a ? ? ?1 ? ? ?2 ? ? ?3 ? ? ?4 ? ? ?5 ? ? ?6 ? ? ?7 ? ? ?8 ? ? ?9 ? ? 10
> NA
> b ? ? 11 ? ? 12 ? ? 13 ? ? 14 ? ? 15 ? ? 16 ? ? 17 ? ? 18 ? ? 19 ? ? 20
> 21
> ?X.1982 X.1983 X.1984 X.1985
> a ? ? NA ? ? NA ? ? NA ? ? NA
> b ? ? 22 ? ? 23 ? ? 24 ? ? 25
>
> Thanks for the assist.
>
> ? ? ? ?[[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>