Clever Geek Handbook

Pandas convert yearly to monthly

I am working on extracting financial data where some is formatted annually and others monthly. My model will need all of this on a monthly basis, so I need the same yearly value that repeats every month. I used this column and tried to adapt the code to my data.

Here is my dataframe:

df.head()

   date ticker value
0 1999-12-31  ECB/RA6  1.0
1 2000-12-31  ECB/RA6  4.0
2 2001-12-31  ECB/RA6  2.0
3 2002-12-31  ECB/RA6  3.0
4 2003-12-31  ECB/RA6  2.0

Here is my desired output, the first 5 lines:

   date ticker value
0 1999-12-31  ECB/RA6  1.0
1 2000-01-31  ECB/RA6  4.0
2 2000-02-28  ECB/RA6  4.0
3 2000-13-31  ECB/RA6  4.0
4 2000-04-30  ECB/RA6  4.0

And my code:

df['date'] = pd.to_datetime(df['date'], format='%Y-%m')
df = df.pivot(index='date', columns='ticker')
start_date = df.index.min() - pd.DateOffset(day=1)
end_date = df.index.max() + pd.DateOffset(day=31)
dates = pd.date_range(start_date, end_date, freq='M')
dates.name = 'date'
df = df.reindex(dates, method='ffill')

df = df.stack('ticker')
df = df.sortlevel(level=1)
df = df.reset_index()

However, it does not repeat the expected months

+3


source to share


2 answers


Do you want to resample

First you need to set the index so that it resample

works. Then you dump and reset the index.

df.set_index('date').resample('M').bfill().reset_index()

         date   ticker  value
0  1999-12-31  ECB/RA6    1.0
1  2000-01-31  ECB/RA6    4.0
2  2000-02-29  ECB/RA6    4.0
3  2000-03-31  ECB/RA6    4.0
4  2000-04-30  ECB/RA6    4.0
5  2000-05-31  ECB/RA6    4.0
6  2000-06-30  ECB/RA6    4.0
7  2000-07-31  ECB/RA6    4.0
8  2000-08-31  ECB/RA6    4.0
9  2000-09-30  ECB/RA6    4.0
10 2000-10-31  ECB/RA6    4.0
11 2000-11-30  ECB/RA6    4.0
12 2000-12-31  ECB/RA6    4.0
13 2001-01-31  ECB/RA6    2.0
14 2001-02-28  ECB/RA6    2.0
15 2001-03-31  ECB/RA6    2.0
...


To deal with this for ticker 

df.set_index('date').groupby('ticker', group_keys=False) \
    .resample('M').bfill().reset_index()
+4


source


I am new to Stack Overflow and cannot add my question as a comment. So I'm posting it back. How can I achieve the same if my df index instead of '12 -31 'has '01 -01-2000', '01-01-2001', '01-01-2002' etc. Monthly episodes end at "01 -01" last year, but I'd like the range to be "12 -01" last year. Appreciate any help.



0


source






More articles:


All Articles