Also filtering is essential. So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.
This is not "filtering", this is faceted search.
I am also a big fan of faceted search, and I found the occasional writing of Simon Willison’s on the topic very informative.
https://simonwillison.net/2018/Oct/4/datasette-ideas/#Facet_...
But yeah great feature, and thanks for the reference.
> Also filtering is essential.
> So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.
As long as it's not tedious to set a few thousand that sounds great.
> What are the other most common formulae that you use?
I mostly use SUM, COUNT and IF (if-then), along with functions to check if a string value is a valid number. Also string formatting of numbers and dates (for concatenating with text).
I've also used lookups, ie find the row matching this and extract the value from the given column in that row. Though I'm not super happy with the way Excel does that, surely some room for improvement.
> Perhaps instead of filtering, you'd like to see outliers or the range of values?
In addition. Sometimes I just want to view all the values matching X, other times I want to quickly see any outliers. Definitely ranges, especially for numbers (just larger than 0 for example, or between 5 and 10).
Sounds like a very interesting project, if you got a link I'd be interested in tracking progress. If not, I'd be happy if you did a Show HN when you're ready :)
> As long as it's not tedious to set a few thousand that sounds great.
> SUM, COUNT and IF (if-then)
Right now I've started a Git repo with a roadmap but I've not uploaded any code yet as I'm still deciding on the architecture:
I'm old school, so ctrl+c, shift+end (maybe tweak selection area with cursors if Excel is being dumb) and ctrl+v.
The way I work, it would have been better to mark a cell itself as being a "constant" or "input parameter", so that when I reference the cell it is treated as an absolute reference when pasting.
That is, if I reference cell B2 in a formula and paste that formula in a different cell, it is B2 itself that determines if it's a relative or absolute reference, not the way I typed it in the formula.
It's not a huge deal, but it would be one of those nice things that makes life more enjoyable.
In comparison, I often have formulas with over 20 cell references and/or multiple formulas referencing the "constants", so getting asked for each would indeed be tedious.
To me, a critical one is easy plots. Checking how an arbitrary column changes as a function of an arbitrary other column by adding a scatter plot in less than 5 seconds is fantastic.
Formulae are also very useful. Adding numerical derivatives or integrals by just putting a formula in a new column is very useful as well. The point is not to have publication-quality, highly accurate numbers, but just quick and dirty operations to see if it warrants further investigation.
I am happy to discuss my use cases if you are interested (it might be going a bit out of topic for this thread).
https://github.com/dotancohen/structuresheet
I'll see what cross-platform plotting libraries I can include. Thank you for the idea, I agree it is important. Yes, I am very interested in your use cases! My Gmail username is the same as my HN username.
sqlite's lack of verifying that your data fits in your defined schema is by far my biggest problem with sqlite.
But the real nice thing is that SQLite will allow spreadsheet-like behaviour by allowing one to enter an invalid data type. I could check that in Python or I can store it and warn "Invalid type blah blah blah". This will make life easier for those coming from a spreadsheet, or importing data. As the program matures, I can reevaluate what to do with invalid data as a default and what options to give the user.
Additionally, because SQLite allows arbitrary data in any column, adding support for e.g. formulae is greatly simplified.
Python / Qt / SQLite
I don't see any part that gives a strongly-typed properties.
Do I miss something?
Scientists are not superhuman. Just like anybody, they’ll jump through a lot of hoops if they think the results justify it, but they are sometimes quite resistant to change for the sake of change, and sometimes even to change itself.
One can be a great chemist or know all there is to know about how purple long-tailed fruit flies from Siberia and have no clue about how computers work. Or be very proficient in a given piece of complex software to process NMR spectra and barely able to operate Outlook. But these people all make do with Excel.
> In a second moment, python, pandas and notebook are pretty accessible too
It’s much heavier, the IDEs are much more complex than Excel, and quite a lot of people on Earth are not natural programmers. Startup time, learning curve, steps to get a useful graph to check a trend, etc. All friction adds up. I’ve seen it countless times: when you show them the results of a complex workflow, they are excited. They start getting distracted when you talk about architecture, and they’re lost when you go into things like pandas and scipy. Then they nod politely, keep doing their stuff in Excel, and call you when they need a bit of wizardry for a paper.
In short, they are regular users, even if the software they use can be highly specific. Ease of use and lack of friction are paramount.
1) Those utilities are not typically included in the workstation image of laptops/desktops unlike MS Excel which is already part of Office 365. Millions are already familiar with the GUI of Excel.
2) The datagrid viewers in those tools are not powerful and feature-rich like Excel. They are often missing features that are taken for granted by Excel users:
- formatting any arbitrary row or column with bold/italics and change the font color or background cell color.
- pivot the data via drag & drop UI (instead of manually writing a SQL cross-tab query)
- hide rows or collapse rows into outlines
- cut rows 37 to 52 and paste them above row 5. That type of behavior is not easy in generic database viewers because most tables -- by typical design of RDBMS table row ids -- do not consider the visible spatial ordering of rows the way the end user wants to see them on the screen unless one adds an extra column to the table such as "gui_view_order_id". Excel has user specified row ordering as default out-of-the-box behavior.
- ... tons of other GUI features like formulas, spell check, etc