r/learnpython • • Apr 13 '22

What is difficult in excel but easy in Python?

If you were to introduce an established excel user [non VBA] to Python, what would you suggest?

What would amaze them? In as few a lines as possible?

404 Upvotes

241 comments sorted by

64

u/Allmyownviews1 Apr 13 '22

This is just a quick list as I am in the midst of writing a report and took a procrastinator break.

  1. Making changes to cells on certain criteria

  2. Algorithmically adjusting data rapidly

  3. Setting up simple easy to edit scripts for rapid analysis of raw data to final product

  4. Handling larger datasets

  5. Rapid pivot table sorts

  6. Finding best fit curves for non normal distributions

16

u/Allmyownviews1 Apr 13 '22

I guess my main experience is that there are times I jump eagerly into excel to perform tasks that are faster there. But in terms of speeding up workflow in a clear manner and then producing analysis to product routines end to end including pdf report output directly to online folders and web pages.

3

u/SirGeremiah Apr 13 '22

Can you point me to some info on #5? This looks like something I could make use of often.

5

u/Allmyownviews1 Apr 13 '22

So I have been performing lots of groupby to create new sorted dataframes and then pivot to reshape the frame. Then doing some iteration counts to derive frequency and durations of data above or below given thresholds. I consider myself expert level at excel and would struggle to get the whole package done and then to be able to run this through a series of million data row csv files made me take the leap to Python.

I suggest taking a look at some of the feature of numpy and pandas libraries which have these functions already laid out.. and the. Use matplotlib library to produce some stunning charts.

This is a link to pivot tables function:

https://www.w3resource.com/pandas/dataframe/dataframe-pivot.php

3

u/SirGeremiah Apr 13 '22

Thanks!

Yeah, anything with the phrase "a million data row" is just a hot nope for me in Excel, given any other choice, at all.

1

u/frazorblade Apr 13 '22

Would you not use power query to achieve the same results in Excel?

Is it easier in your opinion to code the pivot steps rather than “right click unpivot other rows” in PQ.

2

u/Allmyownviews1 Apr 14 '22

I do use PQ if a data is small and even to check that the code is operating for correctly if in testing phase. But it struggles to be as efficient when part of a regular workflow. I also find PQ to go far slower than in scripted in Python. I was at a client office last month and tried to do my analysis in their normal software (excel and power bi). It struggled with the volume of data and I eventually did the data wrangling in Python and then passed back to excel to generate plots they were expecting to see. I could the hand them a pdf of the Python script and method in jupyter notebook format to allow them to repeat if needed.

2

u/frazorblade Apr 14 '22

Yes 100% Excel/PQ is much slower which is a major gripe of mine, but it works for smaller datasets which I’m used to dealing with.

I feel once you’re at the stage of large data (100k-1m+) is when you can afford to look into the most efficient method and I agree python/R is the way forward

→ More replies (2)

1

u/JingleBellea May 01 '22 edited May 01 '22

Sounds very interesting! I would love to know more about your workflow. Would you be up to connect? I will send a private message. :)

1

u/[deleted] Apr 26 '22

Hey I was actually just coming to this sub for help with number 1 👀

I have an excel sheet with a mutations column. In the column, each cell can contain any number of mutations in the format “173C” or “16235T” and so on. Some cells have 10 or so mutations, some have up to 30, and there are ~400 rows of data.

Some how there’s at least one instance of a mutation that just says “DNA” and I know there should be a simple way to edit within cells based on a grep of some sort, but it’s completely evading me.

Is there a good way to do it? Just parse that one column as a series of strings and remove the string “DNA”? (Eventually I want to remove anything that doesn’t match a regex I’ll come up with later, and I know I can just search the spreadsheet and do it manually but I want it online with the rest of my program)

29

u/lukey_dubs Apr 13 '22

it’s very hard to download pdfs and scrape the data with excel, and same with web pages. excel can’t load pages and pull data off to be processed.

4

u/lovestobitch- Apr 13 '22

I often use Able to Extract and convert client’s data from pdf into excel. Sometimes some of the data at the bottom isn’t clean though if it’s a huge file depending upon how they created the file.

4

u/dparks71 Apr 13 '22

Camelot for tables, pypdf2 for searching/bookmarking, pdfplumber for text extraction are how I've gotten the best results. Fuck the .pdf standard so hard, it's such a nightmare to get a decent education on it.

2

u/ProdigalM Apr 13 '22

Very much agree. I'd recommend Pymupdf for scraping pdf files.

1

u/tomtomato0414 Apr 13 '22

well you can, but you wouldn't want to

17

u/ES-Alexander Apr 13 '22

What to show them depends a lot on why you’re showing them things. Is the idea to show them that programming languages have capabilities beyond what excel can do, or just that they can do the same things as excel but better?

Off the top of my head, Python (and most other programming languages) support iteration and lazy evaluation, data nesting beyond 2D, abstraction / functionality reuse (functions, objects, libraries), interactive graphics and plotting, console logging and progress displays, and Excel is poorly suited to all of those.

Then there’s pure efficiency and performance - excel can be useful when interpreting and working with data that’s in comprehensible quantities, but it starts to fall apart when data sets expand or analysis gets more complex, and looking at the raw numbers becomes impractical and unhelpful. If you multiply two 100-million element numpy arrays together it will take about a second. Doing the same in Excel would be glacial, if it was even able to load the values to start with. Then there are libraries like Pandas that allow you to efficiently perform complex analysis and queries.

For frequent analysis tasks, it’s very possible for a Python program to open a file, process it, generate some plots / a report, and be done before excel has even finished loading.

7

u/[deleted] Apr 13 '22

[removed] — view removed comment

7

u/ES-Alexander Apr 13 '22

Well there ya go, learn something new every day :-)

Bit of a tangent here, but I think fundamentally Excel is about looking at data, while programming is about documenting a process (which then gets handed off to the computer to perform).

