Question

Statistical Calculator

  • 11 October 2019
  • 7 replies
  • 32 views
F
Badge +1

Dear FME Forum,

I am relatively new to the Software, and just wondered if it is possible to count "Null" or "Missing" values with the Statistical Calculator?

With the statistical calculator it is possible to count the Total amount of Values for an attribute but not how many that are empty. The Null Mapper or Tester is great, but I am looking for a Transformer for the whole range of attributes.

Should I use a Python Caller for this or is there an existing transformer?

 

My Question stems from PowerQuery

In Microsoft`s PowerQuery it is possible to get an overview of how complete your data is, I am looking for the same result (please see Screenshot below)

 

Here is a link to the PowerQuery Table Profile:

https://docs.microsoft.com/en-us/powerquery-m/table-profile

 

Thanks in advance

 

Fred

7 replies

philippeb
Badge +2

Try to use the parameter Histogram, could do what you're trying to do.

http://docs.safe.com/fme/2019.0/html/FME_Desktop_Documentation/FME_Transformers/Transformers/statisticscalculator.htm

ebygomm
Rank
Userlevel 1
Badge +10

Are you looking to find out how many features contain null or missing data in any attribute or a breakdown for each attribute?

takashi
Rank
Userlevel 2
Badge +17

Hi @freddy17, if only numeric values are valid, you can use the StatisticsCalculator to count the number of numeric values and compute the number of invalid values by subtracting the number of numeric values from the total number of features. See the Numeric Count Attribute parameter.

F
Badge +1

I have attached a FME File + Excel file of what I am trying to achieve. @takashi, as the Values I am looking for are mostly strings. The numeric count wont work.

 

@egomm. I am trying to calculate a percentage of values per attribute, so yes the breakdown for each attribute.

 

I have attached a simple example, but it seems very convoluted. In my real dataset I have about 300 Attributes. Fruit Null Tester.fmw

Fruit Test.xlsx

 

I will read up on the Histogram a bit.

Thanks

ebygomm
Rank
Userlevel 1
Badge +10

My cheats way if i want to avoid python and just need to count null/empty/missing (as opposed to identifying) is to use an arithmetic editor to calculate string length and turn any value of zero to 1 and any value greater than zero to 0 and then use a statistics calculator to sum all the 1's

null_fruit.fmwt

F
Badge +1

My cheats way if i want to avoid python and just need to count null/empty/missing (as opposed to identifying) is to use an arithmetic editor to calculate string length and turn any value of zero to 1 and any value greater than zero to 0 and then use a statistics calculator to sum all the 1's

null_fruit.fmwt

Thanks, that has simplified things a bit. I think I will try with Python as another approach, as I have a few hundred Attributes.

ebygomm
Rank
Userlevel 1
Badge +10

An alternative approaching using the attribute validator - could be slow if you have lots of attributes and lots missing/empty/null because of exploding the list

Reply


Community Stats

28,662
Posts
109,325
Replies
36,528
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.