I dispute this. Yes, the normal spreadsheet view of excel will buckle under 1M rows, but excel has another feature called "Power Pivot" that is backed by an embedded database and scales into the high millions at least.
I've personally used excel on a dataset of 18M rows and PowerPivot handled it just fine.
[0] https://support.office.com/client/Data-Model-specification-a...
[1] https://support.office.com/client/power-pivot-powerful-data-...
Any day: rmarkdown and csv
But yes, big fan of vertipaq which I believe also powers PowerBI.
I had to port some stuff that was using the google sheets API over to manipulating xlsx files instead, and it wasn't a big deal.
The value Excel provides far outweighs the drawbacks of a vendor-specific solution.
A business might want to get to improve, say, their quoting accuracy. I've seen lots of places that write quotes using Excel. They use a complicated spreadsheet to estimate "We need $4500 in parts from vendor A, but in previous projects with components from vendor A often needed rework, so we multiply their quotes by 1.5 to account for the risk and for someone (typically Bob) to rework them; Bob's workload is over 90% and he's less efficient when he works overtime, so multiply his total hours by an additional 1.25, we also have to adjust his hourly rate by 1.5 to account for overtime..."
It's a Hard Problem to convert the quoting process from one of a few engineers who also do quoting by copying and modifying the blank Excel template and years of human domain expertise into a process where data entry techs input stuff to a CRUD webapp. This is fraught with peril because the Javascript/SQL guy you hired to write the webapp (or, heaven help you, the SAP consultant) hasn't been reworking gear from Vendor A for 15 years and sees what looks like an error when the formula for actual cost from vendors B, C, and D takes their quote price multiplied by 1.1 (for shipping? margin? ) and vendor A's quoted price is multiplied by 1.5, and, hold on, the VBA macro separately takes the the estimated dollar amount purchased from Vendor A, divided by 2000, and adds it to the head of maintenance's estimated hourly total for the project?
Making business decisions about logic tied up in Excel formulae is hard. Writing logic in something other than Excel where you can more easily see the business logic is probably harder. Convincing non-technical decision makers to learn VBA to evaluate their vendor selection is probably harder still.
It sounds like VBA has allowed that team to build an advanced prototype of a quote generation web app. The next step seems to be to convert the Excel formulas and scripts into JS or Python. Quality assurance may be a hassle, but that is to be expected with any kind of refactoring.
The key difference is that the Excel spreadsheet is not a prototype: It's an MVP, which includes "viable"; many businesses have been making money with them for years. Another key feature of the 'prototype' is that the team is able to edit the Excel formulas, but burying the formulas as JS or Python (whether locked away serverside or simply obfuscated by nature of being different language with a new learning curve) removes a critical feature.
Would love to hear a bit more about your workflows where you're trying to process an .xlsx file in another system. I'd imagine it would be a nightmare, but haven't ran into it myself :)
The dark pattern is in repeatedly nagging me about this fact.
The suggestion is 'use xlsx or suffer this annoying pop up'.
It's an easy fix. Have an option to disable it.
The other dark pattern, since we are chatting about them, is that you cant disable auto formatting. Excel will try to guess at your data, forcing you to jump through hoops to prevent it. The subtle suggestion is 'just use xlsx and this problem goes away' when in fact, they could offer an option to disable autoformating.
Yes, Pandas has a learning curve but so does Excel once you get into advanced functionality. It's inevitable. Once you get through this it's a fairly intuitive powerhouse.
I think the way that Mito tries to walk the line is by making the Python code visible for the user to see what the equivalent Python looks like + easily usable in your analysis, but also completely generated for you. So hopefully, we're not introducing the confusion of pandas into your workflow.
Could you elaborate on this please ? I work with a lot of datasets, and have found python + libraries (plt/pd/np/scipy/regex) to be far more useful. But, that might just be my inexperience with excel.
Can you give a few examples of analyses that work better in excel than python ?
It's not about which analyses are more performant/easier in one object versus the other, it's how do you most easily introduce the general audience to big data, both reading, manipulating, and transforming.
I actually disagree with their statement tbh, as I think that it's too nuanced of a situation to scope like this.
I used to work in a university, and depending on the dataset and the intended output, I would switch between R and Excel for the students. Those who needed R level analysis eventually saw why it was more useful for them than Excel and got good at seeing when to use R versus when to use Excel.
Those who had datasets/output goals that didn't need heavy lifting really just needed Excel. It's not incorrect to say that learning heavier tooling/languages is a benefit, there is also a time consideration to learn and become efficient at a given toolset. The heavier toolsets have their nuances and accomplishing the same task in less robust toolings like Excel is the more efficient and better approach for those who have extremely limited time and for those who are not likely to need the heavier toolset in the future.
It's just a simple cost benefit analysis -- what tool is going to give the best return on time investment?
There is a very valid and reasonable argument that investing into the heavier toolsets will eventually reach a point where even the simple tasks that Excel and other tools allows users to perform more easily with less knowledge is faster/better with the heavier language; the question is "when is it optimal for a given person to invest the time to get to that stage?", and that's a question that doesn't always have all available data to make an informed decision on since it's hard to predict the future.
Because you can think of Mito as a frontend interface to Pandas, using Mito doesn't prohibit you from building intuition or analyzing your data in the same way you would if you didn't have the spreadsheet frontend. It just helps you write the Python/Pandas code faster + see the most up to date version of your data set in live time.
The typical Mito user uses Mito multiple times throughout an analysis. A common pattern is: start by just visualizing the data in Mito, create a few graphs to help understand the distribution using matplotlib (right now we only have a tiny bit of graphing support), passing the data back into Mito to do some filtering and cleaning, then lastly creating a pivot table output using Mito. Of course, it varies greatly from user to user, but that's a general flow we see often!
I agree, SQL is what I like more for mangling. Except for the pivot/melt part that is
For us we are going the opposite approach, we are building a VB interpreter to make it easier to run, build, and extend existing Excel programs. We allow calling libraries written in WebAssembly and GraalVM supported languages.
Now someone with a bit of balance, can go quite far with it.
I’ve been fairly happy with the default Matlab IDE personally. Visibility and representation of data has the straightforwardness of a spreadsheet. But surely there must be others?
Hot reloading is most famous for being a staple of Lisp languages (but they tie it to the repl rather than as a standalone feature). For Microsoft languages this is provided by Visual Studio (commonly known as edit-and-continue, it is available in some form or other since the original VB days). You can try it out with the embedded VBA interpreter in Excel (under the Developer tab).
For JavaScript this is a recent innovation (driven primarily by the React/SPA crowd). In Java, most IDEs have the feature but it requires a fair bit of setup and configuration (look up hot swap for Intellij). The closest thing Python has is Jupyter which admittedly is not that pleasant to use.
Lisp has a function called LOAD, which can load source and/or compiled code.
RMarkdown + RStudio + knitr
yihui.org/knitr/options/#language-engines
I once made a SAS engine to show coworkers how to adopt report automation without having to rewrite all existing code.
I personally grew so frustrated with the state of GUI development in Jupyter that I tried to fix it in such a way that would allow proper message passing between cells and python code (because you can't wait on Comm events).
> https://github.com/ipython/ipykernel/pull/589
But sadly the priorities of big open source projects don't always match your own. So I had to extract that logic into my own kernel.
1) Mito is an extension to JupyterLab whereas Visidata is a CLI tool. As a result, Mito is a react frontend that is more of a traditional Excel-styled spreadsheet interface. You can use your mouse to perform point-and-click transformations, like writing configuring pivot tables or writing spreadsheet formulas.
2) Mito generates Python/pandas code for every edit the the user makes. So users are generating a script to manipulate their dataframes, running that script, and then continuing to use their dataframes throughout the analysis. People use Mito in a Jupyter notebook the way that they use pandas code, multiple times throughout their analysis, interspersed with graphing, ML, etc.
We're also considering open sourcing the tool, and doing the classic Enterprise Sales / consulting / other value add services on top.
If you have ideas about which direction to take it, would love to hear!
Where Excel falls short, is data size limitations + auditability. Putting more than 1M rows of data into Excel is not possible, and once you get into the low 100K's, it becomes almost unbearable. And handing off an Excel workbooks to a colleague is handing them hours of cell dependency tracing. On the other hand, data size + auditability are the super powers of Python data analysis.
I've been building a Python package, Mito (https://trymito.io/), its an interactive spreadsheet that automatically converts your spreadsheet analysis to the equivalent pandas code. You can write spreadsheet formulas, merge datasets, create pivot tables, etc. And because its implemented in Python, you can manipulate datasets with 10M rows of data with no problem. Our goal is to bring the intuitiveness of Excel data manipulation to Pandas.