python - Pandas convert yearly to monthly -
i'm working on pulling financial data, in formatted in yearly , other monthly. model need of monthly, therefore need same yearly value repeated each month. i've been using stack post , trying adapt code data.
here 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 desired output first 5 rows:
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 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, not repeating months expected
you want resample
first, need set index resample work. backfill , reset 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 handle per ticker
df.set_index('date').groupby('ticker', group_keys=false) \ .resample('m').bfill().reset_index() 
Comments
Post a Comment