How to load Petro.ai into Excel

Lucas Wood Uncategorized Leave a comment

It’s taken me a while to fully consider the full implications of having a great API to query. Nearly every application has access to REST API calls and this makes for a really compelling way to access data. For all the years I’ve been using Spotfire, I haven’t given much consideration to Excel. But it’s an important application and it too, is improved through live data. In this blog, I want to show you how to get access to Petro.ai data through Excel!

Reference

First you’ll need the location of your Petro.ai instance from your administrator. For our demo database you can use http://tst-geologic.petro.ai/docs/index.html, provided by GeoLogic.

This is the reference to all the data that is available inside the tool. For our purposes, we just to pull out the well headers. That address is http://tst-geologic.petro.ai/api/Wells.

Alright on to step one where we setup the data source in Excel.

Setup the Data Source in Excel

You’ll need to use the data source functionality of Excel:

  1. From the Data tab, select Get Data > From Other Sources > From Web.
  2. Let add the web link to the dialog box, and we’ll limit it to 10 like below. Click Advanced and then look specifically at the URL parts.

  3. You’ll come up against a dialog like this, this is the Power Query Editor. We’re going to cheat and I’m going to give you the code to directly connect to it. First press Home.

  4. Click on Advanced Editor, take the code below and replace it with the text I put here: 

let
    Source = Json.Document(Web.Contents("http://tst-geologic.petro.ai/api/Wells" & "?Limit=20")),
data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"wellId", "name", "operatorName", "entityType", "statusCurrent", "statusCurrentDate", "lastProductionDate", "permitDate", "spudDate", "completionDate", "leaseName", "leaseNumber", "wellNumber", "fieldName", "basinName", "lateralLength", "fracStages", "totalProppant", "totalFluidPumped", "perforationUpper", "totalDepth", "measuredDepth", "formationName", "stateName", "countyName", "countryName", "surfaceLoc", "midPointLoc", "bottomHoleLoc", "production", "prod", "eur", "eurLatest", "typeEUR", "typeEURLatest", "typeWeight", "groups", "id", "extra"}, {"petro.wellId", "petro.name", "petro.operatorName", "petro.entityType", "petro.statusCurrent", "petro.statusCurrentDate", "petro.lastProductionDate", "petro.permitDate", "petro.spudDate", "petro.completionDate", "petro.leaseName", "petro.leaseNumber", "petro.wellNumber", "petro.fieldName", "petro.basinName", "petro.lateralLength", "petro.fracStages", "petro.totalProppant", "petro.totalFluidPumped", "petro.perforationUpper", "petro.totalDepth", "petro.measuredDepth", "petro.formationName", "petro.stateName", "petro.countyName", "petro.countryName", "petro.surfaceLoc", "petro.midPointLoc", "petro.bottomHoleLoc", "petro.production", "petro.prod", "petro.eur", "petro.eurLatest", "petro.typeEUR", "petro.typeEURLatest", "petro.typeWeight", "petro.groups", "petro.id", "petro.extra"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"petro.completionDate", "petro.leaseName", "petro.leaseNumber", "petro.basinName", "petro.lateralLength", "petro.fracStages", "petro.totalProppant", "petro.totalFluidPumped", "petro.perforationUpper", "petro.totalDepth", "petro.measuredDepth", "petro.formationName", "petro.countyName", "petro.midPointLoc", "petro.bottomHoleLoc", "petro.production", "petro.prod", "petro.eur", "petro.eurLatest", "petro.typeEUR", "petro.typeEURLatest", "petro.typeWeight", "petro.groups", "petro.extra"}),
    #"Expanded petro.surfaceLoc" = Table.ExpandRecordColumn(#"Removed Columns", "petro.surfaceLoc", {"latitude", "longitude", "type", "coordinates"}, {"petro.surfaceLoc.latitude", "petro.surfaceLoc.longitude", "petro.surfaceLoc.type", "petro.surfaceLoc.coordinates"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded petro.surfaceLoc",{"petro.surfaceLoc.coordinates"})
in
    #"Removed Columns1"

  1. Press Close & Apply, you’re finished!

Basics of Implementing IronPython in Spotfire

Julie Sebby IronPython, Learning IronPython, Spotfire 2 Comments
Over the last few weeks, I’ve released several posts on learning IronPython.  
The response has been extremely positive. Many users identify with the struggle to learn IronPython (and other languages) for Spotfire. One individual reached out to say that he needed more information on where/how to apply the code. I realized I’ve written that post for TERR, but I haven’t for IronPython. So, this post will explain implementing IronPython code. Let’s get to it.
 

Where do you put the code?

So, you found an IronPython code snippet to apply in your own DXP, but you don’t know where it goes. Perhaps, you found something on Brock’s website.
 
