Business Intelligence Tools / Production & Operations

Comparing Average with Values in Same Plot in Spotfire

A common task for an analyst is to plot averaged values in the same chart against quantities of compared variables in order to show the deviation. For instance, a visual representation of salaries of a certain job function in 3 US cities in the last year, can include the US national average to inform viewers of the departure of each city salary from the national average.

If you want to follow along, the download is on Exchange.ai.

In oil and gas, it is common to compare production rates of assets in a chart; and more so, to include the average rate in the same graphical rendition, as a performance indicator.  In this post, we are going to examine an easy way to do just that, using TIBCO Spotfire.

On the surface, the idea of plotting the average of quantities on the same plot with the associated quantities using Spotfire looks quite easy, or so I thought when a client approached me with the problem a few months ago. The problem was to include the average monthly production rate of any group of selected wells in the same plot as the monthly production rate of all the individual wells over a one-year period. The idea was to create a dynamic data table that includes the calculated average production rate of all selected wells in each month joined to the original data table of production rate of each well in each month. It turned out that to accomplish this, it is necessary to use TERR.

The process involves:

  1. supplying data from 3 columns representing period (date), variable (well name) and value (production rate), to TERR
  2. within TERR, calculating the average value (production rate) of all variables (wells) in each period (date)
  3. storing each calculated average as a value (production rate) against each period (date) in a variable preferably called “Average” in a tabular format
  4. merging tabular data in 3 above to the original data table in 1 above
  5. exporting the merge table in 4 above to Spotfire as a dynamic data table, and
  6. in Spotfire, plotting the relevant chart of period (date) on X-axis, value on Y-axis (production rate) and coloring by variable (well) name

Since in the dynamic data table, there is a variable named “Average”, the chart will show average values plotted in the same graph as the individual well values.

Now, let’s look at how to do this in more detail.

Consider the following production data of 3-wells, W-1, W-2, W-3. The corresponding monthly production rate line chart is as follows:

The TERR function to execute steps 1-6 above is as follows:

inputTable <- MonthlyWellProductionTable
productionMonth <- as.character(inputTable[,1])
well_Name <- inputTable[,2]
oilRate <- inputTable[,3]

monthsList <- unique(productionMonth)
wellsList <- unique(well_Name)
wellCount <- length(wellsList)

getOutputTable <- function(prdMth, numberofWells)
{
	j <- 1
	monthOil <- numeric(length(prdMth))
	AvgMonthOil <- numeric(length(prdMth))
	for(month in prdMth)
	{
		monthOil[j] <- sum(oilRate[which(productionMonth==month)])
		AvgMonthOil[j] <- monthOil[j]/numberofWells
		j<-j+1
	}
	
	well_name = 'Average'
	
	df1 = data.frame(MonthYear=prdMth, WellName=well_name, OilRate=AvgMonthOil)
	df2 = data.frame(MonthYear=productionMonth, WellName=well_Name, OilRate=oilRate)
	returnTable <- rbind(df1, df2)
	return(returnTable)
}
MthWellProductionAvg <- getOutputTable(monthsList, wellCount)

In lines 1-4, the original monthly well production data table (MonthlyWellProductionTable) is supplied as an input to the R data function and stored in the variable “inputTable”; and data from the 3 columns are stored in productionMonth, well_Name and oilRate variables.

In lines 6-8, distinct values of month and well names and the number of wells are stored in “monthsList”, “wellsList” and “wellCount” objects.

In lines 10-28, the function “getOutputTable” calculate the average rate for each production month, create a variable named “Average”, stores the calculated average rate against the corresponding month in a table, and then joins the table to the original monthly well production data table.

Finally, line 30 passes the joined table to the output variable “MthWellProductionAvg” which is a data table in Spotfire.

The line chart of the generated data is as shown:

The same principle can be applied to any data set in which values of individual variables are to be compared to statistical functions (average, mean, standard deviation, etc.) of the data in the same plot/visualization.

Download it here.

1 thought on “Comparing Average with Values in Same Plot in Spotfire

  1. AKINNIRAN ELLIOTT Reply

    Hi Theo,

    Quick question, what are the input and output parameters?

    Thanks a lot!

Leave a Reply

Your email address will not be published. Required fields are marked *