aggregate() Function in R with sum

Question

I am a beginner to R. I'm trying to write code in R Script (within Spotfire) to calculate the sum of various columns by date in my data table.

As mentioned above, my data table lists volumes recorded on given dates for various products i.e oil, gas, and water for different wells. My goal is to use the Aggregate function in R to sum oil, gas, and water for all wells by date.

In the past, I have had success with the following script below which takes the average of each product of all wells for a particular date.

NORMALIZED.PRODUCTS.=data.frame(aggregate(x[,c("OIL","GAS","WATER")],by=list(x[,"T"]),FUN=mean))

When I try to change the function in the script above to the Sum function, I get an error.

NORMALIZED.PRODUCTS.=data.frame(aggregate(x[,c("OIL","GAS","WATER")],by=list(x[,"T"]),FUN=sum))

I then read elsewhere that maybe I needed to include na.rm = TRUE, na.action = NULL) after to help the problem, but I am still receiving the error below.

Could not execute function call.
TIBCO Enterprise Runtime for R returned an error: 'Error in aggregate.data.frame(x[, c("OIL","GAS", "WATER"  : no rows to aggregate
    eval(expr, envir, enclos)
    eval(expr, envir, enclos)
    data.frame(aggregate(x[, c("OIL","GAS", "WATER")],
    aggregate(x[, c("OIL","GAS", "WATER"],
    aggregate.data.frame(x[, c("OIL","GAS", "WATER")],
    stop("no rows to aggregate")'.
   at Spotfire.Dxp.Data.DataFunctions.Executors.LocalFunctionClient.OnExecuting()
   at Spotfire.Dxp.Data.DataFunctions.Executors.AbstractFunctionClient.<RunFunction>d__31.MoveNext()
   at Spotfire.Dxp.Data.DataFunctions.Executors.SPlusFunctionExecutor.<ExecuteFunction>d__12.MoveNext()
   at Spotfire.Dxp.Data.DataFunctions.DataFunctionExecutorService.<ExecuteFunction>d__3.MoveNext()

Here is my datatable

WELL                          T         OIL GAS WATER 
FILLMORE E4 24-25 3H LWS    10/11/2019  0   0   0
FILLMORE E4 24-25 3H LWS    10/12/2019  197 66  308
FILLMORE E4 24-25 3H LWS    10/13/2019  70  125 1095
FILLMORE E4 24-25 3H LWS    10/14/2019  79  1,211   881
FILLMORE E4 24-25 3H LWS    10/15/2019  0   0   0
FILLMORE E4 24-25 4H LWS    10/11/2019  0   0   0
FILLMORE E4 24-25 4H LWS    10/12/2019  276 90  374
FILLMORE E4 24-25 4H LWS    10/13/2019  47  93  1061
FILLMORE E4 24-25 4H LWS    10/14/2019  53  890 902
FILLMORE E4 24-25 4H LWS    10/15/2019  0   0   0
FILLMORE E4 25-24 1H LWS    10/11/2019  296 262 964
FILLMORE E4 25-24 1H LWS    10/12/2019  465 1,408   1343
FILLMORE E4 25-24 1H LWS    10/13/2019  -690    3,957   1267
FILLMORE E4 25-24 1H LWS    10/14/2019  81  2,093   1133
FILLMORE E4 25-24 1H LWS    10/15/2019  0   0   0

what error did you get? try to provide a toy sample of your actual data in your post, otherwise no one can help you with based on your description. — Chuan, Oct 16 '19 at 18:58
Thanks for the edits with sample data. The `,` in your data can cause problems. But *the most important question is **what error message do you get?*** — Gregor Thomas, Oct 16 '19 at 19:12
For dealing with the commas, [see this FAQ](https://stackoverflow.com/q/1523126/903061). — Gregor Thomas, Oct 16 '19 at 19:12
I was able to load a csv in spotfire and get BOTH sum and mean functions to work with the code above. If you load the data into R, the numeric columns get converted to factor with a comma. Spotfire can avoid that - if you load the data via the add data table with file option. — ngwells, Oct 25 '19 at 19:17

score 1 · Answer 1 · answered Oct 25 '19 at 19:38

I feel writing solutions for Spotfire is very awkward, but here it goes.

If you named the table 'x' you can just pass the data table into a data function with the input parameters and utilize your code as is. Assuming the columns types are String, Date, Integer,Integer, Integer for the 5 columns.

The output parameters is called 'NORMALIZED.PRODUCTS.' You also need to run the function and follow the prompts to return the data table properly.

If the data table is not named 'x', include:

x<-yourdatatablename NORMALIZED.PRODUCTS.=data.frame(aggregate(x[,c("OIL","GAS","WATER")],by=list(x[,"T"]),FUN=sum))

aggregate() Function in R with sum

1 Answers1