Now, unlike TERR scripts, IronPython scripts don’t run automatically. They must be triggered, usually by a button or a property control, which live in text areas. Therefore, IronPython scripts start in text areas. So, add a text area and follow the instructions below.
 
  1. First, add text area.
  2. Right-click and select Edit HTML. You can also select Edit Text Area.
  3. Then, click the insert action control button in the toolbar. 
  4. You’ll then need to decide whether you want to click on a Button, Link or Image. Buttons are most common. Make a selection in the Control type drop down.
  5. Give it a name in the Display text input box.
  6. Now, click on the Script button on the left hand side of the dialog.
  7. This opens a new dialog where you want to click New. 
  8. Name the script.
  9. Copy and paste the script in the script window.   Make any modifications as needed.
  10. Add any required parameters. 
  11. Finally, click Run Script to make sure it works.
  12. Click Ok.
 
If needed, you may edit the script from the text area going thru similar steps. Or, IronPython scripts can also be edited from the Edit — Document Properties menu. 
 

What about Python modules?

The same user who requested this post also asked if he needed to install anything to use IronPython. He was thinking about how users install R or TERR packages to run TERR scripts. You don’t need to install anything, although you can. There are TIBCO Community posts on how to install custom python modules if you want to use your own or other modules like numpy or pandas. You can find those here and here. If you are reading this, you probably aren’t that far along.  But that’s okay!  There’s plenty to learn.  This should get you started implementing IronPython in your own DXPs.

Spotfire Version

Content created with Spotfire 7.12.

Writing your First JavaScript Vue.js App for Petro.ai

Lucas Wood JavaScript Leave a comment

Getting Petro.ai installed can be an exciting time an open quite a few doors for development, especially when it comes to JavaScript apps. Custom applications become a cinch using the API. In the coming weeks I’ll be putting together some simple applications that you can make on top of the Petro.ai platform. We’ll be using an assortment of languages to communicate with the Petro.ai API so feel free to ask for an example.

Here is the HTML


<script src="https://unpkg.com/vue/"></script>
<h1>Hello, Wells!</h1>
<div id="hello-wells" class="demo">
   <blog-post 
      v-for="well in wells" 
      v-bind:key="well.id" 
      v-bind:title="well.name">
   </blog-post>
</div>

And the JavaScript (Vue.js)

Vue.component('blog-post', {
  props: ['title'],
  template: '<p>{{ title }}</p>'
})

new Vue({
  el: '#hello-wells',
  data: {
    wells: []
  },
  created: function () {
    var vm = this
    // Fetch our array of documents from the Petro.ai wells collection
    fetch('http://<your-petro-ai-server>/api/Wells?Limit=10')
      .then(function (response) {
        return response.json()
      })
      .then(function (data) {
        vm.wells = data['data']
      })
  }
})

And poof! We’ve called the first 10 wells from the Petro.ai wells collection:

Hello, Wells!

DEJOUR WOODRUSH B-B100-E/094-H-01
BLACK SWAN HZ NIG CREEK B-A007-G/094-H-04
BLACK SWAN HZ NIG CREEK B- 007-G/094-H-04
BLACK SWAN HZ NIG CREEK B-G007-G/094-H-04
BLACK SWAN HZ NIG CREEK B-E007-G/094-H-04
BLACK SWAN HZ NIG CREEK B-D007-G/094-H-04
BLACK SWAN HZ NIG CREEK B-C007-G/094-H-04
ZEAL 4-25-46-26
PEYTO WHHORSE 4-9-49-15
BLACK SWAN HZ NIG CREEK A- 096-C/094-H-04

What’s going on here is that the app is pulling directly from the Petro.ai server asynchronously. In the coming weeks, I’ll show how we can create reactive JavaScript applications that will update from the Petro.ai server so that we can watch things like rigdata or real-time production data. This data was provided by GeoLogic and we’ll be setting up a public Petro.ai instance for everyone to develop against.

Learn IronPython for Spotfire…Navigating the API

Julie Sebby IronPython, Learning IronPython, Spotfire 9 Comments

Welcome to my second IronPython post this week.  If you missed the first one, start here.  The first post introduced the code and provided information on how to modify it for other use cases.  This post builds on that by focusing on navigating the Spotfire API reference. The next post will then explain how and why the code is written.  A screenshot of the code is shown below, and then we’ll dive into the API.

Code

This code is grabbing a particular filter in the filter panel (a checkbox filter) and unchecking all the boxes.

Walking Thru the API

I am going to walk thru the API one reference at a time.  Note, I am NOT going to explain exactly what the code is doing (yet).  This section will show you where to find the API references, which in turn will show you how to navigate the API.  The next post will explain what the code is doing and why.

Lines 1, 3, 5

Lines 1, 3, and 5 import namespaces and classes from the Spotfire.Dxp.Application.Filters namespace.

