Looping through an example to generate more examples

amiller17 · March 2018

My data is confidential I cannot post it.

Here is an example of what my data looks like:

I would like to transform it into:

Please help me do this.

Thomas_Ott · March 2018

@amiller17 its hard for me to load this in at the moment but I did take a look at the the data structure. If every 11 rows/examples it changes in column lenght, you will need to do an interative loop with a Filter Range to first select out those rows. THEN i would use a Select Attributes operator to select the missing columns, make it invert and toss them out. Then use the Windowing operator set at 11 to select Data and Att0 through Att9. I think you'll need to change the series ecoding to 'attributes'. Do this all in side the loop and then put a Merge/Append operator outside the loop.

amiller17 · March 2018

I've hacked together a solution using remember and recall. It's not pretty, but it works. I've attached it here for your perusal:

Thomas_Ott · March 2018

@amiller17 I would try the Windowing operator from the Value Series extension. Set the Window to 11.

amiller17 · March 2018

I have tried that, but the operator creates a matrix of missing values (?) which is not very helpful.

sgenzer · March 2018

hi @amiller17 welcome to the community. I read your post several times but could not get my head around what your example was. Perhaps put your "sample" in a excel sheet with "before" and "after" tabs?

Scott

amiller17 · March 2018

I do not know how to edit my original post but here is an excel spreadsheet with what I am looking to do. Note the tabs before and after. Thank you very much for your assistance. Notes about the example in the spreadsheet:

Date and attr1-9 are numbers, ? is a missing value. Every 11 cells is a new example, and I have 49 rows like this one of variable length (from 11*4(44) to 11*300(3,300) cells in length) to process.

amiller17 · March 2018

I do not know how to edit my original post but here is an excel spreadsheet with what I am looking to do. Note the tabs before and after. Thank you very much for your assistance. Notes about the example in the spreadsheet:

Date and attr1-9 are numbers, ? is a missing value. Every 11 cells is a new example, and I have 49 rows like this one of variable length (from 11*4(44) to 11*300(3,300) cells in length) to process.

I never attached the file, my bad: https://docs.google.com/spreadsheets/d/1PSKu7wZ4gXnqQaGXGUiCKkQepPFHXX3d4yFcGHI4qZw/edit?usp=sharing

I tried sharing the .xlsx but the forums wouldn't let me upload the data.

Thomas_Ott · March 2018

@amiller17 its hard for me to load this in at the moment but I did take a look at the the data structure. If every 11 rows/examples it changes in column lenght, you will need to do an interative loop with a Filter Range to first select out those rows. THEN i would use a Select Attributes operator to select the missing columns, make it invert and toss them out. Then use the Windowing operator set at 11 to select Data and Att0 through Att9. I think you'll need to change the series ecoding to 'attributes'. Do this all in side the loop and then put a Merge/Append operator outside the loop.

amiller17 · March 2018

Is there a way to receive the leftovers from a filter-range as an output?

sgenzer · March 2018

ok thanks @amiller17. This looks like a Scott ETL puzzle. @Thomas_Ott's solution will definitely work but I wonder if there's a more elegant solution...stay tuned.

Scott

amiller17 · March 2018

I've hacked together a solution using remember and recall. It's not pretty, but it works. I've attached it here for your perusal:

sgenzer · March 2018

haha yep @amiller17 that will work too. I tried to find something elegant with pivot / depivot plus transpose but could not get it. Looks so simple....

Glad you found a working hack.

Scott

Looping through an example to generate more examples

Best Answers

Answers

Categories