Hacker News Reader

Comment by tgb | Hacker News Reader

Does anyone use SQLite as their daily-driver in lieu of R or pandas for data analysis? I don't think I can use the sqlite command-line since I'd want a fully-developed plotting utility, and it seems less convenient to do the actual analysis part through a sqlite connection in python, say.

View on HN

Not "in lieu", but I have found SQLite to be a good companion to pandas. pandas is good when you want to lend some easily navigable structure to your data without leaving python, but becomes cumbersome (it feels cumbersome esp if you know SQL) when you want to do some serious processing. At such times, I just dump my data into a SQLite DB, and from then on my code has SQL queries to it, which I find are far more readable than corresponding pandas statements (also easier for the reviewer).

Another advantage you get is if you wanted to look at these intermediate data, you don't need to run your code in debug mode and view the dataframe at a breakpoint - you can use something like datasette[1] or a standard SQLite DB viewer.

So a function that does complex data processing has approximately this structure my code:

  (1) <non-pandas python code to fetch data, other "normal" stuff>
  (2) <pandas code to do basic navigation, filtering, grouping etc>
  (3) <dump to SQLite, followed by SQL queries for the really heavy stuff>
  (4) <back to python, possibly pandas, to return results, write to a file, plot etc>

Step (3) used to be pandas for me before, but depending on how complex your operations are this can become hard to read and/or review.

[1] https://github.com/simonw/datasette - datasette doesnt replace a standard DB IDE, but is a very good lightweight alternative to one if don't intend to perform updates/inserts directly on a table.

I have exactly the opposite workflow. Import data into salute, perform as much heavy lifting as possible in salute, and export into a python data science workflow via led to if necessary.

Take a look at DB Browser for SQLite: https://sqlitebrowser.org/

It has some plotting built in. Not sure if it is sophisticated enough for what you need but it might be worth a try.

You can perform sql queries with many of the DB clients available, such as Heidi, or Deaver, and also through python.

Sqlite is OLTP though, for analytical purposes I'd use an OLAP DB like DuckDB.

IMO dumping workload in DB is nice, specially when your dataset doesn't fit in RAM.

I use extensively DbBrowser for sqlite to plot simple graph from sql queries of imported csv files, it is easier than to start a jupyter environment with python and pandas or r studio.

You can always execute SQL in pandas by passing it a connection, getting the best of both worlds.

I do. Apache Superset, Metabase, Redash - all support SQLite. Other BI tools also do.

Yes, setup jupyter magics to execute SQL and it becomes MUCH more useful for data analysis in a notebook. There's a great extension that lets you just write and run SQL queries right in cells: https://blog.jupyter.org/a-jupyter-kernel-for-sqlite-9549c5d... Hook it up to an in memory or on disk SQLite instance and you can kick pandas to the curb.

That's one of the core use cases for Vinum - to provide SQL query engine, complementing Pandas in data analysis (thanks to Arrow), yet retain an ability to use native python and numpy funcs as UDFs. Also, it doesn't require input dataset to fit into memory.

https://github.com/dmitrykoval/vinum

It's complementary, since unlike R/Pandas it can fit too-big-for-memory data - I've used it ad-hoc to store big-ish datasets to pull into R. Though if you're using Python I think that use case is mostly superseded by Dask.

I do. I load data into SQLite using various tools I've written (geojson-to-sqlite, db-to-sqlite etc) and run the analysis in https://datasette.io

Reply on news.ycombinator.com