<p dir="auto">When trying to resample transactions data where there are infrequent transactions for a large number of people, I get horrible performance. Compare the function annualize with the clunkier but faster annualize2 below. Ideally resample should be able to handle multiindex data and resample on 1 of the dimensions without the need to resort to groupby.</p>
<h4 dir="auto">Code Sample, a copy-pastable example if possible</h4>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="import pandas
import numpy as np

def annualize(df):
    def resample(df):
        df = df.set_index('DATE')
        df = df.resample('A').ffill()
        df = df.reset_index()
        return df
    df = df.groupby('ID', as_index = False).apply(resample)
    df['YEAR'] = df['DATE'].dt.year
    df = df.reset_index(drop = True)
    return df

def annualize2(df):
    #DF must be sorted by ID then DATE
    df = df.sort_values(['ID','DATE']).reset_index(drop = True)
    #Extract the year from the date and keep last observation from each year
    df['YEAR'] = df['DATE'].dt.year
    df = df.drop_duplicates(subset = ['ID','YEAR'], keep = 'last')
    #Get the YEAR and ID of the next record down
    ndf = df[['ID', 'YEAR']].shift(-1)
    #Want to duplicate records where ID's between record pairs match and spacing is more than year
    match = (ndf['ID'] == df['ID'])
    reps = np.array(np.maximum(match*(ndf['YEAR'] - df['YEAR']),1), dtype = int)
    reps[-1] = 1 # the last value will by -inf
    #Do the duplication
    df = df.loc[np.repeat(df.index, reps)]
    #Now we need to fix the year field
    cs = np.ones(len(df))
    cs[np.cumsum(reps[:-1])] -= reps[:-1]
    df['YEAR'] = df['YEAR'] + np.cumsum(cs)-1
    df = df.reset_index(drop = True)
    return df

if __name__ == '__main__':
    import StringIO
    import timeit
    #Generate some transaction record data
    data='''ID,DATE,STATUS
    1,3/1/2005,A
    1,4/30/2005,B
    1,6/30/2010,C
    1,8/20/2010,D
    '''
    df = pandas.read_csv(StringIO.StringIO(data))
    df['DATE'] = pandas.to_datetime(df['DATE'])
    print(annualize(df))
    print(annualize2(df))

    #then repeat the rows of the table to get a more realistic sample size for measuring performance
    df = df.loc[np.tile(df.index.values, 10000)]
    df['ID'] = np.repeat(np.arange(10000), 4)

    print('annualize')
    print(timeit.timeit('annualize(df)', setup = 'from __main__ import annualize, df', number = 1))
    print('annualize2')
    print(timeit.timeit('annualize2(df)', setup = 'from __main__ import annualize2, df', number = 1))"><pre class="notranslate"><code class="notranslate">import pandas
import numpy as np

def annualize(df):
    def resample(df):
        df = df.set_index('DATE')
        df = df.resample('A').ffill()
        df = df.reset_index()
        return df
    df = df.groupby('ID', as_index = False).apply(resample)
    df['YEAR'] = df['DATE'].dt.year
    df = df.reset_index(drop = True)
    return df

def annualize2(df):
    #DF must be sorted by ID then DATE
    df = df.sort_values(['ID','DATE']).reset_index(drop = True)
    #Extract the year from the date and keep last observation from each year
    df['YEAR'] = df['DATE'].dt.year
    df = df.drop_duplicates(subset = ['ID','YEAR'], keep = 'last')
    #Get the YEAR and ID of the next record down
    ndf = df[['ID', 'YEAR']].shift(-1)
    #Want to duplicate records where ID's between record pairs match and spacing is more than year
    match = (ndf['ID'] == df['ID'])
    reps = np.array(np.maximum(match*(ndf['YEAR'] - df['YEAR']),1), dtype = int)
    reps[-1] = 1 # the last value will by -inf
    #Do the duplication
    df = df.loc[np.repeat(df.index, reps)]
    #Now we need to fix the year field
    cs = np.ones(len(df))
    cs[np.cumsum(reps[:-1])] -= reps[:-1]
    df['YEAR'] = df['YEAR'] + np.cumsum(cs)-1
    df = df.reset_index(drop = True)
    return df

