When I try to convert my data from long to wide but I'm getting "Aggregation function missing: defaulting to length" error

Question

My data is

state   prod_year   num_oil_wells
AK  2009    10
AK  2008    8
AK  2007    16
AK  2006    17
AK  2005    15
AK  2004    11
AK  2003    9
AK  2002    14...

I want it to be in the following format;

state 2009 2008 2007
AK    10   8    16
AR    a    b    c  (a,b,c the values as per dataset).

Please have a look at http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example I have no idea what you're asking right now — MichaelVE, May 06 '15 at 00:40
Show the code you were running that produced that error message. — MrFlick, May 06 '15 at 05:44

score 0 · Answer 1 · answered May 06 '15 at 07:59

unfortunately you give no code, nor any other information so it is not easy to help you. But as a start, here is a suggestion.

From the error code you are given, it seems your data has duplicate values, or something like that.

What I would suggest is the following, assuming that D is your data.frame:

library(data.table)
D=data.table(D)
D=D[,lapply(.SD,sum),by=c('state','prod_year')]
dcast.data.table(D,state~prod_year, value.var='num_oil_wells')

Now, there is a better way to do that, and without using data.table, but I don't remember exactly how to use it (using dcast, and aggregate). But I hope this is a good start.

When I try to convert my data from long to wide but I'm getting "Aggregation function missing: defaulting to length" error

1 Answers1