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

61–70 of 77 posts

Re: A Relational Spreadsheet

#61

Post by brahbrah »

gigatexal wrote: post #49
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…

I see your pandas (even the arrow based pandas 2.0) and raise you polars. ;)

I see your:

    (
        polars_df1
        .join(polars_df2, on=['state', 'county', 'timestamp'], suffix='_r')
        .with_column(
           ( pl.col('val') + pl.col('val_r')).alias('val')
        )
        .select(['state', 'county', 'timestamp', 'val'])
    )
and raise you:

    pandas_df1 + pandas_df2

Re: A Relational Spreadsheet

#62

Post by webdoodle »

At my previous job, one of the accountants asked me on my first week if I could solve a spreadsheet problem they had involving payroll for it's ~250 employee's. As a 20 year database developer, manager and analyst, I was really hesitant, but after she showed me the amount of time it was taking her to do this every 2 weeks, I sort of felt obliged.

All of the data was from several flat files, it consisted of each employee's hours for the period, accrued sick time, vacation time, comp time, etc. She had to manually copy and paste this data into Excel, fix formatting issues, then use it to calculate several totals. It was then fed into another system (that would only accept Excel files for input), that would print the pay checks with all this data summarized.

This was clearly critical for the organization, and it was taking her a couple days to do manually. I begged her, and my supervisor, to let me do the whole thing in SQL Server or MySQL, but she wouldn't have it, because it HAD to be something she could adjust manually. I explained I could make it look and feel like Excel, but she wouldn't have it.

I realize to anyone in databases (including me), I should have fought harder to do it right. However, as a new employee it was more important to gain trust than show off new technologies. So I compromised.

I setup a system that imported all of the data into SQL Server, cleaned it up, then exported it to Excel. The tricky part was working with VBA, and creating recursive expressions in Excel during the export. It took me about a week of time, mostly because I hadn't used VBA in a decade. The end product, from her point of view, looked exactly like her original spreadsheet. She could then still manually fix any issues that came about before importing it into the paycheck printer.

In the end, it saved her 2 days a week, or 8 days a month or 96 days a year. I became her new best friend. I was always the first to get my paycheck, and never had any issues when I needed support from finance. Probably the smartest move I made early in that job. ;)

Re: A Relational Spreadsheet

#63

Post by anigbrowl »

anigbrowl wrote: post #58
chaps wrote: post #55

Earlier quoted context omitted.

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']…

mfw realizing the last line should be cities.plot... ToT

Re: A Relational Spreadsheet

#66

Post by gigatexal »

brahbrah wrote: post #61
gigatexal wrote: post #49

Earlier quoted context omitted.

I see your pandas (even the arrow based pandas 2.0) and raise you polars. ;)

I see your: ( polars_df1 .join(polars_df2, on=['state', 'county', 'timestamp'], suffix='_r') .with_column( ( pl.col('val') + pl.col('val_r')).alias('val') ) .select(['state', 'county', 'timestamp', 'val']) ) and raise you: pandas_df1 + pandas_df2

Hmmm touché

Re: A Relational Spreadsheet

#67

Post by emeril »

webdoodle wrote: post #62

At my previous job, one of the accountants asked me on my first week if I could solve a spreadsheet problem they had involving payroll for it's ~250 employee's. As a 20 year database developer, manager and analyst, I was really hesitant, but after she showed me the amount of time it was taking her to do this every 2 weeks, I sort of felt obliged. All of the data was from several flat files, it consisted of each emplo…

fwiw, one can import the contents of a list of excel files into one big excel file using some relatively vanilla vba without having to involve sql

I personally usually only involve a database if there's some significant amount of data involved and/or if it's significantly more efficient than the above

I don't get the hate on excel in this thread compared to rdbms's; i.e., just use a few vlookups for some joins which are usually sufficiently performant especially if you don't have that much data in which case you shouldn't be using excel in the first place... (though, if you're "clever" and want higher performance vlookups, just use it's often maligned indexed lookup feature more carefully to allow for both exact and indexed lookups which can be like >100x faster...)

Re: A Relational Spreadsheet

#68

Post by emeril »

gigatexal wrote: post #47

“ In trying to tidy up my finances I had to rebalance my portfolio, but even though I had all of my trades loaded into a spreadsheet, I found it difficult to aggregate them by ticker / account / category and calculate the actionable bit — the stocks and quantities I needed to buy/sell to meet a desired allocation.” Is this not what a pivot table does?

that's exactly what a pivot table does

Re: A Relational Spreadsheet

#69

Post by brahbrah »

gigatexal wrote: post #66
brahbrah wrote: post #61

Earlier quoted context omitted.

I see your: ( polars_df1 .join(polars_df2, on=['state', 'county', 'timestamp'], suffix='_r') .with_column( ( pl.col('val') + pl.col('val_r')).alias('val') ) .select(['state', 'county', 'timestamp', 'val']) ) and raise you: pandas_df1 + pandas_df2

Hmmm touché

Fwiw I actually do prefer polars for standard long format relational operations. But sometimes it’s just more convenient to work with data in other ways. Another example:

Polars:

    polars_df.with_column(
        pl.when(pl.col('timestamp').is_between(
            datetime('2023-03-01'),
            datetime('2023-03-31'),
            include_bounds=True
        )).then(pl.col('val') * 1.1)
        .otherwise(pl.col('val'))
        .alias('val')
    )
Pandas:

     pandas_df.loc['2023-03'] *= 1.1

Re: A Relational Spreadsheet

#70

Post by mamcx »

somat wrote: post #2

I want the same, I want it so much that I have been working on gasp gui code to do such, an activity I have found I am profoundly bad at. But the theory is, I love the relational database, they are a sort of rigorous superset of the spreadsheet, and I have replaced all my spreadsheets with database tables, however while it is very hard to beat sql for rich comprehensive data transforms and analysis, ad-hoc data entry…

I also work in [this space](https://tablam.org).

The tool that was great for this is FoxPro.

Is like access, but the genius thing is that it include a super-charged "repl" aka: the command window.

It allow to combine GUI + Help + Terminal in one single tool, so you can do

    use table
    browse // show a data table
    go first // you can navigate both by mouse/keyboard and by code!
Post reply on HN