Line 1 import Spotfire.Dxp.Application.Filters as filters

Line 1 imports the Spotfire.Dxp.Application.Filters namespace.  If you are in the API reference, this namespace is 8 lines down at the highest level of the hierarchy.  It is also mentioned/linked in the Spotfire.Dxp.Application namespace as shown below.  As you can see in the API reference, this namespace works with filters and filtering schemes in the filter panel.  That is exactly what we are doing – modifying a filter.

Location of namespace.
More specific namespace.

 

Line 3 import Spotfire.Dxp.Application.Filters.CheckBoxFilter

Line 5 from Spotfire.Dxp.Application.Filters import FilterTypeIdentifiers

Lines 3 and 5 import CheckBoxFilter and FilterTypeIdentifiers, which are classes in Spotfire.Dxp.Application.Filters namespace.  Once the class has been added, the developer can reference a class’ properties, methods, fields, etc.

Here’s where to find the classes in the namespace.

Line 7 

Line 7 myPanel = Document.ActivePageReference.FilterPanel

Line 7 references the Document class, which is a class in the Spotfire.Dxp.Application namespace.  The developer didn’t import this namespace because Spotfire imports most of it by default.  ActivePageReference is a property of the Document class. Now, the ActivePageReference gets or sets the currently active Page.  If you are in the API reference and click on Page as circled below, you’ll see this then takes you to the Page class, where Filter Panel is a property.  

Line 9

Line 9 myFilter = myPanel.TableGroups[4].GetFilter("BudgetNode")

Line 7 essentially ends in the Filter Panel.  Then, Line 9 calls the TableGroups property, which is in the FilterPanel class under the Spotfire.Dxp.Application.Filters namespace.  The code also uses the GetFilter method from the FilterGroup class.

Line 11

Line 11 myFilter.FilterReference.TypeId = FilterTypeIdentifiers.CheckBoxFilter

Line 11 references a lot of the API, and this one was a bit confusing at first because I didn’t understand how to move from the filter panel to the actual filter.  TIBCO support helped by showing me this flow chart.  In order to access filters in the filter panel, you must use the TableGroup, even if there is only one table in your DXP.  TableGroup is the path to the FilterHandle, which accesses the filter.

Now, when I was working thru the API, I saw references to the FilterSubGroup, and I wasn’t sure if it was referring to creating a subgroup in the filter panel (select filter in filter panel, right-click, New group) or if it was a different API reference.  Groups in the filter panel would be accessed via TableGroup.SubGroups, but they don’t have to be there.

So, line 11 hits three different classes and multiple properties.  First, it references the FilterReference property in the FilterHandle class.  If you go to the FilterHandle class, you will find the FilterReference property in the API.  This gets the Filter that fits this FilterHandle reference.  If you click on Filter (as circled below), it takes you to the Filter class.  Then the code uses the TypeId property in the Filter class.  That value should equal the CheckBoxFilter field from the FilterTypeIdentifers class.

Line 13

Line 13 checkBoxFilter = myFilter.FilterReference.As[filters.CheckBoxFilter]()

Line 13 is just as busy as Line 11.  It hits the FilterReference property in the FilterHandle class.  That connects with the As method from the Filter class.  Remeber, Line 1 created the filters object, which was equal to the Spotfire.Dxp.Application.Filters namespace, so filters.CheckBoxFilter = Spotfire.Dxp.ApplicationFilters.CheckBoxFilter, which is a reference to the CheckBoxFilter class.

Line 15

Line 15 checkBoxFilter.IncludeEmpty = False

This line uses the IncludeEmpty property, which is in the CheckBoxFilter class.   Can you find this on your own in the API?

Line 17

Line 17 for value in checkBoxFilter.Values:

Here, the code references the Values property of the CheckBoxFilter class. What about this one?  Can you find it on your own in the API?

Line 19

Line 19 checkBoxFilter.Uncheck(value)

Line 19 uses the Uncheck method of the CheckBoxFilter class. Although, note that in this line, checkBoxFilter is an object, not a reference to the class.

Now you know where all of the API references came from!  Before wrapping up, I want to mention that I had to search the API quite a bit.  Now, searching would be easy if the API named all of the properties, methods, fields, classes, etc uniquely.  If my search returned an API result, I would know it is the right reference because there is only one.  Unfortunately, the API is not written that way.  Classes are distinctly named, but other pieces of syntax can have duplicates.  For example, Title is a property of Page, Panel, MapChart, and all other visualization types represented as classes. Keep that in mind as you are exploring (and checking my work).

Summary of API References

