shift() Method: Shift Values in Column Up or Down¶
Date published: 2019-04-21
Category: Data Analysis
Subcategory: Data Wrangling
Tags: shift method, pandas, python
The shift() method for a pandas series helps shift values in a column up or down. This is similar to using the SQL window functions for LAG() and LEAD(). You can learn about these SQL window functions via Mode's SQL tutorial.
In this tutorial, I'll walk through an example of using the shift() pandas series method for analyzing bike rides.
Setup Code¶
Import Module¶
import pandas as pd
import datetime
import random
import numpy as np
Example 1: Duration Idle Time Between Bike Rides¶
In this example, I assume a service in which I lend out a single bike for people to ride throughout the day in San Francisco. Each record in the dataset is the start and end time for a ride.
Create Bike Rides Data¶
The code below creates a list of start and end times of rides.
random.seed(38)
ride_start_time = datetime.datetime.today()
ride_end_time = ride_start_time + datetime.timedelta(minutes=20)
ride_start_times = []
ride_end_times = []
count_of_rides = 6
for ride in range(count_of_rides):
minutes_between_rides = random.randint(8, 100)
ride_start_time = ride_end_time + datetime.timedelta(minutes=minutes_between_rides)
minutes_riding = random.randint(5, 25)
ride_end_time = ride_start_time + datetime.timedelta(minutes=minutes_riding)
ride_start_times.append(ride_start_time)
ride_end_times.append(ride_end_time)
Create a pandas dataframe given the lists ride_start_times and ride_end_times.
data = {'ride_start_time': ride_start_times,
'ride_end_time': ride_end_times
}
df_bike_rides = pd.DataFrame(data)
View the newly created dataframe df_bike_rides.
df_bike_rides
| ride_start_time | ride_end_time | |
|---|---|---|
| 0 | 2019-04-21 21:23:29.711347 | 2019-04-21 21:41:29.711347 |
| 1 | 2019-04-21 22:43:29.711347 | 2019-04-21 22:51:29.711347 |
| 2 | 2019-04-21 23:07:29.711347 | 2019-04-21 23:23:29.711347 |
| 3 | 2019-04-22 01:00:29.711347 | 2019-04-22 01:19:29.711347 |
| 4 | 2019-04-22 02:14:29.711347 | 2019-04-22 02:20:29.711347 |
| 5 | 2019-04-22 03:45:29.711347 | 2019-04-22 03:55:29.711347 |
Find the Mean Duration of Time, in Minutes, Bike is Left Idle Between Rides¶
For example, the first ride ended around 4:24PM and was next used for a ride at 5:52PM. I want to calculate a new column that states there was approximately 86 minutes, equivalent to approximately 5160 seconds, of idle time between these rides.
I want to do a column-by-column comparison. I use the shift() method to create a new column in df_bike_rides that's a shift of value in ride_end_time down one period.
df_bike_rides['previous_ride_end_time'] = df_bike_rides['ride_end_time'].shift(periods=1)
View df_bike_rides.
df_bike_rides
| ride_start_time | ride_end_time | previous_ride_end_time | |
|---|---|---|---|
| 0 | 2019-04-21 21:23:29.711347 | 2019-04-21 21:41:29.711347 | NaT |
| 1 | 2019-04-21 22:43:29.711347 | 2019-04-21 22:51:29.711347 | 2019-04-21 21:41:29.711347 |
| 2 | 2019-04-21 23:07:29.711347 | 2019-04-21 23:23:29.711347 | 2019-04-21 22:51:29.711347 |
| 3 | 2019-04-22 01:00:29.711347 | 2019-04-22 01:19:29.711347 | 2019-04-21 23:23:29.711347 |
| 4 | 2019-04-22 02:14:29.711347 | 2019-04-22 02:20:29.711347 | 2019-04-22 01:19:29.711347 |
| 5 | 2019-04-22 03:45:29.711347 | 2019-04-22 03:55:29.711347 | 2019-04-22 02:20:29.711347 |
The shift() method for a pandas series is similar to a window function in SQL using LAG() and LEAD(). The same operation above would look like the following in SQL:
SELECT *, LAG(ride_end_time) OVER (ORDER BY ride_start_time ASC) AS previous_ride_end_time
FROM df_bike_rides
ORDER BY ride_start_time ASC
Given this new column for previous_ride_end_time, I can subtract the time between a new bike ride's start time and the previous ride's end time. The result is the duration the bike was idle between rides.
df_bike_rides['duration_bike_idle_between_rides'] = df_bike_rides['ride_start_time'] - df_bike_rides['previous_ride_end_time']
View df_bike_rides.
df_bike_rides
| ride_start_time | ride_end_time | previous_ride_end_time | duration_bike_idle_between_rides | |
|---|---|---|---|---|
| 0 | 2019-04-21 21:23:29.711347 | 2019-04-21 21:41:29.711347 | NaT | NaT |
| 1 | 2019-04-21 22:43:29.711347 | 2019-04-21 22:51:29.711347 | 2019-04-21 21:41:29.711347 | 01:02:00 |
| 2 | 2019-04-21 23:07:29.711347 | 2019-04-21 23:23:29.711347 | 2019-04-21 22:51:29.711347 | 00:16:00 |
| 3 | 2019-04-22 01:00:29.711347 | 2019-04-22 01:19:29.711347 | 2019-04-21 23:23:29.711347 | 01:37:00 |
| 4 | 2019-04-22 02:14:29.711347 | 2019-04-22 02:20:29.711347 | 2019-04-22 01:19:29.711347 | 00:55:00 |
| 5 | 2019-04-22 03:45:29.711347 | 2019-04-22 03:55:29.711347 | 2019-04-22 02:20:29.711347 | 01:25:00 |
The new column duration_bike_idle_between_rides shows the duration of idle bike time between rides in the format HH-MM-SS. The value of 01:02:00 is equivalent to saying 1 hour and 2 minutes. Below, I convert that timedelta format into a single numerical value of minutes. I utilize the dt accessor and total_seconds() method to calculate the total seconds a bike is idle between rides. Then I divide this value by 60 to get a value in minutes.
df_bike_rides['minutes_bike_idle_between_rides'] = df_bike_rides['duration_bike_idle_between_rides'].dt.total_seconds()/60
View df_bike_rides below with a new column for the minutes_bike_idle_between_rides.
df_bike_rides
| ride_start_time | ride_end_time | previous_ride_end_time | duration_bike_idle_between_rides | minutes_bike_idle_between_rides | |
|---|---|---|---|---|---|
| 0 | 2019-04-21 21:23:29.711347 | 2019-04-21 21:41:29.711347 | NaT | NaT | NaN |
| 1 | 2019-04-21 22:43:29.711347 | 2019-04-21 22:51:29.711347 | 2019-04-21 21:41:29.711347 | 01:02:00 | 62.0 |
| 2 | 2019-04-21 23:07:29.711347 | 2019-04-21 23:23:29.711347 | 2019-04-21 22:51:29.711347 | 00:16:00 | 16.0 |
| 3 | 2019-04-22 01:00:29.711347 | 2019-04-22 01:19:29.711347 | 2019-04-21 23:23:29.711347 | 01:37:00 | 97.0 |
| 4 | 2019-04-22 02:14:29.711347 | 2019-04-22 02:20:29.711347 | 2019-04-22 01:19:29.711347 | 00:55:00 | 55.0 |
| 5 | 2019-04-22 03:45:29.711347 | 2019-04-22 03:55:29.711347 | 2019-04-22 02:20:29.711347 | 01:25:00 | 85.0 |
I calculate the mean minutes_bike_idle_between_rides value as 63 minutes.
avg_minutes_bikes_idle_between_rides = df_bike_rides['minutes_bike_idle_between_rides'].mean()
avg_minutes_bikes_idle_between_rides
63.0
Example 2: Duration Idle Time Between Bike Rides Per Unique Bike¶
This example below is similar to the one above. However, I assume I now operate a fleet of 2 bikes and rent them out for people to ride to specific stations in the city of San Francisco.
Below I create a pandas dataframe with details on bike ride times, the bike id and the start and end station.
data = {'ride_start_time': ride_start_times,
'ride_end_time': ride_end_times,
'bike_id': [1, 22, 1, 1, 22, 22],
'start_station': ["21st & Folsom", "21st & Folsom", "4th & King", "24th & Valencia", "4th & King", "16th and Mission"],
'end_station': ["4th & King", "4th & King", "24th & Valencia", "Embarcadero & Market", "16th and Mission", "4th & King"]
}
df_bike_sharing = pd.DataFrame(data)
View df_bike_sharing.
df_bike_sharing
| ride_start_time | ride_end_time | bike_id | start_station | end_station | |
|---|---|---|---|---|---|
| 0 | 2019-04-21 21:23:29.711347 | 2019-04-21 21:41:29.711347 | 1 | 21st & Folsom | 4th & King |
| 1 | 2019-04-21 22:43:29.711347 | 2019-04-21 22:51:29.711347 | 22 | 21st & Folsom | 4th & King |
| 2 | 2019-04-21 23:07:29.711347 | 2019-04-21 23:23:29.711347 | 1 | 4th & King | 24th & Valencia |
| 3 | 2019-04-22 01:00:29.711347 | 2019-04-22 01:19:29.711347 | 1 | 24th & Valencia | Embarcadero & Market |
| 4 | 2019-04-22 02:14:29.711347 | 2019-04-22 02:20:29.711347 | 22 | 4th & King | 16th and Mission |
| 5 | 2019-04-22 03:45:29.711347 | 2019-04-22 03:55:29.711347 | 22 | 16th and Mission | 4th & King |
I sort df_bike_sharing first by the bike_id column and then the ride_start_time column.
df_bike_sharing.sort_values(by=['bike_id', 'ride_start_time'], inplace=True)
df_bike_sharing
| ride_start_time | ride_end_time | bike_id | start_station | end_station | |
|---|---|---|---|---|---|
| 0 | 2019-04-21 21:23:29.711347 | 2019-04-21 21:41:29.711347 | 1 | 21st & Folsom | 4th & King |
| 2 | 2019-04-21 23:07:29.711347 | 2019-04-21 23:23:29.711347 | 1 | 4th & King | 24th & Valencia |
| 3 | 2019-04-22 01:00:29.711347 | 2019-04-22 01:19:29.711347 | 1 | 24th & Valencia | Embarcadero & Market |
| 1 | 2019-04-21 22:43:29.711347 | 2019-04-21 22:51:29.711347 | 22 | 21st & Folsom | 4th & King |
| 4 | 2019-04-22 02:14:29.711347 | 2019-04-22 02:20:29.711347 | 22 | 4th & King | 16th and Mission |
| 5 | 2019-04-22 03:45:29.711347 | 2019-04-22 03:55:29.711347 | 22 | 16th and Mission | 4th & King |
For the bike_id column, I shift down values by 1 to create a new column called previous_bike_id. I do this so I can easily compare a bike id to the previous ride's ID to identify the last ride by a bike id for a day.
Similar to LAG()
df_bike_sharing['previous_bike_id'] = df_bike_sharing['bike_id'].shift(periods=1)
df_bike_sharing
| ride_start_time | ride_end_time | bike_id | start_station | end_station | previous_bike_id | |
|---|---|---|---|---|---|---|
| 0 | 2019-04-21 21:23:29.711347 | 2019-04-21 21:41:29.711347 | 1 | 21st & Folsom | 4th & King | NaN |
| 2 | 2019-04-21 23:07:29.711347 | 2019-04-21 23:23:29.711347 | 1 | 4th & King | 24th & Valencia | 1.0 |
| 3 | 2019-04-22 01:00:29.711347 | 2019-04-22 01:19:29.711347 | 1 | 24th & Valencia | Embarcadero & Market | 1.0 |
| 1 | 2019-04-21 22:43:29.711347 | 2019-04-21 22:51:29.711347 | 22 | 21st & Folsom | 4th & King | 1.0 |
| 4 | 2019-04-22 02:14:29.711347 | 2019-04-22 02:20:29.711347 | 22 | 4th & King | 16th and Mission | 22.0 |
| 5 | 2019-04-22 03:45:29.711347 | 2019-04-22 03:55:29.711347 | 22 | 16th and Mission | 4th & King | 22.0 |
In SQL, this operation would be:
SELECT *, LAG(bike_id) OVER (ORDER BY bike_id ASC, ride_start_time ASC) AS previous_bike_id
FROM df_bike_sharing
ORDER BY bike_id ASC, ride_start_time ASC
For the ride_end_time column, I shift down values by 1 to create a new column called previous_ride_end_time. I do this so I can later find the idle bike time between rides.
df_bike_sharing['previous_ride_end_time'] = df_bike_sharing['ride_end_time'].shift(periods=1)
df_bike_sharing
| ride_start_time | ride_end_time | bike_id | start_station | end_station | previous_bike_id | previous_ride_end_time | |
|---|---|---|---|---|---|---|---|
| 0 | 2019-04-21 21:23:29.711347 | 2019-04-21 21:41:29.711347 | 1 | 21st & Folsom | 4th & King | NaN | NaT |
| 2 | 2019-04-21 23:07:29.711347 | 2019-04-21 23:23:29.711347 | 1 | 4th & King | 24th & Valencia | 1.0 | 2019-04-21 21:41:29.711347 |
| 3 | 2019-04-22 01:00:29.711347 | 2019-04-22 01:19:29.711347 | 1 | 24th & Valencia | Embarcadero & Market | 1.0 | 2019-04-21 23:23:29.711347 |
| 1 | 2019-04-21 22:43:29.711347 | 2019-04-21 22:51:29.711347 | 22 | 21st & Folsom | 4th & King | 1.0 | 2019-04-22 01:19:29.711347 |
| 4 | 2019-04-22 02:14:29.711347 | 2019-04-22 02:20:29.711347 | 22 | 4th & King | 16th and Mission | 22.0 | 2019-04-21 22:51:29.711347 |
| 5 | 2019-04-22 03:45:29.711347 | 2019-04-22 03:55:29.711347 | 22 | 16th and Mission | 4th & King | 22.0 | 2019-04-22 02:20:29.711347 |
We only want to calculate a duration the bike was idle in a row if it's a comparison for the same bike id. Below I show the code to replace all values in a row with NaN or NaT if the condition is false.
df_bike_sharing.where(df_bike_sharing['bike_id'] == df_bike_sharing['previous_bike_id'])
| ride_start_time | ride_end_time | bike_id | start_station | end_station | previous_bike_id | previous_ride_end_time | |
|---|---|---|---|---|---|---|---|
| 0 | NaT | NaT | NaN | NaN | NaN | NaN | NaT |
| 2 | 2019-04-21 23:07:29.711347 | 2019-04-21 23:23:29.711347 | 1.0 | 4th & King | 24th & Valencia | 1.0 | 2019-04-21 21:41:29.711347 |
| 3 | 2019-04-22 01:00:29.711347 | 2019-04-22 01:19:29.711347 | 1.0 | 24th & Valencia | Embarcadero & Market | 1.0 | 2019-04-21 23:23:29.711347 |
| 1 | NaT | NaT | NaN | NaN | NaN | NaN | NaT |
| 4 | 2019-04-22 02:14:29.711347 | 2019-04-22 02:20:29.711347 | 22.0 | 4th & King | 16th and Mission | 22.0 | 2019-04-21 22:51:29.711347 |
| 5 | 2019-04-22 03:45:29.711347 | 2019-04-22 03:55:29.711347 | 22.0 | 16th and Mission | 4th & King | 22.0 | 2019-04-22 02:20:29.711347 |
With the above change, I calculate ride_start_time minus previous_ride_end_time where the above condition holds True. With that calculation, I create a new column called duration_bike_idle.
df_bike_sharing['duration_bike_idle'] = (df_bike_sharing['ride_start_time']-df_bike_sharing['previous_ride_end_time']).where(df_bike_sharing['bike_id'] == df_bike_sharing['previous_bike_id'])
df_bike_sharing
| ride_start_time | ride_end_time | bike_id | start_station | end_station | duration_bike_idle | |
|---|---|---|---|---|---|---|
| 0 | 2019-04-21 21:23:29.711347 | 2019-04-21 21:41:29.711347 | 1 | 21st & Folsom | 4th & King | NaT |
| 2 | 2019-04-21 23:07:29.711347 | 2019-04-21 23:23:29.711347 | 1 | 4th & King | 24th & Valencia | 01:26:00 |
| 3 | 2019-04-22 01:00:29.711347 | 2019-04-22 01:19:29.711347 | 1 | 24th & Valencia | Embarcadero & Market | 01:37:00 |
| 1 | 2019-04-21 22:43:29.711347 | 2019-04-21 22:51:29.711347 | 22 | 21st & Folsom | 4th & King | NaT |
| 4 | 2019-04-22 02:14:29.711347 | 2019-04-22 02:20:29.711347 | 22 | 4th & King | 16th and Mission | 03:23:00 |
| 5 | 2019-04-22 03:45:29.711347 | 2019-04-22 03:55:29.711347 | 22 | 16th and Mission | 4th & King | 01:25:00 |
I drop previous_bike_id and previous_ride_end_time since they were intermediary outputs used for calculations. They're not necessary for a final presentation of the critical details.
df_bike_sharing.drop(['previous_bike_id', 'previous_ride_end_time'], axis=1, inplace=True)
The new column duration_bike_idle_seconds shows the duration of idle bike time between rides in the format HH-MM-SS. The value of 01:26:00 is equivalent to saying 1 hour and 26 minutes. Below, I convert that timedelta format into a single numerical value of minutes. I utilize the dt accessor and total_seconds() method to calculate the total seconds a bike is idle between rides of the same bike id. Then I divide this value by 60 to get a value in minutes.
df_bike_sharing['duration_bike_idle_seconds'] = df_bike_sharing['duration_bike_idle'].dt.total_seconds()
df_bike_sharing
| ride_start_time | ride_end_time | bike_id | start_station | end_station | duration_bike_idle | duration_bike_idle_seconds | |
|---|---|---|---|---|---|---|---|
| 0 | 2019-04-21 21:23:29.711347 | 2019-04-21 21:41:29.711347 | 1 | 21st & Folsom | 4th & King | NaT | NaN |
| 2 | 2019-04-21 23:07:29.711347 | 2019-04-21 23:23:29.711347 | 1 | 4th & King | 24th & Valencia | 01:26:00 | 5160.0 |
| 3 | 2019-04-22 01:00:29.711347 | 2019-04-22 01:19:29.711347 | 1 | 24th & Valencia | Embarcadero & Market | 01:37:00 | 5820.0 |
| 1 | 2019-04-21 22:43:29.711347 | 2019-04-21 22:51:29.711347 | 22 | 21st & Folsom | 4th & King | NaT | NaN |
| 4 | 2019-04-22 02:14:29.711347 | 2019-04-22 02:20:29.711347 | 22 | 4th & King | 16th and Mission | 03:23:00 | 12180.0 |
| 5 | 2019-04-22 03:45:29.711347 | 2019-04-22 03:55:29.711347 | 22 | 16th and Mission | 4th & King | 01:25:00 | 5100.0 |
Here is the average seconds each bike_id is idle during the day in seconds between the first and last ride. I group by the bike_id column and calculate the mean of the duration_bike_idle_seconds_time column. I reset the index and rename the columns so this final output is easier to understand.
df_bike_sharing.groupby('bike_id')['duration_bike_idle_seconds'].mean().reset_index().rename(columns={'duration_bike_idle_seconds': 'avg_seconds_idle_between_rides'})
| bike_id | avg_seconds_idle_between_rides | |
|---|---|---|
| 0 | 1 | 5490.0 |
| 1 | 22 | 8640.0 |
Bike id of 22 was left idle longer between rides than the bike id of 1.