if __name__ == '__main__':
    import StringIO
    import timeit
    #Generate some transaction record data
    data='''ID,DATE,STATUS
    1,3/1/2005,A
    1,4/30/2005,B
    1,6/30/2010,C
    1,8/20/2010,D
    '''
    df = pandas.read_csv(StringIO.StringIO(data))
    df['DATE'] = pandas.to_datetime(df['DATE'])
    print(annualize(df))
    print(annualize2(df))

    #then repeat the rows of the table to get a more realistic sample size for measuring performance
    df = df.loc[np.tile(df.index.values, 10000)]
    df['ID'] = np.repeat(np.arange(10000), 4)

    print('annualize')
    print(timeit.timeit('annualize(df)', setup = 'from __main__ import annualize, df', number = 1))
    print('annualize2')
    print(timeit.timeit('annualize2(df)', setup = 'from __main__ import annualize2, df', number = 1))
</code></pre></div>
<h4 dir="auto">Expected Output</h4>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="        DATE  ID STATUS  YEAR
0 2005-12-31   1      B  2005
1 2006-12-31   1      B  2006
2 2007-12-31   1      B  2007
3 2008-12-31   1      B  2008
4 2009-12-31   1      B  2009
5 2010-12-31   1      D  2010
   ID       DATE STATUS    YEAR
0   1 2005-04-30      B  2005.0
1   1 2005-04-30      B  2006.0
2   1 2005-04-30      B  2007.0
3   1 2005-04-30      B  2008.0
4   1 2005-04-30      B  2009.0
5   1 2010-08-20      D  2010.0
annualize
26.4788940619
annualize2
0.0899452309903"><pre class="notranslate"><code class="notranslate">        DATE  ID STATUS  YEAR
0 2005-12-31   1      B  2005
1 2006-12-31   1      B  2006
2 2007-12-31   1      B  2007
3 2008-12-31   1      B  2008
4 2009-12-31   1      B  2009
5 2010-12-31   1      D  2010
   ID       DATE STATUS    YEAR
