Looking For Matches Between A List And A Table

[Cnotey 3]

Hi,

 

I'm having trouble wrapping my head around something and need some help.

 

I'm trying to go through a table, and find the duplicate values of one column, and average the values from the column to the right for all of the duplicates.

 

For instance:

 

Column 1 | Column 2

 

9/28/2015| 100

9/28/2015| 86

9/28/2015| 76

9/29/2015| 50

9/29/2015| 15

9/30/2015| 45

 

 

I want to cycle through Column #1, and find all of the rows that have the same date, and average the values from Column #2

 

This is the result I want:

 

Column 1 | Column 2

9/28/2015| 83.55

9/29/2015| 32

9/30/2015| 45

 

 

Anyone have an easy way to do this?  The only solution I could come up with creates like 36 lists and cycling through comparing each of the lists.

 

Is there a way to do this with just one or two lists?

 

Thanks,

 

Cnotey

[deliter 203]

not very good code but it works,has a few lists inside the function but they are local

add list to list(%dates,$list from text("9/28/2015
9/28/2015
9/28/2015
9/29/2015
9/29/2015
9/30/2015",$new line),"Don\'t Delete","Global")
add list to list(%numbers,$list from text("100
86
76
50
15
45",$new line),"Don\'t Delete","Global")
add list to table as column(&myTable,1,1,%dates)
add list to table as column(&myTable,1,2,%numbers)
define tableAverage {
    add list to list(%mydateCheck,%dates,"Delete","Local")
    plugin command("TableCommands.dll", "add table to table", &results, &myTable)
    set(#position,0,"Local")
    loop($list total(%mydateCheck)) {
        set(#total,0,"Local")
        set(#loop Count,0,"Local")
        loop while($comparison($plugin function("TableCommands.dll", "$table search", &results, $list item(%mydateCheck,#position), "Row Index"),">","-1")) {
            set(#total,$add(#total,$table cell(&results,$plugin function("TableCommands.dll", "$table search", &results, $list item(%mydateCheck,#position), "Row Index"),2)),"Local")
            set table cell(&results,$plugin function("TableCommands.dll", "$table search", &results, $list item(%mydateCheck,#position), "Row Index"),2,"")
            set table cell(&results,$plugin function("TableCommands.dll", "$table search", &results, $list item(%mydateCheck,#position), "Row Index"),1,"")
            increment(#loop Count)
        }
        add item to list(%averages,$divide(#total,#loop Count),"Don\'t Delete","Local")
        add item to list(%Duplicatedates,$list item(%mydateCheck,#position),"Don\'t Delete","Local")
        increment(#position)
    }
    clear table(&results)
    add list to table as column(&results,1,1,%Duplicatedates)
    add list to table as column(&results,1,2,%averages)
}
tableAverage()

[pash 504]

My plugin (Oh. sorry is no average)

plugin command("Advanced Data Text File.dll", "create table from string", "9/28/2015|100
9/28/2015|86
9/28/2015|76
9/29/2015|50
9/29/2015|15
9/30/2015|45", "\\n", "\\|", &myTable)
plugin command("Advanced Data Text File.dll", "remove table duplicate", &myTable, "Col", 0)

Edited September 27, 2015 by pash

[Cnotey 3]

Hah, holy shit I guess I never knew about the $tablesearch command.  That one must be new to Ubot 5!!!

 

Thanks Deliter, saved me again.

 

I ended up figuring it out on my own before I looked at the responses here, but my solution is much sloppier than what you posted.

 

 

*******EDIT*************************************************************************************

 

Actually table search did not do what I needed exactly.  I need to return the values, not the row/column index.

 

This code runs through the first list with deleted dupes, and compares to the list without deleted dupes.  Then adds the value of the date into a list.  Then at the end of each row check, I set the list as a row in the final table.

 

I am still working on the code to average the values in the row if there is more than one value, but I'm on track.

 

Here is what I ended up doing.  Anyone have a simpler solution?

 

Sorry my variable/list/table naming is so sloppy.  I know what they mean! haha.

define Eval Article Data {
    add list to list(%ArticleCleanDates,$plugin function("TableCommands.dll", "$list from table", &SentimentTable, "Column", 1),"Don\'t Delete","Global")
    add list to list(%ArticleDatesNoDup,$plugin function("TableCommands.dll", "$list from table", &SentimentTable, "Column", 1),"Delete","Global")
    add list to list(%ArticleCleanSentiment,$plugin function("TableCommands.dll", "$list from table", &SentimentTable, "Column", 2),"Delete","Global")
    add list to table as column(&SentimentCleaner,0,0,%ArticleDatesNoDup)
    set(#CleanRow,0,"Global")
    set(#UniqueRow,0,"Global")
    set(#UniqueTableColumn,1,"Global")
    loop($list total(%ArticleDatesNoDup)) {
        clear list(%DateHolder)
        loop($list total(%ArticleURLs)) {
            if($comparison($table cell(&SentimentCleaner,#UniqueRow,0),"= Equals",$table cell(&SentimentTable,#CleanRow,1))) {
                then {
                    set(#DateMatch,$table cell(&SentimentTable,#CleanRow,2),"Global")
                    if($comparison(#DateMatch,"= Equals",$nothing)) {
                        then {
                        }
                        else {
                            add item to list(%DateHolder,#DateMatch,"Don\'t Delete","Global")
                        }
                    }
                }
                else {
                }
            }
            increment(#CleanRow)
        }
        set(#CleanRow,0,"Global")
        if($comparison($list total(%DateHolder),">= Greater than or equal to",1)) {
            then {
                add list to table as row(&SentimentCleaner,#UniqueRow,1,%DateHolder)
            }
            else {
            }
        }
        increment(#UniqueTableColumn)
        increment(#UniqueRow)
    }
}

Thanks,

Cnotey

[Cnotey 3]

Here's my output so far.  I still need to average the values, but I think I am going the right direction.

 

http://i.imgur.com/aeftnbl.png

[deliter 203]

did you actually run the code exactly as above?

it returns exactly what you asked for in your post,myresults table is 

 

Column 1 | Column 2

9/28/2015| 83.55

9/29/2015| 32

9/30/2015| 45

[Cnotey 3]

Ah I didn't notice that.  Yes it does work!

 

Thank you so much!