Elaborating on that, in Excel the focus is on the data at each step of a process, and (as I understand it) it's primarily designed around viewing and interacting with data values. That makes it super convenient to look at a (smallish) bunch of data and go "why is there a 3 there?", and have merged and/or formatted cells that show things in a particular way, but conversely it's then not as well suited to analysis at scale, or performing other more general purpose tasks.

In programming the code is a deliverable, for which the focus is on what the steps are, and how different parts are connected to each other. Well-written code makes it easy to change the process (e.g. change the order things occur, swap out one part for another), while also connecting the process to other programs and processes. That makes it less likely to spawn potentially insightful questions like "why is there a 3 there?", but comes with several benefits of power, scalability, and generality, including excellent tools for creating and applying metrics that find interesting data values for you, and potentially even the reasons for why they are as they are.

It's also interesting / worth noting that programming languages can do everything Excel can do, but the same is not true in reverse. That's not to say that Excel isn't useful, or better at some things than most code would be, but programming just has more options and capabilities when solving a problem, and can solve more types of problems. It generally also has at least reasonable equivalents for the things that Excel is actually decent or better at.

2

u/[deleted] Apr 13 '22

[removed] — view removed comment

1

u/dparks71 Apr 13 '22

Jupyter notebooks...

1

u/SirGeremiah Apr 13 '22

Wait, what?? How did I miss that?

1

u/[deleted] Apr 13 '22

[removed] — view removed comment

1

u/SirGeremiah Apr 13 '22

I used to teach XL classes - I guess I haven’t kept up since that ended a couple of years ago.

16

u/naghi32 Apr 13 '22

Not changing random numbers to date format randomly !!!

14

u/Zixarr Apr 13 '22

If an optimist sees the glass as half full, and a pessimist sees the glass as half empty, how does Excel see the glass?

2-Jan

4

u/SirGeremiah Apr 13 '22

It’s a feature.

(I actually had this happen across an entire file while teaching an Excel class. I still don’t know what the hell happened.)

2

u/naghi32 Apr 13 '22

I feel you, I had this happen across a file that was to be imported from a system to another one, over 1m records screwed.

1

u/naghi32 Apr 13 '22

I feel you, I had this happen across a file that was to be imported from a system to another one, over 1m records screwed.

2

u/SirGeremiah Apr 13 '22

Gee, that sounds lovely. I'm sure you had a bright smile on your face for days from that. /s

12

u/Spfoamer Apr 13 '22

I deal with large datasets of acoustical measurements - for example a few weeks of 1-second samples. If I want to do 1-min or 1-hour averages, or calculate other statistics, this is WAY easier with Python. Excel would choke just trying to open the file.

20

u/ahuuho Apr 13 '22

Thanks for asking this question. I am also like you, want to see the answers from people who were excel experts before picking up python

12

u/dupz88 Apr 13 '22 edited Apr 13 '22

I wouldn't even consider myself near expert level in excel, I had some macros I used but spent a lot of time cleaning raw data from our client on a weekly basis. The file was 300k rows, and received updates daily. Every time they had a system error, the report would have about 4k new duplicates added. I knew about python and had wanted to look at starting to learn it for a while, but never had the time to just start. I have a very basic knowledge of programming.

I got frustrated with the 3 hour cleaning of this excel every week, and spent a few days installing anaconda, spyder and then pandas. I started "learning the basics" (googling each step) and testing on our data. After a week, I had automated most of the cleaning and started looking at a few extra nice to have calculations.

Its been a month and now instead of spending 3 hours cleaning that file, I have a script that cleans the file in 5 minues (3mins is importing and exporting to xls). Its 400 lines worth of data cleaning, calculating time differences (excluding weekends, holidays and non business hours between dates) I now also import 5 other datasets, join up the data and export 3 split files for our data cleaning and also our PowerBI reporting.

Its also way more accurate than before since there are no slip ups in processes while waiting for excel to handle the sorting (with duplicates highlighted).

Its been amazing and I just want to automate everything I can now.

1

u/7Seas_ofRyhme Jun 08 '22

I have a script that cleans the file in 5 minues

Do you mind sharing your script ? Interested to learn how u did it, cheers.

7

u/frazorblade Apr 13 '22

I’ve used R and Python and understand they’re wonderful at data manipulation/transformation, but when we’re discussing Excel are we also taking Power Query into account?

My default data transformation tool is PQ because the UI is very intuitive and you can see your results immediately without having the query the results. Some of the one click options like:

  • keep all rows with errors

  • fill up/down (fills nulls with value from above/below)

  • pivot/unpivot with one click

  • intuitive groupby UI (including the ability to nest)

  • intuitive merge (i.e. joins) UI

If I’m exploring data I feel like PQ gives me very quick results but also I’m not at the same level with python/R and I recognise that.

1

u/therealkdog Apr 13 '22

Great question

10

u/tagapagtuos Apr 13 '22

Just to add:

  • time to open Excel and do stuff > time to type python filename.py in the command line. You can even schedule these tasks, or even create your own cli app to automate your automation.

  • creating your own classes is cool.

In as few a lines as possible?

pandas multi indexing

19

u/ahuuho Apr 13 '22

If someone could demonstrate these over a screencast, doing it in excel versus achieving it through python, so many would ❤ it

6

u/SirGeremiah Apr 13 '22

I’d even pay a few dollars for that education.

4

u/RajjSinghh Apr 13 '22

If you have any of these topics in mind, I can implement them in excel and python for you.

4

u/AungThuHein Apr 13 '22

Any YouTube videos like this around already?

1

u/jppbkm Apr 13 '22

Check out the python mitosheets package

9

u/canopey Apr 13 '22

How about an example?

