Jump to content
We've recently updated our Privacy Statement, available here. ×

How to split a data table into multiple data tables based on column value


Chad Holland

Recommended Posts

I have seen a few things similar, but I am not an R/TERR coder, so I am not real sure what I need to change in the code. For example:

https://community.spotfire.com/questions/splitting-data-table-test-train-an...

What I am trying to do is simply splt my singular data table into seperate data tables based on a "Catagory" column. In this case, I have several type curves that I want to plot in lines and curves, but I need them to be seperated into actual seperate tables for my particula case to work properly in spotfire.

There are just 4 columns:Year, Normalized Month, Type Curve Catagory (ie. cum oil, cum boe, net boe, etc.), and a value. I need these plotted astargets against my various wells well tests as they come online. Note, having them in the same table doesn;t work, due to the need for filtering and other marking functionality. Lines and curves is best, as these targets are static for the year.

Also, I realize I can split these out in excel manually, and load them independently, but this is not the only use case I have for this example, so figuring it out in this more simplistic case, would be beeficial.

Link to comment
Share on other sites

You cannot really do it in R/TERR as you would need to know in advance what and how many output tables you are going to generate, which in this case you do not know. You could probably do it in IronPython. It also depends on what environment you are using (on your desktop, on the web, on the cloud) as the options differ.

 

Somehow though I think you'd be better off trying to keep everything in one table - it would be so much less pain. Can you elaborate on why this filtering and marking functionality would not work on a single table Also uploading a representative example dxp would help.

 

Gaia

Link to comment
Share on other sites

Hi Chad,

Please try this IronPython script. You'll need to change table_name and column_name to match your source data table.

 

from Spotfire.Dxp.Data import DataTable

from Spotfire.Dxp.Data import RowSelection

from Spotfire.Dxp.Data.Import import DataTableDataSource

from Spotfire.Dxp.Data import DataValueCursor

from Spotfire.Dxp.Data import IndexSet

table_name = 'spotfire_test'

column_name = 'SIM'

table = Document.Data.Tables[table_name]

unique_rows = table.Select("Rank(RowId(),'asc',[%s]) = 1" % column_name)

column = table.Columns[column_name]

cursor = DataValueCursor.Create(column)

unique_values = []

for row in table.GetDistinctRows(unique_rows.AsIndexSet(),cursor):

if cursor.CurrentDataValue.HasValidValue:

unique_values.append(cursor.CurrentDataValue.ValidValue)

for value in unique_values:

rows = table.Select("[%s] != %s" % (column_name,value))

new_table_name = Document.Data.Tables.CreateUniqueName("table_%s" % value)

if not rows.AsIndexSet().IsEmpty:

print("creating " + new_table_name)

new_table = Document.Data.Tables.Add(new_table_name, DataTableDataSource(table))

new_table.RemoveRows(rows)

Link to comment
Share on other sites

Gaia,

 

Thanks for the comment. Bascially, when you filter in Spotfire, it eliminates from the visualizations dataset, any data filtered out. This goes not only for the main visualizations, but also for the lines&curves part of a visualization. I need those lines and curves to stay static. If the user accidently filters out my lines and curves values, then the static line will disappear.

 

Also, with lines & curves, when you make a line from data table, you can only selct your columns you cannot use a custom expression to limit your data. So if you have several differnt independent lines you need to plot in the same table, spot fire just sees one continuous dataset and will try to draw a line between all the points. I have not found a way to break the lines on a parameter, like line name.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...