Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A Relational Spreadsheet

kevinlynagh.com

Post reply on HN
Print view View original

51–60 of 77 posts

Re: A Relational Spreadsheet

#51

Post by gigatexal »

jiggawatts wrote: post #43
rrrrrrrrrrrryan wrote: post #24

Earlier quoted context omitted.

Microsoft Access is this. It was quite popular in its heyday, but as time wore on, it turned out that people eventually either want scalable, real, full-blown databases, or infinitely flexible spreadsheets. Anything built with a product in that grey area in between will eventually want to go one way or the other.

Access had the capability to use a full-blown SQL Server back end, while retaining 90% of the flexibility on the front end. For example, adding a column in Access would add a column in the database table! We can have our cake and eat it too. The mistake with Access was that instead of keeping a deathgrip on its legacy file-based roots, it ought to have "grown up" and become a web-native HTML5 app that uses SQL Server…

Airtable does this, no? There’s just no SQL part. But they got the UI part down.

Re: A Relational Spreadsheet

#52

Post by capableweb »

cldellow wrote: post #22
capableweb wrote: post #18

Earlier quoted context omitted.

excel.new has a "weird" TLD, no mention of Microsoft in the domain and asks me to auth with my Microsoft account before I can even see the site. If I came across this in the wild, I'd for sure think this is some phishing or generally bad website.

Those are all valid points, of course, and you can navigate to excel.office.com if you don't trust the OP (although it will also immediately redirect you to an auth window) The .new TLD is pretty nifty. sheets.new gets you the same thing as excel.new, but for Google.

At least excel.office.com is on the official office.com domain, easy to verify.

Using X.new seems like a bad idea, there is no way for me to understand if it's actually a official project by the companies themselves or just a random developer who has set that up. Maybe one day it'll ask someone for auth, they don't notice it's at microsoftweb.com instead of microsoftonline.com and get phished.

Really hard to trust random domains when a simple excel.office.com/new would do just as fine.

Re: A Relational Spreadsheet

#53

Post by pbronez »

gigatexal wrote: post #51
jiggawatts wrote: post #43

Earlier quoted context omitted.

Access had the capability to use a full-blown SQL Server back end, while retaining 90% of the flexibility on the front end. For example, adding a column in Access would add a column in the database table! We can have our cake and eat it too. The mistake with Access was that instead of keeping a deathgrip on its legacy file-based roots, it ought to have "grown up" and become a web-native HTML5 app that uses SQL Server…

Airtable does this, no? There’s just no SQL part. But they got the UI part down.

I’m so annoyed there’s no SQL API for Airtable… even if it was read only.

Re: A Relational Spreadsheet

#54

Post by narush »

anigbrowl wrote: post #34

[Laughs in pandas] I don't want to be dismissive, this is nice work and it's clean and lightweight. But it might be good to look at existing solutions in this area - pandas was developed within the financial industry to solve exactly this sort of issue. If you need more topological flexibility there is xarray, and if you need spreadsheet type immediacy it's worth looking into Mito. Rustaceans should look into pola.rs…

Mito co-founder here - thanks for the mention! OP you can check out Mito here [1].

Tldr: Mito is a spreadsheet that you can use to edit pandas dataframes, from directly within a Jupyter notebook. For every edit you make, Mito generates the equivalent python code, allowing you to perform some tricky pandas operations with the ease of a spreadsheet.