0   1 2005-04-30      B  2005.0
1   1 2005-04-30      B  2006.0
2   1 2005-04-30      B  2007.0
3   1 2005-04-30      B  2008.0
4   1 2005-04-30      B  2009.0
5   1 2010-08-20      D  2010.0
annualize
26.4788940619
annualize2
0.0899452309903
</code></pre></div>
<h4 dir="auto">output of <code class="notranslate">pd.show_versions()</code></h4>
<h2 dir="auto">INSTALLED VERSIONS</h2>
<p dir="auto">commit: None<br>
python: 2.7.11.final.0<br>
python-bits: 64<br>
OS: Windows<br>
OS-release: 7<br>
machine: AMD64<br>
processor: Intel64 Family 6 Model 62 Stepping 4, GenuineIntel<br>
byteorder: little<br>
LC_ALL: None<br>
LANG: None</p>
<p dir="auto">pandas: 0.18.1<br>
nose: 1.3.7<br>
pip: 8.1.2<br>
setuptools: 20.3<br>
Cython: 0.23.4<br>
numpy: 1.11.0<br>
scipy: 0.17.0<br>
statsmodels: 0.6.1<br>
xarray: None<br>
IPython: 4.1.2<br>
sphinx: 1.3.5<br>
patsy: 0.4.0<br>
dateutil: 2.5.3<br>
pytz: 2016.4<br>
blosc: None<br>
bottleneck: 1.0.0<br>
tables: 3.2.2<br>
numexpr: 2.5<br>
matplotlib: 1.5.1<br>
openpyxl: 2.3.2<br>
xlrd: 0.9.4<br>
xlwt: 1.0.0<br>
xlsxwriter: 0.8.4<br>
lxml: 3.6.0<br>
bs4: 4.4.1<br>
html5lib: None<br>
httplib2: None<br>
apiclient: None<br>
sqlalchemy: 1.0.12<br>
pymysql: None<br>
psycopg2: None<br>
jinja2: 2.8<br>
boto: 2.39.0<br>
pandas_datareader: None</p>
<p dir="auto">Hello,</p>
<p dir="auto">I work on a dataframe with a multi index (Date, InputTime) and this dataframe may contain some NaN values in the columns (Value, Id). I want to fill forward value but by Date only and I don't find anyway to do this a in a very efficient way. I'm using Pandas 0.16.2 and numpy 1.9.2.</p>
<p dir="auto">Here is the type of dataframe I have :</p>
<p dir="auto">Dataframe example<br>
<a target="_blank" rel="noopener noreferrer nofollow" href="https://cloud.githubusercontent.com/assets/15088142/10427077/b33c3e20-70e6-11e5-9c28-1644791ef46d.png"><img src="https://cloud.githubusercontent.com/assets/15088142/10427077/b33c3e20-70e6-11e5-9c28-1644791ef46d.png" alt="image" style="max-width: 100%;"></a></p>
<p dir="auto">And here is the result I want :</p>
<p dir="auto"><a target="_blank" rel="noopener noreferrer nofollow" href="https://cloud.githubusercontent.com/assets/15088142/10427084/c331ff54-70e6-11e5-8ee8-bb499c6cc6e5.png"><img src="https://cloud.githubusercontent.com/assets/15088142/10427084/c331ff54-70e6-11e5-8ee8-bb499c6cc6e5.png" alt="image" style="max-width: 100%;"></a></p>
<p dir="auto">So to properly fillback by date I can use groupby(level=0) function. The groupby call is fast but the fill  forward function applied on the "group by dataframe" is really too slow.</p>
<p dir="auto">Here is the code I use to compare simple fill forward (which doesn't give the expected result but is run very quickly) and expected fill forward by date (which give expected result but is really too slow).</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="import numpy as np
import pandas as pd
import datetime as dt

# Show pandas &amp; numpy versions
print('pandas '+pd.__version__)
print('numpy '+np.__version__)

# Build a big list of (Date,InputTime,Value,Id)
listdata = []
d = dt.datetime(2001,10,6,5)
for i in range(0,100000):
    listdata.append((d.date(), d, 2*i if i%3==1 else np.NaN, i if i%3==1 else np.NaN))
    d = d + dt.timedelta(hours=8)

# Create the dataframe with Date and InputTime as index
df = pd.DataFrame.from_records(listdata, index=['Date','InputTime'], columns=['Date', 'InputTime', 'Value', 'Id'])

# Simple Fill forward on index
start = dt.datetime.now()
for col in df.columns:
    df[col] = df[col].ffill()
end = dt.datetime.now()
print &quot;Time to fill forward on index = &quot; + str((end-start).total_seconds()) + &quot; s&quot;

# Fill forward on Date (first level of index)
start = dt.datetime.now()
for col in df.columns:
    df[col] = df[col].groupby(level=0).ffill()
end = dt.datetime.now()
print &quot;Time to fill forward on Date only = &quot; + str((end-start).total_seconds()) + &quot; s&quot;"><pre class="notranslate"><code class="notranslate">import numpy as np
import pandas as pd
import datetime as dt

# Show pandas &amp; numpy versions
print('pandas '+pd.__version__)
print('numpy '+np.__version__)

# Build a big list of (Date,InputTime,Value,Id)
listdata = []
d = dt.datetime(2001,10,6,5)
for i in range(0,100000):
    listdata.append((d.date(), d, 2*i if i%3==1 else np.NaN, i if i%3==1 else np.NaN))
    d = d + dt.timedelta(hours=8)

# Create the dataframe with Date and InputTime as index
df = pd.DataFrame.from_records(listdata, index=['Date','InputTime'], columns=['Date', 'InputTime', 'Value', 'Id'])

# Simple Fill forward on index
start = dt.datetime.now()
for col in df.columns:
    df[col] = df[col].ffill()
end = dt.datetime.now()
print "Time to fill forward on index = " + str((end-start).total_seconds()) + " s"

# Fill forward on Date (first level of index)
start = dt.datetime.now()
for col in df.columns:
    df[col] = df[col].groupby(level=0).ffill()
end = dt.datetime.now()
print "Time to fill forward on Date only = " + str((end-start).total_seconds()) + " s"
</code></pre></div>
<p dir="auto">Here are the time results I have:</p>
<p dir="auto"><a target="_blank" rel="noopener noreferrer nofollow" href="https://cloud.githubusercontent.com/assets/15088142/10427113/fd415ec4-70e6-11e5-96fd-5b669e738843.png"><img src="https://cloud.githubusercontent.com/assets/15088142/10427113/fd415ec4-70e6-11e5-96fd-5b669e738843.png" alt="image" style="max-width: 100%;"></a></p>
<p dir="auto">So, the fill foward on the group by dataframe is 10000 times slower than the simple fill forward. I cannot understand why pandas is running so slowly. I need to have comparable perfs with the simple fill forward so just a couple of milliseconds.</p>
<p dir="auto">Could somebody address the performance issue? Or give me a solution to do this kind of action in a very efficient way?</p>
<p dir="auto">Thanks</p>