Manipulating a dataframe in R

Question

I have the following dataframe in R:

id    year    count 
1     2013    2
1     2014    20
2     2013    6
2     2014    7
2     2015    8
3     2011    13
...
999   2016    109

Each id is associated with at least 1 year, and each year has a count. The number of years associated with each id is pretty much random.

I wish to rearrange it into this format:

id    2011_count 2012_count 2013_count 2014_count ...
1     0          0          3          20         ...
2     0          0          6          7          ...
...
999   ...        ...        ...

I'm pretty sure someone else has asked a similar question, but I don't know how/what to search for.

Thanks!

also you cannot apply numbers as the first character in column names... — Cyrus Mohammadian, Sep 18 '16 at 02:59
will every year occur in your original dataset? that makes a big difference... — Cyrus Mohammadian, Sep 18 '16 at 03:17

apruden · Answer 1 · 2016-09-19T00:28:52.980

-1

Something like:

result <- reshape(aggregate(count~id+year, df, FUN=sum), idvar="id", timevar="year", direction="wide")
result[is.na(result)] <- 0
names(result) <- gsub("count\\.(.*)", "\\1_count", colnames(result))

edited Sep 19 '16 at 00:28

answered Sep 18 '16 at 03:02

apruden

368
2
8

that actually doesn't produce his desired outcome...if every year doesnt occur in the OP's original dataset (not to mention NAs) – Cyrus Mohammadian Sep 18 '16 at 03:17
edited answer to match OP result – apruden Sep 19 '16 at 00:29

Manipulating a dataframe in R

1 Answers1