Weighted Mode

[melissapluke]

Hi all,

I'm trying to get a Mode for Price based on the total occurrences per Procedure. Item is not taken into consideration, but does come in the base data so I've included it here. I can do this in a script or in the UI. I'd rather take care of it in the script however.

table1:
LOAD * INLINE [

Item, Procedure, Occurrence, Price

A, 7214F, 500, $333

B, 7214F, 1000, $250

C, 7214F, 250, $333

A, 7213F, 500, $600

B, 7213F, 100, $750

C, 7213F, 250, $203

A, 7212F, 500, $433

B, 7212F, 1000, $250

C, 7212F, 2150, $433
];

The results I'd want are:

Procedure, ModePrice

7214F, $250

7213F, $600

7212F, $433

Any ideas?

Thanks!

[sunny_talwar]

May be this

table1:
LOAD * INLINE [
    Item, Procedure, Occurrence, Price
    A, 7214F, 500, $333
    B, 7214F, 1000, $250
    C, 7214F, 250, $333
    A, 7213F, 500, $600
    B, 7213F, 100, $750
    C, 7213F, 250, $203
    A, 7212F, 500, $433
    B, 7212F, 1000, $250
    C, 7212F, 2150, $433
];

Left Join (table1)
LOAD Procedure,
	 Mode(Price) as ModePrice
Resident table1
Group By Procedure;

But that gives me this

For 7214F, I see the Price $333 repeating twice. Also, I don't really see anything repeating for 7213F, so not sure how would you pick 600? Are you looking for Median instead?

[melissapluke]

Hi,

This is just a sample of the data. The issue i'm trying to solve is the $333 repeating itself. There will be lots of rows like this.  I'd like to pick the $250 instead because it has the highest number of occurrences per price, regardless of the "item" field.  Sorry if that wasn't clear before.  For procedure 7213F, I'd like to pick 600 because it has the highest number occurrences per price.

[sunny_talwar · Accepted solution]

That make sense now.. try this

table1:
LOAD * INLINE [
    Item, Procedure, Occurrence, Price
    A, 7214F, 500, $333
    B, 7214F, 1000, $250
    C, 7214F, 250, $333
    A, 7213F, 500, $600
    B, 7213F, 100, $750
    C, 7213F, 250, $203
    A, 7212F, 500, $433
    B, 7212F, 1000, $250
    C, 7212F, 2150, $433
];

Left Join (table1)
LOAD Procedure,
	 FirstSortedValue(Price, -Occurrence) as ModePrice
Resident table1
Group By Procedure;

[melissapluke]

Thanks, Sunny! That is what I was looking for!