If you want to walk thru everything again, here is a summary of the namespaces, classes, properties, methods, and fields used in this code.

  • Spotfire.Dxp.Application namespace
    • Document class
      • ActivePageReference property
    • Page class
      • FilterPanel property
  • Spotfire.Dxp.Application.Filters namespace
    • CheckBoxFilter class
      • IncludeEmpty property
      • Values property
      • Uncheck method
    • Filter class
      • TypeId property
      • As method
    • FilterGroup class
      • GetFilter method
    • FilterHandle class
      • FilterReference property
    • FilterPanel class
      • TableGroups property
    • FilterTypeIdentifiers class
      • CheckBoxFilter field

Conclusion

Unfortunately, there’s a whole lot I haven’t addressed yet.  For example, what if you don’t know the difference between a namespace, property or class? Don’t worry, I’ll get there in a future post.  What if you don’t understand why each piece of the API was called.  I’ll get there too.  This learning process is a long game.  The next post explains how the code is written.

Spotfire Version

Content created with Spotfire 7.12.

Learn IronPython for Spotfire…One Code Snippet At A Time

Julie Sebby IronPython, Learning IronPython, Spotfire 2 Comments

This is going to be a big week of posts, three posts to be exact.  I’m heading to Houston, which means a lot of time on planes and time for writing.  The foundation for the posts is a piece of IronPython code that checks and unchecks the checkboxes in a filter.  I needed code to do this for a project I was working on last week.  A Google search turned up this TIBCO community link.  The second response was exactly what I needed.

Now, I’m on a bit of a learn IronPython kick, and I wanted to understand to how this was written.  I started by looking up each piece of API in TIBCO’s API reference.  In the past, the API reference read like Greek to me.  But walking thru the API one piece at a time, combined with the other 16 hours I’ve put into IronPython, really helped me understand what each line of code was doing.  I got really excited and started writing it up.  Per usual, it was too long.  So, now I’m on a plane breaking it down into 3 posts, for ease of consumption.  Here’s how it’s going to go.

  1. Post 1 (this post) takes a high-level look at the code and explains how to modify it for different use cases. This will familiarize you with the code.  I’ll also talk about objects versus syntax to build on the post I wrote last week on Learning IronPython.
  2. Post 2 will talk about the API in detail by explaining where each piece of the API is and how to navigate to it.
  3. Post 3 (include link) will explain what the code is doing and why. This is something I have shied away from in the past because I didn’t have the understanding to do so.  I’m getting there one code snippet at a time, and you’re coming with me.

We are going to get to the code, but first, I want to clarify my requirements.

Requirements

Ideally, I wanted one piece of code to check and uncheck all the checkboxes.  The TIBCO article presented two code snippets, so I created two buttons with two sets of code, one for checking and another for unchecking.  I’m going with this even though a slightly better iteration would be one code snippet with “IF” logic that looks to see if the boxes are checked and if they are, it unchecks them and vice versa. I’m going with two buttons for now. Gotta walk before you run.

Code Screenshots

Now, you can copy and paste the code from the TIBCO link, so I won’t include that here.  I will show my screenshots so there is no question about spacing.

Check All

Uncheck All

Next, let’s talk about code syntax versus object.

Learning IronPython

Last week, I discussed how to figure out if a particular word in the code is an object the developer created or part of the syntax.  I suggested running a simple test by changing the name of the object.  If the code still runs, it’s an object, not syntax.  To build on that though, the equals sign usually signals an object.  Keywords, like “as”, “for”, and “in” are also indicative an object is coming.

For example, the words circled in the screenshot below were created by the developer.  They could be any word the developer chooses.  Also, all of the other references to Filters are uppercase, indicating they are API references.  That’s not to say that objects can’t be uppercase, but if all the API references are in uppercase, it makes sense to name objects in lowercase.

Now, I don’t want to just show you which parts of the code are objects.  I want to explain why the developer created the objects.  Generally speaking, developers create objects to make referencing things in code easier.  I’ll use “filters” as an example.  This object is only used one other time after creation in line 13.  So, the two lines of code shown below illustrate the difference with and without the object.

Line 13 without an object —

checkBoxFilter = myFilter.FilterReference.As[Spotfire.Dxp.Application.Filters.CheckBoxFilter]()

versus Line 13 with an object —

checkBoxFilter = myFilter.FilterReference.As[filters.CheckBoxFilter]()

Here’s another example using “myPanel” from line 9.

Line 9 without an object —

myFilter = Document.ActivePageReference.FilterPanel.TableGroups[4].GetFilter("BudgetNode")

versus Line 9 with an object —

myFilter = myPanel.TableGroups[4].GetFilter("BudgetNode")

Next, let’s talk about how to modify this code for your own use cases.

Modifying for Different Scenarios

There are four modifications you might want to make to this code.

  1. Obviously, you’ll want to change the column name to your own columns of data.
  2. If there is more than one table in your analysis, you will most likely need to change the TableGroup index.
  3. You might want to account for Empty values.
  4. You might need to check and uncheck more than one filter.

