7 ms·

Excel, and more importantly, the spreadsheet is the best way to build intuition for a dataset, hands down. The alternatives, especially when it comes to Python

Excel, and more importantly, the spreadsheet is the best way to build intuition for a dataset, hands down. The alternatives, especially when it comes to Python and the default pandas output in a Jupyter Notebook are horrendous. 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/ 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.

tryitnow 6y ago
What's the pricing model? There's a lot of potential here.
- aarondia 6y ago
  We're still exploring the business model to support the project. Right now, Mito is free to trial, and we're experimenting with a subscription plan somewhere in the range of $10 per month. I'd say about half of our users are using it for free and half of them are on some sort of subscription. 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!
Robotbeat 6y ago
What non-spreadsheet programming languages/environments do you think work better than Python/Jupiter? 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?
- hypermachine 6y ago
  A lot of environments do not have easy to use hot reloading out-of-the-box (and also quick GUI creation support). This is the primary reason why development feels less intuitive in a lot of text based languages versus more visual tooling like Excel which can give instantaneous feedback. 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.
  - lispm 6y ago
    > but they tie it to the repl rather than as a standalone feature Lisp has a function called LOAD, which can load source and/or compiled code.
- airstrike 6y ago
  > What non-spreadsheet programming languages/environments do you think work better than Python/Jupiter? RMarkdown + RStudio + knitr
  - jdgoesmarching 6y ago
    I really wish Python had the kind of support for Markdown that R does. Notebooks are fine, but the simplicity of having essentially a plain text file is just amazing.
    - Something1234 6y ago
      Rmarkdown theoretically supports python. I haven't used it though.
screye 6y ago
> best way to build intuition for a dataset 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 ?
- csydas 6y ago
  Not the GP, but I think they are speaking specifically to non-programmers with this statement. 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.
  - iamacyborg 6y ago
    I think it's more simple than that. The way the data is presented to you in Excel makes it incredibly easy to grok.
loudmax 6y ago
IMHO where Excel falls short is in interoperability. Try processing .xlsx files in anything other than Excel and it can be painful. Let's see the new more open Microsoft really embrace competing on an even playing field that doesn't rely on ossified proprietary file formats.
- rchaud 6y ago
  Whatever is in your XLSX can be converted to a static CSV file. Charts, pivot tables and formulas won't be transferred over, but then again I wouldn't expect them to. The value Excel provides far outweighs the drawbacks of a vendor-specific solution.
  - zelphirkalt 6y ago
    Now we only need to get everyone to export to a standardized CSV, perhaps one adhering to its own name, comma separated values, and we are good.
  - LeifCarrotson 6y ago
    The data in an Excel file can be exported as CSV, but the value in an Excel file is the dependencies and formulas that have been built up into a representation of the business rules. The problem is that this often becomes the only representation of the rules, and auditing it or puzzling the data out of that Excel sheet after the fact is like pulling teeth. 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.
hypermachine 6y ago
Mito looks really interesting, I look forward to trying it out. I'd there a way to sign up for Mito without the hubspot meeting? 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.
- aarondia 6y ago
  Feel free to Twitter DM me @_aaronDR, would love to hear about what you're building + get you set up with Mito :)
igorkraw 6y ago
How does it compare to visidata?
- aarondia 6y ago
  There's a bunch of ways that Mito and Visidata are different. The two largest probably being: 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.
serjester 6y ago
Horrendous? Strongly disagree if you're doing anything non-trivial. As soon as you start mixing the two, both become worse. You're basically losing the UX benefits of native Excel and adding confusion to Pandas. 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.
- aarondia 6y ago
  That's a good point -- and maybe horrendous was too strong of a word. It's actually been really interesting talking to people who come from the Excel world vs the Python world first. There's a group of people we've ran into who have never used Excel before, and for them, you're right, giving them a spreadsheet interface to write formulas and manipulate their data is actually a huge disadvantage. 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.
algorithmsRcool 6y ago
> 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. 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-and-limits-19aa79f8-e6e8-45a8-9be2-b58778fd68ef https://support.office.com/client/Data-Model-specification-a... [1] https://support.office.com/client/power-pivot-powerful-data-analysis-and-data-modeling-in-excel-a9c2c6e2-cc49-4976-a7d7-40896795d045 https://support.office.com/client/power-pivot-powerful-data-...
- texasbigdata 6y ago
  Big fan of this, but powerpivot sits on vertipaq (I believe) which is an in memory columnar DB or sorts (apologies if that’s incorrect). So at this point you’re getting awfully close to direct querying (another msft feature) which while analogous resembles more traditional db/client if you squint hard enough. But yes, big fan of vertipaq which I believe also powers PowerBI.
  - craig_asp 6y ago
    Just to clarify.. Yes, vertipaq is the tech behind power pivot, power bi and sql server analysis services (in tabular mode) and the same column-oriented storage is also used in sql server. Excel generates queries against the data stored in a vertipaq model. You cannot write normal excel formulas on top of it and you have to use DAX (a unique to msft language, which is the replacement for MDX) instead, which is pretty much a no-go for anyone but well-trained power users.
- aarondia 6y ago
  There's also a workaround in Google Sheets where you can store your data in BigQuery and use the spreadsheet to interact with it.
- stilisstuk 6y ago
civilized 6y ago
I'm a data scientist who isn't an Excel wizard and I'd like to have the other direction, turn my dplyr code into Excel
- aarondia 6y ago
  I'm less familiar with the R ecosystem, but there are tools in the Python world to go that direction -- https://xlsxwriter.readthedocs.io/ https://xlsxwriter.readthedocs.io/
agumonkey 6y ago
Excel, depending on who organizes the data, can lead to god-table with 123 columns. Now someone with a bit of balance, can go quite far with it.
- 1980phipsi 6y ago
  New sheets are your friend.
