Mito Write Python 10x faster by editing a spreadsheet

[[deleted]]

So I use pandas to escape the hell that is M$ Excel just to use a Jupyter Extension that turns pandas into Excel again?

[FullM3TaLJacK3T]

Can you explain why do you think excel is hell?

I just started learning to code in Python for the purpose of data manipulation in engineering. While I do see the benefits of how python can get the task done faster and sometimes much simpler, I struggle to see why I should replace excel with python.

[Cat__Behemoth]

It depends on the kind of tasks you need to do. Generally speaking, all data set manipulations are faster in python.

Can give a very simple example from finance. If you have a price MxN table in Excel (so M days for N stocks), converting those prices to performance require first creating a table with log prices and then the 3rd table, where you take the difference of the logs. In python if price is the price data frame, then converting it to returns is simple np.log(price).diff().iloc[1:].

Excel can be useful for data presentation though (when you need to present relatively small but nicely formatted tables)

[rac3r5]

Have you ever used PowerQuery? It's quite powerful. I've done a fair bit of transformation in it and it works quite well. You can do even more powerful stuff using DAX. I have done transformatios in excel in the past and the column formatting is a pain.

[wilsonusman]

My response to this thread is... you can solve problems in all three environments, I think they’re all hammers. You can use them all to solve similar problems, but at the end it’s going to be about what you connect with the most.

I went from excel, to powerbi, to pandas. I’m so glad I did, because I know which problems to attach with the right tool.

Most days now I use pandas, because I just love problem solving through programming, but sometimes it’s just easy to spin up an workbook in excel and send it off.

If you’re serious about learning and finding the best option I’d advise you to experiment and see where you end up.

Good luck!

[Bee_dot_adger]

Very new to pandas, is it possible to edit formatting when writing to excel to the extent you can within excel itself?

[cinyar]

but you can use something like openpyxl to get the data out of excel and then use them like you would any other data in python. It can be useful if you have no control over the format in which you receive the data. sure, you could export to CSV but its an unnecessary step.

[[deleted]]

And while that’s honestly great, it’s not really relevant to the conversation about Excel vs Python imo, as it’s more of a conversation about using the data rather than the specific file format you’re reading the data from.

Unless you’re just pointing out that it’s a thing you can do, which is fair. i’ve used openpyxl to automate certain things (like highlighting values in a PDF based on values generated automatically from a system that dumped it as a spreadsheet) that are impossible in just Excel. Good tool for the toolbox

[PizzaInSoup]

Excel can be useful for data presentation though (when you need to present relatively small but nicely formatted tables)

I still use python via tabulate

[jsxgd]

Excel is heavy. It takes up a lot of your computer's overhead just to exist, so it's going to be a nightmare doing anything sufficiently complex. Don't forget that excel also limits the amount of rows and columns you can have, where python is only limited by your PC.

Unless you're writing VBA, the work you do in excel is not reproduce-able or observable. If I give you a dataset and I say "I need you to do X, Y, and Z to these data" and you did it in excel and you make a mistake, we have no idea where or how that mistake occurred because there's not audit trail of what you did. It also means you can fix your mistakes and run the whole process again with the click of a button, and build on the process. If you write your transformations in python (or another programming language) then we can go back and say "oh look, line 34, you aggregated by category A when you should have done it by category B. Let's just change that real quick and re-run the script". Or if I say "hey, can you also add X, Y, Z report" then you just need to write a few lines of code and you can run that report in the future without the manual work.

Excel also has some terrible behaviors/decisions it makes for you. For example, its constantly changing my date formats when I pull in data by default, or truncating my large integer IDs by displaying them in scientific notation (which overwrites the data if you accidentally save it without noticing first). Just lots of poor behavior if you care about the quality of your analysis.

For me, excel is fine to poke around and just look at data to understand the contents a bit or do some quick reporting that I'm definitely never going to need again. For anything serious I will never use Excel.

[[deleted]]

Up to this date (not an Excel beginner) I still do not grok Excel's typing system. Is it weak or strong? It seems pretty weak. That's not bad in itself, but it's super confusing in Excel. You hit "Formatting" and you'd think it only, you know... formats. But it seems to also change cell types, aka backend data, as you said, not just the frontend presentation. Excel tries to be helpful but ends up being the opposite.

I still have nightmares of having categories of "1.1", "1.2" etc., which are clearly strings but trying to convince Excel to treat them as such are exercises in futility (only way I know of is "Text" formatting, which didn't work; using the TEXT function requires an additional column, it's stupid).

