Calculated column not recognized in R dcast

0
votes

Using R. I'm a newbie. I did try to search for this particular error/situation and came up empty handed. Here goes:

I created a numeric column by subtracting an existing column from another column 

MYDATA$NETREV <- (MYDATA[60] - MYDATA[51])

When I try to used dcast on the new column doing this: 

NETREV.TREND <- dcast(MYDATA, SCHOOL ~ YEAR, value.var="NETREV")

I get this error: 

Error in `[.data.frame`(value, overall) : undefined columns selected

I've tried referring to the new column by position, value.var=MYDATA[61] and when I do that, I get an error that starts with :

Error: value.var (list(OPREVADJ = c(-9280446, -14437883, -12637590, -14365373, -17149995, -13960077, -11458410, -3701678, -861092, -10071075, 23965, -5324362, -5974479, 14275488, -6118691, -7801750, -7838486, -14343695, NA, -17785841, -14357459, -14787673, -480654 ... etc.

Using dcast with any other column in my data works fine and does exactly what it's supposed to do.

Sorry I didn't spell this out earlier. This is what my data look like:

SCHOOL  YEAR    REVENUE EXPENSES

A   2011    10000000    12000000

A   2012    15000000    14000000

A   2013    16000000    15700000

B   2011    8000000 6000000

B   2012    7500000 6500000

B   2013    7770000 5500000

I created the new column NETREV (which is obviously revenue minus expenses)

SCHOOL  YEAR    REVENUE EXPENSES    NETREV

A   2011    10000000    12000000    -2000000

A   2012    15000000    14000000    1000000

A   2013    16000000    15700000    300000

B   2011    8000000 6000000 2000000

B   2012    7500000 6500000 1000000

B   2013    7770000 5500000 2270000

I want the dcast to make it look like the below:

SCHOOL  2011    2012    2013

A   -2000000    1000000 300000

B   2000000 1000000 2270000
1
rreshape2dcast
Check the column names. What do you get from names(MYDATA)[names(MYDATA) %in% c("SCHOOL", "YEAR")]? – Pierre L
Please provide a reproducible example by including sample data. Good references for what and how to include are here: help/mcve and reproducible examples. – r2evans
@jgadoury's answer will solve the immediate function, but you will also probably want to not use the default aggregation function, length. I would recommend sum instead. – Gregor Thomas

1 Answers

1
votes

If you want to access columns in a data.frame, you should use MYDATA[, i] where i is your column number.

MYDATA$NETREV <- (MYDATA[, 60] - MYDATA[, 51])