Creating Excel Pivot Tables

KNIME Analytics Platform
,
1

I am currently working on replicating common Excel Spreadsheet functions using KNIME and Python, I want to share what I’ve made so far: How to Create Excel Pivots in KNIME. I’ll provide more details later. For now you may check my workflow in the community hub. This will be a series I’m planning on for Spreadsheet Automation.

3 Likes
2

Here is the link of my full walkthrough of the workflow, enjoy!

Excerpt
Description: Using KNIME Analytics Platform to replicate and automate one of the commonly used Excel Functions – Pivot Tables.

Pivot Table is one of the most commonly used function in Excel, it helps users summarize data and manipulate it in order to gain insights from it. There are couple of ways to automate Pivot Tables in Excel, one is using VBA and Python, both can replicate the Pivot Table itself, the downside is both require backgrounds in programming to use it effectively. The other option is to use Low-code Analytics Platform like KNIME which is beginner friendly tool use for automation, visualization, data science, machine learning, and more, but the downside is you are only replicating the “Format” of a Pivot Table and not the actual Excel Pivot itself. So I have created a KNIME Workflow that can create Actual Excel Pivot Table in the spreadsheet with all of its functionality such as:

  1. Selecting Table/Range for its Source
  2. Inserting the Pivot Table in any cell or sheet in the workbook
  3. Dragging Field Items into its respective Area (Filter, Column, Row, Value)
  4. Different Calculation Types (Sum, Count, Average, etc.)
  5. Enabling/disabling Subtotals, Repeat Label Items, Grand Totals for Rows and Columns
  6. Layout Types (Classic, Compact, Outline, Tabular)
  7. Style Types

Users can reuse the workflow or its nodes in their respective projects without any Python background, but they should take note of the requirements that need to be installed first before they can run the KNIME workflow.

Requirements:

How to Set Up the KNIME Python Extension

Note: You may require to install KNIME extensions to use some of the nodes in the workflow.

Python Libraries Required:

To Install Python Libraries, Go to Start Menu, Select Anaconda3 Folder, Select Anaconda Prompt (Anaconda3), Type: pip install name_of_python_library

Note: You may require additional modules if the listed libraries have pre-requisite modules needed to install first in order to run such as (Ast, Jpype, Numpy, Pickle, etc.) if this did not came with the Anaconda3 installation.

Author: Daniel Si

Continue reading Here

3 Likes
3

Hi @danielsidata ,

Thanks for sharing your detailed overview of using KNIME for Excel Pivot Tables. It’s helpful and seems beginner-friendly!

Best,
Keerthan

2 Likes
4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.