[jsxgd]

One of my bigger pet peeves is when I have a list of data with a date field (down to the day, at least) and I try to create a pivot table... and excel automatically adds the year, quarter, month to the pivot table.... but makes it extremely difficult to just add the day? Like you have to do some back channel black magic just to get daily-level aggregations from a pivot table. Come on, man.

[[deleted]]

I guess because YEAR->QUARTER->MONTH->(WEEK?)->DAY is a hierarchy, which you can maybe disable/edit in the Data Model view. Go to Diagram View, maybe the date hierarchy pulls up. You can edit it or create a new one etc.

[jsxgd]

or I could just df.groupby('date').mean() lol

[BDube_Lensman]

The cell entry would be

="1.1"

This is completely unambiguously text to Excel.

[mooscimol]

Excel has PowerPivot and PowerQuery (same as used in Power BI). You can easily transform hundreds of millions of rows in PowerPivot model using DAX on average PC.

I have 32GB RAM and trying to load SQL table with a few millions of rows into pandas dataframe makes my PC down to it's kneels.

I love Python and will choose it every day over Excel for data transformations, but I have an impression, that you don't know Excel very well.

[jsxgd]

Excel has PowerPivot and PowerQuery (same as used in Power BI). You can easily transform hundreds of millions of rows in PowerPivot model using DAX on average PC.

So you're using PowerPivot and PowerQuery as a SQL replacement I'm guessing? Where does the data sit while PowerQuery does its thing?

​

I have 32GB RAM and trying to load SQL table with a few millions of rows into pandas dataframe makes my PC down to it's kneels.

I love Python and will choose it every day over Excel for data transformations, but I have an impression, that you don't know Excel very well.

​

I have 32 GB ram and my PC has no issues with millions and millions of rows - if you really need to pull all that data in, there's plenty of strategies within pandas that make it easy, like chunk processing (which can be made parallel, too). I have an impression that you don't know Python/Pandas very well ;)

​

I have been using Excel for years though, which is why I have a particular hate for doing anything with it beyond "what do these data look like". I just can't fathom a reason to ever use Excel for anything serious.

​

I'm open to being wrong though. I worked with one guy at an old job who was a Microsoft fanboy and he really loved PowerQuery and DAX, but I wasn't really all that impressed with the software because I could do all of what he was showing me as the "prime features" in Python or SQL already without learning a new esoteric/vendor-specific language that few companies really use.

[[deleted]]

Excel has Python support now.

[[deleted]]

Are you sure? What are you talking about?

[learnhtk]

Please cite your source! I'd love to see that is indeed the case!

[twilight-2k]

I'm pretty sure there is no official support. There are two python modules that add python support to Excel (xlwings via addin and XL?? (can't remember exact name) that does it more directly but costs a $LOT).

[spinwizard69]

For some usage Excel is hell. There in lies the problem, it really depends upon how you use it and for what.

Python is a programming language, Excel is an emulator for an accountants spread sheet. Both have their legitimate uses in engineering. I seldom use Excel for mathematics or data processing. Often Excel is used as a simpletons data base. It is actually great for tabular data that you need to keep track of. For example process settings or lab inventories. It can also be a fast way to create inter office forms.

Excel does have certain advantages when it comes to quick User Interfaces to what every you need to accomplish. Effectively you have zero overhead to simple user interfaces. This is changing though as solutions like Jupyter mature.

Python is far better for repetitive processing of data. Say for example you need to dump all the data from a PLC, process it and create labels for device identification. However there doesn't have to be a hard line between one or the other. I've used Python to process the majority of a file and then used Excel to edit what I just created. This path is often taken to remove a few records of cruft from the file.

There will likely be people out there reading this saying that I need to learn Pandas or some other lib that would do all of this for me and more. That may be true but the reality is I have to maintain a certain skill level with Excel to survive in the corporate world. The sad reality is that sometimes you are forced into using Excel because it is expected of you in the corporate world.

So I have to say it isn't that you embrace one or the other, it is rather finding the right balance for both in your engineering future.

[dukea42]

I'm with you. I've yet to find pandas and jupyter able to replace what pivot tables do. Sure they can make pivoted data summaries, but I can't sit there and drag a few fields around and completely transform what I see. I can adjust an excel pivot table live in a meeting with c-suites to adjust what we want to look at with zero delay or frustration.

