Shifting

We wish to shift the values of a vector up or down by a given offset. Shifting allows us to access a vector’s previous or following values enabling us to compute changes.

Previous

We wish to shift the value of a vector down by one i.e. we wish to obtain the previous value.

In this example, we wish to compute the difference and rate of change between the value of each element of the column attr_1 and the value of its previous element. The data frame is sorted by the value of the column date.

df_2 = df \
    .sort_values(by='date') \
    .assign(delta=lambda x: x['attr_1'] - x['attr_1'].shift(),
            change=lambda x: x['delta'] / x['attr_1'])

Here is how this works:

  • We sort the data frame by date using sort_values(by='date') so we can get the previous value relative to the date column. See Sorting.
  • In order to refer to the data frame returned by sort_values in the assign() statement (more precisely; in the same data manipulation chain), we need to use a lambda function. See Basic Transformation.
  • In x['attr_1'].shift(), we use the shift() method of Pandas data frames to shift the values of the column attr_1 down by 1.
  • By default shift() shifts down by 1 position, i.e. an offset of 1. The argument name is periods.
  • In delta = x['attr_1'] - x['attr_1'].shift(), we compute the difference between the current value of attr_1 and its previous value.
  • In change = lambda x: x['delta'] / x['attr_1'], we compute the rate of change. We use a lambda function because we wish to refer to a column that we created in the same call to assign().

Extension: Specify Offset Value

We wish to shift the value of a vector down by an arbitrary offset.

In this example, we wish to compute the difference and rate of change between the value of each element of the column attr_1 and its value one week prior (seven days earlier). The data frame is sorted by the value of the column date.

df_2 = df \
    .sort_values(by='date') \
    .assign(prev_week=lambda x: x['attr_1'].shift(7),
            delta=lambda x: x['attr_1'] - x['prev_week'],
            change=lambda x: x['delta'] / x['attr_1'])

Here is how this works:

This code is similar to the code above with one exception: We pass the desired offset value to the argument periods of shift(), which here is periods=7.

Extension: Fill NA

When we shift a vector, we inadvertently create missing values corresponding to the shift. By default, those missing values are encoded as NA. We can specify an alternative value.

In this example, we wish to fill the missing values resulting from the shift with 0.

df_2 = df \
    .sort_values(by='date') \
    .assign(prev_val=lambda x: x['attr_1'].shift(fill_value=0),
            delta=lambda x: x['attr_1'] - x['prev_val'],
            change=lambda x: x['delta'] / x['attr_1'])

Here is how this works:

  • This code is similar to the code above with one exception: We pass the desired value to use to fill NAs generated by shifting to the argument fill_value of shift(), which here is fill_value=0.
  • Note that this approach is preferable to filling NAs after shifting because there may be other NAs in the data that we do not necessarily wish to replace.

Extension: Shift Per Group

We wish to shift the value of a column down by one i.e. we wish to obtain the previous value based on an ordered column per group, where the groups are specified by the column col_1

In this example, we wish to compute the difference between the value of each element of the column col_2 and the value of its previous element based on the column date for each group in col_1.

df_2 = df. \
    sort_values(by=['col_1', 'date']). \
    assign(lag_col=lambda x: x.groupby('col_1')['col_2'].shift(1))

Here is how this works:

This code is similar to the code above with one exception: We sort the data frame by col_1 and date and then group by col_1.

Next

We wish to shift the value of a vector up by one i.e. we wish to obtain the next value.

In this example, we wish to compute the difference and rate of change between the value of each element of the column attr_1 and the value of its next element. The data frame is sorted by the value of the column date.

df_2 = df \
    .sort_values(by='date') \
    .assign(next_val=lambda x: x['attr_1'].shift(-1),
            delta=lambda x: x['next_val'] - x['attr_1'],
            change=lambda x: x['delta'] / x['attr_1'])

Here is how this works:

  • This code is similar to the code under Previous above except that to shift up, we pass to the periods argument of shift() a negative value, which in this case is periods=-1 because we wish to shift up by one i.e. obtain the next value.
  • The same extensions covered under Previous above can be applied here.
PYTHON
I/O