First & Second Modifications

The first modification is the simplest to make.  Just change the column name circled below.  Next, the code “myPanel.TableGroup[4] is telling Spotfire which table to get BudgetNode from.  The number four is an index of table positions in the filter panel. The index values start at zero.  My table is the 5th in the list as shown in the second screenshot below, so I need 4 in the index to get the column from the correct data table in the filter panel.

Third Modification

The third modification relates to what happens when there are empty values in the filter. 

If the Boolean value is set to False, (Empty) will not be unchecked when the button is clicked.  If you want (Empty) to be unchecked, set the value to True.

Line 15 checkBoxFilter.IncludeEmpty = False

Change to…

Line 15 checkBoxFilter.IncludeEmpty = True

 

Modification 4

If you need to uncheck multiple filters, copy and paste lines 9 thru 19.  Then modify the myFilter, checkBoxFilter, and value objects.  Rename them something like mFilter1, checkBoxFilter1, and value1 so that they are their own distinct objects and don’t conflict with previously defined objects. Now, I am going to walk thru the API via this code example.

 

Conclusion

Lastly, you might have noticed the post introduced two code snippets but only followed one thru to this conclusion.  The code snippets are similar enough that you should be able to make the same modifications described for both pieces of code.

The next post is going to dive deep into the API.  Be ready to follow along.  Click here to jump straight to it.

Spotfire Version

Content created with Spotfire 7.12.

 

 

C#, IronPython, and Python Reference Links

Julie Sebby IronPython, Learning IronPython 1 Comment

This list of links is an accompaniment to my post on Learning IronPython for Spotfire.  Please leave a comment if any of the links are broken.

C#

IronPython

Python

Learning IronPython for Spotfire

Julie Sebby IronPython, Learning IronPython, Spotfire 8 Comments

Intro

Ever since I learned what IronPython was and what it could do in Spotfire, I’ve had a “goal” to learn IronPython. I put the word goal in air quotes because learning IronPython isn’t a goal. Or at least, it’s not a very good one because it’s not measurable.  How do I know when I’ve accomplished it? And that’s not to say I haven’t learned any IronPython over the years.  Clearly, I have.  I write about it a lot, but I aspire for deep knowledge.  

The struggle was knowing how to compartmentalize learning IronPython. How could I break it down into something measurable? Finally, in October I commited to 100 hours of IronPython.  Now, that’s measurable.  The amount of time is somewhat arbitrarily.  However, I expected that if I spent 100 hours on IronPython, I would know significantly more than when I started and be able to explain it other people.
 
So, did that happen? OF COURSE! Heck, I’m only 16 hours in, and I understand so much more now than I did 16 hours ago. Now, you might be saying — Wait, you set the goal in October, and you’re only 16 hours in? Yes. That is correct. I do have a day job and this awesome blog and HOLIDAYS. Stuff happens, but life (and my career) is a long game, so no giving up.
 
That is my somewhat long-winded explanation to this post. I know all users struggle with applying IronPython in Spotfire, and I want to make that easier on you. Since it’s a bit of an unknown path for me, I can’t break this down into a series like I have other topics. You’ll just see Learning IronPython posts now and again.
 
This post in particular is going to cover the following…
  1. An explanation of why learning IronPython is so hard
  2. My initial learning goals
  3. My learning methods
  4. Some syntax and structure via a code example
Please feel free to comment if you see places where I am going awry, but be nice.
 

Why is learning IronPython so hard?

When learning a new coding language, many users look for books or online tutorials. If you google “learn IronPython”, the results are surprisingly sparse. The first result is to a set of documentation, and the second is Quora. Anytime Quora is in your top results, you are in trouble.  The fourth reference is in the UK….you see where this is going.
If you search Amazon for books on IronPython, you get the results shown below.  After 3 or 4 books, the results shift to books about Python. None of the results look suitable for beginners.  Strike two.
 
At some point, you have to ask, what is IronPython?  IronPython.net will tell you…
IronPython is an open-source implementation of the Python programming language which is tightly integrated with the .NET FrameworkIronPython.net
That might lead you to think that you need to learn Python, and that would be helpful, but Wiki will tell you…..
IronPython is written entirely in C#, although some of its code is automatically generated by a code generator written in Python.Wikipedia
  The hamster wheel is now starting to turn, and perhaps you remembered that Spotfire extensions are also written in C#, and you have seen a reference to C# in the TIBCO API documentation, as shown here.
 
Wait….you mean I should actually be learning C#??? Yup.  Now, things get a lot easier. 

Learning Goals