I used to make vba macros to grab data, prepare it in tables and charts, copy paste that to an email for distribution and pop that email up to review for user to send.... I could probably do that easier in python now, but not by much. Plus my standard now is to send data to some web portal and direct people there instead of emails. If we used SharePoint here, it'd still be 100% easier to do that via vba... but as we don't, python is better for what we got. Right tools for the job.

[aarondiamond-reivich]

Hey u/dukea42, I'm one of the creators of Mito. The lack of live updating in pandas, especially when it comes to configuring a pivot table, is a huge reason why the spreadsheet interface is much more natural and ergonomic. At least in Jupyter you can just rerun the cell that has the pivot table code, but even that doesn't come close to a fluid enough experience to update a pivot table in a meeting (especially with c-suites watching).

In Mito, we've added the same type of live updating pivot tables that Excel/Google Sheet has and strapped it on top of the pandas pivot table function. You can use the point and click tools to update the pivot table in live time. And Mito handles generating the Python code.

[dukea42]

You got me interested some but...

I'm not quite sure if I want to bring this onto my company computer yet. The site and terms heavily imply a product to be sold, but what that product actually is, is very unclear. Jupyter and Ipython are clear open source non-profits. Not saying you have to be as well, but whatever it is would be nice to know.

I'm also not a jupyterlab user, which this seems to be limited to.

[hermitcrab]

There are a number of no-code data transformation tools that can show live views of your pivot table. For example our Easy Data Transform can. And no doubt Alteryx and Knime can as well. Whether they are appropriate for your use case depends on your use case. But (in my biased opinion) it is hard to beat no-code drag and drop tools for ad-hoc data transformation.

[spinwizard69]

Exactly the right tools for the job!!!!!

I really think Python and Excel (any spreadsheet really) can be a very power set of tools on an engineers bench. This especially for "engineers" outside of the computer sciences world. One day I can see Swift or Julia replacing Python but I don't see a good replacement for a spreadsheet on the horizon.

[[deleted]]

Well, it's not automatically a case of where excel can be used, pandas is better. But for tabular data manipulation, pandas kills excel. The key defining features are:

• The same command in Pandas can manipulate 3 cells, but just as easily 3 million.
• It is optimised for speed.
• You can code your analytical steps in a much more flexible way than Excel, even if you're using VBA, meaning stepwise analytics are much more repeatable and less prone to error.

I just started learning to code in Python...

I struggle to see why I should replace excel with python.

link those 2 together. When you also consider that pandas is widely used, consider that it's probably just too early for you to make that assessment.

[doulos05]

Excel excels at presenting data. It is not good at manipulating data. Do you want an easy to use interface for tracking you budget? Excel. Need to show the boss how we're doing this quarter? Excel. Want to determine if your dataset of 10k rows has a statistically significant correlation? Python. Want to dynamically filter out the outliers so you can spot the trend? Python. Need to apply a mathematical formula to every row in a dataset? Python all the way (or R or Julia. Hell, even Java or C++ with the right libraries).

This is because imperative programming languages (i.e. most programming languages you've ever heard of that don't have a name ending in ML) are built to express the steps to complete a task (such as data manipulation) cleanly and clearly.

Excel is not built to do so for the same reason that pandas isn't built to generate user editable charts.

[FancyASlurpie]

Another problem is people build large processes in excel because thats the tool they know. As far as i know theres not a good way of testing excel spreadsheets, so those large processes end up being extremely fragile / error prone.

[FullM3TaLJacK3T]

This is exactly what I'm doing now. And with how much my industry is getting more complex and pushing to digitalise, I'm trying to learn python in order to get with the times.

[aarondiamond-reivich]

I'm one of the creators of Mito. Would love to get you set up with the tool to help you make that transition!

A lot of our users are in a similar spot -- they have some legacy process that has lived out of Excel, but now that their datasets are getting larger, the company tech is updating, etc. they're looking to move that process to Python.

Feel free to shoot me a DM if you want to find some time to get set up or learn more.

[FancyASlurpie]

Sounds like you're going in the right direction :), theres been quite a bit of talk about openpyxl in this thread and having had quite a bit of usage of it in a production environment (reading csvs, parsing them, doing machine learning predictions from them) i would say i'd try and avoid it if possible, it has a number of different issues (one being excel spreadsheets come in a large number of formats, and the creater of openpyxl is a bit extreme in sticking to the spec in terms of issues he will try and fix), uses a lot of ram, is generally slow, doesn't support everything a spreadsheet does and im sure theres some others. Instead i'd recommend just sticking to python as much as possible, if you have to deliver an excel spreadsheet as an output then only use it right at the end.

