How to create a lagged data structure using pandas dataframe

user_1232506 0
Viewed 71691

Example

s=pd.Series([5,4,3,2,1], index=[1,2,3,4,5])
print s 
1    5
2    4
3    3
4    2
5    1

Is there an efficient way to create a series. e.g. containing in each row the lagged values (in this example up to lag 2)

3    [3, 4, 5]
4    [2, 3, 4]
5    [1, 2, 3]

This corresponds to s=pd.Series([[3,4,5],[2,3,4],[1,2,3]], index=[3,4,5])

How can this be done in an efficient way for dataframes with a lot of timeseries which are very long?

Thanks

Edited after seeing the answers

ok, at the end I implemented this function:

def buildLaggedFeatures(s,lag=2,dropna=True):
'''
Builds a new DataFrame to facilitate regressing over all possible lagged features
'''
if type(s) is pd.DataFrame:
    new_dict={}
    for col_name in s:
        new_dict[col_name]=s[col_name]
        # create lagged Series
        for l in range(1,lag+1):
            new_dict['%s_lag%d' %(col_name,l)]=s[col_name].shift(l)
    res=pd.DataFrame(new_dict,index=s.index)

elif type(s) is pd.Series:
    the_range=range(lag+1)
    res=pd.concat([s.shift(i) for i in the_range],axis=1)
    res.columns=['lag_%d' %i for i in the_range]
else:
    print 'Only works for DataFrame or Series'
    return None
if dropna:
    return res.dropna()
else:
    return res

it produces the wished outputs and manages the naming of columns in the resulting DataFrame.

For a Series as input:

s=pd.Series([5,4,3,2,1], index=[1,2,3,4,5])
res=buildLaggedFeatures(s,lag=2,dropna=False)
   lag_0  lag_1  lag_2
1      5    NaN    NaN
2      4      5    NaN
3      3      4      5
4      2      3      4
5      1      2      3

and for a DataFrame as input:

s2=s=pd.DataFrame({'a':[5,4,3,2,1], 'b':[50,40,30,20,10]},index=[1,2,3,4,5])
res2=buildLaggedFeatures(s2,lag=2,dropna=True)

   a  a_lag1  a_lag2   b  b_lag1  b_lag2
3  3       4       5  30      40      50
4  2       3       4  20      30      40
5  1       2       3  10      20      30
pandas python
9 Answers

As mentioned, it could be worth looking into the rolling_ functions, which will mean you won't have as many copies around.

One solution is to concat shifted Series together to make a DataFrame:

In [11]: pd.concat([s, s.shift(), s.shift(2)], axis=1)
Out[11]: 
   0   1   2
1  5 NaN NaN
2  4   5 NaN
3  3   4   5
4  2   3   4
5  1   2   3

In [12]: pd.concat([s, s.shift(), s.shift(2)], axis=1).dropna()
Out[12]: 
   0  1  2
3  3  4  5
4  2  3  4
5  1  2  3

Doing work on this will be more efficient that on lists...

user_12402680

Here is a cool one liner for lagged features with _lagN suffixes in column names using pd.concat:

lagged = pd.concat([s.shift(lag).rename('{}_lag{}'.format(s.name, lag+1)) for lag in range(3)], axis=1).dropna()
user_34424090

You can do following:

s=pd.Series([5,4,3,2,1], index=[1,2,3,4,5])
res = pd.DataFrame(index = s.index)
for l in range(3):
    res[l] = s.shift(l)
print res.ix[3:,:].as_matrix()

It produces:

array([[ 3.,  4.,  5.],
       [ 2.,  3.,  4.],
       [ 1.,  2.,  3.]])

which I hope is very close to what you are actually want.

user_11814820

Assuming you are focusing on a single column in your data frame, saved into s. This shortcode will generate instances of the column with 7 lags. 

s=pd.Series([5,4,3,2,1], index=[1,2,3,4,5], name='test')
shiftdf=pd.DataFrame()
for i in range(3):
    shiftdf = pd.concat([shiftdf , s.shift(i).rename(s.name+'_'+str(i))], axis=1)

shiftdf

>>
test_0  test_1  test_2
1   5   NaN NaN
2   4   5.0 NaN
3   3   4.0 5.0
4   2   3.0 4.0
5   1   2.0 3.0
user_16246810

Based on the proposal by @charlie-brummitt, here is a revision that fix a set of columns:

def shift_timeseries_by_lags(df, fix_columns, lag_numbers, lag_label='lag'):
    df_fix = df[fix_columns]
    df_lag = df.drop(columns=fix_columns)

    df_lagged = pd.concat({f'{lag_label}_{lag}':
        df_lag.shift(lag) for lag in lag_numbers},
        axis=1)
    df_lagged.columns = ['__'.join(reversed(x)) for x in df_lagged.columns.to_flat_index()]

    return pd.concat([df_fix, df_lagged], axis=1)

Here is an example of usage:

df = shift_timeseries_by_lags(df_province_cases, fix_columns=['country', 'state'], lag_numbers=[1,2,3])

I personally prefer the lag name as suffix. But can be changed removing reversed().

user_37809570
Related