Now, it may seem like a bit of backtracking, but I want to explain what I set out to learn. If you’ve read the blog for any length of time, you know I’ve posted IronPython code snippets before. How can I do that but not really know IronPython? Easy…I learn by looking at code online, breaking it down bit by bit, and learning by doing (i.e modern learning). However, without a solid understanding of the underlying architecture, that method is limited. Thus, my primary goals for the first 10 hours were…
  1. Find better resources
  2. Get an understanding of IronPython structure
  3. Get an understanding of the Spotfire API
  4. Apply that understanding in Spotfire code examples
It turns out, that took 16 hours, not 10. I had to put in 16 hours of learning before I felt confident enough to write this post.
 

Learning Methods

I started my learning process by evaluating what I knew and didn’t know. Right before I kicked off this journey, I learned about The Spotfire IronPython Quick Reference.  This is an amazing website for learning IronPython for Spotfire. I started this code snippet from the Quick Reference to make a basic assessment.
 
 
As a result, here are a few questions that came up.  
  1. Why is there no reference to the namespace? Most IronPython that I’ve seen before always starts with the “import something” command.
  2. IronPython is an object-oriented programming language. How do I differentiate between developer named objects and syntax that was part of the code structure?
  3. I know references to the API should be capitalized. If that’s true why are “page” and “visual” in page.Visuals and visual.Title lowercase?
  4. I can see that “Pages” is a property in the Document class. “Visuals” and “Title” are properties in the Page class. “Title” is also a property in the Visual class. Thus, why does the code only call the Document class with “Document.Pages”? There is no reference to the Page class or the Visual class. (This question might be difficult if you aren’t familiar with traversing the Spotfire API).
So, let’s answer those questions.
 

Syntax & Structure

Why is there no reference to the namespace? Most of the classes in the Spotfire.Dxp.Application namespace load by default, so you don’t have to import. There are some exceptions like DocumentSaveSettings and DocumentOpenSettings. Thank you TIBCO support for that answer.
 
How do I differentiate between developer named objects and code structure? Simple. You test it. In the code snippet provided, you might wonder if “page” in “for page in Document.Pages:” is part of the code structure or an object. Replace “page” with any other word. If the code runs, it was a named object. If it fails, it’s part of the code structure.  It’s also lower case, so that is a hint.  All references to the API are in uppercase. 
 
Why are “page” and “visual” in “page.Visuals” and “visual.Title” not capitalized? They are objects, not references to the API. They could be any word.
 
I can see that “Pages” is a property in the Document class. “Visuals” and “Title” are properties in the Page class. “Title” is also a property in the Visual class. Thus, why does the code only call the Document class with “Document.Pages”?  To follow along with the answer to this, go to the Spotfire API reference. Open the Spotfire.Dxp.Application namespace (first namespace in the API). Click on the Document Class. The intro to the Document Class says this:
A document opened in a running instance of TIBCO Spotfire is referred to as an Analysis Document. The document not only contains a series of metadata information (see DocumentMetadata), but it also contains references to the data itself (see DataManager), and to various other components being part of the document, such as pages, filterings, bookmarks, etc. As soon as data has been opened in TIBCO Spotfire, an instance of this class can be accessed through the Document property of the AnalysisApplication. This is regardless of whether the data was opened through the user interface or programmatically. TIBCO Support
There’s a lot going on in that statement.  To explain it, go to the Spotfire.Dxp.Application namespace and click on the line below it that says AnalysisApplication Class.
You can see that Document is, in fact, a property of the namespace, as indicated in the description.  If you click on Document, it jumps to the Document class.  How does that answer the original question?  We’ll get there.  I just wanted to start with an explanation from the API and show you how navigating it works.  Our original question asked about the Document class, which is where you should be in the API reference if you are following along. So….
Pages is a property of the Document class. The code “for page in Document.Pages:” will get the pages of the document. More specifically, it is getting a collection of pages.  If you click on the Pages property in the Document class, it will take you to the screen show below.  There, you’ll note that the Type = PageCollection. The pages of the document are part of a PageCollection.  Now, click on PageCollection.
Now you jump down to the PageCollection class, which is below the Page Class and essentially because you are in the PageCollection Class you also have access to the properties of the Page class.  Title and Visuals are both properties of the Page class (as noted a while ago in the post).   The code only calls the Document class because you can access Title and Visuals by navigating the hierarchy as we just did.
As someone new to understanding the structure, I find this a bit confusing.  Because the API reference is organized like a tree, I expect to navigate it in a certain way, and that is clearly not how it should be navigated.  Hopefully, this will make more sense in another 10 hours or so.
 
 

Conclusion

Once I started searching for C# references, I found several very good tutorials worth sharing. To keep everything straight, I started saving tutorials and links by C# structure/syntax so I could have them handy and not have to search each time.  I’ve made the list available for you in this post. I will maintain this as I move thru learning. Please feel free to comment with other links, and I’ll add them.
 