I remember that I had to join two tables using an index match in Excel (had to attach the state name to every row for 3000 rows). I don't remember the exact number of rows but it was A LOT. Through index>match, Excel would execute the function but it would take a loooooong time for it to complete (plus my macbook would start firing of its rocket engines). You could tell it was taking a lot of processing power.

Then I went ahead and tested the same task in Python (pandas) using df.left_join and Python literally spat out the results in <3 seconds. That's when I understood the power of Python vs. Excel.

3

u/frazorblade Apr 13 '22

Power query in excel can do joins…

0

u/canopey Apr 13 '22

I didn't say excel could not do joins

5

u/frazorblade Apr 13 '22

But your example you chose to compare index/match vs a python join

A better comparison would be PQ join vs python join…

3

u/Ben77mc Apr 14 '22

100%. Too many people think they’re advanced with Excel but don’t even have a clue about PQ/M language/DAX.

Learning these transformed my work more than any other thing could have done - I can automate very complex tasks and am seen as an excel god at work.

7

u/ConfusedSimon Apr 13 '22

Maintenance. I've seen too many buggy excel models with nobody knowing how they work.

6

u/synthphreak Apr 13 '22

Working with integers-as-strings is a breeze with Python but a major PITA with Excel.

If your .csv has string values like "000123", Excel will parse that as numeric data and convert it to 123. This "intelligence" will cause downstream components to break unless you add the leading zeros back in.

4

u/frazorblade Apr 13 '22

Power query: import csv -> change type to text. If for some reason you’re still having trouble then Text.PadStart([string], “0”, 6)

5

u/perchslayer Apr 13 '22

You are meaning to ask the right question, but missing the mark. Totally understandable, however.

Here is reality: the folks who cling to tools like Excel do so for reasons apart from the software itself. It's not about the program, but the data.

Thus, they perceive that their own personal "value" is buried in the data they "own" and refined or derived on their desktop. And so they will keep it right there until hell freezes over.

They are uninterested in shared workflows, data, or anything else...but will never say so.

With that in mind the answer to your question, broadly, is data sharing.

But you will never overcome the friction by marketing the virtues of Pandas or Python. You are barking up the wrong (money) tree, Virginia.

6

u/frazorblade Apr 13 '22

You might be stuck viewing Excel as a hokey spreadsheet from a decade ago, they’ve made strides to improve the software.

The features that are easily overlooked are very powerful and intuitive in Excel these days, here’s a brief list:

  • Power Query does data manipulation very competently and has an intuitive UI to help beginners

  • Power Pivot allows use of DAX and star schema relational tables to build extremely powerful pivot tables

  • the ability to share a workbook with all of these features baked into the core software can’t be overstated

  • Excel is so ubiquitous it exists on the vast majority of business machines, it’s extremely accessible unlike running python scripts

I’m not trying to take a dig here just stating that Excel is evolving and there are many valid use cases for it, some of them directly compete with python

1

u/perchslayer Apr 14 '22

Thanks for updating a neckbeard boomer geezer like myself. With all due respect, however, it doesn't address in any way the point I was making.

5

u/frazorblade Apr 13 '22

A lot of people seem to be missing some of Excels newer features so as someone who is highly fluent in Excel I’ll explain what python does better without going into things that can easily be achieved in Power Query:

  • Charting: plotly for example, makes beautiful charts that are highly customisable and interactive - if I could convert all my Excel dashboards to plotly pages I would be in heaven

  • Automation: I’ve built a simple python script that can open hundreds of Excel workbooks update some basic values, trigger a SQL function (within PQ) refresh all tables/charts and save a named copy of the file. VBA and VBS can achieve it too but it’s way faster and simpler in python

  • Data manipulation: I can install pandas, numpy and a plethora of other packages to do high level statistical modelling and data science that Excel, Power Query and VBA simply don’t have the chops to pull off.

Now what some of you are stating that Excel can’t handle isn’t necessarily true these days. Python May still be the best option but Excel is competent:

  • Data manipulation: Excel + Power Query can handle a lot of data (millions of rows but speed is not its forte), issues with CSVs and data types is easily handled here, we shouldn’t be discussing Excel mismanaging dates etc because it’s less of an issue now. Power Query blurs the lines between SQL and python, there’s a lot of shared functions/techniques here.

  • Data Analysis: DAX (which is also used in PowerBI) creates dynamic measures which can be useful in pivot tables, these are often specific use cases when presenting interactive reports to clients/colleagues. Sometimes a big interactive Excel pivot table is exactly what you need.

  • Quick and dirty analysis: personally it’s fast and simple for me to open some data, slap it into a pivot table, get summed figures and maybe build a basic chart to visualise a trend - this could take me under a minute on a zoom call when someone asks a question. I can do all of that with one hand on my mouse if I had to.

1

u/7Seas_ofRyhme Apr 14 '22

Thanks for sharing your thoughts on this.

In summary, Power Query, DAX and VBA are the key topics one should master for Excel ?

2

u/frazorblade Apr 14 '22

Absolutely

2

u/7Seas_ofRyhme Apr 15 '22

Gotcha, time to start learning Excel then, the reason is because I noticed most entry-level jobs require Excel as a fresh grad. Besides, I generally do not use Excel much as compared to SQL and Python during my studies. Hence, I think I gotta start from scratch and find some good resources on Excel (any suggestions or resources on r/excel is a great start ?)

3

u/frazorblade Apr 15 '22

Yeah it’s mostly people asking for help over there. There’s some fantastic YouTube channels though

  • SQLBI for DAX/PowerBI

  • ExcelIsFun for general quirky techniques for Excel and Power Query

  • Chandoo for Excel

You won’t need to know VBA unless you’re getting into some very technical stuff

2

u/7Seas_ofRyhme Apr 15 '22

Thanks for sharing some recommendations for YouTube channels on Excel/Power BI, I'll check them out next time.

You won’t need to know VBA unless you’re getting into some very technical stuff

