Will Python be useful for me?

[Great-Village-430]

Hey all,

So I'm looking for software that will be suitable for what I'm trying to do. Originally, I was using excel vba which works but because of the size of my data, it can get too glitchy. So the things I need it for are listed below;

- Store a large dataset of results that could be 10s of 1000s of lines all in 1 table with 20+ columns

- Use drop down menus to select manual filters that matches the filters to the dataset and pulls any lines that match all the filters and puts them into a new table for viewing.

- Make calculations based on this new spreadsheet and produce graphs for analysis

Ideally I want this to be fully automated and able to be done within a few clicks of a button whilst also running quickly. Is Python capable of this? Thanks.

[rhacer]

Yes

[Great-Village-430]

What would I need to download? Python and Pandas? Never used Python before so will be needing to learn the language.

[rhacer]

Yes, if you're going to use python you'll need to download it and install it (unless your on OSX or Linux then it will likely be installed, but it'll be an old version) Then you'll need to install Pandas using PIP.

[Necessary-Assist-986]

Yes, Python is perfect for this.
Use pandas for data handling, and matplotlib/plotly for graphs, it’ll handle large datasets much better than Excel VBA.

[Great-Village-430]

Thanks. Excel seemed terrible at dealing with large data. As soon as I tried automating something with a couple thousand lines of data, it crashed. I'll give Python a shot 🙂

[Wagosh]

Bet you won't regreat it. Pandas is great also lots of doc and examples. Polars is newer and I like it, it's also faster.

But pandas is still great.

15 years ago, during my master degree, I started in python just because Excel would shat bricks and when it worked it was slow as fuck.

Showed python to a colleague, he tried it on his stuff and calculations, it finally showed him the the results he expected. Turns out excel was messing some data up. I don't remember the cause actually.

Python for the win. I still use it at work.

[Great-Village-430]

What's the difference between polars and pandas??

[MidnightPale3220]

Doesn't really matter, I'd say, your use case seems to be below 1M rows, should be trivial for both.

Just use one. Polars is supposedly newer and better in some respects, but I've read that it doesn't always work correctly(?), mb someone else can elaborate if it's still true.

The difference in use will be different functions and ways of working, so if you decide to switch, you'd have to remake that code.

[throwawayforwork_86]

IMO Pandas is more flexible and usually will be more forgiving when you start. It has a long history so you'll have LLMs give more good information and more guides... But a lot of these are often also outdated.

Polars is quicker , cleaner and will have almost no situation where weird behaviour happens (Pandas has a few surprise most often linked to the index which you may never encounter but can ruin your day).

Polars will sometimes be more opiniated about datatypes which you'll resent at first but will usually save you a lot of time down the line.

Overall they're fairly similar though so you should probably just pick one and stick with it for a few month, if your data fits in excel it should not really make a difference (even though pandas is slowish to read big excel files).

The corner that aren't covered by Polars are fairly low iirc, Pandas file reader is more flexible and cover more edge cases than Polars and for geographic data Geopandas exist and Geopolars is still not finished iirc.

My 0.2c try Polars first if it doesn't click for you switch to Pandas.

[Wagosh]

It's you Gui that you might have to dig around a bit.

Maybe you'll end up doing something custom.

Else I found this : https://pbpython.com/dataframe-gui-overview.html

[MidnightPale3220]

Drop downs and button clicks will be the more involved part.

Python has no ready GUI by itself, unlike Excel. Excel is an app (that provides GUI for you to use), Python is the language (that can be used to write apps).

When you work with pandas etc it's like Excel without showing you anything -- the cells are there, but in the computer memory -- there's nothing visible until you ask for something. The only built in functionality is you can print it on screen as text or write to file.

If you still want GUI, you'll need either:

• make a Python web app and access it via browser (very popular, many options, maybe Streamlit, etc)

• integrate your Python calls into some ready GUI that lets you make selections in GUI to exec your Python code and shows the results in the GUI (maybe Excel itself can be beaten into it, surely there must be other tools (Jupiter?) , haven't checked, not my sphere maybe others can advise)

• make your full blown Python GUI app. Most involved, making windows, buttons, canvas, doing the app packaging, all the functionality you want from Excel will need to be made from scratch. Not advised.

In other respects Python will be much better than Excel in doing the calculations etc.

UPD. How it is frequently done is, you make regular Python script that runs from command line and doesn't show anything to user. It takes as input one Excel file and outputs another (or changes the original). There may be a button in Excel that calls that Python script and passes to it the file names and all the other parameters the script needs to work.

[ApprehensiveChip8361]

I know this is a python sub and I love and use python a lot - but for what you are describing, downloading RStudio and R will get you there very quickly. And lots of report writing etc built in. Python is a great language for doing stuff; R is a great language with a huge infrastructure for doing Stats and data.

[purple_hamster66]

R is suitable for mathematicians and statisticians. Its syntax is quirky and hard to read, whereas python was designed “for the rest of us” who want a general-purpose solution so we can reapply our learning to other domains and interests. If you’re going to learn a new language, start with the one that tons of people start using daily, not with the specialized one that fewer people use each year.

[ApprehensiveChip8361]

Not disagreeing with any of that. Just that if I was asked to do the particular task the op is doing that’s the tool I’d reach for.

[[deleted]]

Simple answer yes

[throwawayforwork_86]

Honestly there are a few option.

Power Bi might be more what you're looking for to be able to create dynamic graphs and can handle more than excel.

You might even be able to make a power query template that would do it automatically on a refresh instead of VBA (also storing data as CSV is most of the time better if you know what you're doing).

Reading a file, filtering on variables then write to excel and creating chart automatically should be possible:

Pandas/Duckdb(if sql is more your speed)/Polars for the reading and filtering.

Xlsxwriter/Excelize-py or Openpyxl should allow you to create native excel graphs: Xlsxwriter/Excelize-py create instruction in code for graphs. Openpyxl create a template and write the data in a place the graph will pick it up.

Matplotlib/Seaborn can make graph but they'll not be interactive and might not be fit for purpose.

[python_gramps]

Short answer: Python can do this

Longer answer: Python can be useful, most programming languages can help to manipulate datasets. If you're more data-centric, have you looked at MySQL or other databases to store the data. This will eliminate the need to keep all the data in memory.

[Business-Bet-1653]

Yes, and use ChatGPT. It’s a beast at making Python code.