Spotfire Version

All content created with Spotfire 7.12.

How to Add Lines to a Probit Plot with IronPython

Julie Sebby IronPython, Production Engineering, Reservoir Engineering, Spotfire, Spotfire Tricks 2 Comments

A few weeks ago, I wrote a post detailing how to create a multiple variable probit plot.  This post improved upon an older post on creating a single variable probit plot.  Part of those instructions included adding several “supplemental” lines via Lines and Curves like P10, P90, and the Median.  This is actually the most time-consuming part of the process.  Each line must be added one by one.

Today, while reviewing my instructions, I realized I had to do better. I know this can be done with IronPython! A quick Google search pulled up this TIBCO community post that I was able to use as a guide. I modified that script to work for my probit plot use case. Now, I have a piece of code that will add all of those lines and is easily modifiable and scalable.

The Code

Here is what the code looks like in my DXP.  I made the following modifications from TIBCO’s original:

  1. Changed BarChart to ScatterPlot to suit my visualization
  2. Modified the expressions from an average to the Percentile, P10, P90, and Median.
  3. Added code for vertical lines.

Code for Copy & Paste

from Spotfire.Dxp.Application.Visuals import *

scatterPlot = sp.As[ScatterPlot]()

#Add Horizontal Straight Line
horizontalLine1 = scatterPlot.FittingModels.AddHorizontalLine(‘P90([Y])’)
horizontalLine2 = scatterPlot.FittingModels.AddHorizontalLine(‘P10([Y])’)
horizontalLine3 = scatterPlot.FittingModels.AddHorizontalLine(‘Median([Y])’)
horizontalLine4 = scatterPlot.FittingModels.AddHorizontalLine(‘Percentile([Y],20)’)
horizontalLine5 = scatterPlot.FittingModels.AddHorizontalLine(‘Percentile([Y],30)’)
horizontalLine6 = scatterPlot.FittingModels.AddHorizontalLine(‘Percentile([Y],40)’)
horizontalLine7 = scatterPlot.FittingModels.AddHorizontalLine(‘Percentile([Y],60)’)
horizontalLine8 = scatterPlot.FittingModels.AddHorizontalLine(‘Percentile([Y],70)’)
horizontalLine9 = scatterPlot.FittingModels.AddHorizontalLine(‘Percentile([Y],80)’)

#Add Vertical Straight Line
verticalLine1 = scatterPlot.FittingModels.AddVerticalLine(’10’)
verticalLine2 = scatterPlot.FittingModels.AddVerticalLine(‘100’)
verticalLine3 = scatterPlot.FittingModels.AddVerticalLine(‘1000’)

Detailed Steps

  1. Add a Text Area to the page, right-click, select Edit HTML.
  2. Click the Add Action Control button.
  3. Name the button.
  4. Click the Script button.
  5. Click the New button.
  6. Name the script.
  7. Copy and paste code.  Modify to suit.
  8. Add a parameter called “sp” and connect it to your visualization.
  9. Run script to test. Click OK to close on script window.
  10. Modify the HTML as shown to hide the button.  You don’t want to click it again.

Caveats

  1. Once you run the script, it does not need to be run again.  When you clicked Run Script the first time, 13 lines were created.  Clicking again will create another 13 lines.  I made this mistake when testing.  Then, I had to delete a ton of lines one by one! (Please upvote my Idea to allow users to delete more than one line at a time).
  2. The script creates the lines, but you still have to edit them one by one.  This might also be possible with IronPython, but I haven’t dug that far yet.
  3. If you copy and paste from my code snippet above, you’ll need to replace the quotes.  Spotfire won’t recognize them correctly from copy and paste.

This should make setting up probit plots just a little bit faster.  You can also modify this code any time you want to add multiple lines to a different visualization or another probit plot.

Spotfire Version

Content created with Spotfire 7.12.

Spotfire Best Practices

Julie Sebby Spotfire, Spotfire Tricks Leave a comment

This post is a follow-up to the 7 part series I wrote on Decomposing Spotfire Projects.  The idea first came to me when I documented a large enterprise project. I didn’t build it. It wasn’t my baby, but it was important to the company. Management wanted it documented in case the author moved on. I also think about this subject every time I build a project for a user. What’s the best way to go about helping the user get a solid grasp on the project?  Is there a formulaic way to decompose a Spotfire project or at least a good order of operations.  Decomposing a project you didn’t build is often quite difficult and can seem like trying to assemble a jigsaw puzzle with 1,000 pieces.  Where do you even start?

So, I broke it up into 7 pieces.  As I wrote the series, I realized I was also writing a bit of a best practices guide or a do’s and don’ts guide for project development.  In an effort to help my readers, I thought a summary post would be helpful.  Thus, this post summarizes 3 – 5 pieces of development advice from each part in the series.  Keep in mind, I am not going to elaborate in great detail.  All the info you need is in each post.  Use this to jog your memory.

Data Tables & Data Sources

  1. Use a naming convention.
  2. Delete unused tables, connections, and data sources.
  3. Limit data as much as you can.  Less is more.

Data Functions

  1. Leave comments in your code to explain what the code is doing in case someone has to modify it later.
  2. Provide a general description of what the data function is doing in the description section.
  3. Include code that will install and attach any required packages.

Data Wrangling

  1. Clean up your joins.  Don’t join to the same table over and over again.  Delete duplicates and have one join and only one join from table to table.
  2. Architecture is the single most important component of any project.  Put time into planning it out.  Don’t just start building.
  3. Document your architecture choices.  Include information on not just what you did but why you did it.

Document Properties

  1. Delete unused document properties.
  2. Document what each document property should influence or control. l
  3. Use a naming convention.

Columns & Calculations

  1. Delete unused calculations or columns.
  2. Add a description to the calculation if it gets complex.
  3. Use naming conventions.
  4. Use Exclude columns transformations to exclude any columns that aren’t needed.

Text Areas & Scripts

  1. Don’t copy and paste from Word into a text area.  Just don’t do it.
  2. Include descriptions in scripts and data functions that explain where they are used or what they impact in the project.
  3. Use the text area to explain how the user should move thru a workflow or text area.
  4. Use HTML and CSS.  That’s not really a best practice per se, but learning even a little bit of HTML will make text areas so much better.

Visualizations & Data Limiting

  1. Remove the unnecessary.  Hide column selectors.  Only show what a user needs to see in a legend if a legend is even needed.
  2. Make data limiting as visible as possible with legend items or naming conventions.
  3. Minimize usage of custom expressions in visualization properties.
  4. Don’t put too many visualizations on a page.  Four is usually the limit.
  5. Articulate the business question you are trying to ask and answer with visualizations.

Conclusion

Now, I know some of these may seem super obvious, but I never cease to be amazed at what people create or leave behind.  Please take these to heart.  The developer that comes after you will be thankful.  

Moving Averages in Spotfire

Julie Sebby Spotfire 2 Comments

This week a user contacted me for assistance setting up a 3-month moving average calculation.  He’d already attempted it, but the result was wrong.  This is a common problem with the moving average function because of the way it’s built.  That’s not to say that it’s built wrong.  It’s just wasn’t built the way he wanted it to be built.

To explain, I will begin with an example of the Moving Average aggregation used on the y-axis of a visualization because it’s the easiest to understand.  Then, I’ll move on to a moving average calculation in a calculated column, which is a bit different.

Example of Moving Average Written on Y-Axis

The first screenshot below shows the configuration of the bar chart below it.  We are using the Moving Average aggregation and have chosen an Interval size of 3.  The actual expression makes use of the Last Periods node navigation method.  Note, Spotfire uses the term “Last Periods”.  A period is whatever you put on the x-axis, whether that be days, weeks, or months.  In our example, a period is a month.

In it’s simplest form, the expression sums up oil prod then averages it over the last three periods on the x-axis.   However, it’s a bit more complex because there is also an If statement after the average.  The If statement is counting periods on the x-axis.  A result is returned only when the count is 3.  If the result is not 3, null is returned.  That’s why the result is null until the visualization makes it past 3 periods of data.

 

When you are using a similar expression in a calculated column, it works a bit differently.

 

Example of Moving Average as Calculated Column

In this example, I am going to use a 3-day moving average rather than a 3-month moving average.  The premise is the same.    I calculated the 3-day moving average with this expression:

Avg([Gas Prod]) over (Intersect([Well Name], LastPeriods(3,[Prod Dt])))

That expression says — Average Gas Prod for each Well Name over the last three periods as defined by the Prod Dt.  In this case, Prod Dt is a day of the month.  Thus, the expression will average Gas prod for each Well Name for the last three days.  Here is the data:

As you can see, Spotfire is taking the first day of gas prod and dividing by one.  Then it adds day 1 and day 2 and divides by 2.  Thus, the first two days aren’t really a 3-day moving average.  This may work for you or it might not.  If you don’t want to see the average until 3-days have passed, simply add 2 more calculations.  One is a counter for the days.  The other is an if statement. This will return null until 3 days have passed, just as the previous example did.

Counter — Rank([Prod Dt],[Well Name])

3-day moving average — If([Count Days]<3,null,Avg([Gas Prod]) over (Intersect([Well Name],LastPeriods(3,[Prod Dt]))))

Hopefully, this clarifies how the function works and also how to use the Last Periods node navigation method.

Spotfire Version

Content created with Spotfire 7.12.