Linux alternative to Excel pivot table

Question

My company uses some simple MySQL queries to generate a CSV report of a bunch of stats (about 20 columns, rows in the low thousands). We then use Microsoft Excel to create pivot tables to explore and drill down into the data.

I’ve tried using LibreOffice Calc but the pivot tables don’t support calculated fields, which we use extensively.

Interactivity is key, but it doesn’t need to be spreadsheet-like.

Charts/graphs are nice, but we mostly need tabular data.

There are lots of products that seem "enterprisey" and use OLAP. I’m not against that but their websites tend to be geared towards executives and it’s hard to see what they actually do.

Answer 1

I would suggest taking a look at python/iPython & pandas data visualization.

• Interactive in web browser with iPython & Scriptable
• Pivot tables are a single line: e.g.: df.pivot(index='date', columns='variable', values='value')

From this really nice example by Chris Moffitt.

• Calculated fields no problem at all - this is Pandas
• Can directly read/refresh the SQL via SQLAlchemy or read the csv file.
• Free gratis & Open Source
• Cross Platform Mac/Linux/Windows/Workstations/Supercomputers/RaspberryPi
• Lots of import/export/publication options
• Lots of graphing and visualization options built in or via matplotlib

Answer 2

You can try Web Pivot Table. It is totally web based and run inside browsers and it has full functionalities as excel pivot table. Of course it supports add calculated field as well.

There are demo and Documents show you how to use it. Installation is quite simple and user interface is pretty intuitive.

It creates pivot table and pivot chart synchronizedly and support OLAP cube pivot as well.

It has two editions. Free edition is free for anyone or any company. Professional edition can be embedded into any other applications or software and need to purchase a license.

Answer 3

As for 2018 I want to recommend our free web BI tool SeekTable.com that can connect directly to MySql database and create pivot tables, charts and usual tabular reports. Results may be exported to CSV/Excel/PDF or shared by email or via link, and it is possible to define custom calculations with built-in formula expressions, or with SQL-expressions.

By the way, tool can be used with CSV files directly (up to 50mb zipped), other SQL databases, and even NoSQL data sources like MongoDb or ElasticSearch.