Skip to content
Calculate Shifts, Percent Change, and Windows on Time Series
  • AI Chat
  • Code
  • Report
  • Calculate Shifts, Percent Change, and Windows on Time Series

    Use this template to perform foundational manipulations on your time-series data. It covers shifting your data backward or forwards in time (i.e., lags and leads), calculating the percent change between periods, and window aggregations. There are many applications for these types of manipulations, including tracking financial assets, sales forecasting, and analyzing marketing data.

    To swap in your dataset in this template, the following is required:

    • You must have a dataset with a date column that can be parsed by pandas. This is checked in the code, and if you encounter difficulties, you can consult the documentation for further assistance.
    • You must have at least one variable that you are interested in analyzing (e.g., price, sales, etc.).

    The placeholder dataset in this template is Google stock price data, containing the closing price on each trading day.

    # Load packages
    import pandas as pd
    from pandas.api.types import is_datetime64_any_dtype as is_datetime

    Setting Up Your Data

    Before you begin, you will want to set up your data correctly. The read_csv() function from pandas offers several arguments that make working with time series easier from the start:

    • index_col will allow you to immediately set the date as the index, allowing easier manipulations afterward.
    • parse_dates instructs pandas to parse the index as a date if possible.

    You can then slice the DataFrame to select the dates/times you are most interested in using. You can read more about time series and date functionality within pandas here. The string you use to slice the DataFrame can be a partial match or a full match.

    # Replace this with the name of the column that contains your date information
    date_col = "date"
    # Replace with the file you want to use and load your dataset into a DataFrame
    df = pd.read_csv("google.csv", index_col=date_col, parse_dates=True)
    # Check that the index is correctly converted to a date
    print("The index been parsed as a date: " + str(is_datetime(df.index)))

    If the code above returns False, then you will need to use pandas' to_datetime() function to correctly set the index to a date.

    Next, you can specify the variable of interest and choose a date range.

    # Specify the variable of interest
    var_col = "close"  # Replace this with the name of the column you want to analyze
    # Reduce the DataFrame down to the relevant columns
    df_subset = df[[var_col]]  # Pass in the column(s) of interest as a list here
    # Select the date range you want to explore
    df_time = df_subset[
        "2020-1-1":"2021-1-1"  # Pass in the date ranges you are interested in here
    # Reset the index
    # Preview the DataFrame

    Shifting Data

    The .shift() method allows you to shift data by a given number of periods. A negative number will produce a lag backward in time, and a positive number will produce a lead forward in time.

    Below, we use Workspace's Visualize feature (available in the DataFrame output) to plot the resulting DataFrame. To create the plot, we select "Line" as the type, assign "date" and "price" to the x and y axis respectively, and group by the "period". To add a title to the plot, simply click in the space where the title should appear and add your own!

    # Use this line to specify the number of periods to shift
    shift_periods = 60
    # Create a column shifted backward by the number of periods specified above (lag)
    df_time["lag_data"] = df_time[var_col].shift(-shift_periods)
    # Create a column shifted forward by the number of periods specified above (lead)
    df_time["lead_data"] = df_time[var_col].shift(shift_periods)
    # Melt the DataFrame in preparation for visualization and drop null values
    df_shift = df_time.melt(id_vars="date", var_name="period", value_name="price").dropna()
    # Inspect the DataFrame
    Current Type: Line
    Current X-axis: date
    Current Y-axis: price
    Current Color: period

    Original Data with 60 Period Lag and Lead

    Percent Change

    The .pct_change() method allows you to calculate the percentage change between the current row and another previous row. There are two things to note about the code shown below.

    • The periods parameter specifies which row to use when calculating the percentage change. It defaults to 1, which means it uses the immediately previous row. Here, 30 periods are used.
    • By default, .pct_change() returns a decimal. .mul(100) multiplies the percentage by 100 for easier reading.

    Note: We again use the Workspace "Visualize" feature in the output of the DataFrame to easily create a line plot of our data.

    # Use this line to specify the rate of change you want to calculate
    pct_return_periods = 30
    # Create a column with the percentage increase and multiply by 100
    df_time["percent_change"] = (
    # Select relevant columns and drop null values
    df_pct = df_time[["date", "percent_change"]].dropna()
    # Preview the DataFrame