How to add calculated columns with IronPython Row by Row

[Tze Yik Lim]

I can find some online resource how to add calculated column with IronPython with expression like example below.

from Spotfire.Dxp.Data import CalculatedColumn

cols = Document.Data.Tables["TIBER"].Columns

cols.AddCalculatedColumn("NEWCOLUMN","[oldcolumn] * 3.289");But my situation is slightly different. I need different expression for different row. I had columns name "Site" & "device". I already sort the "device" & "Site"column ascending. I need to calculate new columns "Bunch" based on data from column "Site" & "device". The algorithm would be loop row by row for column "Site", check "Site" n+1 & n. If "Site" n+1 < "Site" n, then bunch+1. Can anyone advise how i can achieve this

 

 

 

 

Site

device

Bunch

 

 

1

1

1

 

 

2

2

1

 

 

3

3

1

 

 

4

4

1

 

 

1

5

2

 

 

2

6

2

 

 

3

7

2

 

 

1

8

3

 

 

2

9

3

 

 

3

10

3

 

 

4

11

3

 

 

5

12

3

[Tyger Guzman 2]

Your algorithm is not easy to understand. Could you rewrite it or display as python code you'll be using  

[Tyger Guzman 2]

You can also look into the Over Function to acheive getting the previous or next row :

 

https://community.spotfire.com/questions/how-bring-next-row-value-current-row

 

create an extra column for RowID

RowId() as [MyRowID]Then use a second Calulated column:

If(First([site]) OVER (Next([MyRowID]))>[site],[bunch] + 1,[bunch])

[Tyger Guzman 2]

Can you post your input and expected output. Your algorithm description is fuzzy

[Tyger Guzman 2]

Also the the two answers I posted are separate and no need to combine them. 

 

The script works to run a complicated python function line by line. 

 

The expression was if I misuderstood your algorithm to be wanting to get the next row value. 

[Tze Yik Lim]

Hi tyger, 

 

Thanks for fast response. Here is my algorithm. I think your last python code will rewrite another new data table which is not my intention. I want to create a new column with original data table. 

 

1. First sort the table by device first, site second by ascending order (Must do - done by original code)

 

2. Check column "site" row by row in that sequence. If the next "site" value < current "site" value, then new calculate column "Bunch" need to increment by 1. First group of "Bunch" number start from 1. So, if the 1st next "site" value < current "site" value, the "Bunch" will be 2 and so on. 

[Tyger Guzman 2]

There is a method to create a temporary table with the function results then join it back to your orginal table (you cannot add or update values in data table)

You can find those suggestions here:https://stackoverflow.com/questions/34068783/spotfire-add-column-from-python-list

The method I purposed to create a new data table is attached here. I suggest creating the new table and use the new table in your visuals and analysis. Treating the python script as a transformation and only being concerned that you got your resulting data table.

[Tze Yik Lim]

hi tyger, 

 

Thanks for your example. The original algorithm given to you still not fully correct. I change a little bit. I did work on your dxp file. But when i try on my dxp file, it still not correct. I guess it is due to my original data table is not sort correctly even i use the visualization. Can you please further advise I think i am very close to solution. attached my dxp file.