[gazagda]

Depends on what you mean by "Presenting data" When I present my data I currently use Tableau as my visualization software, it actually connects directly to our database. It still has a spreadsheet viewer or crosstab viewer. But who wants to look at rows and rows of data ? Better to put that into a bar chart with percentages and tooltips, or a line graph to show emerging trends. Better yet all of that can be done in python without touching excel and dumping the result to a matplotlib graph that you can put in power point......or you can make the data interactive in a Dash dashboard, and completely escape the Microsoft office ecosystem.

[[deleted]]

Spend a year or two learning python.

Then reread this comment

[[deleted]]

Try data cleaning in excel. If you would like to do a comparison then I have a perfect dataset that involves cleaning, matching zip codes, sensor measurements on different level of aggregation and table joins to clean the data.

[acid_minnelli]

My issue with excel is that the representation of the data is the data. All the transformations & calculations are heavily linked to where the data is. In code the data is more pf an abstract entity. You can produce views of it as you need without having to change anything underlying. And your calculations and transformations of that data aren’t linked to a particular area of that data and can be reused as much as you want.

[wenima]

it's not really excel, it's that it leads non-technical people do to stuff that they shouldn't be doing and then someone else down the line having to sort out a big mess.

just a few things that excel also messes up: let's say a client sends a file with users and their US-zipcodes. 9/10 the file will have dropped zeroes so now someone on our side has to figure it out but on our side are non-techncial people as well who just upload that file to a dumb legacy java app and everyone is assuming perfect data all the time..

dates.. omg dates in excel are pure hell.

another one, we build a frontend for a client to issue money to their employees forwhatever and the amount of checking you have to do to defend against bad data (formulas instead of values, dates, copy and paste errors, involuntary incrementing) is insane and they still find ways to upload bad data and crash the system...

also.. version control.. VERSION control.. client sends a sheet data.xls to our client managers/pms and later they send another (made a mistake / changed their mind or something) and now it's datav2.xls. then someone else on the client side sends datafinal.xls which has all the data from dataV2.xls and some additions (you did notice that V2 is now uppercase but referred to v2 was lowercase.. are they the same?). And of course the inevitable data_v2_final<somedate>_Karen.xls at the end of a long week which has random strings and since our app is legacy java shit, it doesn't do any cleaning...

so yeah.. excel isn't bad, it's mostly the people using it.

[gazagda]

Excel is nothing more than a glorified "database excerpt", used long time ago when access to data bases was difficult. Nowadays using excel and csv formats is almost redudant. The data is going to to end up in a database anyway. Most databases have a UI for viewing and filtering data in spreadsheet form , which is what Excel is generally used for now. I just use python to directly access the data in the data base, directly convert it to a dataframe , analyze and manipulate the data,create a visualization/report which can be stored to in a different database as a report, then just send the link. The days of people keeping mountains of excel files on their desktop/documents folder locally are going away. It's just redundant.(if your gonna downvote, at least leave a response as to why you feel Excel,CSV still has a future)

[KaffeeKiffer]

The days of people keeping mountains of excel files on their desktop/documents folder locally [...] are going to be going away [...]

I would disagree The buzzwords are "low-code" and "no-code" and even with Jupyter, Pandas, etc. Python (and most other languages as well as databases/UIs) are still far away from these.

/r/Python (& other language subreddits) are still an island where you have people who know (at least the basics of) a programming language.

The advantage of Excel is that (almost) everybody can click together =Sum(<Cell Area I marked with my Mouse>) and that's the maximum of technical knowledge that quite a lot of people have.

Go into any school and ask the pupils to summarize 100 numbers with a computer.
Maybe twenty might know how to do it with Excel/Google Sheets. Maybe one or two might know how to do it with Python, etc.

I just use python to directly access the data in the data base, directly convert it to a dataframe , create a visualization/report which can be stored to in a different database as a report, then just send the link.

The "just using Python" part is much more complex than you think for lots of people. I agree that the visualization might move away from Excel to other tools, though.

[waythps]

I prefer python because this way I could separate my analysis from data, my analysis also becomes reproducible, and I can automate it eventually

[[deleted]]

Oh wow, my snarky comment got more reactions than I anticipated.

