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: https://github.com/pola-rs/polars
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.
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['confused'].very(simple['example']) # the thing being explained
I can't recommend a book unfortunately - I just worked with McKinney's own materials and suffered for a while until things started to click. Once I realized what I found frustrating about the tutorial materials I began to realize that I could read it more selectively - and also that the code base is in constant flux. There are often 2 or 3 different ways to do the same thing, with different approaches being deprecated or promoted over time.
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')
> This leads to a lot of logical conditions, since to prevent double-counting each aggregate tuple’s conditions must assert both that the matching tuples matched and that the non-matching tuples didn’t.
I understand and know to handle edge cases with SQL, I'd have to learn that all over again with a custom language and its own unique quirks. If I was putting such an investment, I'd want it to be better established.
Unless of course it's to scratch an itch and this is a perfect way to start out and share. Nice work and good luck.
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
(
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'])
)
pandas_df1 + pandas_df2Polars:
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_df.loc['2023-03'] *= 1.1