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

[Chad Holland]

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.

[Gaia Paolini]

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

[Matthew Stahl]

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)

[Chad Holland]

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.