Split budget column by total number of months to give average monthly budget

Question

I am trying to create a new column that has the average monthly budget based on the duration of a campaign

My current data set looks like this

Client	Start Date	End Date	Campaign	Total Budget
ABC	01/01/2024	31/01/2024	123	1,000
DEF	01/01/2024	31/03/2024	456	15,000
XYZ	01/01/2024	30/06/2024	789	60,000

What I would like to do is to equally divide the Total Budget column for each line based on the duration of the start/end dates

• Row 1: 1 month

• Row 2: 3 months

• Row 3: 6 months

• etc

I'm not sure where to begin with trying to code this so I was wondering if this was even possible to do?

My expected result would be the below

Client	Start Date	End Date	Campaign	Total Budget	Avg Monthly Budget
ABC	01/01/2024	31/01/2024	123	1,000	1,000
DEF	01/01/2024	31/03/2024	456	15,000	3,000
XYZ	01/01/2024	30/06/2024	789	60,000	10,000

Answer 1

1. If the dates are in datetime format, you can subtract them, which will generate a timedelta.
2. From that you can get the number of days and calculate the corresponding number of months.
3. Then just divide the Total Budget by the number of months.

# optional: convert dates to datetime if they aren't already
df["Start Date"] = pd.to_datetime(df["Start Date"], format="%d/%m/%Y")
df["End Date"] = pd.to_datetime(df["End Date"], format="%d/%m/%Y")

df["Avg Monthly Budget"] = df["Total Budget"] / (
    (df["End Date"] - df["Start Date"]).dt.days // 30
)

Result:

  Client Start Date   End Date  Campaign  Total Budget  Avg Monthly Budget
0    ABC 2024-01-01 2024-01-31       123          1000              1000.0
1    DEF 2024-01-01 2024-03-31       456         15000              5000.0
2    XYZ 2024-01-01 2024-06-30       789         60000             10000.0