In practice, most of our most active users _are_ in the financial industry. They turn to Mito because they’re transitioning from spreadsheets to a programming language (for one of many reasonsg, but doing so while not knowing how to program is challenging. For most people, the hard part of writing code is the actually process of writing code - so we give these folks a spreadsheet interface they already know.

Happy to answer any questions / hear any feedback!

P.S. We’re open core + source available. Check it out if that’s your thing [2]

[1] https://trymito.io [2] https://GitHub.com/mito-ds/monorepo

Re: A Relational Spreadsheet

#55

Post by chaps »

anigbrowl wrote: post #44
chaps wrote: post #35

Earlier quoted context omitted.

[Sobs in hours spent in pandas documentation] Or, just use a relational database. Alternatively, airtable and similar are basically relational databases that have some really neat features that let you create relationships by just copying/pasting data, or importing CSVs. They're limited in a lot of ways, but it solves a certain set of problems that can't be solved with code or excel.

I went through that as well; Wes McKinney is super-smart, but not the best teacher, and unfortunately his style of example has become a norm ion Stack Overflow etc. import pandas as pd df = pd.whaaargarrrrbllllll[(['what']['the']['fuck'), is.this['shit'], I, mean, seriously] (outputs) df[.astype('int64').fillna('spork') df.groupby[['uppers']['downers']['all arounders']].join(inner, child, trauma, (yes && no)) df['con…

The year is 2025. You wake up after what feels like a long nap and you haven't touched pandas since you wrote this comment. In front of you is a pen and piece of paper with instructions to hand-write some pandas code to transform a collection of XLSX files of census data and municipal data into a choropleth representing deltas between the datasets. The pandas documentation is printed in-full on a bookshelf to your left. You have 20 minutes.

Re: A Relational Spreadsheet

#56

Post by franciscop »

Ah nice memories, one of my first open source projects was to take a Google Sheet and convert it into a lightweight "database", basically parse the data and parse it into a big array of JSON data that could be used in any website:

const data = await driveDb('sheet-id'); console.log(data); // [{ name: 'John', age: 31 }, { name: 'Sarah', age: 27 }, ...]

https://github.com/franciscop/drive-db/

It was born very similarly to how the article describes it. For low-amount of data, a spreadsheet is IMHO a much better low-tech collaborative tool than a database.

Re: A Relational Spreadsheet

#57

Post by goosedragons »

anigbrowl wrote: post #34

[Laughs in pandas] I don't want to be dismissive, this is nice work and it's clean and lightweight. But it might be good to look at existing solutions in this area - pandas was developed within the financial industry to solve exactly this sort of issue. If you need more topological flexibility there is xarray, and if you need spreadsheet type immediacy it's worth looking into Mito. Rustaceans should look into pola.rs…

Isn't Pandas just a Python clone of R dataframes?

Re: A Relational Spreadsheet

#58

Post by anigbrowl »

chaps wrote: post #55
anigbrowl wrote: post #44

Earlier quoted context omitted.

I went through that as well; Wes McKinney is super-smart, but not the best teacher, and unfortunately his style of example has become a norm ion Stack Overflow etc. import pandas as pd df = pd.whaaargarrrrbllllll[(['what']['the']['fuck'), is.this['shit'], I, mean, seriously] (outputs) df[.astype('int64').fillna('spork') df.groupby[['uppers']['downers']['all arounders']].join(inner, child, trauma, (yes && no)) df['con…

The year is 2025. You wake up after what feels like a long nap and you haven't touched pandas since you wrote this comment. In front of you is a pen and piece of paper with instructions to hand-write some pandas code to transform a collection of XLSX files of census data and municipal data into a choropleth representing deltas between the datasets. The pandas documentation is printed in-full on a bookshelf to your le…

  import pandas as pd
  import geopandas as gpd

  census_df = pd.read_excel('census_data_yuge.xlsx').fillna(0)
  muni_df = pd.read_excel('muni_data_yuge.xlsx.').fillna(0)

  # assumes same criteria & column names in both
  census_df.drop(['address 1', 'address 2', 'zip'], inplace=True)
  muni_df.drop(['address 1', 'address 2', 'zip'], inplace=True)

  cities = gpd.DataFrame(muni_df.groupby(['city']).mean() - census_df.groupby(['city']).dropna().mean())
  gpd.plot(column='num_residents', cmap='bwr')
A fun exercise. I stuck to the time but score it as a C- because I cheated with geopandas (which I've never used but seems to do a lot of this out of the box) and gave myself unrealistically clean imaginary data. I have parsed census data once and remember it being a lot of work to tidy up before I even tried to answer my question.

Re: A Relational Spreadsheet

#59

Post by anigbrowl »

goosedragons wrote: post #57
anigbrowl wrote: post #34

[Laughs in pandas] I don't want to be dismissive, this is nice work and it's clean and lightweight. But it might be good to look at existing solutions in this area - pandas was developed within the financial industry to solve exactly this sort of issue. If you need more topological flexibility there is xarray, and if you need spreadsheet type immediacy it's worth looking into Mito. Rustaceans should look into pola.rs…

Isn't Pandas just a Python clone of R dataframes?

I believe so, yes. I just came to it through Python first, I hate R and try never to think about it.

Re: A Relational Spreadsheet

#60

Post by anigbrowl »

narush wrote: post #54
anigbrowl wrote: post #34

[Laughs in pandas] I don't want to be dismissive, this is nice work and it's clean and lightweight. But it might be good to look at existing solutions in this area - pandas was developed within the financial industry to solve exactly this sort of issue. If you need more topological flexibility there is xarray, and if you need spreadsheet type immediacy it's worth looking into Mito. Rustaceans should look into pola.rs…

Mito co-founder here - thanks for the mention! OP you can check out Mito here [1]. Tldr: Mito is a spreadsheet that you can use to edit pandas dataframes, from directly within a Jupyter notebook. For every edit you make, Mito generates the equivalent python code, allowing you to perform some tricky pandas operations with the ease of a spreadsheet. In practice, most of our most active users _are_ in the financial indu…

I tell everyone to start with Mito now. If I had had it around when I learned pandas I think I would have got comfortable in 6 weeks rather than 6 months.
Post reply on HN