4 ms·

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

lofatdairy 4y ago
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.
- oogway8020 4y ago
  Tidyverse is awesome, RStudio too
eddieroger 4y ago
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.
- D13Fd 4y ago
  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.
CJefferson 4y ago
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.
- erikcw 4y ago
  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/ https://ponyorm.org/
FredPret 4y ago
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.
- lofatdairy 4y ago
  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.
yuliyp 4y ago
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.
- simonw 4y ago
  "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#creating-tables https://sqlite-utils.datasette.io/en/stable/python-api.html#...
- HyperSane 4y ago
  I didn't say ALL uses. Just the ones where Excel is being used as a crappy combination of programming language and database.
LeifCarrotson 4y ago
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.
dahart 4y ago
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.)
lelanthran 4y ago
> 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.
- somat 4y ago
  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.

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.

oogway8020 4y ago
Tidyverse is awesome, RStudio too

eddieroger 4y ago

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.

D13Fd 4y ago
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.

CJefferson 4y ago

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.

erikcw 4y ago
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/ https://ponyorm.org/

FredPret 4y ago

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.

lofatdairy 4y ago
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.

yuliyp 4y ago

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.

simonw 4y ago
"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#creating-tables https://sqlite-utils.datasette.io/en/stable/python-api.html#...
HyperSane 4y ago
I didn't say ALL uses. Just the ones where Excel is being used as a crappy combination of programming language and database.

LeifCarrotson 4y ago

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.

dahart 4y ago

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.)

lelanthran 4y ago

> 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.

somat 4y ago
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.