Noted, I guess I'll focus on Power Query, then DAX for Excel

4

u/Hextall2727 Apr 13 '22

The simplest answer I give* is updating new figures and analyses when new data comes in. I'm and environmental scientist/consultant, and we're constantly receiving new data. With python, a new plot is generated with the push of the "run" button. With excel, there's a lot more manual labor needed to update figures. The tradeoff is usually a higher upfront labor cost getting the python set up relative to the first excel plot.

Another answer is generating hundreds of figures or tables with teh push of a button. we might have several hundred environmental samples that we have to crunch the data on... doing it in excel is cumbersome and often results in errors. When new staff start using python, we train them to use Excel to QC their python results. It quickly becomes apparent trying to recreate one dot on a figure in excel why python is so much more useful.

*I have to give this explanation to project managers in my own company when I get pulled into their teams for data analysis. My company formed from a merger of two companies... one a nerd based environmental modeling company (where I came from, and where coding is a basic skill... although we started with IDL and transitioned to python) and one a typical engineering company focused on dredging.

7

u/AdministrativeFig948 Apr 13 '22

Hey guys, I'm the excel specialist ready to be amazed!

3

u/Electrical-Ad-1798 Apr 13 '22

One thing I recently need was a vlookup on two columns. My spreadsheet had "category" and "type" columns for which there was a computed type in the third column. For a new entry we wanted that computed type for a given category and type. It could be done in Excel (I think) but it was a breeze in Python. To be fair it would have been equally easy with VBA I guess.

3

u/Cheddarific Apr 13 '22

Multiple layers of if statements and working with arrays can become very tedious and visually challenging in Excel, where you either need to create giant formulas with dozens of parentheses or else only see one snippet of a multi-cell formula at a time. Python handles this in a much simpler (to write and to read) way. I can’t understand my 4-line formula in Excel after a couple years without looking at it. I can understand my 100 line python code perfectly.

Recursion (performing one or multiple actions repeatedly until you reach a desired result) is 1000 times better in Python.

Creating and running new formulas experimentally, then turning that formula off.

3

u/analyst_2001 Apr 14 '22

Python is a high-powered option for data science and analytics, while Excel's key benefit is its simplicity and ease of use. Because python can handle enormous volumes of data, automate reporting, and connect to databases from the notebook smoothly, it is a versatile and powerful tool for analysts working in data-driven enterprises and amateurs wishing to develop their analytical abilities. Python has also been recognized as the most popular language since 2016 because of the large number of open-source statistical libraries available and the active community members who contribute to making Python even better.

2

u/jafner007 Apr 13 '22

Quickly plot large datasets and then zoom and pan for review and save portable plots (as images or html).

