pandas 每年转换为每月 [英] Pandas convert yearly to monthly

问题描述

我正在研究提取财务数据，其中一些数据以年度格式设置，而另一些则是每月格式.我的模型每个月都需要它，因此我需要每个月重复一次相同的年值.我一直在使用此堆栈帖子，并尝试修改代码对我的数据.

I'm working on pulling financial data, in which some is formatted in yearly and other is monthly. My model will need all of it monthly, therefore I need that same yearly value repeated for each month. I've been using this stack post and trying to adapt the code to my data.

这是我的数据框:

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

这是我期望的输出前5行:

Here is my 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

还有我的代码:

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 is not repeating the months as expected

推荐答案

您要 resample

首先，您需要设置索引，以使resample起作用.然后回填并重置索引.

First, you need to set the index so that resample will work. Then you backfill 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
...

---

要按照ticker

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

这篇关于 pandas 每年转换为每月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！