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.  Count data population

    Employee
    Posted 08-30-2016 10:01

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

    Originally posted by: islandz

    I am looking to count the population of data in each field in an input file. I have a file that is 3.6M records and 118 fields. I need an output that has the count of non-null data points in each of the 118 fields.

    I am trying the data analyzer, but it has been running for a couple of hours with no progress.

    Any assistance would be much appreciated.

    Thank you


  • 2.  RE: Count data population

    Employee
    Posted 09-01-2016 08:25

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

    Originally posted by: awilliams1024

    Hi,

    Assuming the distribution of NULL values is sparse in the data, maybe you could turn the problem on its head and:
    - Identify the records that contain one or more NULL values
    - Use the Data Analyzer to count the NULL values in each of those records
    - Calculate the number of non-NULL values in each field.


    node:Count_All_Records
bretype:core::Agg
editor:Label=Count All Records
editor:sortkey=57c8455646ac1b20
input:@40fd2c7427456e5b/=Static_Data.40fe6c55598828e5
editor:bend=0=330|130
editor:bend=0=330|250
output:@40fd2c744c862db0/=
prop:GroupBy=<<EOX
true
EOX
prop:Script=<<EOX
TotalNumRecords = groupCount()

emit TotalNumRecords
where lastInGroup

EOX
editor:XY=390,250
end:Count_All_Records

node:Output_Records_Containing_NULL
bretype:core::Filter
editor:Label=Output Records Containing NULL
editor:sortkey=57c8401a765b74d9
input:@40fd2c74167f1ca2/=Static_Data.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
if firstExec then {
	FldList = inputFields(1)
	NumFlds = numFields(1)
}

FoundNull = false
i = 0

while i < NumFlds {
	if(field(FldList.getItem(i)).isNull()) then {
        FoundNull = true
        break # exit the if loop immediately
    }
    i = i + 1
}

emit * where FoundNull

EOX
editor:XY=390,130
end:Output_Records_Containing_NULL

node:Merge_and_Output_Required_Fields
bretype:core::Lookup
editor:Label=Merge and Output Required Fields
editor:sortkey=57c8459644b42bcb
input:@40fd2c746abc6dc7/=Data_Analyzer.4485e7a8783f0c7b
input:@40fd2c74486e4494/=Count_All_Records.40fd2c744c862db0
editor:bend=1=600|250
editor:bend=1=600|140
output:@40fd2c7445835585/=
prop:InputKey=<<EOX
true
EOX
prop:LookupKey=<<EOX
true
EOX
prop:Script=<<EOX
NumberNonNull = 2:TotalNumRecords - '1:Null Count'
emit '1:Field Name', '1:Null Count'
emit 2:TotalNumRecords
emit NumberNonNull
exclude referencedFields(2,{{^LookupKey^}})

#where matchIsFound

# Note: If you want to avoid collisions with fields on the inputs
# prefix the second emit statement with the keyword "default"
# or "override".


EOX
editor:XY=680,130
end:Merge_and_Output_Required_Fields

node:Data_Analyzer
bretype:core::Data Analyzer
editor:sortkey=57c843b757e5744a
input:@4485e77f45b11f00/=Output_Records_Containing_NULL.40fd2c7420761db6
output:@4485e7a46b2e0308/=
output:@4485e7a8783f0c7b/=
output:@4485e7d2359004a0/=
editor:XY=530,130
node:Convert_Analyzed_Data
bretype:::Convert Analyzed Data
editor:shadow=45707e621cf53f4e
input:@448036dc3f874917/=
input:@448036de4a0163a2/=
output:@448036e822166362/=
end:Convert_Analyzed_Data

node:Analyze_Data
bretype:::Analyze Data
editor:shadow=45707e623ee06853
input:@4665b06168a410a5/=
output:@4665b05e24fd0056/=
output:@44760e944dc14b66/=
output:@447742cf261c5399/=
end:Analyze_Data

node:Bypass
bretype:::Bypass
editor:shadow=45707e6245ea298b
input:@4485e83011372f7e/=
input:@4485e83264650bad/=
output:@40fd2c7436717256/=
end:Bypass

node:Sample
bretype:::Sample
editor:shadow=45707e625df65d94
input:@42f2fda164ca714b/=
output:@42f303b10a020091/=
node:crop_sample
bretype:::crop sample
editor:shadow=45707e62708e1af7
input:@40fd2c74167f1ca2/=
output:@40fd2c7420761db6/=
end:crop_sample

node:InternalRandomId
bretype:::InternalRandomId
editor:shadow=45707e6268437045
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:InternalRandomId

node:add_InternalRecordCount_InternalRandomId
bretype:::add InternalRecordCount, InternalRandomId
editor:shadow=45707e6217fb744e
input:@40fd2c746abc6dc7/=
input:@40fd2c74486e4494/=
output:@40fd2c7445835585/=
end:add_InternalRecordCount_InternalRandomId

node:count_records
bretype:::count records
editor:shadow=45707e6233f01425
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:count_records

end:Sample

end:Data_Analyzer

node:Static_Data
bretype:core::Static Data
editor:sortkey=57c83feb56c05123
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
color:string, id:int,type:string,rand:int,junk:string
Red,1,primary,27959,okay
Green,2,NULL,7138,"""Let's Go"", he said"
Blue,3,primary,15790,maybe
Yellow,4,secondary,17809,nope
Cyan,5,secondary,16901,Not NULL
Magenta,6,secondary,15488,null
Orange,7,NULL,14584,NULL
Chartreuse,8,tertiary,3465,True
Aquamarine,9,tertiary,18650,1234
NULL,10,tertiary,26377,23.4
Violet,11,tertiary,27272,06/25/70
Fuchsia,12,tertiary,6803,"June 25, 1970"
Grue,13,fictional,7373,2:46 PM
Bleen,14,fictional,22852,1.00001
Octarine,15,fictional,11228,3.1415
Garrow,16,fictional,26567,1.2.3.4.5
Gendale,17,fictional,30176,
Hooloovoo,18,fictional,12092,WieRD CaSED stRINg
Fire,19,NULL,12345,14:46
Ice,20,NULL,12345,2:46 PM 06/25/1970
EOX
editor:XY=270,130
end:Static_Data


  • 3.  RE: Count data population

    Employee
    Posted 09-01-2016 08:34

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

    Originally posted by: awilliams1024

    BTW, the Data Analyzer also has the option to not perform the data type conversion operations on the input data set. This may improve the performance if you only need to access the output of the analysis pin.
    See the 'Perform Conversion' property on the node.

    Regards,
    Adrian