Associate
- Joined
- 5 Sep 2020
- Posts
- 296
Hi everyone. I've started exploring Pandas and so far am impressed. I was wondering if anyone has been able to largely replace using a spreadsheet like Excel with Pandas?
Anyone using Python+Pandas to replace Excel?
- Thread starter Rikmeister
- Start date
Soldato
Surely its going to depend on the use case. The interactivity and instant feedback in excel is going to be hard to beat when you're tinkering with the model and getting it all set up as you want. Where python + panda would be better is some kind of batch / automated flow of "read inputs from some file / webservice and then spit out results and pretty pictures".
If your data is more complex and starts to be a nightmare to even show on the row/column/tabs view of excel then again, pandas will be better as its constrained by having to present it all in a largely fixed model. Many years ago I used Lotus Improv that tried to solve this multi-dimensional problem, but its sales were never enough to sustain the product and it got axed.
If your data is more complex and starts to be a nightmare to even show on the row/column/tabs view of excel then again, pandas will be better as its constrained by having to present it all in a largely fixed model. Many years ago I used Lotus Improv that tried to solve this multi-dimensional problem, but its sales were never enough to sustain the product and it got axed.
In my mind they’re different use cases. Excel is a bit more interactive and you can do transforms on imported data with the Power Query engine, whereas Pandas is lot more code heavy, which gives you greater control. Exploratory analysis via something like Jupyter is very powerful though.
Soldato
I had been drawn into them yes and was impressed how useful it is for example dumping info into a postgresql database.
As Mesai said though the ease and interaction of datasets in Excel generally is used but can totally see it for specific tasks how that could be the better workflow.
As Mesai said though the ease and interaction of datasets in Excel generally is used but can totally see it for specific tasks how that could be the better workflow.
- Joined
- 5 Sep 2020
- Posts
- 296
Hi everybody. Sorry I haven't been around to respond to your replies (everyone a great answer by the way!).
I think @peterwalkley and @Mesai identified a key attraction of Excel is it's interactivity, which to my mind is both a strength and a weakness. Initially anyone could open up an Excel spreadsheet for the first time and start usinging it productively in minutes. However, this could also lead to sloppiness with, for example, text being put into numeric fields. Where as with Python + Pandas there is a very clear separation between code and data.
I think @peterwalkley and @Mesai identified a key attraction of Excel is it's interactivity, which to my mind is both a strength and a weakness. Initially anyone could open up an Excel spreadsheet for the first time and start usinging it productively in minutes. However, this could also lead to sloppiness with, for example, text being put into numeric fields. Where as with Python + Pandas there is a very clear separation between code and data.
- Joined
- 5 Sep 2020
- Posts
- 296
Hi gpuerrilla,
Are you saying you're impressed with Pandas or Excel? I've been using Excel a longtime and think it's fantastic, however, even though I'm only starting to use Pandas I can see how it could be useful far beyond the scope of Excel.
I'm thinking that Pandas could be better for my workflow, but for another person the reverse could very well be true.
Are you saying you're impressed with Pandas or Excel? I've been using Excel a longtime and think it's fantastic, however, even though I'm only starting to use Pandas I can see how it could be useful far beyond the scope of Excel.
I'm thinking that Pandas could be better for my workflow, but for another person the reverse could very well be true.
Soldato
Impressed with pandas and python personally. Problem is I do not get a lot of time to jump deeper into it.
Regarding excel, I am still baffled why even experienced users that do not use the Power Query/Pivot continue with formula and macros, but maybe they stick to what they know.
- Joined
- 5 Sep 2020
- Posts
- 296
[QUOTE="gpuerrilla, post: 35273813, member: 39819"
Regarding excel, I am still baffled why even experienced users that do not use the Power Query/Pivot continue with formula and macros, but maybe they stick to what they know.[/QUOTE]
I was thinking about seriously about using Power Query. I'm slightly nervous about MS changing licensing; I did some work in Flow/Power Autimate then some of the components went from free with the Office 365 licence to a different Tier that would have cost a lot more money and I didn't feel I could make a business case for.
The Pandas route promises to be very powerful and flexible; I'm looking forward to what I read is SQL-like functionality and much more.
Regarding excel, I am still baffled why even experienced users that do not use the Power Query/Pivot continue with formula and macros, but maybe they stick to what they know.[/QUOTE]
I was thinking about seriously about using Power Query. I'm slightly nervous about MS changing licensing; I did some work in Flow/Power Autimate then some of the components went from free with the Office 365 licence to a different Tier that would have cost a lot more money and I didn't feel I could make a business case for.
The Pandas route promises to be very powerful and flexible; I'm looking forward to what I read is SQL-like functionality and much more.
Soldato
Yeah I can understand some of your point, there are features M$ bring out such as XLOOKUP or intellisense within PQ which you only get if your a O365 subber, whereas in my work license which is O2019 you dont get that which is another annoyance to force you into some tier I agree.
Some of the Python library's still need the Excel model and thus the application installed. This seems to be a way of writing less VBA and doing it in Python instead. But VBA can still do much of the same stuff.
Microsoft seems to be pushing People to Power Bi especially if you distribute via Microsoft Cloud technologies. They are trying and failing to kill VBA on the desktop. Most People barely scratch the surface of VBA.
That said if you live in Python and have specific use cases that have dedicated libraries like pandas then it make sense to conciliate there rather than VBA.
Our crowd decided on tableau. Which is yet another proprietary transformation of data which they all struggle with. But the data always comes from and ends up in Microsoft product of one sort or another.
Microsoft seems to be pushing People to Power Bi especially if you distribute via Microsoft Cloud technologies. They are trying and failing to kill VBA on the desktop. Most People barely scratch the surface of VBA.
That said if you live in Python and have specific use cases that have dedicated libraries like pandas then it make sense to conciliate there rather than VBA.
Our crowd decided on tableau. Which is yet another proprietary transformation of data which they all struggle with. But the data always comes from and ends up in Microsoft product of one sort or another.
I've done a lot in VBA over the years, written large systems in it. Where I am now they aren't interested. I automate my own stuff. But they seem to delight in creating hugely manual and complex processing of data. I'm looking at one at the moment and it's got over 100 separate manual steps. VBA, SQL, SSIS, XML, BAT, CMD files. Manual File copying etc. Then they run it twice only modifying it slightly differently. I'm going to collate it all in VBA.
But it would ideal project to also parallel in it Python. Just for the optics.
But it would ideal project to also parallel in it Python. Just for the optics.
What do you mean by this? What's the context?
I'm not sure what you're asking.
Pandas is specifically for data analysis. There are better tools than Excel for Data Analysis and Data Visualisation.
Its a minor subset of what Excel is used for. You can't replicate what excel is used for solely with Python and Pandas. (unless thats all you do with it)
Some of the other Python Libraries allow you do the stuff that Pandas can't do. But you need excel installed to use them.
I'm open to correction. I'm only going by the tutorials I've watched. I was hoping I could replace Excel but I'm realising I can't.
Last edited:
Ah sorry; I'm familiar with what python/pandas and excel are separately from each other but not so much combining them. You mention you can't replicate other things excel does and some need to have excel installed still so could you provide some context - as in what python modules and what is the context of what you're trying to do that still requires excel/can't be done in python (even just generally)?
Are you doing something with an old spreadsheet with a bunch of formulas, VBA etc... and then doing something in pandas with data that has been processed in excel for example?
Ultimately I'm outputting hundreds of Excel workbooks that have precise formatting and interactivity for the end user.
The data is coming from many sources, SSIS, SQL, Excel, Cobol, XML etc. I'm also reusing some of someone else's VBA, as I don't see the point of recoding someone else's complex business rules.
I'll refactor it to remove as many of the manual steps as possible and add interactivity, removed hardcoding. Should bring the number of manual steps from 100+ to under 10, and will run mostly unattended.
But thats largely irrelevant. it was suggested you could replace excel with Python and Pandas. But its a different feature set. Its a bit like asking can you replace a Knife with a fork. In my opinion.
Though perhaps for 90% of users, they use a fork and a knife the same way (poor analogy) as they only use that subset of features anyway.
The data is coming from many sources, SSIS, SQL, Excel, Cobol, XML etc. I'm also reusing some of someone else's VBA, as I don't see the point of recoding someone else's complex business rules.
I'll refactor it to remove as many of the manual steps as possible and add interactivity, removed hardcoding. Should bring the number of manual steps from 100+ to under 10, and will run mostly unattended.
But thats largely irrelevant. it was suggested you could replace excel with Python and Pandas. But its a different feature set. Its a bit like asking can you replace a Knife with a fork. In my opinion.
Though perhaps for 90% of users, they use a fork and a knife the same way (poor analogy) as they only use that subset of features anyway.
The issue is not wanting to need excel (installed) in the solution. But I have to have it it, I might as well not re-invent the wheel with what's already written.
The example I watched used this. https://docs.xlwings.org/en/stable/installation.html
Maybe you can link to a library that can do "everything" excel can do without excel being installed. Because thats what I want. I'd prefer not to have to have Excel in the solution.
I don't think Pandas is it. though I'm open to be convinced otherwise.
The example I watched used this. https://docs.xlwings.org/en/stable/installation.html
Maybe you can link to a library that can do "everything" excel can do without excel being installed. Because thats what I want. I'd prefer not to have to have Excel in the solution.
I don't think Pandas is it. though I'm open to be convinced otherwise.
Last edited: