I'm new to Pandas, and slightly new to Python as well (but not development in general). I've got a chunk of code that works, but it feels like I'm missing out on the Pandas/Python way of doing something, and would love some feedback.
In short, I'm doing calculations over a fixed period of time (say 60 months) where I apply a bunch of different financial calculations to generate various debits and credits. In this example I'm calculating gross incomes. I have models that represent the
Incomes, and want to create Dataframes with the corresponding data that I can eventually sum together for final answers.
Full code for experimentation is available at https://repl.it/repls/BlissfulShabbyColdfusion and repeated here:
### Income Models: ### start_date end_date amount yearly_raise 2019-01-01 2025-01-01 100.0 0.03 2020-01-01 2022-01-01 200.0 0. ### Base DataFrame: ### YearOffset 2019-01-01 0 2020-01-01 1 2021-01-01 2 2022-01-01 3 2023-01-01 4
Then the code is as follows:
import datetime import numpy as np import pandas as pd class Income: def __init__(self, start, end, amount, yearly_raise): self.start = start self.end = end self.amount = amount self.yearly_raise = yearly_raise # Two sample income models incomes = [ Income(datetime.date(2019, 1, 1), datetime.date(2025, 1, 1), 100, 0.03), Income(datetime.date(2020, 1, 1), datetime.date(2022, 1, 1), 200, 0.) ] # Create dataframe with index of the next five years dates = pd.date_range('2019-01-01', periods=5, freq='YS') df = pd.DataFrame(np.arange(len(dates)), index=dates, columns=['YearOffset']) def calculate_monthly_income(row): """Given a dataframe row, add up all incomes applicable to that row/month""" value = 0. for income in incomes: # Filter on overlapping date ranges first if pd.Timestamp(income.start) > row.name or pd.Timestamp(income.end) < row.name: continue value += row['GrossIncome'] + income.amount * (1 + income.yearly_raise) ** row['YearOffset'] return value # Initialize a GrossIncome column and do the math df['GrossIncome'] = 0. df['GrossIncome'] = df.apply(calculate_monthly_income, axis=1)
And the after, which yields the correct results:
### After Calculations: ### YearOffset GrossIncome 2019-01-01 0 100.000000 2020-01-01 1 303.000000 2021-01-01 2 306.090000 2022-01-01 3 309.272700 2023-01-01 4 112.550881
- I'm using
apply()to iterate through the rows and assigning the result back into the dataframe. This works and beats using a for loop, but seems like there's probably a better way to do this.
- I'm still using a
for ... into iterate through multiple
- The filtering by applicable date range also seems like I should probably be using some built-in Pandas thing like
mask, but I'm unsure of where to start looking.
There's a lot here, I know, but if there's some good feedback on the current approach or recommendations for better approaches, I'd love to hear them!