Pandas from Excel, plotly express, done. (Of course, you can make it more complicated, but you don't have to)

2

u/jwink3101 Apr 13 '22

Nested control logic. It is not impossible by any means but excel forces you to either define intermediate cells or nest if statements. So much easier in Python

Monte-Carlo samples are also tough. Doable but really not very easy

2

u/IlliterateJedi Apr 13 '22 edited Apr 13 '22

Really good plotting with Pandas and Seaborn. Even without Seaborn, a simple df.plot(kind='hist') or whatever is a game changer for exploratory data analysis. df.describe() to get info about the whole table is also great. I used to be an Excel power user and I can hardly use it anymore after learning Pandas.

Edit: I highly suggest this book if you are wanting to learn Pandas and matplotlib/seaborn. It's an excellent resource. Packt books are frequently pretty 'ehhh' but this one is actually good.

Extra edit:

Being able to analyze and work with a data set without actually editing the raw files is extremely valuable. Any time I open an Excel file and manually change things, it just makes me cringe. It's better to be able to process your data and save a new file with the changes without actually touching the original source.

This also makes processing data a lot faster because instead of manually changing things in Excel, you just run your script or file-read-in-function.

2

u/ghostfacekhilla Apr 13 '22

Bunch of people in here need to learn power pivot and power query. I've never convinced a basic business user to learn a programming language, but I've built and handed off massively important power query based stuff and then because it's GUI and excel they learn it and become self sufficient.

2

u/d_Composer Apr 14 '22

.fillna()

2

u/asterik-x Apr 14 '22

The file organization

2

u/Ok_Operation6364 Sep 05 '23

I know this is an old post but great question by OP

3

u/Weissenberg Apr 13 '22

It’s horses for courses really.

As others have touched on, python & pandas is great for handling & manipulating massive datasets. Or automating simple tasks.

A basic one that might be applicable for a few people is collating multiple workbooks / sheets into one. Granted you can do it in power query, but it’s still quite tedious.

Say you’ve got a folder filled with daily reports & you need to combine them into one big table so you can pivot or graph it over the month.

You’d glob the folder, append to a data frame with a loop & then export the frame to excel.

Edit - One thing to bear in mind. Don’t automate someone out of a job! By all means, show them how they can enrich their workflow, but don’t create something that makes them redundant.

1

u/Xdbao Apr 13 '22

Creating dummy variables. Have to manually create new columns for new dummy variables in Excel I guess.

1

u/SirGeremiah Apr 13 '22

What do you mean by “dummy variable”?

1

u/unhott Apr 13 '22

Import Pandas + seaborn

Load an existing excel file or csv Plot something a few different ways.

This won’t be many lines in python and even with defaults, seaborn produces pretty plots. Or just show them the gallery.

1

u/dutchmaster77 Apr 13 '22

There are a lot but can I just say the biggest for me is the variable types. Constant headache with Excel. I have recently been working on putting a script together automating an analysis done in Excel for the first time in two years and it’s driving me crazy. I don’t know how I coped with so much Excel and VBA in my last job

1

u/VanshCodes Apr 13 '22

Everything seems to me too easy in python rather than excel. of course CSV is a must.

xlsx is damn obnoxious and difficult to use,

1

u/Rj_LM Apr 13 '22

Creating websites

1

u/Xzenor Apr 13 '22

Running it on Linux and FreeBSD

1

u/BobDope Apr 13 '22

Holding onto your will to live

1

u/erm-waterproof Apr 13 '22

I find making pivot tables and additionally creating simple calculated columns from the pivot table easier in excel than in python

1

u/kilroy_wh Apr 13 '22

I agree for first steps it is easier. However in excel you need these things too (besides maybe an environment)

1

u/siddsp Apr 13 '22

imo pretty much everything from my experience. Whenever I want to apply a function to some data to get x and y values for plotting, I never use excel although that's what I used to do before I knew Python that well.

1

u/ledepression Apr 13 '22

For some reason I find everything in Python easier than Excel

1

u/p1zz1cato Apr 13 '22

floating point precision with powers of large (or very small) numbers

1

u/TakeOffYourMask Apr 13 '22

Making a histogram

1

u/[deleted] Apr 13 '22

Regex.

Scraped by for work with some VBA but could make my life so much easier of I could use some Python for a couple of tasks.

1

u/omgouda Apr 13 '22

At work they had an excel tool that basically you would paste some raw data in and it would flow thru a series of other tabs until you had an output. The problem is that the raw data dimensions were random hence the subsequent flow of data needed to be adjusted. This was incredibly messy. Of course, this could have been solved using dynamic ranges in excel but it would have been too much trouble to go through the entire process again and create dynamic ranges. Since the tool wasn’t built with dynamic ranges in mind, this created some other confusions.

A simple way to get around this would be to use pandas in python and use functions to get the desired output.

Way faster and much easier to debug if ever there was an issue.

1

u/TtamMountain Apr 14 '22

If you're a super vba user, then the reason is less obvious but still obvious. There's more documentation around python, making it easier for a beginner to handle something that would be rather difficult in excel.

A few examples of things easy in python, but difficult in Excel/VBA

  • Integrating with external API

  • Multi-index matching

  • Complex data structures

  • Modularity The list goes on.

The main pros about Excel in comparison to Python are:

  • Charting

  • Ease of Use

  • Widely used -- virtually every business uses excel (or very similar tool in some fashion)

Of course, you're question depends on what you want to use it for. :)

1

u/[deleted] Apr 14 '22

Don't show any development ie. have your scripts already made and show end results.

Take multiple messy excel spreadsheets, clean them with python, and save them to a "clean" directory. They could even be the same messy spreadsheet. Make them +100,000 rows for good measure.

1

u/backbishop Apr 14 '22

Try making subplots 🙄

1

u/ZEUS_IS_THE_TRUE_GOD Apr 14 '22
  • Code versioning

Do I need to say more?

1

u/Mondoke Apr 14 '22

For me the main advantage is having the compete process you do written down and having the program to being able to run on different files with minimal or even zero changes to the code base (if you use stuff like argparse) write a script that does that tedious process you do every month, and then just run it in seconds, without having to check if you've done all the steps. Plus, the logic can be as wild as you wish, which is not usually the case with the limitations on the logic Excel is usually capable of.

Outside of this, there are two other things that are just beautiful to have python do for you.

First, the management of raw data. Is your input data a zipped folder with several files inside of it? Just have the zipfile module handle that for you. Do you need to access to information in your company's postgres database? No problem, buddy, psycopg to the rescue. Do you get an obscure file format Microsoft doesn't even know about? Pandas may have a read_x function to open it and then use the information just as if it came from an innocent csv.

And the other extremely cool thing that's not easy to do in excel is the ability to generate a lot of similar files coming from the same information. A chart for each month, or for every day, or even better, one for each subcategory in your dataset. And all of them with matching formats. Or if you have to break a huge file on 100s on individual tables to split your work, send to your clients or whatever you need. Just write the correct loop and see the files appearing in your folder. You can even generate the files already zipped and ready to be sent by email (or have python to send them for you).

2

u/7Seas_ofRyhme Jun 08 '22

write a script that does that tedious process you do every month

By any chance, do you mind sharing the script ? Interested to learn how u did it, cheers.

1

u/[deleted] Apr 14 '22

Anything involving the web (except maybe azure stuff)

1

u/Vile_Vampire Apr 14 '22

Handling more than 1mm rows

1

u/nwatab Apr 14 '22

Show a chart of 20,000 pairs of (x, y). Excel would freeze that's why I learned Python 10y+ ago.

1

u/NoFaithInThisSub , Very Beginner. Apr 14 '22

reading a csv without opening the file?

1

u/Sirico Apr 14 '22

Loading a WB and your program quickly

1

u/QultrosSanhattan Apr 14 '22

Excel is very good for one time calcuations.

Python is good for repeating the same calculation over and over again.

1

u/rajrup_99 Apr 20 '22

Do you know , quantum entanglement calculation can be done in python's 3 lines of code?

one of IBM's video I saw that. I was very proud that I am also a python developer

and this is not from desktop environment to website from ML to mathematical quantum computation python can do anything , this much super flexibility python has.

Not only that if you give enough time you can made an application which is even better than excel using python. I love Python

1

u/vvndchme Apr 23 '22

df.corr()

1

u/SirDantesInferno Apr 25 '22

Nested loops in searches

1

u/Shady_Hero May 01 '22

like microsoft excel?

if so then i'd assume coding

1

u/Puzzleheaded_Bee_486 May 03 '22

Best ways to learn Python for data science?

1

u/keseymour May 03 '22

Take a spreadsheet with one column that contains delimited data and add a row with all of the same information in it for each of the items in the cell.

From

1 dog,cat,pig <lots of other columns>

To

1 dog <lots of other columns>

2 cat <lots of other columns>

3 pig <lots of other columns>

0

u/CatOfGrey Apr 13 '22

What would amaze them? In as few a lines as possible?

So you built the spreadsheet around the data. Now, you are going to get new data.

Imagine if Excel just 'looked at last month's spreadsheet, and re-created it automatically'. Well, that's Python/Pandas.

2

u/ghostfacekhilla Apr 13 '22

This isn't a problem with excel. You can easily set up power query to pull data from a sql database or various other sources and blend and manipulate those queries into your necessary datasets.

1

u/Ben77mc Apr 14 '22

Exactly this. 90% of the reporting spreadsheets I created at my old job were directly linked to SQL queries in power query and all I needed to ever do was refresh. So powerful.

0

u/PaddyIsBeast Apr 13 '22

It's hard to think of a thing excel is better at, apart from being noob friendly.

0

u/[deleted] Apr 13 '22

[deleted]

3

u/imgprojts Apr 13 '22

Python has dash plotly.... Much better than Excel graphs.

0

u/[deleted] Apr 13 '22

[deleted]

3

u/SirGeremiah Apr 13 '22

Agreed. The comparison is best made to Excel+VBA. Otherwise, it’s like asking how Excel is better than Access (not better, just suited to different tasks), when the question should be when is SQL Server better than Access.

3

u/frazorblade Apr 13 '22

As I’ve mentioned a couple of times a more apt comparison is Excel+PQ+DAX+VBA (which all exist out of the box) and we’re starting to blur the lines a bit more.

1

u/SirGeremiah Apr 13 '22

I don’t even know what two of those are. LOL

3

u/frazorblade Apr 13 '22

That’s one of the issues I’ve identified in my separate post. I know we’re in a learn python sub but there are use cases for Excel. Some people here still view it as a 2D spreadsheet.

3

u/SirGeremiah Apr 14 '22

Agreed. And even as such, it does a lot we don’t need to program for. I’ve helped clients with small (and occasionally medium) data issues for 30 years. Only occasionally has Excel not easily served the need.

2

u/Bluegenio Apr 13 '22

I'm an Excel user who incorporates Pandas with zero VBA knowledge or experience.

0

u/[deleted] Apr 13 '22

[deleted]

1

u/Mondoke Apr 14 '22

I'm not bluegenio, but I'm kind on the same track as them. I went from Excel to Python without having learnt vba. Some of the stuff I do is dealing with zip files (both to read and to write), manage geographical information (distances, coordinates to cities, etc), read from a postgres database, and generating a lot of files with the same script.

0

u/[deleted] Apr 13 '22

[deleted]

1

u/RemindMeBot Apr 13 '22

I will be messaging you in 19 hours on 2022-04-14 17:01:25 UTC to remind you of this link

CLICK THIS LINK to send a PM to also be reminded and to reduce spam.

Parent commenter can delete this message to hide from others.


InfoCustomYour RemindersFeedback

0

u/[deleted] Apr 13 '22

[deleted]

2

u/trippygg Apr 13 '22

Your last part is right but everything is wrong. Those formulas you mentioned aren't meant to be joins.

Two ways join, power pivot or power query (heck you can even union). Power query in particular allows you to this with different outside data sources (SQL, Other spreadsheets, etc) automated fashion. Power query is low code as well and doesn't change the workbook format like macros.

2

u/Kerbart Apr 14 '22

Powerquery might not offer full SQL functionality but it goes a long way and it’s certainly enough for most business users

388

u/[deleted] Apr 13 '22

[removed] — view removed comment

44

u/7Seas_ofRyhme Apr 13 '22

What's worth learning in Excel then ?

300

u/leweyy Apr 13 '22

Since customers will refuse to accept anything else...

df.to_excel()

173

u/shibbypwn Apr 13 '22

df.to_csv() gang checking in, because fuck all that unnecessary overhead :)

