Hacker News Reader

Comment by HyperSane | Hacker News Reader

Python + SQLite should replace a huge amount of Excel uses.

Hardly. I can often do in Excel in 5 minutes what would take me an hour or two with Python.

I've got some output from something or a table of data I can copy-paste into Excel, quickly do some text-to-columns or string manipulation (tweaking it instantly if I see things don't look right) to get the data into a usable format, then go and build up some formulas, sorts, and plots to answer whatever question I had / get something I can share with others.

Each of those steps is doable with Python + SQLite, but takes more time: I have to write my own data parser (yes, Python's string utils are very good for this, but generally Excel is faster to use, despite using Python for years for my job. I have to then create a schema for the data, and then I have to iterate on my analysis functions, and then finally write my own output / futz around with a graphing library, rerunning whenever I want to tweak something (instead of just changing a cell or chart parameter and seeing it instantly reflected on screen.

There is a crossover point where the complexity of the analysis, amount of data, or otherwise makes Excel less practical, but it absolutely has a place as a super-helpful tool, even for those capable of using more powerful general purpose programming languages.

"I have to then create a schema for the data"

You might find my sqlite-utils Python library interesting - one of its key features is that it can create the right schema for you automatically to fit the data (as a Python dict or list of dicts) that you pass to it: https://sqlite-utils.datasette.io/en/stable/python-api.html#...

I didn't say ALL uses. Just the ones where Excel is being used as a crappy combination of programming language and database.

With respect, and as a guy who learnt Python to work with Pandas on datasets too large for Excel, I'm not so sure.

Excel allows non-programmers, of whom there are very many, to visually explore and play with their data. I was already a programmer when I learnt Python; it took many years to learn those skills.

Non-programmers usually do not even want to think about their data in an abstract and general way anyway.

I completely agree, drag and drop is a hard habit to break for a lot of people who have never coded before. I will say Pandas is pretty easy to learn (obviously mileage may vary but I found it far more syntactically consistent then it's cousin Numpy), but not being able to see what you're doing makes it hard to just jump in and work, you probably will start on small toy datasets and work up to working blind on larger datasets.

That's really dismissive of the learning curve of learning two things versus one, as well as having the expertise to install Python and maybe SQLite versus Office being installed on most computers already. Python and SQLite also have no portability as compared to emailing someone an Excel sheet. I love writing SQL whenever I have the chance, but "should" is a really strong word here.

It's not just learning two things vs. one. It's learning a text-, command-, and script-based interface vs. a mostly visual one.

You could likewise say "a huge amount of file operations should be performed with rsync" and just ignore the fact that it's much harder to learn than a drag-and-drop setup.

Hehe, learning how to use Pivot Tables should replace a huge amount of Python uses. I say that as a huge advocate for Python!

Of course, this really comes down to using the right tool for the job. Python should be used for automating batch processes, and using Excel manually should be used for data exploration. Jumping into automation before you know exactly what you want is premature, and doing something manually over and over again is wasting time. Us programmers sometimes reach for code writing and automation before we’re actually ready, and waste time writing the wrong code. (I am guilty of this.)

I personally vote R + Tidyverse only because I know a ton of older people in data/stats background who have done work in SAS and R, but struggle a bit with knowing what to do in Python. If all you're doing is tabular data all day, R handles that pretty well by default and RStudio is a much better IDE than even a Jupyter notebook for a more interactive paradigm.

Tidyverse is awesome, RStudio too

If I'm going to be the one to use and maintain it? If it's a black box to my customer? If I expect considerable complications, extensions, scope creep, performance needs? Absolutely. Maybe I'd use C# instead of Python to simplify installation on the non-developer Windows machines I'm surrounded by, but that depends on the context. A little Node app on the LAN that everyone can access with their desktop or mobile browser can be nice too, though browsers keep breaking stuff in the name of security so I've stopped doing that much. I'm also a fan of domain-specific language config files written in Lua; there are many better ways to build powerful, extensible, useful applications than an overloaded Excel sheet.

But if those things are not true, if I'm going to be the one to test out an idea and hand something off to my coworker or customer, building in Excel means they're likely to figure it out instead of their eyes glazing over when I explain that they need to install Jupyter, or a text editor, or Visual Studio, or to modify the Lua config file, or invoke something from the command line...they're not going to do any of those things, they're calling me when ~~it breaks~~ their inputs and requirements change.

It's similar in my primary role building industrial manufacturing control systems. Rockwell Automation is a terrible company with asinine licensing fees and outdated technology...but they're the safe choice if you want to transfer system ownership and not get calls about that one machine with the "Linux NUC" for the next 20 years.

I would love that to be true, but I'll be honest -- I consider myself a good programmer, but a couple of times I've tried Python + SQLite and ended up just giving up and using csv or JSON.

Is there a nice library which makes it easy to use SQLite in Python even close to as easy as Excel? Or even as easy as Python lists? Anything which requires me to fill my Python code with SQL strings is (in my opinion) disqualified.

I recently inherited a codebase that uses Pony ORM[0]. Might be worth taking a look if you want your interactions to look as similar to vanilla python expressions as possible.

[0] https://ponyorm.org/

> Python + SQLite should replace a huge amount of Excel uses.

It won't even replace the edge-case uses, n/mind the majority of uses.

Excel combines two things that most environments keep separate. storing data. and calculation/logic.

I have this theory that a relational database management system is basicly a grown up spreadsheet. and it is, it does great, far better that excel at storing and querying data. And you have better choices for logic, from views to full blown stored procedures(most will probably use an external program instead). all of which keep your logic out of your data.

Reply on news.ycombinator.com