LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only

  • 1.  Picking random samples from a data set / group

    Employee
    Posted 03-09-2016 00:42

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: jma

    Hi,

    I'm quite new to Lavastorm but catching up quickly. However, I didn't find a solution to this from help or browsing the forum.

    I have a data set of customers with different characteristics and I want to create a sample customer set which includes 1-n random customers from each category or characteristic.

    I tried this with Agg Ex and random() but hit the problem that when the Group starts (firstInGroup), I don't have the full count of rows in the group. If I understood right, groupCount increases with every iteration and doesn't give the final count until lastInGroup is reached.

    So how I can emit 1-n random rows from each group?

    Also:

    emit *
    where groupCount == x

    gives an error, I wonder why?

    I'm using version 4.6, trying to upgrade in the next few days. Many thanks for your help!


  • 2.  RE: Picking random samples from a data set / group

    Employee
    Posted 03-26-2016 11:12

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: awilliams1024

    Hi,

    this node generates a sample of data for each of the specified groups. The sample size can be an absolute number of records or a percentage of the records in each group.

    Copy the code below and paste it into the BRE canvas.

    Regards,
    Adrian

    node:Sample_By_Group
bretype:core::Composite
editor:Label=Sample By Group
editor:sortkey=56f58f4b4a9d5582
editor:icon=sampler.ico
input:56f58f5f2f355a2a/=.40fe6c55598828e5
inputxy:0=90,100
inputrotation:0=0
output:56f6d19e5f5f3312/sample=
outlink:0=/=Output_the_Sampled_Records_for_Each_Group.4b4668e708143fb4
outputxy:0=980,100
outputrotation:0=0
inclockxy:Inclock=20,30
outclockxy:Outclock=400,30
prop:DefaultAction=Edit
editor:XY=340,180
editor:UsageDoc=Outputs a randomized subset of the data for the specified groups.  The sample can be an absolute number or a percentage of the input set.
editor:propdef=GroupBy|string|General||Not Blank
editor:propdef=SampleSize|string|General||Not Blank
editor:propdoc=GroupBy=The field or a comma-separated list of the fields to the group the samples by.
editor:propdoc=SampleSize=Size of the sample set desired. This can either be an absolute number or a percentage (specified by including the percentage symbol as a suffix").
node:Count_Number_of_Records_in_Each_Group
bretype:core::Agg Ex
editor:Label=Count Number of Records in Each Group
editor:sortkey=56f580c34a7548b7
input:@4b4668c040aa5a85/=Sort_using_the_GroupBy_fields.40fd2c746a2a3b47
editor:bend=0=360|100
editor:bend=0=360|170
output:@4b4668e708143fb4/=
prop:Script=<<EOX
if firstExec then {
	Sample__Size = "{{^SampleSize^}}".replace("%", "").double()
	sampleByPercentage = regexIsMatch("{{^SampleSize^}}", "%")
}

Gp__Cnt = groupCount()
emit referencedFields(1,{{^GroupBy^}}),Gp__Cnt
where lastInGroup

EOX
editor:XY=420,170
node:Bypass
bretype:::Bypass
editor:shadow=4b467f7e02db3a85
input:@4b467f7e129d45c1/=
input:@4b467f830ffe047b/=
output:@40fd2c7436717256/=
end:Bypass

node:Sort
bretype:::Sort
editor:shadow=4b467f8972dc33df
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

node:Agg
bretype:::Agg
editor:shadow=4b467f9b3d5028c0
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Agg

end:Count_Number_of_Records_in_Each_Group

node:Merge_in_Group_Counts
bretype:core::Lookup
editor:Label=Merge in Group Counts
editor:sortkey=56f5810c3da80bd9
input:@40fd2c746abc6dc7/=Sort_using_the_GroupBy_fields.40fd2c746a2a3b47
input:@40fd2c74486e4494/=Count_Number_of_Records_in_Each_Group.4b4668e708143fb4
editor:bend=1=490|170
editor:bend=1=490|110
output:@40fd2c7445835585/=
prop:InputKey=<<EOX
{{^GroupBy^}}
EOX
prop:LookupKey=<<EOX
{{^GroupBy^}}
EOX
editor:XY=560,100
end:Merge_in_Group_Counts

node:Add_Randomized_Score_to_Each_Record
bretype:core::Agg Ex
editor:Label=Add Randomized Score to Each Record
editor:sortkey=56f581ba500b7899
input:@4b4668c040aa5a85/=Merge_in_Group_Counts.40fd2c7445835585
output:@4b4668e708143fb4/=
prop:Script=<<EOX
if firstInGroup then {
	gp__Size = Gp__Cnt # Get the number of records in each group once (to improve performance)
}

Rec__Score = random(gp__Size) # Generate the random score for each record range = (0 - n-1)


emit * 
emit Rec__Score

EOX
editor:XY=660,100
node:Bypass
bretype:::Bypass
editor:shadow=4b467f7e02db3a85
input:@4b467f7e129d45c1/=
input:@4b467f830ffe047b/=
output:@40fd2c7436717256/=
end:Bypass

node:Sort
bretype:::Sort
editor:shadow=4b467f8972dc33df
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

node:Agg
bretype:::Agg
editor:shadow=4b467f9b3d5028c0
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Agg

end:Add_Randomized_Score_to_Each_Record

node:Sort_Records_in_Each_Group_by_the_Random_Score_Descending
bretype:core::Sort
editor:Label=Sort Records in Each Group by the Random Score (Descending)
editor:sortkey=56f58c935a4f5460
input:@40fd2c743ebf4304/=Add_Randomized_Score_to_Each_Record.4b4668e708143fb4
output:@40fd2c746a2a3b47/=
prop:CompareOrderExpr=<<EOX
{{^GroupBy^}},reverse(Rec__Score)
EOX
editor:XY=760,100
end:Sort_Records_in_Each_Group_by_the_Random_Score_Descending

node:Output_the_Sampled_Records_for_Each_Group
bretype:core::Agg Ex
editor:Label=Output the Sampled Records for Each Group
editor:sortkey=56f58d6309796c95
input:@4b4668c040aa5a85/=Sort_Records_in_Each_Group_by_the_Random_Score_Descending.40fd2c746a2a3b47
output:@4b4668e708143fb4/=
prop:Script=<<EOX
if firstInGroup then {
	Int__Cntr = 0
	sampleByPercentage = regexIsMatch("{{^SampleSize^}}", "%")
	Sample__Size = "{{^SampleSize^}}".replace("%", "").double()
}

recordShouldBeSampled = 
    if sampleByPercentage then
		Int__Cntr < floor(Sample__Size / 100.0 * Gp__Cnt)
	else
        Int__Cntr < Sample__Size


emit *
where recordShouldBeSampled
exclude Gp__Cnt,Rec__Score,Sample__Size

Int__Cntr = Int__Cntr + 1
EOX
editor:XY=890,100
node:Bypass
bretype:::Bypass
editor:shadow=4b467f7e02db3a85
input:@4b467f7e129d45c1/=
input:@4b467f830ffe047b/=
output:@40fd2c7436717256/=
end:Bypass

node:Sort
bretype:::Sort
editor:shadow=4b467f8972dc33df
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

node:Agg
bretype:::Agg
editor:shadow=4b467f9b3d5028c0
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Agg

end:Output_the_Sampled_Records_for_Each_Group

node:Sort_using_the_GroupBy_fields
bretype:core::Sort
editor:Label=Sort using the GroupBy field(s)
editor:sortkey=56f58f93224a18c8
input:@40fd2c743ebf4304/=^.56f58f5f2f355a2a
output:@40fd2c746a2a3b47/=
inclock:/=Validate_Parameters
prop:CompareOrderExpr=<<EOX
{{^GroupBy^}}
EOX
editor:XY=240,100
end:Sort_using_the_GroupBy_fields

node:Validate_Parameters
bretype:core::Filter
editor:Label=Validate Parameters
editor:sortkey=56f591b15abf02a5
input:@40fd2c74167f1ca2/=^.56f58f5f2f355a2a
output:@40fd2c7420761db6/=
prop:Script=<<EOX
# Check sample size is positive

if regexIsMatch("{{^SampleSize^}}","^-") then abort("The sample size must be positive.\n")

# Check for numeric sample size or a percentage

match =    regexIsMatch("{{^SampleSize^}}","^\\d+$") 
		or regexIsMatch("{{^SampleSize^}}","\\d+%$") 
		or regexIsMatch("{{^SampleSize^}}","\\d*\\.?\\d*%$")

if not(match) then abort("The sample size must be a number or a percentage.\n")

consume(1,true)

emit match

EOX
editor:XY=170,40
end:Validate_Parameters

end:Sample_By_Group


  • 3.  RE: Picking random samples from a data set / group

    Employee
    Posted 04-13-2016 02:14

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: jma

    Thank you! It works very well