103

u/leweyy Apr 13 '22

You really think your customer will open a CSV by importing the data as a data table rather than double clicking the file?

Leading 0s, fucked. Column widths, fucked. Etc.

33

u/SSJKiDo Apr 13 '22

My coworker just set MS Excel as the default program for opening CSV files 😂

37

u/notalwayscapslock Apr 13 '22

As long they don't have to handle datasets with more than 1M rows I don't blame them

22

u/shutchomouf Apr 13 '22

Most people who work in Windows do.

→ More replies (8)

11

u/EndimionN Apr 13 '22

.csv is love .csv is life

80

u/lukey_dubs Apr 13 '22

excel is a little bit easier to learn, and most business / finance uses cases don’t have such complex requirements, so excel is perfect for a lot of people

34

u/[deleted] Apr 13 '22

Yeah, and for lots of quick small data stuff, I prefer using Excel because it's faster.

33

u/[deleted] Apr 13 '22 edited Apr 13 '22

XLOOKUP/VOOKUP, COUNTIF/SUMIF/AVERAGEIF, CONCATENATE, LEFT/RIGHT, MAX/MIN/AVERAGE, AND/OR, IF, maybe pivot tables.

Frankly Excel is just another tool that has it's uses and is well worth using. It can be much faster and more visually pleasing if you have a csv, xlsx, etc... to just throw it into Excel or a Google Spreadsheet and do the basic manipulation. The latter is particularly great because then you have

  • Basic data analysis

  • Basic visualization (just tabular data or even graphs)

  • Ability to share and collaborate far more easily than python

Plus most people already have excel installed, telling someone to install python, some packages, etc... is difficult. Think of it like a basic docker container with all the packages you need installed for basic data manipulation.

11

u/7Seas_ofRyhme Apr 13 '22

> Ability to share and collaborate far more easily than python

This is true, although there's google collab, but yeah gsheets is way easier I reckon.

> Plus most people already have excel installed, telling someone to install python, some packages, etc... is difficult.

Truee

22

u/[deleted] Apr 13 '22

[removed] — view removed comment

11

u/[deleted] Apr 13 '22 edited Apr 13 '22