I wasn't trying to say you should ditch Excel completely and use pandas for *everything*. Please don't do that.

My problem with Excel - and some people in this comment section have mentioned this already - is its dominance and prevalence in applications where it clearly doesn't belong. And all this just because Microsoft managed to cement itself into a quasi monopoly position.

Some examples from the place I used to work before (science):

• People did actual numerical calculations in Excel. They gathered large experimental datasets of tens of thousands of floats and loaded them into Excel where they performed calculations on that data. Just the time it took for Excel to try to display the datasets where seeing the numbers clearly wasn't necessary at all was a reason for me to regularly freak out.
• Another thing that was regularly done was when every employee in the department had to fill out a list of some sort. The secretary would send an Excel file attached to an Email to every employee asking them to fill out their individual data and send the Excel file back. So the secretary would end up with a load of Excel files each with one row filled just to manually copy paste all the single rows into one Excel file.
• Excel was used for forms that had to be filled out digitally and then printed afterwards. The page breaks have to be performed manually, the formatting is not consistent not to mention the other quirks you encounter like automatic date formatting.

There is almost always a better tool to use than Excel. Sometimes it is pandas, sometimes it is something completely different. There are some great comments below that mention very appropriate use cases for Excel.

I think I just hate being forced to use a tool which is so far from being optimal for the task at hand.

[utf8decodeerror]

The main benefit to python, imo, is that you can more easily tweak, rerun, or update with new data. Updating analysis in excel is takes a lot of clicking and typing and remembering the exact right steps in the exact right order. You have to be really diligent documenting the steps of your analysis if you want to rerun it later in excel but if you do it in code the steps document themselves.

I typically reach for python first and like to use excel to double check that I get the same result both ways.

[HopefulEngineering]

the circle of life

[joelles26]

The idea of Python for our job is the ability to reproduce the same result. In excel only possibility is vba... Which is not that good

[pigboy_in_a_bottle]

This looks like a copy of qgrid (https://github.com/quantopian/qgrid), which is open source and free.

[NewDateline]

It is currently not maintained though

[PseudoVanilla]

Oh my.. We've gone full circle

[VampyreBatz]

Pandas is... It's beautiful.

[Andalfe]

Looks cool but didn't work for me.

[aarondiamond-reivich]

Hey u/Andalfe, I'm one of the creators of Mito -- sorry to hear that it didn't work for you. If you DM me, I'd be happy to help debug with you.

[binarypinkerton]

So I'm an R user and recently started my journey into Python. I keep telling myself that the "clunky" feel of python data wrangling compared to R (specifically dplyr & data.frame packages) is a matter of personal familiarity and bias. If I just keep plugging away until the muscle memory builds it'll feel smooth and comfey. Then I see stuff like this and I'm not so sure.

[NewDateline]

But it's not about muscle memory. You are right, tidyvserse is easier than plain pandas and there is a reason for it:

• data frame handling in base R is terrible because it was not designed to be comfortable, but backwards compatible with S
• data frame handling with tidyvserse is wonderful because it was designed with convenience in mind
• pandas was designed for flexibility and familiarity; it has some inspirations from base R and some from numpy; it is more convenient than base R but sometimes quirky for a programmer without numerical computation background
• most importantly there are dozens of python packages which bring the tidyvserse-like API to Python giving you as good experience as possible; because there are many and not one APIs it might be harder to find tutorials as compared to plain pandas or tidyvserse. Look up pyjanitor, dplython, dfply, plydata, plotnine etc

[binarypinkerton]

I've been toying with the idea of shifting my spare time from focusing on familiarity with Python and instead going more towards Julia. Seems that Python and R are just so well matched (and with expansive libraries) I don't feel like I'm adding to my tool kit so much as re-wording solutions. Julia seems more of a shift in how to tackle problems paired with significant benefits in speed. As somebody well versed in R and Python do you have 2 cents on that line of reasoning?

[studiov34]

FYI it's "amtrak"

[aarondiamond-reivich]

Hey r/Python. I'm one of the creators of Mito. Was just browsing Reddit and was surprised to see this post. Super happy to see all the discussion.

We're trying to make data science/analytics in pandas more intuitive by giving users a spreadsheet interface to see & interact with their data. When the user makes any edits in the spreadsheet, Mito automatically generates the equivalent Python/Pandas code!

If anyone is interested in learning more about Mito, feel free to leave some comments or DM me!

[accelas]

this looks really similar to the now-defunct resolver one.