sonthonax 6y ago
Nice product, I noticed that you are updating the next Jupyter cell; what was your solution to doing that reliably since `set_next_input` is so damn flakey? 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 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.

tryitnow 6y ago

What's the pricing model? There's a lot of potential here.

aarondia 6y ago
We're still exploring the business model to support the project. Right now, Mito is free to trial, and we're experimenting with a subscription plan somewhere in the range of $10 per month. I'd say about half of our users are using it for free and half of them are on some sort of subscription. 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!

Robotbeat 6y ago

What non-spreadsheet programming languages/environments do you think work better than Python/Jupiter? 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?

hypermachine 6y ago
A lot of environments do not have easy to use hot reloading out-of-the-box (and also quick GUI creation support). This is the primary reason why development feels less intuitive in a lot of text based languages versus more visual tooling like Excel which can give instantaneous feedback. 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.
- lispm 6y ago
  > but they tie it to the repl rather than as a standalone feature Lisp has a function called LOAD, which can load source and/or compiled code.
airstrike 6y ago
> What non-spreadsheet programming languages/environments do you think work better than Python/Jupiter? RMarkdown + RStudio + knitr
- jdgoesmarching 6y ago
  I really wish Python had the kind of support for Markdown that R does. Notebooks are fine, but the simplicity of having essentially a plain text file is just amazing.
  - Something1234 6y ago
    Rmarkdown theoretically supports python. I haven't used it though.

screye 6y ago

> best way to build intuition for a dataset 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 ?

csydas 6y ago
Not the GP, but I think they are speaking specifically to non-programmers with this statement. 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.
- iamacyborg 6y ago
  I think it's more simple than that. The way the data is presented to you in Excel makes it incredibly easy to grok.

loudmax 6y ago

IMHO where Excel falls short is in interoperability. Try processing .xlsx files in anything other than Excel and it can be painful. Let's see the new more open Microsoft really embrace competing on an even playing field that doesn't rely on ossified proprietary file formats.

rchaud 6y ago
Whatever is in your XLSX can be converted to a static CSV file. Charts, pivot tables and formulas won't be transferred over, but then again I wouldn't expect them to. The value Excel provides far outweighs the drawbacks of a vendor-specific solution.
- zelphirkalt 6y ago
  Now we only need to get everyone to export to a standardized CSV, perhaps one adhering to its own name, comma separated values, and we are good.
- LeifCarrotson 6y ago
  The data in an Excel file can be exported as CSV, but the value in an Excel file is the dependencies and formulas that have been built up into a representation of the business rules. The problem is that this often becomes the only representation of the rules, and auditing it or puzzling the data out of that Excel sheet after the fact is like pulling teeth. 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.

hypermachine 6y ago

Mito looks really interesting, I look forward to trying it out. I'd there a way to sign up for Mito without the hubspot meeting? 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.

aarondia 6y ago
Feel free to Twitter DM me @_aaronDR, would love to hear about what you're building + get you set up with Mito :)

igorkraw 6y ago

How does it compare to visidata?

aarondia 6y ago
There's a bunch of ways that Mito and Visidata are different. The two largest probably being: 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.

serjester 6y ago

Horrendous? Strongly disagree if you're doing anything non-trivial. As soon as you start mixing the two, both become worse. You're basically losing the UX benefits of native Excel and adding confusion to Pandas. 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.

aarondia 6y ago
That's a good point -- and maybe horrendous was too strong of a word. It's actually been really interesting talking to people who come from the Excel world vs the Python world first. There's a group of people we've ran into who have never used Excel before, and for them, you're right, giving them a spreadsheet interface to write formulas and manipulate their data is actually a huge disadvantage. 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.

algorithmsRcool 6y ago

> 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. 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-and-limits-19aa79f8-e6e8-45a8-9be2-b58778fd68ef https://support.office.com/client/Data-Model-specification-a... [1] https://support.office.com/client/power-pivot-powerful-data-analysis-and-data-modeling-in-excel-a9c2c6e2-cc49-4976-a7d7-40896795d045 https://support.office.com/client/power-pivot-powerful-data-...

texasbigdata 6y ago
Big fan of this, but powerpivot sits on vertipaq (I believe) which is an in memory columnar DB or sorts (apologies if that’s incorrect). So at this point you’re getting awfully close to direct querying (another msft feature) which while analogous resembles more traditional db/client if you squint hard enough. But yes, big fan of vertipaq which I believe also powers PowerBI.
- craig_asp 6y ago
  Just to clarify.. Yes, vertipaq is the tech behind power pivot, power bi and sql server analysis services (in tabular mode) and the same column-oriented storage is also used in sql server. Excel generates queries against the data stored in a vertipaq model. You cannot write normal excel formulas on top of it and you have to use DAX (a unique to msft language, which is the replacement for MDX) instead, which is pretty much a no-go for anyone but well-trained power users.
aarondia 6y ago
There's also a workaround in Google Sheets where you can store your data in BigQuery and use the spreadsheet to interact with it.
stilisstuk 6y ago

civilized 6y ago

I'm a data scientist who isn't an Excel wizard and I'd like to have the other direction, turn my dplyr code into Excel

aarondia 6y ago
I'm less familiar with the R ecosystem, but there are tools in the Python world to go that direction -- https://xlsxwriter.readthedocs.io/ https://xlsxwriter.readthedocs.io/

agumonkey 6y ago

Excel, depending on who organizes the data, can lead to god-table with 123 columns. Now someone with a bit of balance, can go quite far with it.

1980phipsi 6y ago
New sheets are your friend.

sonthonax 6y ago

Nice product, I noticed that you are updating the next Jupyter cell; what was your solution to doing that reliably since `set_next_input` is so damn flakey? 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 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.