You are 100% correct, I am just so used to VLOOKUP I still forget about it (plus my personal Excel version on my home desktop doesn't have it, nor does Google Sheets)

3

u/SMAMtastic Apr 13 '22

I’ve been an INDEX(MATCH) stan for a few years now but just recently made the switch to XLOOKUP.

→ More replies (2)

2

u/Bakkone Apr 13 '22

Worse performance

22

u/climber_g33k Apr 13 '22

I'm a biologist, with an interest in data science, so this is my take.

Visual basic for apps is easier to learn.

If you are not strictly a computer science role, it's unlikely that you have python installed, but you certainly have excel installed.

The user interface is already known worldwide, you don't have to spend time writing a user interface.

No matter what you make, someone will tell you, "but excel can do [x]".

The widget builder is 1000x better than tkinter.

My recommendation is to learn both, and know when to apply each tool.

2

u/Ella_121 Apr 13 '22

but you certainly have excel installed

I wish this was true. I recently gave an introduction into creating online charts to a collaborator who didn’t. He didn’t even realise he did not have Excel when opening the data he was meant to use.

3

u/bonferoni Apr 13 '22

Python comes preloaded on all macs

6

u/hulknc Apr 14 '22

Not anymore!

As of 12.3 python is gone.

1

u/bonferoni Apr 14 '22

!! Thats sad, i always appreciated that little feature

2

u/hulknc Apr 14 '22

It’s made a lot of Mac Admins a little miffed as thats how they got a lot of computer management scripting done.

2

u/proverbialbunny Apr 14 '22

Maybe they'll have to go back to Perl?

To the dark ages with them!

→ More replies (1)

3

u/climber_g33k Apr 13 '22

This i did not know!

3

u/bonferoni Apr 13 '22

Fun facts. That being said, im pretty sure its python 2.7. So would need updating fpr sure

1

u/TigreWulph Apr 13 '22

But you shouldn't be using system py as your interpreter for your project. You should be creating a virtual environment which means users are having to install python anyway.

That being said python is loads better than excel.

9

u/cinemabaroque Apr 13 '22

Python is way more powerful than Excel, not better. For 90% of use cases you can do things faster and more clearly in Excel with knowing just a handful of formulas and inserting charts.

→ More replies (3)

0

u/[deleted] Apr 13 '22

I'm not at all comp sci, let alone strictly, but I do have python and don't have excel.

Sorry. I know I'm exceptional. Just chipping in :)

→ More replies (1)

9

u/narwalfarts Apr 13 '22

A) Collaboration with colleagues who dont know python

B) For simple datasets and simple calculations, it sometimes is easier to work in Excel, as everything is neatly visualized in a table

6

u/CatOfGrey Apr 13 '22

I have not found a better tool for looking at 'raw data' than a spreadsheet. No where else can I do a quick sort, look at all 100,000 rows in one of 25 columns, then look at other data, and see 'everything' with just a mouse click or keyboard shortcut.

Full disclosure: I have been a spreadsheet user since the 1980's, and am in process of gradually moving my processes to Python (or occasionally, R). If the community has a suggestion, I'm game.

5

u/TigreWulph Apr 13 '22

Do your math and data manipulation in python then output an excel file for use after the fact. That's basically my job.

3

u/7Seas_ofRyhme Apr 14 '22

Sounds like a plan. What if that company doesn't have Python installed ?

4

u/TigreWulph Apr 14 '22

I mean for my job we self installed. The machines came without it. But you do have to work within the realities of your employer if they won't let you install python then it's not gonna happen, and you'll have to use what they offer... Or make a use case pitch, and get python.

2

u/CatOfGrey Apr 13 '22

That's where I am currently.

I audit my work in an Excel spreadsheet.

1

u/JingleBellea May 01 '22

Would you be up to connect with MSFT excel team and talk more about Python + Excel?

→ More replies (1)

1

u/JingleBellea May 01 '22

Why are you moving your process to Python? Would you be up to connect and talk more? :)

5

u/Hextall2727 Apr 13 '22

pivot tables. I use excel to QC my python results, and knowing how to use pivot tables to try and recreate results is extremely helpful.

1

u/TigreWulph Apr 13 '22

How? Whenever I've tried to do this with the datasets we have, it completely chokes my machine.

I wrote a module to basically do csv to csv and excel to excel comparison so that when we shifted our code base to 3.9 from 2.7 I could insure that the products we were giving to our clients were a match still.

Trying it in excel first was completely untenable.

9

u/[deleted] Apr 13 '22

What's worth learning in Excel then ?

The basics of spreadsheet manipulation. You can teach just about anyone some basic Excel work. Code not so much, people are very resistant.

4

u/Almostasleeprightnow Apr 13 '22

I try to think of excel as a communication tool rather than a math tool. Although it is very powerful, if you are having to do the same thing again and again, there is a lot of room for mistakes. Also, if you have a lot of rows, there is frustration, and sometimes prevention of work, in load time. I.e. sometimes you can't load all the rows

However, if your goal is to quickly show someone some results, excel is a great tool. If you just need to bang out a one time calculation, excel is a great tool.

If you are collaborating with someone and want to look at the same sample data in the same way, excel is a great tool.

7

u/_Adjective_Noun Apr 13 '22

It's often worth learning how to output data or reports to excel, since they're very easy for people to access.

I use openpyxl for this a lot, while we do all the good stuff python side, it's very useful having a tool to dump into a standard xlsx, since then any non-technical person can grab a graph and reformat slightly to match their needs.

1

u/SirGeremiah Apr 13 '22

This is something I need to get into more. I suspect I can make better tools with Python, but VBA is an easier start, since you essentially have the entire interface of the A in VBA (in this case, Excel) already provided.

3

u/CowboyBoats Apr 13 '22

