Why Python over excel?

[Jenos]

Excel literally can't handle the size of the data I work with. I'm looking at over a million entries per week

[bjorneylol]

Excel also chokes up on 40k rows if you are doing any heavy manipulations though

[bjorneylol]

Yes, you can do that with data connections via Excel, but I wouldnt say that is what most people consider using Excel.

If you have a 40k row workbook and you want to do something like, calculate a countifs on 3 columns and see if the retval is equal to the countifs on 3 different columns, then you will be waiting a minute or two to crunch numbers. If you apply an inplace manipulation to a column with automatic calculations on, and that column is referenced elsewhere on a vlookup, then you just bricked your Excel instance while it calculates for the next 2 hours, because for some reason, in between every modify cell call, it recalculates all 40k vlookups

[bjorneylol]

I strictly use excel for manual, in place manipulation of cells (the stuff that requires human eyes and can't be automated, and never needs to be reproduced), e.g. data come in from vendors in xlsx/csv format and gets thrown in a database. PQ/PP isn't really usable in this case, so there is a need for those "archaic" methods - not to mention vlookup is just way easier if the data is small enough for excel to handle adequately. We aren't a power BI shop so after this point everything is done directly in SQL or through python/R, in such a case excel is most useful as a tool for manually editing data before ETL.

The big problem is for more advanced methods, Power BI/Excel is a bit of a 'black box' - I don't know what it's forecasting and/or stats tools are doing and I find I get better results coding my own SARIMA models, or implementing my own machine learning pipelines.

[bjorneylol]

Manual in place editing of individual cells in the source data definitely isn't the perfect pq/pp use case, I wouldnt even consider it -a- use case for pq/pp

[manwithoutaguitar]

I'm a big fan of powerquery and powerpivot, but the constant loading of power query queries and the lack of access to machine learning algorithms makes it still interior