CODEWITHSUNDEEP
rsumaggregate
Ben Searcy
Ben Searcy

Reputation: 21

aggregate() Function in R with sum

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

Upvotes: 2

Views: 370

Answers (1)

ngwells
ngwells

Reputation: 188

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. enter image description here

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))

enter image description here

Upvotes: 1

Related Questions