Convert Quarterly Dataframe To Monthly And Fill Missing Values In Pandas

For a quarterly dataframe like this: date gdp rate 0 2003/3/1 523.82 0.1 1 2003/6/1 1172.83 0.2 2 2003/9/1 1882.48 0.4 3 2003/12/1 3585.72 0.1 4

Solution 1:

Another solution is create DatetimeIndex, then use DataFrame.asfreq with method='bfill' and MS for start of month and last convert to periods by DataFrame.to_period:

df['date']=pd.to_datetime(df['date'])df=df.sort_values(by=['date'],ascending=[True])df.set_index('date',inplace=True)df=df.asfreq('MS',method='bfill').to_period('M').reset_index()print(df)dategdprate02003-03   523.820.112003-04  1172.83   0.222003-05  1172.83   0.232003-06  1172.83   0.242003-07  1882.48   0.452003-08  1882.48   0.462003-09  1882.48   0.472003-10  3585.72   0.182003-11  3585.72   0.192003-12  3585.72   0.1102004-01   706.770.2112004-02   706.770.2122004-03   706.770.2

Solution 2:

This works:

import pandas as pd

df['date'] = pd.to_datetime(df['date']).dt.to_period('M')
# df['date'] =  pd.to_datetime(df['date'], format='%Y/%m/%d')df = df.sort_values(by=['date'], ascending=[True])
df.set_index('date', inplace=True)

df = df.resample('M').bfill().reset_index()
print(df)

output:

dategdprate02003-03   523.820.112003-04  1172.83   0.222003-05  1172.83   0.232003-06  1172.83   0.242003-07  1882.48   0.452003-08  1882.48   0.462003-09  1882.48   0.472003-10  3585.72   0.182003-11  3585.72   0.192003-12  3585.72   0.1102004-01   706.770.2112004-02   706.770.2122004-03   706.770.2