r/learnpython • u/mister_patience • 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?
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
1
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
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
1
u/SirGeremiah Apr 13 '22
Wait, what?? How did I miss that?
1
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
10
u/tagapagtuos Apr 13 '22
Just to add:
time to open Excel and do stuff > time to type
python filename.pyin 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
1
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
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
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
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
2
2
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
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
1
1
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
1
1
1
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
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
1
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
1
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
1
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
1
1
1
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
0
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
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
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.
Info Custom Your Reminders Feedback
0
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
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
→ More replies (8)22
11
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
33
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
Apr 13 '22
[removed] — view removed comment
11
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
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.
→ More replies (1)2
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
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.
4
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.
2
u/proverbialbunny Apr 14 '22
In case you didn't know: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.pivot_table.html
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
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!
VLOOKUPis super super worth learning, especially if you don't yet know how to code.SUMIFandSUMIFSas 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 usesedand 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
2
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
0
Apr 13 '22
[deleted]
29
u/unhott Apr 13 '22
I’m sure there are some libraries that can change your mind.
→ More replies (3)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 (4)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)5
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
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
2
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
1
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.
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.
Making changes to cells on certain criteria
Algorithmically adjusting data rapidly
Setting up simple easy to edit scripts for rapid analysis of raw data to final product
Handling larger datasets
Rapid pivot table sorts
Finding best fit curves for non normal distributions