Excel is awesome! VLOOKUP is super super worth learning, especially if you don't yet know how to code. SUMIF and SUMIFS as well used to solve problems for me day after day. CONCAT, IFERROR, string slicing and concatenation functions. It yields really well to experience, and to Googling things you don't know. It's a wonderful planning and organizing tool; heck, I used to use it to compose bash scripts sometimes before I knew more about how to use sed and the Vim :substitute command. It's just not good for actually developing software; but the fact that that even has to be pointed out, because people can and will deploy actual software using nothing but Excel as a backend - while that is insane - really goes to show the sheer power and flexibility of the tool.

3

u/RegulatedMedium Apr 13 '22

The benefit of Excel is that you can make programs to handle simple tasks without having to actually create a program with a GUI imo.

3

u/SilmarilsOrDeath Apr 13 '22

If your organization blocks python then everything is worth learning in excel

2

u/7Seas_ofRyhme Apr 14 '22

blocks python

How about google collab?

2

u/TheRiteGuy Apr 13 '22

Point and click UI vs learning a programming language.

1

u/Astrokiwi Apr 14 '22

Small manual operations on tables, and direct inspection or annotation of data.

Like if you have 100 rows and 10 columns, and you want to swap a couple of rows, manually fix some typos, and inspect each row by eye and add notes in a column because, say, the data contains plain text descriptions and it would be less accurate and more work to try to write a text parser.

0

u/angry_mr_potato_head Apr 13 '22

Not much beyond the basic input. That’s a lot easier in excel but is also one of the causes of headaches because people put data in that doesn’t make sense or I difficult to read in because it was presented visually for other people to read in Excel.

0

u/guitarerdood Apr 13 '22

You've answered your own question

0

u/[deleted] Apr 13 '22

[deleted]

29

u/unhott Apr 13 '22

I’m sure there are some libraries that can change your mind.

https://seaborn.pydata.org/examples/index.html

28

u/SirGeremiah Apr 13 '22

I doubt it. They didn’t say graphs were better in XL - just easier. And they are. I can knock out an Excel graph from reasonable data in just a few clicks. No matter how good I get at Python, it’ll never be easier for simple, basic charts.

→ More replies (8)
→ More replies (3)

5

u/synthphreak Apr 13 '22

nice

I personally think Excel's default graphs look like crap. I think we're just used to them.

You can create a lovely histogram with just a single line using matplotlib: plt.hist(data).

→ More replies (6)
→ More replies (4)

5

u/SirGeremiah Apr 13 '22

How much of this varies from what VBA can do? I’m dabbling a bit in Python, and still trying to understand when it would be a better choice than VBA for me. I like the structure of Python better, but am much more familiar with VBA basics.

7

u/mac-0 Apr 13 '22

As someone who learned VBA before Python, there's really no advantage to VBA. Sure it's built in, but it's clunky and will teach you bad practices. Plus, VBA is a pretty useless skill on its own -- it's a nice tool to create quick little automations, but because you are limited to Excel and MS Access, there is no scalability and you will quickly run into problems that just can't be solved in Excel/Access.

2

u/SirGeremiah Apr 13 '22

I’ve always only done small work, so scalability isn’t likely an issue for me in the near future. If I ever learn enough, that might become a factor, but I’m pretty late in my career, so unlikely I’ll end up in that situation.

As for learning bad practices, that’s probably a done deal, since I’ve been using VBA off and on for about 30 years.

17

u/[deleted] Apr 13 '22

[removed] — view removed comment

2

u/SirGeremiah Apr 13 '22

That was helpful.

I was specifically asking about your prior list. I don’t understand some of the bullet points, so want to know if all of those items are also different from VBA.

4

u/mrmanwoman Apr 13 '22

I see you missed pivot tables. Entire excel courses boiled down to 1 line of code.

2

u/Wodanaz_Odinn Apr 13 '22

I'd add testing to this too.

2

u/IndependentVillage1 Apr 14 '22

Python knows what is and isn't a date

2

u/JagicMohnson Apr 14 '22

Can someone elaborate a little more on that 4th bullet point about dictionaries? I live and die by indexmatch but am trying to apply more Python to my job.

1

u/kronos55 Apr 13 '22

These can be done with numpy right?

1

u/a5s_s7r Apr 14 '22

Never f… up your data because of locale issues: , / .

1

u/JingleBellea May 01 '22

Hi, we are inviting data analysts who use both python and excel to 1:1s to share opinions on how to build better features in data analytics in Excel. It seems you would be great to chat with. I will send a private message to you for more details, thank you!

0

u/magestooge Apr 13 '22

For regular users of excel, being strongly typed is a disadvantage. It makes things far more complicated than they are in Excel.

Date time handling is quite good in Excel. Without the dateutil library, it's a nightmare to recreate all the functionality we have in Excel.

Programmatically appending to lists and concatenating list in Excel just became ridiculously easy with the introduction of vstack function (very recent addition).

Excel recently introduced a slew of string processing functions. Still no regex, but it's getting better.

All valid points otherwise. We really need better version control system.

8

u/Vaphell Apr 13 '22

Date time handling is quite good in Excel. Without the dateutil library, it's a nightmare to recreate all the functionality we have in Excel.

you mean like when the biology people had to rename some protein, because its symbol Sep in a random ass string caused implicit conversion to a date in excel all the time, and if you miss it in a huge blob of data, you are fucked? Not to mention the classic bug of year 1900 (not a leap year according to reality, a leap year according to excel since forever).

As for other domains where shit can seriously break, the finance people dealing with long numerical strings aka account numbers can have their shit converted to numbers and mangled, because of insufficient precision to store the number.
Excel is outright dangerous to data consistency.

2

u/magestooge Apr 13 '22

You're right. I should have said date time manipulation capabilities.

I do wish they'd introduce better string to date conversion though.

And yes, Excel is best avoided for scientific data, finance is where it works well.