Hello everybody!
I would like to know if it is possible to create Excel’s TRIMMEAN function in knime?
Returns the mean of the interior of a data set. TRIMMEAN calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set. You can use this function when you wish to exclude outlying data from your analysis.
You can find Mean under the “aggregation” tab in the GroupBy node. (Just don’t select any columns the “Groups” tab) As long as the column is numerical then it should be an option.
not sure if it completely mimics your TRIMMEAN behaviour, but just slicing records of the top and bottom of a table seems a bit of a blunt way to remove outliers.
what I typically do in that case is use the ‘robust statistics’ from the boxplot node
this will give me the cut-off values for 1st and 3rd quartiles (IQR) of the boxplot whiskers and just remove anything that doesn’t fit in between.
Just a proposal, see if it works for you!
Herman
You could always do it as a process as well. Calculate the number of values to be trimmed from top or bottom by percentage you are targeting, subtract the bottom trim amount from the total table length, then use both as flow variables to control a row filter node and trim top and bottom, then calculate mean via GroupBy node on the remaining values.
If you use it regularly then it would be a good idea to wrap it into a simple component.
Either flow variable controlled or not, the top K node can also be of use here.
Example on random number range from 0 - 100000. With the subsequent left anti join you can exclude the found records.
thx @ArjenEX ! didn’t know the ‘top K selector’ node existed!
I always used the ‘Sorter’ + ‘Sampling’ node combo to do this, but the top-k one combines them neatly
Herman
Indeed, it’s a quite unknown node but according to the description it’s the most efficient way (not considering the boxplot route which is also a nice way)
The node behaves the same as a combination of the Sorter node followed by a Row Filter that only keeps the first k rows of the table except for the order of the rows which depends on the Output order settings. Note, however, that the implementation of this node is more efficient then the node combination above.
Yes, it is possible to create Excel’s TRIMMEAN function in KNIME. I offer two possibilities below.
The TLDR:
I have included a Python implementation as there are many on the forum who are interested in developing nodes using Python and would like to see a comparison between the two approaches.
The basic test workflow, available on the hub, is shown below. An empty table is created with 3,000 rows. Then two random number assigners fill two columns with numbers between 0 and 1,000. This data is then fed to each node. The nodes have configuration to select the numeric columns for which TRIMMEAN is to be calculated and the percentage (as decimal fraction between 0. and 1.) of values to drop.
The KNIME component filters the input table and iterates over each column (blue nodes). The final metanode is to rename the RowID to TRIMMEAN.
The core of the workflow is:
quantile and the threshold is set by a flow variable.Details of the Calculate Upper and Lower Thresholds metanode.
The KNIME nodes in the Python version of the component are much simpler, consisting of configuration nodes and Python script node.
The Python script is below. The workflow is very similar to the KNIME workflow. When reading the script note the use of plurals. By convention, plural variable names are used in Python scripts to indicate multiple values (such as lists, dictionaries) whereas single values are not pluralised.
df.df, selecting the column in the iteration variable column, selecting the required values with filter and then calculating the mean .mean().trim_mean_values.The only twist on most Python scripts implemented in Python nodes is that after converting the dictionary of TRIMMEAN values for each column the dataFrame is transposed to match the same format output as the KNIME node - if the data is not transposed then their is one TRIMMEAN column with a row for each column value.
import knime.scripting.io as knio
import pandas as pd
# Import data as a Pandas dataFrame
df = knio.input_tables[0].to_pandas()
# Calculate upper quantiles and value of upper thresholds
upper_quantiles = 1 - (knio.flow_variables['percent-to-excude'] / 2)
upper_thresholds = df.quantile(q=upper_quantiles, interpolation="linear")
# Calculate lower quantiles and value of lower thresholds
lower_quantiles = knio.flow_variables['percent-to-excude'] / 2
lower_thresholds = df.quantiles(q=lower_quantiles, interpolation="linear")
# Iterate over columns, filter data and calculate mean.
trim_mean_values = {}
for column in df.columns:
lower_threshold = lower_thresholds[column]
upper_threshold = upper_thresholds[column]
filter = (df[column] > lower_threshold) & (df[column] < upper_threshold)
trim_mean_values[column] = df[column][filter].mean()
# Create output dataFrame and transpose to match KNIME implementation
output_table = pd.DataFrame({"TRIMEMEAN" : trim_mean_values}).transpose()
# Export TRIMMEAN values on output port
knio.output_tables[0] = knio.Table.from_pandas(output_table)
Hope that helps
when trying to open the workflow it shows this error, can you tell me how to install the extension?
KNIME has identified that it needs to download additional components. If you answer yes, it will download those components and install them auto-magically. After it has downloaded the components it will prompt to restart KNIME so they become available, at which point you can load the workflow and it should run
when confirming an error message appears, I believe it could be a blockade from my company or lack of configuration, is there another way to download? or solve?
If you click on the Details>> button it will provide more information which might explain what is happening.
If you are working on a corporate network with a firewall then you may need to discuss with them whether they can proxy the requests to KNIME servers. I can’t recommend anything which circumvents your organisation’s corporate IT security policies.
You may want to check you KNIME configuration. If you open the File->preferences->Install/Update->Available Software Sites. Then you should have the KNIME Analytics Platform and Trusted Community Extensions selected (I have a couple of other sites selected which you might not see).
If you open File->Install KNIME extensions and type Data Generation into the search box you should see the KNIME Data Generation package in the list. If you select that package and click next/finish then it should install the necessary nodes.
Otherwise, turn everything off and back on again. Make a cup of tea. Try again after ten minutes before you call the IT department 
.
I was able to install this extension, thank you very much!!
However, I would like to know if you have the possibility of using a column as a reference, like the example in the print below, I have a code and my sale, I would like to perform a Trimmean for each individual code
You can put the TRIMMEAN in a group loop and iterate over the codes. You may need a constant value node to add a column with the code value so that you can associate the mean with the code at the end of the loop.
DiaAzul
Modified workflow attached:
Trimmean3.knwf (48.6 KB)
Changes:
include box. The categories from this column will determine the grouping of the rows. Note that I have also selected Enforce Inclusion. It is worth understanding into which category (Exclude or Include) KNIME adds new columns if they are added to your workflow. If you select Enforce Inclusion then any new columns are added to the Exclude list. If you Enforce exclusion, then new columns are added to the include list. If, when you are creating bigger workflows you do not want to add new grouping categories then you will need to select Enforce inclusion to ensure that they are always excluded. Many is the time that adding additional columns (by design, or accident) has caused problems when those columns are added in the wrong category.
I set the Constant Value Column to append a new column (COD) and set the value of that column to the variable GroupIdentifier (if you push the V button to the left of the line that starts with the Value settings dialogue you will get a modal dialogue with a drop down to select the variable. The warning at the bottom shows that the value is set by a variable.
I also added a string manipulation (Variable) node because KNIME (rather unhelpfully) encloses the group name (GoupIdentifer) variable in percentage symbols (grrrr!).
Loop End. What you had was fine. I have a preference for Generating new row IDs and unchecking the Add iteration column option so changed those options.
I also changed the data type of the COD column from Double to Integer (formats nicer without the decimals), you could also change it to string (depends upon the nature of your codes). To change the type of the column open the table, double-click on the column heading. A modal dialogue box will open and you can select the data type of your column.
Hope that helps
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
