pandas 创建新的日期行并向前填充列值 [英] Pandas create new date rows and forward fill column values

问题描述

我有一个像这样的数据框:

I have a dataframe like this:

id     date       value
 1  12/01/2016      5 
 1  25/02/2016      7 
 1  10/03/2017      13 
 2  02/04/2016      0 
 2  06/07/2016      1 
 2  18/04/2017      6 

对于每个ID，都有一个带有值的开始日期，每隔几个月就有一行带有日期和值的行. 我想为每个ID创建一个时间序列.因此，我想在第二天(直到今天)插入新行，该值将从上一行向前填充.

For each id there is a start date with a value and every few months there is another row with a date and a value. I'd like to create a timeseries of each id. So I'd like to insert new rows with the next day (until today), where the value will be forward filled from the previous row.

因此数据框变为:

id     date       value
 1  12/01/2016      5 
 1  13/01/2016      5 
 1  14/01/2016      5 
 1  15/01/2016      5
 ...
 1  20/04/2017      13 
 ...
 2  18/04/2017      6 
 2  19/04/2017      6 
 2  20/04/2017      6 

例如，回答类似问题:追加datetime行并向前填充熊猫数据框中的数据

Eg answer to a similar question: Appending datetime rows and forward filling data in pandas dataframe

但是我的数据框没有为日期时间建立索引.

But my dataframe isn't datetime indexed.

感谢任何帮助！

推荐答案

考虑groupby和merge方法:

import pandas as pd
from io import StringIO
from datetime import date

txt= """
id     date       value
 1  12/01/2016      5 
 1  25/02/2016      7 
 1  10/03/2017      13 
 2  02/04/2016      0 
 2  06/07/2016      1 
 2  18/04/2017      6 
"""

df = pd.read_table(StringIO(txt), sep="\s+", parse_dates=[1], dayfirst=True)

def expand_dates(ser):
    return pd.DataFrame({'date': pd.date_range(ser['date'].min(), date.today(), freq='D')})

newdf = df.groupby(['id']).apply(expand_dates).reset_index()\
          .merge(df, how='left')[['id', 'date', 'value']].ffill()

这篇关于 pandas 创建新的日期行并向前填充列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！