Python Forum

Hi everyone,

I am looking at getting quarterly data to monthly. For the amounts I want to get the average between the two amounts. Below is an example of what I mean:

Have:

d = {'Date': [31/12/2010,31/03/2011,30/06/2011], 'Amount': [1000,1300,1550]}
df = pd.DataFrame(data=d)
df

Want:

d = {'Date': [31/12/2010,
31/01/2011,
28/02/2011,
31/03/2011,
30/04/2011,
31/05/2011,
30/06/2011,
], 'Amount': [1000,
1100,
1200,
1300,
1383,
1467,
1550,
]}
df = pd.DataFrame(data=d)
df

I appreciate any help.
Thanks

d = {'Date': [31/12/2010,31/03/2011,30/06/2011], 'Amount': [1000,1300,1550]}

This is not valid Python code. Try to run it with the interpreter.

You didn't say how you want the amounts divided up!

import pandas as pd

d = {'Date': ["31/12/2010","31/03/2011","30/06/2011"], 'Amount': [1000,1300,1550]}
df = pd.DataFrame(data=d)
# convert to datetime
# df['Mycol'] = pd.to_datetime(df['Mycol'], format='%d%b%Y:%H:%M:%S.%f')
df['Date'] = pd.to_datetime(df["Date"], format='%d/%m/%Y')
df.dtypes # returns as below
"""
Date      datetime64[ns]
Amount             int64
dtype: object"""
start_date = df["Date"].min() # Timestamp('2010-12-31 00:00:00')
end_date = df["Date"].max() # Timestamp('2011-06-30 00:00:00')
# fill column starting from a given start date and a given end date
# pandas.date_range(start=None, end=None, periods=None, freq=None, tz=None, normalize=False, name=None, inclusive='both', *, unit=None, **kwargs)
df2 = pd.DataFrame({'date':pd.date_range(start=start_date, end=end_date, freq="ME")})

Gives:

Output:
df2
                   
        date
0 2010-12-31
1 2011-01-31
2 2011-02-28
3 2011-03-31
4 2011-04-30
5 2011-05-31
6 2011-06-30

Missed the amounts part!

amounts = [1000, 1100, 1200, 1300, 1383, 1467, 1550]
df2["amounts"] = amounts

The amounts are average across the range. I used an easy example because I did not know how to explain it. so 1000,1300 the difference is 300 and the time steps between the quarters is 3 so it increases by 100 each time. so 1000, 1100, 1200 and then you get 1300. For the second quarter I used a more complicated example so someone would not share a solution that added a fixed amount each time.

So to (Q2-Q1)/3) + Q1 = Q1t+1 and then for the following month (Q2-Q1)/3)+Q1t+1 = Q1t+2 and the Q2=Q2 which should also = (Q2-Q1)/3) + Q1t+2.

I hope that makes sense

Thanks